We are going to show the effects of the new patches applied to Percona HighPerf release. As you see from the following graphs, there is significant difference to normal version when the data bigger than buffer pool (right graph shows CPU usage)
The workload emulates TPC-C and has a same characteristic to DBT-2 (it is not DBT-2, but custom scripts, we will publish them eventually). There are no delays between transactions (no thinking time, no keying time), it uses MySQL C API and the server side prepared statement.
The server has 8core CPU and RAID storage (RAID10 / 6 disks). The data population is along to the scale factor 40WH (:=~4GB). It is enough bigger than the data cache of the storage.
main common settings
innodb_buffer_pool_size = 2048M
innodb_thread_concurrency = 0
innodb_max_dirty_pages_pct = 70
innodb_flush_method = O_DIRECT
The next graphs show the frequency of IO, and you can see we can get more IO performance with the new patches.
Then, how the patches contribute to the improvements in this case.
split_buf_pool_mutex_fixed_optimistic_safe.patch
The patch splits global InnoDB buffer_pool mutex into several and eliminates waitings on flush IO and mutex when there is no enough free buffers. It helps if you have performance drops when data does not fit in memory.
Attention!(Windows only): You should not use with AWE. It is not tested at all…
control_io-threads.patch
Generally RAID storage can parallelize IO accesses to several disks. This patch enables to control the number of the IO threads on Unix/Linux and makes the threads to be used equally for parallel using. By default InnoDB uses
1 insert buffer thread, 1 log thread, 1 read thread, 1 write thread. With innodb_file_io_threads InnoDB will use
1 insert buffer thread, 1 log thread, N/2-1 read thread, N/2-1 write thread. You can start from “innodb_file_io_threads = 10” for almost all RAID storages. The more disks the RAID have, the bigger number may make sense.
* This test uses “innodb_file_io_threads = 34” (But, it is redundant value…)
control_flush_and_merge_and_read.patch
This patch makes more parameters about IO configurable. Normally, you may not need to configure the following parameters. But, in some extreme cases, it is worth to tune.
The following parameters are added.
innodb_read_ahead (default 3)
This controls [enable/disable] of read-ahead.
3: normal
2: enable linear read-ahead only
1: enable random read-ahead only
0: disable both
innodb_ibuf_contract_const (default 5) – Usual value
innodb_ibuf_contract_burst (default 20) – Burst value
The bigger value makes the more effort not to stock records to the insert buffer.
(Though, the more IO occur at the same time.)
The big value of “Ibuf: size” (like > 1000) may tend to losing the performance. If you have powerful IO system, you might set bigger values.
innodb_ibuf_contract_* is count of pages InnoDB pages tries to to
the buffer pool and merge the ibuf contents to them.
innodb_ibuf_contract_const is used during run a batch of insert buffer merge every 10 seconds, and
innodb_ibuf_contract_burst is used – comment from InnoDB code /* If there were less than 5 i/os during the one second sleep, we assume that there is free disk i/o capacity available, and it makes sense to do an insert buffer merge. */
innodb_buf_flush_const (default 10)Â * Usual value
innodb_buf_flush_burst (default 100) * Burst value
These control the number of blocks flushed at once.
(flushing: writing modified db pages by flush_list order)
If you use extremely fast device (e.g. ramfs), the bigger value may help the performance.
* This test uses
“innodb_read_ahead = 0”
(The both of read-ahead are disabled)
“innodb_ibuf_contract_const = 50000”
“innodb_ibuf_contract_burst = 50000”
(For our workload it is better to not store many records in the insert buffer)
In conclusion, If you are using fast RAID storage, and/or observe performance decrease caused by shortage of free buffers you may try Percona HighPerf release.
