MySQL statement performance problem locating--from SQL statement to disk IO check

Write in front: This article only for the IO causes MySQL performance problem localization, other such as CPU, mysql parameter configuration, program itself and other issues need to be further supplemented.

Background: A SQL Build Table statement was run for 15 seconds: (

STEP1:

Open profiling

• SET profiling = 1;

Shut down

• SET profiling = off;

Find the SQL statement ID that is running slow

• show profiles;

View time-consuming and specific quantitative data such as SQL statement Cpu/io

• Show Profile CPU,SWAPS,BLOCK IO,MEMORY,CONTEXT Switches,IPC,PAGE faults,SOURCE forQuery the;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" class= " Alignnone size-full wp-image-542 "src=" http://10.1.2.225/wp-content/uploads/2016/03/Image.png "alt=" image "Width=" "height=" 456 "style=" border:0px;vertical-align:middle;margin:5px 20px 20px 0px;height:auto; "/>

Conclusion:

From the visible CPU time is not much, but the IO operation takes up most of the time. Let's find out which processes on the server are consuming IO resources.

STEP2:

View Server Linux IO:

• iostat -x 1

650) this.width=650; "class=" Alignnone size-full wp-image-543 "src=" http://10.1.2.225/wp-content/uploads/2016/03/ Image1.png "alt=" Image "width=" 752 "height=" 447 "style=" border:0px;vertical-align:middle;margin:5px 20px 20px 0px; Height:auto; "/>

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Find out which processes are doing the crazy IO operation:

• iotop

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" class= " Alignnone size-full wp-image-544 "src=" http://10.1.2.225/wp-content/uploads/2016/03/Image2.png "alt=" image "Width=" 665 "height=" 391 "style=" border:0px;vertical-align:middle;margin:5px 20px 20px 0px;height:auto; "/>

Conclusion:

It is found that the disk reads and writes are high, and the SVCTM and await are very different.

The value of 1,await is much higher than the value of SVCTM, which means that the I/O queue waits too long for the applications running on the system to become slower.

The value of the 2,%util key is also an important metric for measuring disk I/O,%util is close to 100%, indicating that there are too many I/O requests from the disk and that the I/O system is already full-load and that the disk may have bottlenecks.

Therefore, it is true that disk high IO operations cause SQL performance issues. And is MySQL itself io particularly high.

SETP3:

Use Pt-ioprofile to find out which files have high IO operations. The command is as follows

• Yum Install percona-toolkit-2.2.17-1.noarch.rpm

• PT-Ioprofile-- Profile-PID=44937 --Cell=Sizes

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" class= " Alignnone size-full wp-image-545 "src=" http://10.1.2.225/wp-content/uploads/2016/03/Image3.png "alt=" image "Width=" 1154 "height=" 399 "style=" border:0px;vertical-align:middle;margin:5px 20px 20px 0px;height:auto; "/>

Summary: As can be seen in the database, some table read and write operations is the main cause of the high IO has been a major reason, and seriously affect the SQL query performance.

After you navigate to a specific table or file, you can take action accordingly. You can replace disk devices, use faster SSD disks, combine program read and write rules, select the appropriate disk file system type, or optimize from the program itself.

This article is from the "Scattered People" blog, please be sure to keep this source http://zouqingyun.blog.51cto.com/782246/1755311

MySQL statement performance problem locating--from SQL statement to disk IO check