A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”
Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):
- sysbench –test=oltp –db-driver=mysql –oltp-table-size=1000000 –mysql-engine-trx=yes –oltp-test-mode=complex –oltp-read-only=off –oltp-dist-type=special –max-requests=0 –num-threads=8 –max-time=120 –init-rng=on run
MySQL Settings:
In the first test MySQL is set to the following ACID related settings. This will give us results for TPS performance without full ACID compliance – very common settings on a server that is handling blogs, ad serving, general business websites, and other roles where full ACID is not required and performance is valued over the benefits of full ACID. These are important settings when we look at the difference in performance when we change to full ACID in the second test.
- innodb_flush_log_at_trx_commit = 0
- sync_binlog=0
- transaction-isolation=REPEATABLE-READ
System configuration and InnoDB buffer pool size:
- XEON E5345 Series 2.33ghz 8-core, 16GB RAM, Local SATA 7.2K disks
- innodb_buffer_pool_size = 10G
Full result set from sysbench:
Summary OLTP test statistics:
- queries performed:
- transactions: 172426 (1436.83 per sec.)
- read/write requests: 3276664 (27304.51 per sec.)
- other operations: 344882 (2873.91 per sec.)
Non-ACID results:
We can simplify the results by looking at the following TPS results for this non-ACID test:
- transactions: 172426 (1436.83 per sec.)
Full ACID results:
Let’s go ahead and run the test again with different ACID settings. This will give us the TPS results for full ACID compliance:
- innodb_flush_log_at_trx_commit = 1
- sync_binlog=1
- transaction-isolation=REPEATABLE-READ
We get the following results for TPS:
- transactions: 3197 (26.58 per sec.)
- read/write requests: 60743 (505.04 per sec.)
- other operations: 6394 (53.16 per sec.)
Final Results:
So as you can see the difference between full ACID settings and not (on the same server with only those values on the cnf being changed) results in a huge difference in performance on this standard database server. We can now hand this data to the customer and they will know what impact the settings will have on their application’s performance and what to expect when running full ACID vs non-ACID.
More info on using sysbench here: http://sysbench.sourceforge.net
#1 by Andy on 2010/06/20 - 4:36 pm
Quote
1) binlog is not required for full ACID. Moreover, MySQL’s group commit is broken when binlog is enabled (http://kristiannielsen.livejournal.com/12254.html), leading to dramatically lower performance.
If you disable binlog, you’ll still have full ACID but will get dramatically higher performance
2) For database servers that require ACID, a RAID controller with BBU is almost a must have. With BBU you’ll also see a much higher performance even when binlog is enabled.
#2 by admin on 2010/06/20 - 5:03 pm
Quote
If you are referring to XAs as group commits then that’s actually one level up from regular ACID settings and requires SERIALIZABLE, which also slows down performance: “A global transaction… extends ACID properties “up a level” so that multiple ACID transactions can be executed in concert as components of a global operation that also has ACID properties. (However, for a distributed transaction, you must use the SERIALIZABLE isolation level to achieve ACID properties. It is enough to use REPEATABLE READ for a nondistributed transaction, but not for a distributed transaction.)” http://dev.mysql.com/doc/refman/5.1/en/xa.html
BBC does improve write performance but that is not in the scope of this post, as I’m just discussing sysbench usage to show results. SSDs would be faster than the SATA drives and 3ghz CPUs would be faster than the 2.33ghz ones on this server, but again this post is about sysbench and not tuning recommendations in general.
Pingback: Gout Pain – Gout Pain Relief Using Cherry Juice | Treatment of Gout
#3 by Andrew on 2010/06/21 - 12:27 am
Quote
The main hit here is sync-binlog=1 rather than innodb-flush-log-at-trx-commit=1. As other comments have said, sync-binlog is not required for ACID and comes at a big performance hit:
http://www.mysqlperformanceblog.com/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/
These sorts of tests would be more useful by testing variables in isolation.
#4 by admin on 2010/06/21 - 12:55 pm
Quote
You’re definitely correct. That’s an important point to clear up – although sync_binlog=1 is not required for basic ACID support in MySQL, the company I work for offers three levels of data integrity configurations: non-ACID, ACID, and ACID+. In ACID+ we set sync_binlog=1 to ensure that any process failure / hardware failure will not result in data/transaction loss. FTD: “A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).”
The server in the test did not have a battery backed cache option so it’s pretty clear what the performance difference is with sync_binlog=1 on said server.
Anyway, sysbench produces a great report to show the #s needed to choose configurations for an application’s required database performance.