MySQL Bugs: #25478: transaction structure lost in replication

Bug #25478	transaction structure lost in replication
Submitted:	8 Jan 2007 21:04	Modified:	12 Mar 2007 15:55
Reporter:	Hal Finkel	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.14-0.glibc23	OS:	Linux (Fedora Core 4)
Assigned to:		CPU Architecture:	Any
Tags:	binary log, innodb, replication, transaction

Description:
I have a chained setup (multi-master), A (server id 10) -> B (server id 20) -> C (server id 30) -> A. I had been inserting data on server A and that data was replicated to server B and from server B to server C. The write performance on server C was 10 to 100 times slower than that on server's A and B (server B has an identical hardware/software setup to that of server C). SHOW INNODB STATUS showed that the number of fsync's/sec was much higher on server C than on either A or B. Also, while executing the replicated statements, the number of locks held on server C was near 1 while servers A or B would hold thousands of locks while executing the replicated statements.

The binary log on server A shows a BEGIN statement followed by a number of inserts, and then a COMMIT. The relay log on server B has the same and the binary log on server B has the same. On the server C, the binary log also seems the same as those of server B, but its relay log shows a number of inserts each followed by a COMMIT (I belive the cause of the performance problems is that the server is executing the statements in this fashion).

How to repeat:
On server A:
create table test1 (k integer not null auto_increment, t varchar(64), primary key (k)) ENGINE=InnoDB;

BEGIN;
insert into test1 (t) values ('a');
insert into test1 (t) values ('b');
insert into test1 (t) values ('c');
insert into test1 (t) values ('d');
COMMIT;

I'll attach samples of the end of the binary logs (unparsed by mysqlbinlog).

The relay log on server C

Attachment: server_c_relay.log.txt (text/plain), 1.71 KiB.

Binary log from server C (notice how the transaction structure has changed from the relay log - unparsed by mysqlbinlog)

Attachment: server_c.log.txt (text/plain), 1.63 KiB.

Thank you for a problem report. Please, send my.cnf files content from servers A, B, C. Just for completeness.

my.cnf file from server a

Attachment: server_a_my.cnf (application/octet-stream, text), 676 bytes.

my.cnf file from server B

Attachment: server_b_my.cnf (application/octet-stream, text), 676 bytes.

my.cnf file from server C

Attachment: server_c_my.cnf (application/octet-stream, text), 676 bytes.

I've uploaded the my.cnf files from servers A, B and C. I removed the actual IPs, host names, database names, usernames and passwords.

Sorry for a delay with this bug report. Please, try to repeat with a newer version, 5.1.15, on all the servers, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".