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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.