Troubleshooting MySQL Performance

Growth. High volumes. The amounts of users, views, data are increasing. Database queries run slower? Clients are hanging in the process list waiting for something? The server is unresponsive? What to do?

MySQL has tools that allow to figure out which changes lead to a loss in performance.

During this tutorial based on my experience of technical support I will show simple-to-use methods that allow to discover and eliminate causes of unwanted behavior. I will describe the order which they should be used in, what method to start with, how to delve deep into a problem and test safely.

Growth. High volumes. The amounts of users, views, data are increasing. Database queries run slower? Clients are hanging in the process list waiting for something? The server is unresponsive? What to do?

MySQL has tools that allow to figure out which changes lead to a loss in performance.

During this tutorial based on my experience of technical support I will show simple-to-use methods that allow to discover and eliminate causes of unwanted behavior. I will describe the order which they should be used in, what method to start with, how to delve deep into a problem and test safely.

We will look at the following subjects:
- creation and usage of a test server;
- nuances of slow query debugging;
- specifics of the locking system;
- influence that hardware and configuration makes on performance;
- data aggregation with minimal strain on the server.

I will describe MySQL server architecture and the specifics of its various parts such as the optimizer, the table engines, the locking system.

We are going to look both at embedded tools that are always available as well as command line utilities that make the job easier. We will pay separate attention to the debugging capabilities that have appeared in the latest versions.