Shulou(Shulou.com)05/31 Report--

How to analyze the location of MySQL performance bottleneck? for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Guide reading

Start with an on-site analysis of how to locate performance bottlenecks.

Investigation process

You receive an alarm message that the load of the MySQL instance at the backend of an online business is relatively high, so log in to the server to check and confirm.

1. First of all, we check and confirm at the OS level.

After logging in to the server, our goal is to first identify which processes are currently causing high load, and where these processes are stuck and what is the bottleneck.

Generally speaking, the disk Imax O subsystem on the server is the most likely to become a bottleneck, because its read and write speed is usually the slowest. Even for today's PCIe SSD, its random I-map O read and write speed is not as fast as memory. Of course, there are a variety of reasons for the slow disk Iamp O, which needs to be confirmed.

In the first step, we usually look at the overall load. If the load is high, it is certain that all processes are slow.

Instructions can be executed

Some processes / services consume more CPU resources (services respond to more requests or there are some application bottlenecks)

A serious swap occurs (out of available physical memory)

A serious outage occurs (due to SSD or network reasons)

Disk IZAGO is slow (will cause CPU to wait for disk IGUBO request)

At this point, we can execute the following command to determine which subsystem the bottleneck lies in (horizontal view):

[yejr@imysql.com:~] # toptop-11:53:04 up 702 days, 56 min, 1 user, load average: 7.18,6.70, 6.47Tasks: 576 total, 1 running, 575 sleeping, 0 stopped, 0 zombieCpu (s): 7.7%us, 3.4%sy, 0.0%ni, 77.6%id, 11.0%wa, 0.0%hi, 0.3%si, 0.0%stMem: 49374024k total, 32018844k used, 17355180k free 115416k buffersSwap: 16777208k total, 117612k used, 16659596k free, 5689020k cached PID USER PR NI VIRT RES SHR S% CPU% MEM TIME+ COMMAND14165 mysql 2008822m 3.1g 4672 S 162.3 6.6 89839 cached PID USER PR NI VIRT RES SHR S 59 mysqld40610 mysql 20 025.6g 14g 8336 S 121.7 31.5280908 mysqld49023 mysql 20.16.9g 5.1g 4772S 4.6349409 mysqld

It is clear that the first two mysqld processes led to a high overall load.

And, as can be seen from the statistical results of the line Cpu (s),% us

The amount of data required to read and write at one time is too large, which leads to a large read and write value for disk I _ (max) O. For example, tens of thousands of rows of data or even more have to be read or updated in a SQL. It is best to find a way to reduce the amount of data to be read and written at one time.

There is no appropriate index in the SQL query to complete conditional filtering, sorting (ORDER BY), grouping (GROUP BY), data aggregation (MIN/MAX/COUNT/AVG, etc.), add an index or rewrite SQL

There are a large number of requests in an instant, which is generally as long as it can withstand the peak. To be on the safe side, it is necessary to improve the configuration of the server properly, in case the peak cannot be resisted, the avalanche effect may occur.

Because of the increase in load caused by some scheduled tasks, such as doing data statistical analysis and backup, this consumes a lot of CPU, memory and disk I / O, and is best executed on a separate slave server.

The server's own energy-saving strategy finds that the CPU will reduce the frequency when the load is low, and then increase the frequency automatically when the load increases, but it is usually not so timely, resulting in insufficient performance of CPU and can not resist sudden requests.

When using raid card, it is usually equipped with BBU (backup battery for cache module). In the early days, lithium battery technology is generally used, which requires regular charge and discharge (DELL server is once every 90 days, IBM is 30 days). We can discharge it in advance before the next charge and discharge time, but most of the new generation servers use capacitive batteries, so this problem does not exist.

The file system uses ext4 or even ext3 instead of xfs, which is likely to cause% util to reach 100% when the pressure is high, but iops can no longer be improved. In general, xfs can be greatly improved.

The kernel's io scheduler strategy uses cfq instead of deadline or noop, which can be adjusted directly online or greatly improved.

This is the answer to the question on how to analyze MySQL performance bottlenecks. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.