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:

# 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:

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

# 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).

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

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