Key 10 MySQL performance optimization tips

Like all relational databases, MySQL seems to be an elusive monster. It will stop at any time, limit the application to stagnation, or put your business at risk.

In fact, many of the most common errors are hidden behind MySQL performance issues. In order to ensure that your MySQL server is always running at full speed, it is important to provide consistent performance and eliminate these errors. However, these errors are often hidden in workload and configuration problems.

Fortunately, many MySQL performance problems have similar solutions, making troubleshooting and tuning MySQL an easy-to-manage task. Here are 10 tips to get the best performance from MySQL.

1. Analyze workloads

By analyzing workloads, you can find the most expensive queries in further tuning. In this case, time is the most important thing. Because when you issue query commands to the server, you rarely focus on anything else but how to quickly complete the query. The best way to analyze workloads is to use a Query Analyzer such as MySQL Enterprise Monitor, or a tool such as the Percona Toolkit pt-query-digest.

These tools are able to capture queries executed by the server, listing the task list in descending order based on response time. They put the most expensive and time-consuming tasks on the top, so you can see where you need to focus. The Workload analysis tool aggregates similar queries on a single line, allowing managers to view slow queries and view queries that are fast but executed more than once.

2. Understand four basic resources

Functionally, a database server requires four basic resources: CPU, memory, hard disk, and network. If any one of these four resources is weak, unstable, or overloaded, it can result in poor performance across the database server. Understanding basic resources is critical in two specific areas: selecting hardware and Troubleshooting.

When selecting hardware for MySQL, you should ensure 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. Typically, the enterprise chooses a faster CPU and hard disk for the server, but the memory is severely inadequate. In some cases, the cheapest way to dramatically improve performance is to increase memory, especially for workloads that are subject to disk read speed. This may seem counterintuitive, but in many cases the hard disk is overused because there is not enough memory to hold the data that the server is using.

Another example of getting this balance is the CPU. In many cases, if the CPU is fast, MySQL performs well because each query is single-threaded and cannot run in parallel between CPUs. When troubleshooting, you should check the performance and usage of these four resources to see if they are performing poorly or work overtime. This knowledge can help you to solve problems quickly.

　　

3. Do not use MySQL as a queue

Queues and queue-like access scenarios sneak into the app without your knowledge. For example, you set up a project state so that, before execution, a particular worker process (worker processes) can tag it, then you create a queue unintentionally. For example, Mark e-mail messages as not sent, send them, and then mark them as sent.

Queues can cause problems with two major reasons: they serialize workloads and prevent tasks from being processed in parallel. This causes the tasks that are being processed and historical data that were previously processed in the work to be arranged in a form based on the sequence. This increases both the latency of the application and the load on MySQL.

　　

4. Filter results in the cheapest way

The best way to optimize MySQL is to do cheap and imprecise work first, then do difficult, precise work on a small scale, and then generate datasets.

For example, suppose you calculate the area within a given radius for a geographic coordinate point. In many programmers ' toolboxes, the first tool is the spherical half-normal vector formula to calculate the length of the sphere. The problem with this approach is that the equation requires many trigonometric operations and requires a CPU with strong computational power. Spherical semi-normal computation not only runs slowly, but also causes a spike in machine CPU usage. You can decompose the calculation before using the spherical half-normal vector formula. Some decomposition calculations do not require the use of trigonometric functions.

　　

5. Understand two extended death traps

Extensibility may not be as vague as you think. In fact, extensibility has precise mathematical definitions, which are expressed in the form of equations. These equations point out the reasons why the system cannot be extended, but also the reasons why they should be extended. The Universal Extension Law (Universal Scalability Laws) reveals and quantifies the extensibility features of the system. It explains the scaling problem through two fundamental costs: serialization and Crosstalk (Crosstalk).

Parallel processing requires that serialization be aborted, which limits their extensibility. Similarly, if parallel processing requires constant dialogue with each other to coordinate work, it restricts each other. To avoid serialization and crosstalk, the application is better extended. What have these been translated into within MySQL? The results are different. However, some cases should avoid locking in a particular line. As mentioned in the 3rd trick, the reason for poor queue extensibility is this.

6, do not pay too much attention to configuration

The database administrator spends a lot of time tuning the configuration. The results of the adjustment are usually not greatly improved, but sometimes damage is caused. I have found that many "optimized" servers often have crashes, low memory, and poor performance when performing a slightly higher-intensity operation.

While MySQL's default settings at delivery are heavily outdated, you don't need to configure each item. It is best to make basic corrections and set adjustments as needed. With 10 options adjusted correctly, you can give your server 95% maximum performance. In many cases, we do not recommend the so-called adjustment tools because they simply provide a general set of settings that does not make any sense to a particular case. Some tools even contain dangerous and faulty device code.

　　

7, attention to paging query

Paged query application can cause server performance to drop greatly. These apps display search results on a Web page and then jump to the page with a link. Typically, these applications cannot use indexes for aggregation and categorization, but instead use the limit and offset statements, which causes a significant increase in server workloads and discards rows. Optimization options are often found in the user interface. Overrides display the number of pages in the results, and links to each page, respectively. This allows you to display only the links to the next page. You can also prevent the finder from browsing pages that are too far from the homepage.

　　

8, save statistics, improve alarm valve value

Monitoring and alerting is essential, but how is the monitoring system handled? When they publish false alarm messages, the system administrator sets the email filtering rules to stop these noises. Soon your surveillance system will be completely useless. Personally, it should be monitored in two ways: capture indicators and alarms. It's important to capture and save the metrics as much as possible, because when you try to figure out what adjustments you need to make in your system, you're glad you saved them. If there's a strange problem on a given day, you'll be happy to be able to draw a graph of the changes in the server workload.

　　

9, understand the index of the three major rules

The index is probably the most misunderstood item in the database. Because they work in many ways, this leads to confusion about how indexes work and how servers use them. It will take a lot of effort to get it straight. When properly designed, indexes are primarily used in databases to achieve the following three important purposes:

1) They let the server look for adjacent row groups instead of individual rows. Many people believe that the purpose of an index is to look for a single row, but finding a single line results in a slow disk operation at any time. Looking for a row group is much better, and it's more appealing than looking for a row at a time.

2) They let the server avoid sorting the results in the desired reading order, which is expensive to sort. Read rows faster in the desired order.

3) They are able to satisfy all queries from an index, fundamentally avoiding the need to access the form. This is called overwriting an index or index query.

If you can design indexes and queries that match these three rules, your query speed will increase dramatically.

　　

10, the use of peer expertise

Don't stand alone. It's great if you're struggling to think about a problem and start making smart solutions. In 20 times, 19 problems will be solved smoothly. But there will be a time when you are overwhelmed, resulting in a lot of money and time, precisely because the solution you are trying is just plausible.

The significance of creating a MySQL-related resource network is far greater than the Toolset and troubleshooting guide. Many experienced professionals are hidden in forums and quiz sites. Meetings, exhibitions and local users ' collective activities will give us the opportunity to gain new insights and connect with our peers, which can be very helpful at a critical moment.

Key 10 MySQL performance optimization tips