Key 10 MySQL performance optimization skills

Welcome to the Linux community forum and interact with 2 million technical staff. Just like all relational databases, MySQL seems to be an incredible monster. It will stop at any time, limit applications to stagnation, or put your business at risk. In fact, many of the most common errors are hidden behind MySQL performance problems. To ensure that your

Welcome to the Linux community forum and interact with 2 million technical staff> just like all relational databases, MySQL is an incredible monster. It will stop at any time, limit applications to stagnation, or put your business at risk. In fact, many of the most common errors are hidden behind MySQL performance problems. To ensure that your

Welcome to the Linux community forum and interact with 2 million technicians>

Like all relational databases, MySQL is an incredible monster. It will stop at any time, limit applications to stagnation, or put your business at risk.

In fact, many of the most common errors are hidden behind MySQL performance problems. To ensure that your MySQL server runs at full speed and provides continuous and stable performance, it is very important to prevent these errors. However, these errors are often hidden in workload and configuration problems.

Fortunately, many MySQL performance problems have similar solutions, which makes troubleshooting and MySQL adjustment an easy-to-manage task. The following are 10 tips for MySQL to achieve the best performance.

1. Analyze the workload

By analyzing the workload, you can find the most expensive query for further adjustments. In this case, time is the most important thing. When you send a query command to the server, you seldom pay attention to other things except how to quickly complete the query. The best way to analyze the workload is to use a query analyzer such as MySQL Enterprise Monitor or Percona Toolkit's pt-query-digest tool.

These tools can capture the queries executed by the server and list the tasks in descending order based on the response time. They will pin the most expensive and time-consuming tasks to the top, so that you can know where you need to focus on. The workload analysis tool aggregates similar queries in one row, allowing managers to view slow queries and query results that are fast but executed multiple times.

2. Understand four basic resources

In terms of functionality, a database server requires four basic resources: CPU, memory, hard disk, and network. If any of the four resources has poor performance, instability, or overload, the performance of the entire database server may be low. Understanding basic resources is crucial in two specific areas: Hardware Selection and troubleshooting.

When selecting hardware for MySQL, make sure that all components with excellent performance are selected. It is also important that these components match each other to achieve a reasonable balance between them. Generally, enterprises choose high-speed CPUs and hard disks for servers, but the memory is insufficient. In some cases, the cheapest way to significantly improve performance is to increase memory, especially for workloads that are subject to disk read speeds. This seems to be a bit out of common sense, but in many cases, the hard disk is overused because there is not enough memory to save the data being used by the server.

Another example of getting this balance is the CPU. in many cases, if the CPU speed is high, the MySQL performance is very good, because each query is run in a single thread, and cannot run in parallel between CPUs. When troubleshooting, you should check the performance and usage of these four resources and check whether they are of low performance or overload. This knowledge can help you solve problems quickly.

3. Do not use MySQL as a queue

The queue and access solutions similar to the queue will quietly enter the application without your knowledge. For example, if you set a project status so that a specific Worker Process can mark it before execution, you create a queue inadvertently. For example, mark an email as not sent, send them, and mark them as sent.

The queue may cause some problems. There are two main reasons: they serialize the workload and prevent tasks from being processed in parallel. As a result, the tasks being processed and historical data previously processed in the job are arranged in a form according to the sequence. In this way, both application latency and MySQL load are increased.

[1] [2]