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

This article focuses on "MySQL performance bottleneck troubleshooting positioning how to solve", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the problem of MySQL performance bottleneck.

Example analysis of MySQL performance bottleneck troubleshooting location

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, after we check at the OS level to confirm that we have logged in to the server, our goal is to first confirm which processes are causing

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.

You can execute the instruction w or sar-Q 1 to view the load data, for example:

[yejr@imysql.com:~] # w 11:52:58 up 702 days, 56 min, 1 user, load average: 7.20, 6.70, 6.47USER TTY FROM LOGIN@ IDLE JCPU PCPU WHATroot pts/0 1.xx.xx.xx 11:51 0.00s 0.03s 0.00s w

Or the observation of sar-Q:

[yejr@imysql.com:~] # sar-Q 1Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com) 01 PM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 blocked02:51:19 PM 2016 _ x86 pound 6424 CPU 02:51:18 PM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 blocked02:51:19 PM 4 2305 6.41 6.98 7.12 302 PM 2 2301 6.41 6.98 7.12 402:51:21 PM 0 2300 6.41 6.98 7.12 502:51:22 PM 6 2301 6.41 6.98 7.12 802:51:23 PM 2 2290 6.41 6.98 7.12 8

Load average roughly indicates how many tasks are waiting in the queue in the current CPU. The more you wait, the higher the load. On the server running the database, if the load value is more than 5, it is already relatively high.

There may also be a number of reasons for high load:

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 is:

[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.

Moreover, from the statistical results of the Cpu (s) line, we can see that the values of% us and% wa are higher, indicating that the current larger bottleneck may be on the CPU consumed by the user process and disk Imax O wait.

Let's first analyze the situation of disk Ibank O.

Execute sar-d to confirm whether disk Ibank O is really large:

[yejr@imysql.com:~] # sar-d 1Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com) 01 AM dev8-0 5338.00 162784.00 1394.00 30.76 5.24 0.98 0.19 100.0011 1Linux 54 AM dev8-0 5134.00 148032.00 32365.00 11:54:32 AM dev8 35.14 6.93 1.34 0.19 100.1011 AM dev8 54 AM dev8-0 5233.00 161376.00 996.00 31.03 9.77 1.88 0.19 100.0011 AM dev8 54 AM dev8-0 4566.00 139232.00 1166.00 30.75 5.37 1.18 0.22 100.0011 4665.00 145920.00 630.00 31.41 5.94 1.27 0.21 100.0011 Flux 54 AM dev8-0 4994.00 156544.00 546.00 31.46 7.07 1.42 0.20 100.00

Then use iotop to confirm which processes consume the most disk Imando resources:

[yejr@imysql.com:~] # iotopTotal DISK READ: 60.38M yejr@imysql.com:~ s | Total DISK WRITE: 640.34 K basedir=/usr/local/m~og_3320/mysql.sock s TID PRIO USER DISK READ DISK WRITE SWAPIN IO > COMMAND16397 be/4 mysql 8.92M hand s 0.00BAccord s 0.00% 94.77% mysqld-- basedir=/usr/local/m~og_3320/mysql.sock-port=3320 7295 be/4 mysql 10.98M Universe s 0.00BUnip s 0.00% 93.59 % mysqld-- basedir=/usr/local/m~og_3320/mysql.sock-- port=332014295 be/4 mysql 10.50 M basedir=/usr/local/m~og_3320/mysql.sock s 0.00 Bqure s 0.00% 93.57% mysqld-- basedir=/usr/local/m~og_3320/mysql.sock-- port=332014288 be/4 mysql 14.30 M Accord s 0.00 Bhand s 0.00% mysqld-- basedir=/usr/local/m~og_3320/mysql.sock-- port=332014292 be/4 mysql 14. 37 M mysqld 0.00 Bhand s 0.00% 91.23% mysqld-basedir=/usr/local/m~og_3320/mysql.sock-port=3320

As you can see, an instance with a port number of 3320 consumes a lot of disk Imax O resources, so let's see what queries are running in this instance.

2. Check and confirm at MySQL level

First, take a look at what queries are currently running:

[yejr@imysql.com (db)] > mysqladmin pr | grep-v Sleep+----+----+-+----+ -+ | Id | User | Host | db | Command | Time | State | Info | + -+- -+ | 25 | x | 10.x:8519 | db | Query | 68 | Sending data | select max (Fvideoid) from (select Fvideoid from t where Fvideoid > 404612 order by Fvideoid) T1 | 26 | x | 10.x:8520 | db | Query | 65 | Sending data | select max (Fvideoid) from (select Fvideoid from t where Fvideoid > 484915 order by Fvideoid) T1 | 28 | x | 10.x:8522 | db | Query | Sending data | select max (Fvideoid) from ( Select Fvideoid from t where Fvideoid > 404641 order by Fvideoid) T1 | | 27 | x | 10.x:8521 | db | Query | Sending data | select max (Fvideoid) from (select Fvideoid from t where Fvideoid > 324157 order by Fvideoid) T1 | 36 | x | 10.x:8727 | db | Query | Sending data | select max (Fvideoid) from (select Fvideoid from t where Fvideoid > 324346 order by Fvideoid) T1 | + -+

You can see that there are many slow queries that have not been completed, and it can also be found from slow query log that this kind of SQL occurs very frequently.

This is a very inefficient way to write SQL, resulting in the need to scan the entire primary key, but you only need to get a maximum value, as you can see in slow query log:

Rows_sent: 1 Rows_examined: 5502460

It is very inefficient to scan more than 5 million rows of data each time, but only to read a maximum value.

After analysis, the SQL can be completed in single-digit milliseconds with a simple modification, but it originally took 150-180 seconds to complete, raising the N-th power.

The method of transformation is to sort the query results in reverse order and get the first record. The original practice is to sort the results in positive order and take the last record, Khan.

At this point, I believe that everyone on the "MySQL performance bottleneck troubleshooting positioning how to solve" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.