The configuration of the system as below:
- CPU: 6 Core Intel E5640 * 2, 2 threads with each core, 12 Cores/24 Threads in total
- Memory: 16G Bytes
- Hard Disk: 146GB * 2
Red Hat Enterprise Linux 5.6, with base kernel ( Linux Kernel 2.6.18-238.el5 ) 1st run, Oracle Enterprise Kernel ( Linux Kernel 2.6.32-100.26.2 el5) for the 2nd run.
MySQL 5.5.9 Community Edition with InnoDB storage engine
- Setup Sun X4170 M2
- Install Red Hat Linux 5.6
- Install MySQL 5.5.9
- Install Sysbench 0.4.12
- Tune MySQL
# The number of threads might be more than 1000, set to 1500 to accommodate it
max_connections = 1500
# The server has 16 GB RAM, evaluate the overhead from OS and Sysbench,
# 12 GB innodb buffer pool, could be fully utilize the available memory
innodb_buffer_pool_size = 12G
# Multiple innodb buffer pools could reduce contention for some of resources such as
# free lists, flush lists, LRUs and buffer pool mutex
# New file format from MySQL 5.5
# Increase the number of threads for writing dirty pages
innodb_write_io_threads = 8
# Increase the number of I/O threads for read operations
innodb_read_io_threads = 8
# Increase the number of innodb_thread_concurrency to fully utilize the multiple
# cores/threads environment
innodb_thread_concurrency = 24
# innodb_flush_log_at_trx set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. This a conservative approach.
# If set this to 0 or 2 might reduce disk I/O and increase the measurements of the test.
innodb_flush_log_at_trx_commit = 1
- Test against MySQL on Red Hat 5.6 based kernel with Sysbench
nohup sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000000 --mysql-user= --mysql-password= prepare
Execute the test with the following command, and collect the test results from the outputs of nohup
nohup sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000000 --num-threads= --mysql-user= --mysql-password= run
- Substitute Red Hat 5.6 based kernel with Oracle Enterprise Linux Kernel
# cd /etc/yum.repos.d/
# wget http://public-yum.oracle.com/public-yum-ol6.repo
# wget http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
# yum -y install kernel-*
After reboot the server, you will see the kernel had been upgraded with the following command:
# uname -a
# yum info kernel-uek
- Test against MySQL on Oracle Enterprise Kernel
- Consolidate the testing results Collect the number of read/write and transactions with each run, and generate line charts.
The benchmark result summarized as chart 1 and Chart 2, for the scale of transaction per second was too small, I created the 3rd chart to depict it.
- Chart 1 is the measurement for read-only transaction, MySQL on OEL got more than 54,000 operations at 900 threads while with everything the same except Red Hat, we got around 20,000 read-only operations and down to 11,000 at 900 threads.
- In Chart 2, the red line is number of read/write operations with Red Hat based Kernel, the number of average read/write was 15,426 compare the measurement from the previous benchmark of average 4,033 read/write operations, this 382% improvement is a very one improvement (although the power of the server was better the previous one).
- In Chart 2, the purple line shows the number of read/write operations with Oracle Unbreakable Kernel, it performed much better than the original kernel, Oracle UEK start from 32,110 and reach to the peak of 39,941 at 700 threads. On the contractually, Red Hat Based Kernel got 20,493 at the smallest number of threads and slipped down onward. Oracle UEK get 36,200 operations which is 234% improvement! And if we compare the measure from the previous benchmark, with technical stack from Oracle (hardware and OS kernel) we can enjoin near 9 times throughput.
- The number of transaction shows in chart 3, it is consistent to the chart 1, UEK start from 1,690 transaction per second, creep to 2,102 at 700 threads, however Based Kernel start from 1,078 and slip down there after, the gap extended to 1,343 at 700 threads.
Chart 1. Read-only benchmark
Chart 2. Read-Write Benchmark
Chart 3. Read-write benchmark with transaction per second measurementConclusion
In the passed year, MySQL had lots of improvements, they enable MySQL to be the database that support mission critical applications. We hope the testing would help us figure out the right technical combination (from hardware, operating system to the database) for our customers. The result of this benchmark shows Oracle technical stack would offer superior performance for the user. With Oracle Linux we got near 300% performance improvement when the number of concurrent connection is 900, and compare with the result form previous benchmark, we got almost 9 times throughput, although the configuration of the hardware is better, it's still a significant improvement.
However I did not apply any performance analyze tool (for example sar) to collect the performance related information from the operating system layer, thus I have no idea what makes this huge difference between Red Hat based Kernel and Oracle UEK, hope I could have the opportunity to run the same benchmark with performance analyze tool.