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

... comments powered by Disqus