High IO load source positioning _ MySQL

Locate sources with high IO load bitsCN.com

Preface:

In general O & M work, we often encounter such a scenario where the server's IO load is very high (util in iostat ), however, the source process of the I/O load and the source file cannot be quickly located, and corresponding policies cannot be implemented to solve the problem.

This phenomenon is more common in MySQL. before MySQL 5.6 (performance_schema provides io instrument), we can only guess the high IO caused by MySQL, but cannot identify the file load.

For example, ibdata Flushing? Or random reading of cold-door ibd?

This article will introduce a simple process for locating high I/O loads.

Tool preparation:

Iotop: http://guichaz.free.fr/iotop/

Pt-ioprofile: http://www.percona.com/downloads/percona-toolkit/2.2.1/

Step 1: View iostat

Iostat-x 1: Check the I/O Status. you can see that the disk dfa has a high I/O load. next we will locate the specific load source.

Step 2: Locate the load source process in iotop

Iotop is essentially a python script that obtains thread IO information from proc and summarizes it.

It can be seen that most of the IO sources come from the mysqld process. Therefore, we can determine that the dfa load source is a database.

Step 3 pt-ioprofile locate the load source file

The principle of pt-ioprofile is to append a strace process to a pid for IO analysis.

The following is a warning from the official website:

However, it works by attachingStraceTo the process usingPtrace (), Which will make it run very slowlyStraceDetaches. In addition to freezing the server, there is also some risk of the process crashing or encounter badly afterStraceDetaches from it, or indeedStraceNot detaching cleanly and leaving the process in a sleeping state. As a result, this shocould be considered an intrusive tool, and shocould not be used on production servers unless you are comfortable with that.

Find the process ID corresponding to the mysqld process through ps aux | grep mysqld, and check which file occupies the most I/O time through pt-ioprofile.

By default, this tool displays the time occupied by IO.

It is more useful for locating problems through IO throughput. Use the -- cell = sizes parameter to display the result in B/s mode.

We can see that the main source of IO load is sbtest (sysbench's IO bound OLTP test ).

In addition, the stress is mainly concentrated on reading and retrieval.

BitsCN.com