Returning to post Group commit and real fsync I made several experiments:
I ran sysbench update_key benchmarks without —log-bin, with —log-bin, and with —log-bin and —innodb-support-xa=0 (default value is 1). Results (in transactions / sec)
| threads |
without —log-bin |
—log-bin |
—log-bin and
—innodb_support-xa=0 |
| 1 |
1218.68 |
614.94 |
1010.44 |
| 4 |
2686.36 |
667.77 |
1162.60 |
| 16 |
3993.59 |
666.14 |
1161.56 |
| 64 |
3630.55 |
665.18 |
1151.36 |
So we can see group commit is not only broken when XA is enabled but also if XA is disabled but binary log is enabled. Performance without XA can be twice as good as with XA if binary logs are enabled as Innodb will need to flush its log buffer only once. So, if you are using —log-bin with innodb tables it makes sense to set —innodb-support-xa=0
Related
Vadim TkachenkoVadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks.
Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products.
He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.
[…] Watch out for MySQL 5.0 - if you enabled Binary log with MySQL 5.0 you loose group commit which can dramatically increase commit rate for multiple user load. See this post by Vadim for benchmark results. […]
[…] problem in both cases was MySQL 5.0 broken group commit […]
Hi,
What version of 4.1 did group commit first show up in?
Thanks,
Mike
Hello,
Please, what means “XA” ?
I cannot find the meaning on the web.
Regards
Jonatas
Jonatas,
http://en.wikipedia.org/wiki/X/Open_XA
[…] PZ: You can disable binary logging to get Group Commit back. Though this is often not the option. Here are some benchmarks we did. […]
[…] it before, see http://www.mysqlperformanceblog.com/2006/05/03/group-commit-and-real-fsync/ and http://www.mysqlperformanceblog.com/2006/05/19/group-commit-and-xa/ The problem is the InnoDB tries to keep the same order of transactions in binary logs and in […]
[…] read a bit more about in Jeremy Cole’s post on MySQL replication. You can also see some handy benchmarks that compare MySQL with and without binary […]