Pencarian

Rss Posts

 

 

 

Benchmarking MySQL ACID performance with SysBench

Jun 20, 2010

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

Basisdata, Server, Jaringan & Keamanan, Sindikasi     Comments Off