When your car doesn’t start, you don’t just blindly change the
battery, starter, fuel pump, spark plugs or all of the above.
Instead, you go to your mechanic and ask him to check what is
wrong (or you check it yourself if you are the mechanic) and then
fix whatever is broken.
Yet very often I see DBAs doing exactly the opposite with their
MySQL servers. Rather than assessing what is the server so busy
with, they keep changing configuration options until the problem
“goes away”. Alternatively, they add more RAM, more CPUs or
faster disks, depending on which resources seems to be the most
busy at a time. Or they switch to a new server altogether.
MySQL (with a help of some tools) has a really convenient way to
analyse the workload and see clearly what exactly is MySQL so
busy doing. And even how much improvement you can expect by, say,
fixing a specific MySQL query.
…
[Read more]