Shulou(Shulou.com)06/01 Report--

This article mainly introduces "what are the MySQL performance optimization skills". In daily operation, I believe many people have doubts about MySQL performance optimization skills. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what are the MySQL performance optimization skills?" Next, please follow the editor to study!

1. Analyze workload

By analyzing the workload, you can find the most expensive queries in further adjustments. In this case, time is the most important thing. Because when you issue query instructions to the server, you pay little attention to anything other than how to complete the query quickly. The best way to analyze a workload is to use a tool such as MySQL Enterprise Monitor's query analyzer or Percona Toolkit's pt-query-digest.

These tools can capture queries executed by the server and list tasks based on response time in descending order. They top the most expensive and time-consuming tasks so you know what you need to focus on. Workload analysis tools aggregate similar queries in one row, allowing managers to view slow queries and queries that are fast but have been executed multiple times.

2. Understand the four basic resources

In terms of functionality, a database server requires four basic resources: CPU, memory, hard disk, and network. If any of these four resources is weak, unstable, or overloaded, it may result in poor performance of the entire database server. Understanding basic resources is critical in two specific areas: hardware selection and troubleshooting.

When choosing hardware for MySQL, you should make sure that all components with high performance are selected. These components match each other, and it is also important to achieve a reasonable balance between them. Usually, enterprises will choose fast CPU and hard drives for their servers, but there is a serious shortage of memory. In some cases, the cheapest way to significantly improve performance is to increase memory, especially for workloads that are constrained by 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 being used by the server.

Another example of achieving this balance is CPU. In many cases, if CPU is fast, MySQL performance is excellent because each query runs in a single thread 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 underperforming or overloaded. This knowledge can help you solve problems quickly.

3. Do not use MySQL as a queue

Queues and access schemes similar to queues will sneak into the application without your knowledge. For example, if you set a project status so that a specific Worker Process (worker process) can mark it before execution, you are inadvertently creating a queue. For example, mark e-mails as unsent, then send them, and finally mark them as sent.

Queues cause problems for two main reasons: they serialize workloads and prevent tasks from being processed in parallel. This causes the tasks in progress and the historical data previously processed in the work to be arranged in a single form in sequence. This not only increases the delay of the application, but also increases the load of MySQL.

4. Filter the results in the cheapest way

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

For example, suppose you calculate the area within a given radius of a geographic coordinate point. The first tool in the toolbox of many programmers is the spherical semi-positive formula to calculate the length of the sphere. The problem with this method is that the equation requires a lot of trigonometric operations and requires a CPU with strong computing power. Spherical semi-positive vector computing not only runs slowly, but also causes the utilization of machine CPU to soar. You can decompose the calculation before using the spherical semi-positive vector formula. Some decomposition calculations do not require trigonometric functions.

5. Identify two scalable death traps

Extensibility may not be as vague as you think. In fact, extensibility has a precise mathematical definition, which is expressed in the form of equations. These equations point out not only the reasons why the system cannot be extended, but also the reasons why they should be extended. The universal extension law (Universal Scalability Law) reveals and quantifies the scalability characteristics of the system. It explains the extension problem through two basic costs: serialization and crosstalk (Crosstalk).

Parallel processing requires that serialization must be aborted, which limits their extensibility. Similarly, if parallel processing requires always talking to each other to coordinate work, then it limits each other. In order to avoid serialization and crosstalk, the application is better extended. What are these translated into within MySQL? The results are not the same. However, some cases should avoid locking in specific lines. As mentioned in the third tip, this is the reason for the poor scalability of queues.

6. Don't pay too much attention to configuration

Database administrators spend a lot of time adjusting the configuration. The result of the adjustment usually does not improve much, on the contrary, it sometimes brings damage. I have found that many "optimized" servers often suffer from crashes, insufficient memory, and poor performance when performing slightly more intensive operations.

Although the default settings for MySQL at the time of delivery are seriously out of date, you don't need to configure everything. It is best to make basic corrections and setting adjustments as needed. There are 10 options that can be adjusted correctly to maximize the performance of the server at 95%. In many cases, we do not recommend so-called tuning tools because they only provide a rough setting and do not make any sense for a particular case. Some tools even contain dangerous and incorrect device codes.

7. Pay attention to paging query

Paging query applications will greatly degrade the performance of the server. These apps display search results on a web page and then jump to the corresponding page via a link. Typically, these applications cannot use indexes for aggregation and classification, but instead use LIMIT and OFFSET statements, which results in a significant increase in server workload and abandonment of rows. Optimization options are often found in the user interface. Instead of displaying the number of pages in the results, as well as links to each page. This allows you to display only the link to the next page. You can also prevent queries from browsing pages that are too far from the home page.

8. Save the statistical data and increase the alarm threshold

Monitoring and alarm are essential, but how is the monitoring system handled? When they post fake alarm messages, the system administrator sets e-mail filtering rules to stop the noise. Soon your surveillance system will be completely useless. Personally, it should be monitored in the following two ways: capture indicators and alarm. It's important to capture and save metrics as much as possible, because when you try to figure out what adjustments need to be made in the system, you'll be glad you saved them. If there is a strange problem one day, you will be glad that you have the ability to draw a graph of changes in server workload.

9. Understand the three rules of the index

The index is probably the most misunderstood item in the database. Because they work in many ways, people are often confused about how indexes work and how servers use them. It takes a lot of work to figure them out thoroughly. When properly designed, the index is mainly used in the database for the following three important purposes:

1) they let the server look for groups of adjacent rows instead of individual rows. Many people believe that the purpose of an index is to find a single row, but finding a single row can lead to disk operations at any time, which is very slow. It's much better to find a row group, which is more attractive than looking for one row at a time.

2) they allow the server to avoid sorting the search results in the desired row reading order, which is very expensive. Reading lines in the desired order will be faster.

3) they can satisfy all queries from an index, fundamentally avoiding the need to access the form. This is called an override index or index query.

If you can design indexes and queries that conform to these three rules, your query speed will be greatly improved.

10. Make use of the professional knowledge of your peers

Don't go it alone. If you're thinking about a problem and working on a sensible solution, that's great. In 20 times, 19 times the problem will be solved smoothly. But one of them will overwhelm you and cost you a lot of money and time, to be exact, because the solution you are trying is only plausible.

Creating a network of MySQL-related resources is much more important than the toolset and troubleshooting guidelines. Many experienced professionals are hidden in forums and Q & A sites. Conferences, exhibitions and local user group activities all provide us with opportunities to gain new insights and connect with our peers, which will be very helpful to you at critical moments.

At this point, the study of "what are the MySQL performance optimization skills" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.