Guest blog by Sam Sawyer, Product Application Engineer, Product Marketing and Management
Oracle’s MySQL database is commonly used in transactional database applications serving hundreds of users, while delivering thousands of transactions per second. A very important aspect of transaction database system design is to build in the ability to fully utilize available resources — or to add more resources to match a growing load.
Database applications require low latency and high IOPS performance to avoid becoming I/O-bound. This is especially the case when attempting scale-up strategies with multiple application instances. Utilizing fast, low latency SSD based storage, such as SanDisk® Optimus drives, is a key enabler for improving system performance.
Put a Rocket on Your Tortoise
The availability of powerful, yet inexpensive multi-core processors has enabled the use of parallel computing approaches. This is especially true when supporting multiple instances of applications or virtual machines that can run on the same server system. An obvious example of an easy and transparent acceleration strategy is to improve the storage system performance by replacing spinning hard disk drives (HDDs) with SSDs.
SanDisk testing confirms that SSD drives can improve peak IOPS performance by a factor of 100 and average I/O latency by a factor of 30 or more, depending on the number of HDDs drives that are replaced. Some additional examples of accelerating a server system include adding more memory, using faster processors, and processors with more cores.
MySQL TPS Performance
The test scripts, which form the basis for this blog posting, create eight Oracle MySQL database instances with the purpose of simulating a typical single server computing environment. In this environment, each MySQL client has exclusive use of a database instance.
The storage workload is generated using sysbench scripts that initiate database queries to drive the default InnoDB storage engines in the MySQL instances. In this way, the test simulates, large numbers of users launching transactions on each of the database instances.
So let’s put the question to a test.
System/Environment Test Setup
A Dell 720 server with 16 available drive bays was utilized for this testing. Internal SATA boot drive utilized for Operating System and MySQL executable files. MySQL database created on 8 SanDisk Optimus Ascend 400GB SSDs in a 100% redundant RAID10 configuration with all of the database-related storage, including log files residing on the RAID10 SSD volume.
The chart above shows the transactions per second that is achieved as the workload scales from one MySQL instance to eight simultaneously running instances.
Note that the total number of transactions per second steadily increases from one to six instances, peaking at 5,700 and then tailing off for instances seven and eight. This establishes that the peak number of transactions per second is achieved with 6 simultaneous MySQL instances.
The response time, as measured in the average number of msec per transaction (right axis of the adjacent chart) increased by a factor of 4—as the databases increased from one instance to eight instances. However, the average response time for query completion was still a very low 1.6 msec in the case of the eight database instances.
The test results for multiple database instances show that this approach can significantly improve performance, in terms of transactions per second, by a factor of more than 2.5:1, compared to a single MySQL instance. The strategy will reduce the cost per database transaction — and it will also improve server utilization.
If the application can be parallelized with multiple simultaneous available instances, co-locating (on a single server) multiple MySQL server instances using fast storage, such as SanDisk Optimus SAS SSDs, provides a good balance between cost, complexity, and performance on today’s x86 industry-standard server hardware.
Interested readers are invited to check out the full white white paper covering additional topics related to improving database performance including: scaling-out with multiple servers to improve MySQL performance, analysis of CPU utilization, details of the test configuration, and additional guidelines for optimizing computer system performance. The entire white paper will be available to download from sandisk.com/enterprise.
Be sure to stop by the SanDisk booth #1429 at Oracle OpenWorld and also visit SanDisk kiosk (SLX-020) at the Oracle Linux and Virtualization Showcase to learn more about the full range of SanDisk enterprise flash solutions for your Oracle environment. Follow our conversation on Twitter @SanDiskDataCtr and LinkedIn where we will keep you updated with the latest from the event.