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

[8 Jan 2007 21:04] Hal Finkel
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).
[8 Jan 2007 21:09] Hal Finkel
The relay log on server C

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

[8 Jan 2007 21:10] Hal Finkel
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.

[10 Jan 2007 15:40] Valeriy Kravchuk
Thank you for a problem report. Please, send my.cnf files content from servers A, B, C. Just for completeness.
[11 Jan 2007 6:40] Hal Finkel
my.cnf file from server a

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

[11 Jan 2007 6:41] Hal Finkel
my.cnf file from server B

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

[11 Jan 2007 6:42] Hal Finkel
my.cnf file from server C

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

[11 Jan 2007 6:46] Hal Finkel
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.
[12 Feb 2007 15:55] Valeriy Kravchuk
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.
[13 Mar 2007 0:00] Bugs System
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".