Benchmark MySQL 5.5.9 on Oracle Sun X4170 M2

As I posted the testing result from MySQL on HP server (refer to the previous benchmark), my colleagues from Oracle System Practice are very interesting in MySQL performance on Sun's x86 servers (of course we hope we can proof the servers from Oracle are superior than HP). With the help from our VAD partner - Bestcom, we got a Sun X7140 M2 installed in our data center, thanks for the endeavor form my colleague Cano Lai, the server was setup and and ready for testing very soon.

Technical Specification

The configuration of the system as below:

Server

• CPU: 6 Core Intel E5640 * 2, 2 threads with each core, 12 Cores/24 Threads in total
• Memory: 16G Bytes
• Hard Disk: 146GB * 2

Operating System

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.

Database

MySQL 5.5.9 Community Edition with InnoDB storage engine

Tasks

• Setup Sun X4170 M2
• Install Red Hat Linux 5.6
• Install MySQL 5.5.9

We downloaded MySQL for Red Hat & Oracle Enterprise Linux 5 (x86, 64-bit) from MySQL download site, apart from MySQL server, shared component is needed for compiling Sysbench.

• Install Sysbench 0.4.12

Following the installion steps on Sysbench web site http://sysbench.sourceforge.net/docs/#install, would get things done easily, the only thing that need to take care of is to install MySQL shared component in advanced, and let the software and libraries should be accessible.

• Tune MySQL

Some of tuned options in my.cnf file are list as below:

# 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
innodb_buffer_pool_instance=3
# New file format from MySQL 5.5
innodb_file_format=Barracuda
innodb_file_per_table
# 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

The following command would generate data for the test, we had 100 million rows in the largest table.

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

Apart from MySQL performance on Oracle server, we were curious if there is any difference between the Based Kernel and Oracle Unbreakable Kernel. There was time available after the 1st run, we decided to have the same benchmark on the 2nd run which had the same configuration with the previous run, but replace the OS kernel with Oracle UEK. To upgrade the kernel is not a difficulty job, the required command as below:

# 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

The steps are the same as task 6.

• Consolidate the testing results Collect the number of read/write and transactions with each run, and generate line charts.

Testing Results

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 measurement

Conclusion
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.