Analysis of MySQL server performance

With regard to MySQL server performance, there may be more points to focus on, such as: How to confirm that the server has achieved the best performance state, to find out why a statement is not executed fast enough, and the user described as a "pause", "heap" or some intermittent troubleshooting of "stuck-up". This looks very simple. But it turns out that there is a simple way to detect the signs of noise.

This approach is focused on measuring where the server spends its time, and the techniques used are profiling.

MySQL performance issues may differ from one person to another. For example: number of queries per second, CPU utilization, scalability, and so on.

Everyone has a different understanding of performance in different scenarios.

But in this we will give a formal definition:

MySQL performance refers to the measurement of the time required to complete a task, in other words, performance is response time .

Performance optimization is to reduce the response time as much as possible under a certain workload.

Some people think that performance optimization is a misunderstanding of reducing CPU utilization. Resources are consumed and used to work, so sometimes consuming more resources can speed up queries. CPU utilization is only a phenomenon, not a good measurable goal.

Similarly, if performance optimization is only considered as the increase in query per second, this is only throughput optimization. Throughput improvements can be seen as a by-product of performance optimizations. (throughput is defined as the number of queries per unit of time, which is exactly the inverse of our performance definition)

The second principle of optimization: unable to measure can not be effectively optimized. So, the first step should be to measure where the time is spent.

Unsuitable measurements:

Start and stop measurements at the wrong time

The measured information is aggregated, and the arrest is the target activity itself.

A common mistake, for example, is to look at slow queries and then troubleshoot the entire server to determine where the problem is.

The time required to complete a task can be divided into two parts: execution time and wait time.

Anatomy MySQL Query:

1. Profiling Server Load

Capturing MySQL queries into the log file

Analyze query Logs

2. Anatomy of a single query

Using Show profile

Use Show status

Using the slow query log

3. Using profiling

Summary of keywords are : response time, throughput, measurement, optimization, slow query log.

Analysis of MySQL server performance