tag:blogger.com,1999:blog-16959946.post3254880046070851307..comments2023-12-09T16:44:47.897+01:00Comments on The Data Charmer: The price of safe data - Benchmarking semi synchronous replicationGiuseppe Maxiahttp://www.blogger.com/profile/15801583338057324813noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-16959946.post-90334864597679920382011-10-13T10:14:37.093+02:002011-10-13T10:14:37.093+02:00Hi,
I have done a similar test with mysqlslap. The...Hi,<br />I have done a similar test with mysqlslap. The test was done with MyISAM, InnoDB and with and without semi-synchronous replication.<br />I used MySQL 5.5.16 on a small server with 1GB RAM, dual core, RAID-1 72GB disk (Dell M610)<br />Both master and slave have the same hardware and setup.<br /><br />Without semi-synchronous replication:<br />mysqlslap --concurrency=10 --iterations=100 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --engine=MyISAM<br />Benchmark<br /> Running for engine MyISAM<br /> Average number of seconds to run all queries: 3.191 seconds<br /> Minimum number of seconds to run all queries: 2.958 seconds<br /> Maximum number of seconds to run all queries: 3.676 seconds<br /> Number of clients running queries: 10<br /> Average number of queries per client: 0<br /><br />mysqlslap --concurrency=10 --iterations=100 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --engine=InnoDB<br />Benchmark<br /> Running for engine InnoDB<br /> Average number of seconds to run all queries: 3.994 seconds<br /> Minimum number of seconds to run all queries: 3.540 seconds<br /> Maximum number of seconds to run all queries: 4.577 seconds<br /> Number of clients running queries: 10<br /> Average number of queries per client: 0<br /><br />With semi-synchronous replication:<br />mysqlslap --concurrency=10 --iterations=100 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --engine=MyISAM<br />Benchmark<br /> Running for engine MyISAM<br /> Average number of seconds to run all queries: 3.234 seconds<br /> Minimum number of seconds to run all queries: 2.998 seconds<br /> Maximum number of seconds to run all queries: 3.885 seconds<br /> Number of clients running queries: 10<br /> Average number of queries per client: 0<br /><br />mysqlslap --concurrency=10 --iterations=100 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --engine=InnoDB<br />Benchmark<br /> Running for engine InnoDB<br /> Average number of seconds to run all queries: 4.051 seconds<br /> Minimum number of seconds to run all queries: 3.545 seconds<br /> Maximum number of seconds to run all queries: 5.690 seconds<br /> Number of clients running queries: 10<br /> Average number of queries per client: 0Petervdbhttps://www.blogger.com/profile/04175815942629847745noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-85008533437688099082011-06-19T17:54:55.364+02:002011-06-19T17:54:55.364+02:00"I don't see anything wrong. I was not ex..."I don't see anything wrong. I was not expecting the impact on performance to be so high. That's all."<br /><br />By my math the overhead is 200 microseconds per commit. Your workload is way too simple and thus a 200 microsecond overhead makes this 3X slower. Silly benchmarks lead to silly results.Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-87388436266451722952011-05-19T19:57:30.184+02:002011-05-19T19:57:30.184+02:00Great write up - you just saved me a load test!Great write up - you just saved me a load test!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-16959946.post-21353128558680679712011-05-19T09:53:33.526+02:002011-05-19T09:53:33.526+02:00I think some people will use it like this:
They h...I think some people will use it like this:<br /><br />They have a async replication with both master and slave configured with sync_binlog=1 and innodb_flush_log_at_trx_commit=1 and other durability settings.<br /><br />After switching to semi-sync they will relax the durability settings as it's more-or-less guaranteed that the data will also be on one of the slaves.<br /><br />In that case the performance penaly might be acceptable for some.<br /><br />I would like to see it benchmarked againt other semi-sync and sync replication setups for other databases like PostgreSQL and Oracle with DataGuard.Daniël van Eedenhttps://www.blogger.com/profile/14757324605223498151noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-3416751499022242622011-05-18T23:38:05.559+02:002011-05-18T23:38:05.559+02:00Giuseppe,
the cost here is a cost of the round tr...Giuseppe,<br /><br />the cost here is a cost of the round trip of a packets between nodes. BTW, did you monitor a network activity during both tests?.. - I'm curious if it'll be the same rates in packets/sec and KB/sec. (As well it's very possible that NO DELAY option is not used on the sockets and short delays are added on each packet - so some network tuning should be also involved).<br /><br />Rgds,<br />-DimitriDimitrihttp://dimitrik.free.fr/blognoreply@blogger.comtag:blogger.com,1999:blog-16959946.post-76341605201281598742011-05-18T22:21:06.221+02:002011-05-18T22:21:06.221+02:00@hingo,
I was using sync_binlog=0, yes.@hingo,<br />I was using sync_binlog=0, yes.Giuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-50052083961712357662011-05-18T22:03:03.936+02:002011-05-18T22:03:03.936+02:00Giuseppe, we seem to often be engaged with the sam...Giuseppe, we seem to often be engaged with the same thoughts without knowing it!<br /><br />Did you notice my tests in this area http://openlife.cc/blogs/2011/may/drbd-and-semi-sync-shootout-large-server<br /><br />In my tests: Having innodb_flush_log_at_trx_commit=1 and sync_binlog=1 using semi sync replication adds negligible overhead. What's better, with semi-sync replication I can safely set sync_binlog=0 (it's already replicated, no need to sync it locally) and get hugely improved performance over a single node system.<br /><br />I assume you did not sync after each commit, did you?hingohttps://www.blogger.com/profile/09201666166374161923noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-70971147392645507242011-05-18T10:33:07.476+02:002011-05-18T10:33:07.476+02:00@Dimitri,
I don't see anything wrong. I was no...@Dimitri,<br />I don't see anything wrong. I was not expecting the impact on performance to be so high. That's all.<br /><br />I am trying to figure out if such impact is acceptable or not.<br /><br />P.S. My name is <a href="http://datacharmer.blogspot.com/2006/09/whats-in-name.html" rel="nofollow">Giuseppe</a>Giuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-33277237702430053972011-05-18T10:25:57.720+02:002011-05-18T10:25:57.720+02:00Guiseppe,
with all my respect, but what do you se...Guiseppe,<br /><br />with all my respect, but what do you see wrong here?.. - the case you're comparing is similar to compare an I/O WRITE test, like:<br /><br />write();<br />write();<br />...<br />write():<br /><br />vs<br /><br />write(); fsync();<br />write(); fsync();<br />...<br />write(); fsync();<br /><br />for sure in case you're waiting for every write sync you'll be slower comparing to writing to the filesystem buffer..<br /><br />same for semi-sync replication, and sure there is a cost.<br /><br />Rgds,<br />-DimitriDimitrihttp://dimitrik.free.fr/blognoreply@blogger.comtag:blogger.com,1999:blog-16959946.post-74129783838761840572011-05-18T08:07:15.459+02:002011-05-18T08:07:15.459+02:00@mysqlscott,
it's a 1GB/s connection.
But I ha...@mysqlscott,<br />it's a 1GB/s connection.<br />But I have also a 100mb connection on those machines. Using the slower NIC I get this:<br /><br />$ time mysql < employees.sql <br /><br />real <b>2m41.270s</b><br />user 0m3.664s<br />sys 0m4.985sGiuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-72382661347948456942011-05-18T00:28:11.397+02:002011-05-18T00:28:11.397+02:00The overhead from semi-sync in this case is ~200 m...The overhead from semi-sync in this case is ~200 microseconds per commit. I don't think that is a big deal for most use cases.Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-42249537723023725292011-05-18T00:08:12.298+02:002011-05-18T00:08:12.298+02:00Curious what the connection between servers was? A...Curious what the connection between servers was? Assuming more round trips, perhaps a slower connection would multiply the delay.mysqlscotthttps://www.blogger.com/profile/01818920460352079309noreply@blogger.com