Maximizing Sysbench OLTP performance for MySQL

Sysbench is a popular open source benchmark used to measure performance of various parts of the operating systems and (one) application (database). Since we are talking about MySQL, I will concentrate on the database part of the test.

The oltp test in the sysbench benchmark creates a single table and runs a set of queries against it. Each row in the table is around 250 bytes and by default it creates 1000 rows. For our experiment we used 10 million rows. Allan has blogged about the details regarding the experiments; I will present an alternate view to those experiments.

The trick to getting good numbers with Sysbench and MySQL is very simple

1. Maximize CPU utilization
2. Reduce delays due to IO

Maximize CPU Utilization

It is very important to utilize all the resources in the system to get maximum performance. If you have a multi-core machine (many processors already have multiple cores), this means that you will have to execute several threads in parallel to to fully utilize all the cores in the system. For Sysbench, this is achieved by using the --num-threads argument. We got the best performance when the number of threads is usually close or equal to the number of cores in the system. You also need to increase the innodb threads concurrently executing inside innodb. The meaning of innodb_thread_concurrency has changed since MySQL 5.0.19, so be sure to set it to the right value. We found the best number to be 0 (unlimited threads).

Reduce delays due to IO

One way to eliminate IO delays is by not doing any IO i.e cache everything. Each row in the Sysbench test table is around 250 bytes. For a 10 million row table, it is about 2.5GB of data. Most systems come with more memory than that, and hence you should be able to cache the table in memory.

MySQL uses the innodb_buffer_size variable to determines the size of the cache it uses. If you have sufficient memory (more than 2.5GB in our case), you could cache the whole table. An alternative strategy (especially if you are using 32bit MySQL) is to choose a smaller value for the innodb_buffer_cache and let the table be cached in the filesystem buffer cache; but I suspect it is more efficient to cache the data at the innodb level. This should eliminate or considerably reduce all the reads.

To eliminate writes, you can either choose to do a read-only test, or use a cache-enabled disks for the writes. The cache can either be NVRAM, or the write cache on the disk. Note: Using cache that is not battery backed is very risky. Do it at your own risk.

If you are still seeing delays due to IO(which in turn translates to idle time on the system), you can try adding more threads to soak up the available CPU. You have to be very careful to find the right balance.

To recap, We got the best numbers using

1. Use multiple sysbench threads via the --num-threads parameter
2. Set innodb_thread_concurrency = 0 in my.cnf
3. Set innodb_buffer_size equal to or greater than the table size

Thats it! Here is the my.cnf that gives me the best numbers.

[mysqld]
datadir=/mysqldata

innodb_data_home_dir = /mysqldata
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /mysqldata
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 400M
innodb_log_buffer_size = 64M
innodb_thread_concurrency = 0