[Maria-developers] first results from replication benchmarks
Hi everybody, here are some first results from my benchmarks of replication / group commit performance. The benchmark is sysbench OLTP rw, data set fitting in memory. I tested a total of 27 configurations, 9 each for Maria-5.5.36, Maria-10.0.9 and MySQL-5.6.16. Variations were for binlog on/off, SBR vs. RBR and sync_binlog=0/1. For MariaDB I also tested XtraDB vs. InnoDB, for MySQL it was GTID on vs. GTID off. Not surprisingly turning on the binlog comes with a performance penalty. Also sync_binlog=1 adds another penalty. Quite consistently RBR was a little bit slower than SBR for all combinations (I also observed the binlogs to be bigger for RBR by a factor of nearly 2). The only real surprise was the cost of GTID in MySQL as can be seen in the attached plot. MariaDB-10.0 has GTID enabled by default (you cannot even turn it off) Right now the same benchmark is running again, this time using a single disk for binlogs (was a fast RAID before). Next step is applying binlogs to a slave with different settings for number of slave threads etc. XL
Hi!
"Axel" == Axel Schwenke <axel@askmonty.org> writes:
Axel> Hi everybody, Axel> here are some first results from my benchmarks of replication / group commit Axel> performance. Axel> The benchmark is sysbench OLTP rw, data set fitting in memory. I tested a Axel> total of 27 configurations, 9 each for Maria-5.5.36, Maria-10.0.9 and Axel> MySQL-5.6.16. Variations were for binlog on/off, SBR vs. RBR and Axel> sync_binlog=0/1. For MariaDB I also tested XtraDB vs. InnoDB, for MySQL it Axel> was GTID on vs. GTID off. <cut> Axel> Next step is applying binlogs to a slave with different settings for number Axel> of slave threads etc. So the current benchmark was when using one slave thread? When trying parallel replication, remember to test both when doing updates in one databases and in many databases. In this case MySQL 5.6 should only scale in the second case. One reason GTID could be slow on MySQL 5.6 is that it will automaticly enable the binary log on the slave. Do you think this is the case? Regards, Monty
Michael Widenius wrote:
Axel> The benchmark is sysbench OLTP rw, data set fitting in memory. I tested a Axel> total of 27 configurations, 9 each for Maria-5.5.36, Maria-10.0.9 and Axel> MySQL-5.6.16. Variations were for binlog on/off, SBR vs. RBR and Axel> sync_binlog=0/1. For MariaDB I also tested XtraDB vs. InnoDB, for MySQL it Axel> was GTID on vs. GTID off.
<cut>
Axel> Next step is applying binlogs to a slave with different settings for number Axel> of slave threads etc.
So the current benchmark was when using one slave thread?
This was without any slave. Just running sysbench vs. the master, to make it write a binlog.
When trying parallel replication, remember to test both when doing updates in one databases and in many databases.
I have modified sysbench to use multiple databases (configurable). For this test I used 32 databases with 4 tables each.
One reason GTID could be slow on MySQL 5.6 is that it will automaticly enable the binary log on the slave. Do you think this is the case?
Unfortunately this result was *wrong* :( Somehow the machine was configured to use the "ondemand" cpufreq scheduler. Not sure if I was that myself or if somebody else was on the machine. This is also the reason for the unnormal degradation at lower concurrencies. I have now rerun the benchmark and now there is only a very small penalty for enabling GTID. I attach the plot again and now also the spreadsheet. XL
participants (2)
-
Axel Schwenke -
Michael Widenius