When there is 100ms between master and semi-sync slave:

[root@perconaserver vagrant]# sysbench –db-ps-mode=disable –mysql-host=localhost –mysql-user=root –mysql-db=semi –num-threads=32 –report-interval=1 /usr/share/sysbench/oltp_insert.lua run
[ 1s ] thds: 32 tps: 256.22 qps: 256.22 (r/w/o: 0.00/256.22/0.00) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 32 tps: 286.30 qps: 286.30 (r/w/o: 0.00/286.30/0.00) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 32 tps: 289.18 qps: 289.18 (r/w/o: 0.00/289.18/0.00) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 32 tps: 290.83 qps: 290.83 (r/w/o: 0.00/290.83/0.00) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 32 tps: 319.08 qps: 319.08 (r/w/o: 0.00/319.08/0.00) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 32 tps: 286.84 qps: 286.84 (r/w/o: 0.00/286.84/0.00) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 32 tps: 289.76 qps: 289.76 (r/w/o: 0.00/289.76/0.00) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 32 tps: 289.30 qps: 289.30 (r/w/o: 0.00/289.30/0.00) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00

Thanks

Any updated figures for newer MySQL (e.g. 5.7) please?
Oracle claimed that 5.7 largely improved replication.

Also, does the new “Codership’s recommended tuning” make any differences please?

Thanks

The link for label “Codership’s recommended tuning” is outdated. It should be changed to http://galeracluster.com/documentation-webpages/configurationtips.html.

Sometimes people miss the “game changers” which change performance picture completely. WAN vs LAN is one of such as round trip can be 100+ times longer. Other one is HDD without Raid with BBU compared to one with, the difference would be some 200 fsync/sec vs 10.000+ again close to 100x difference. Similar numbers apply to SSD vs HDD

The point is if you’re testing on LAN I would not make any assumptions about WAN performance because it is likely to be a lot different.

Interesting to see though what Group Commit essentially does not work with Semi-Sync in 5.5 I wounder if it is going to be resolved in 5.6 as it can be a serious bottleneck.

Justin: 5.5.24, yes 🙂 Risk in my my opinion is based on the likelihood of all nodes going offline at once.

Andy: sync_binlog was off in all tests. innodb_support_xa was ON in the tests where I was using log-bin.

Seems like it is true then.

For the control what settings did you use for sync_binlog and innodb_support_xa?

Justin: There are no risk other than what you already know: If a node crashes, some transactions may not have been written to disk. However, when the node comes back up, it will do whatever is necessary to sync up with the other nodes anyway, including the option of wiping out its own data and taking a full copy from another node. Or to put it another way, once a crashed node comes back up, it is out of sync anyway, so it doesn’t matter at which point the data on disk has gotten out of sync.

So relaxing InnoDB flushing is essentially based on the idea that a commit is durable due to the replication, not due to flushing to disk. (MySQL NDB Cluster has the same approach.)

Are there risks of innodb_flush_log_at_trx_commit=2 on XtraDB cluster? Can data get out of sync?

About 2ms latency in the first test: in us-east-1 zone ping RTT seems to be ~0.5 ms, so it is really hard to improve on that 2ms result when running PXC in EC2. You probably can’t go below 1ms.

Regards,
Alex