Speed up your MySQL database
Fast Track
Admins are often faced with the task of solving performance problems of which they only have a rough idea or a vague description. Thus, the first task is to discover precisely what the difficulty is. This step alone helps to restore order, and it involves asking questions like: When exactly does the problem occur? What exactly happens before the user notices that the application is slowing down? What did the user click before this?
Along with such questions to discover the root cause of problems, MySQL logfiles help complete the picture. In any case, it is always useful to investigate the problem immediately after it occurs. Trying to understand what happened three weeks later is going to involve far more work. Finally, you need metrics, metrics, metrics. Ideally, you will have a monitoring infrastructure in place that already acquires all the necessary performance data.
Identify the Objective
Once you have described and documented the performance problem, you can then define the objectives behind your tuning actions. Are you looking for a shorter response time or more throughput? One objective could be, say: "Action X takes 3.5 seconds to complete after clicking – in future, we want to be done in less than one second." If you are facing more of a throughput problem, the objective could be: "Given 30 percent more load next year, we still want the system to respond to an order within 350ms on average."
It is essential to select realistic objectives – that means objectives that at least respect natural limits that you cannot influence. Depending on the network, you will have an upper limit for the transmission speed, or you might have technical limits (searching through 100 million rows will take a dozen seconds or so), or you might even encounter financial limits to tuning. (Yes, an SSD could give you 10,000 IOPS, but the customer doesn't want to pay for this.) Performance tuning always involves compromise – whether this be related to financial or security concerns or expressed in terms of the expense of corrections and code changes.
Finding the Bottleneck
The first task is to identify the bottleneck on the technical side. This can be the web browser, the network, the web or application server, the database, or even the hardware. On database machines, the limiting factors are typically the I/O, RAM, and CPU resources – in that order.
Which of these resources is currently imposing restraints on database performance can be discovered with tools such as iostat
for the I/O system, free
and vmstat
for memory use and swapping, as well as top
or mpstat
for the CPU load. To discover more about memory use, you can deploy the free
command, as shown in Listing 1.
Here's what the most important values in Listing 1 mean:
Listing 1
Using free
# free -m total used free shared buffers cached Mem: 117080 113060 4020 2420 1980 62260 -/+ buffers/cache: 48810 68270 Swap: 119730 0 119730
- Mem total : Total amount of available RAM: 117,080MB in this case.
- Mem used : Amount of RAM used by the applications: 48,810MB in this case.
- Mem free : Potentially free RAM: 68,270MB in this case.
- Mem cached : RAM used by the operating system for the filesystem cache: 62,260MB in this case.
- Swap used : There is no swap memory used in this example.
If RAM is the limiting resource, Mem used will be moving in the direction of Mem total , while Mem free tends toward zero. At the same time, the operating system gets rid of the filesystem cache (Mem cached ) (it approaches zero), and Swap used moves away from zero in the direction of Swap total .
You can determine the memory use of the most important process (mysqld
) as shown in Listing 2. The memory-specific values have the following meanings:
Listing 2
Memory Use of mysqld
# ps aux | egrep 'mysqld|VSZ' USER PID %CPU %MEM VSZ RSS START TIME COMMAND mysql 1568 0.0 0.0 4440 744 Sep08 0:00 mysqld_safe mysql 2337 0.1 5.9 29163040 7115280 Sep08 13:00 mysqld
- VSZ : The memory requested from the operating system by the process (virtual size).
- RSS
: The memory kept in RAM by the operating system (resident set size). This memory is part of the Mem used
area shown by the
free
command. The sum of the RSS values for processes should be approximately equal to Mem used . If the sum of the VSZ values for all processes is well above the available memory (Mem total ), then your RAM is overcommitted, which can lead to swapping at full load, thereby slowing down the system.
To discover the presence and extent of system swapping – and what effect this has on the I/O system, check out Listing 3.
Listing 3
Using vmstat
# vmstat 1 procs -------------memory---------- ----swap---- -----io----- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 0 0 10848 139772 3612 793656 0 0 0 0 788 2583 7 5 88 0 0 0 0 10924 152000 3584 689528 0 76 4 124 802 2759 7 6 88 0 0 <- < Start of swapping > 0 1 103260 117124 3528 636156 0 92336 0 92556 834 2607 7 5 83 5 0 2 1 126168 138116 3552 553200 0 22908 348 23076 1143 2880 6 3 57 35 0 <- < I/O System loaded (wa) > 0 3 265376 149720 1136 378148 0 105300 252 105348 993 3002 6 4 62 29 0 0 14 495028 117264 560 329792 0 137352 1252 137488 880 3506 5 2 22 71 0 <- < Severe swapping > 0 10 597776 117244 320 317840 0 29400 588 29400 664 2522 2 2 0 96 0 <- < Process killed > 0 13 1667548 8263784 784 304768 296 0 920 0 298 675 0 0 25 75 0 <- < Swapped out > < RAM back > 1 7 1659568 8247460 1200 314888 6900 0 16056 1132 905 1995 6 1 24 70 0 1 2 1654428 8148296 2516 373604 13004 0 49036 1100 2444 7135 8 6 68 19 0 1 0 1649624 8128492 3088 396844 328 0 2604 28 497 1634 4 1 94 1 0
Determining the load on the I/O system is somewhat more complex. You need to identify four different I/O patterns: sequential reading and writing and random reading and writing, which occurs frequently in databases. Normal hard disks do a good job of sequential read/write, whereas SSDs are far better at random read/write.
You can query the load on the I/O system, as shown in Listing 4 for sequential writing to an SSD (single-threaded, 16KB blocks, O_DIRECT
). Here, r/s
and w/s
stand for the number of read or write operations per second (IOPS), await
is the average service time for all I/O requests, and rKB/s
and wKB/s
indicate the volume of data read or written (throughput).
Listing 4
I/O System Load
# iostat -xk 1 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 2.00 8.00 2.00 9500.00 16.00 151948.00 31.99 1.07 0.11 4.00 0.11 0.09 88.40
If your read or write throughput is high (as in Listing 4), you are looking at sequential operations. Random read or write operations result in far lower throughput.
That just leaves the CPU load. You'll need to take a closer look here. In the example in Listing 5, the system seems to have 71 percent load while idle, but core 1 (e.g., one thread in a MySQL connection) is fully loaded for extended periods.
Listing 5
Using mpstat
# mpstat -P ALL 1 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle all 26.94 0.00 0.59 1.05 0.00 0.02 0.00 0.00 0.00 71.40 <- < 71% idle > 0 1.00 0.00 0.37 0.00 0.00 0.06 0.00 0.00 0.00 98.57 1 100.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2 3.77 0.00 1.32 1.32 0.00 0.00 0.00 0.00 0.00 93.58 3 2.82 0.00 0.69 2.89 0.00 0.00 0.00 0.00 0.00 93.60
Once the limiting factor – or the guilty process – has been identified, you can start thinking about how to tackle the performance problem. In the case of MySQL, you can identify five levels at which to tackle the difficulties and hopefully resolve them.
Level 1: The Hardware
Speed problems can often be resolved by throwing hardware at them. And, this approach can even be cheaper than investing person-months in improving the application code or the architecture. Investments in better – that is, potentially more expensive – hardware will not scale well. Poor application design or an ill-considered architecture will quickly see the application reach its limits again. New hardware mainly buys you time to fix the actual problem.
If you want to deploy faster hardware, you will typically want to invest in more RAM and better I/O systems (RAID 10, battery-buffered I/O controllers, SSDs) for a database machine. These actions can seriously improve database performance.
Buy this article as PDF
(incl. VAT)