MySQL Troubleshooting

Effects of Other Software

Everything we’ve discussed so far is important when the MySQL server runs in any environment. Ideally, it should run in a dedicated environment and use all physical resources the machine has. But some sites use MySQL in shared environments. This includes shared hosting, where many instances of the mysqld server are running on behalf of different customers, and systems simultaneously running mysqld with client applications and other processes.

When you tune a MySQL installation in such configurations, you need to check two additional things: how many resources other processes use in their normal operations and how many resources they allocate at critical times.

Under normal loads, you can guess how many resources are left after other programs reserve theirs, and set MySQL’s options appropriately. Critical loads are usually surprising and can lead to sudden, mysterious MySQL errors or even crashes. There is no universal rule about what to do in these situations. Just remember that third-party software can affect a MySQL installation, analyze the potential load, and take action to compensate for the effects.

Thus, if you anticipate that some other application has a critical load at time X, measure its resource use under the load and adjust MySQL server options accordingly. In such environments, it makes sense to limit resources at the OS level or use virtualization. In contrast to previous sections, I am advising here to add restrictions rather than remove them.

The worst case occurs when an unusually heavy load cannot be predicted. You learn about it only when you hit a problem. So if MySQL starts failing when it should not, always remember the effects of concurrent processes, check OS logs, and see whether other applications can affect MySQL’s access to resources. It is also valuable to install software that monitors the activity of all processes running on the same box together with your MySQL installation.

• A good method to ascertain whether the MySQL server was affected by another OS process is to run a problematic query in an isolated environment. This is the same method recommended in How Concurrency Affects Performance.