Maximizing Sysbench OLTP performance for MySQL
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
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
- Maximize CPU utilization
- 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
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
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
- Use multiple sysbench threads via the --num-threads
- Set innodb_thread_concurrency = 0 in my.cnf
- Set innodb_buffer_size equal to or greater than the table
Thats it! Here is the my.cnf that gives me the best numbers.
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