Pt-ioprofile analysis to see the true IO situation of MySQL

When it comes to system tuning for IO-intensive applications, we often need to know the system performance and usage, such as the system CPU memory IO Network, combined with the application's own traffic, and MySQL performance indicators to synthesize the analysis. For example, we divide the system pressure into three stages: from the client side to the Web server, to MySQL.

1. Customer Volume: We can access the log from the Web app, view the traffic (usually record the time),

2. System indicators: Compare Dstat, Iostat/mpstat pidstat, etc. to collect the corresponding system performance indicators,

3. mysql: Use MySQL status, or Mycheckpoint and other tools to collect MySQL cache, query and other data.

But the problem, we can easily collect the system layer, the device layer of IO data, but the lack of a thoughtful tool to tell you how many files opened by the application, the file read and write ratio, the number of Fsync executed, random or sequential read and write, in addition, MySQL is a large and well-designed system, Using some of the columns of the scheme, such as table cache, thread cache and so on to enhance the IO, we are relatively easy to get his memory, hit rate, the number of files, but it is not intuitive to know exactly how much data it read and write to the physical IO device.

Ioprofile is a tool that provides intuitive quantitative data to describe the real-world read and write volume of a process to an IO device.

Because the implementation is injected into the thread using Strace, sudo is required at run time, as in this way:

sudo./pt-ioprofile-p 8534-c Count

sudo./pt-ioprofile-p 8534-c Sizes

Thursday, April 23, 2015 -: -: *csttracing Process ID8534Total Read Pwrite Fsync open close filename613878     613878          0          0          0          0/redmine/mysql/data/mycheckpoint/sv_diff.frm406924     406924          0          0          0          0/redmine/mysql/data/mycheckpoint/sv_sample.frm18432          0      18432          0          0          0/redmine/mysql/data/Ib_logfile13029       3029          0          0          0          0/redmine/mysql/data/mycheckpoint/custom_query_view.frm

sudo./pt-ioprofile-p 8534-c Times

+:8534total     pwrite      fsync filename   0.100162   0.000271   0.099891 /redmine/mysql/data/ibdata1   0.003826   0.000000   0.003826 /redmine/mysql/data/ib_logfile0

sudo./pt-ioprofile-p 8534-c sizes-g filename

When someone uses ioprofile in a production environment, there is a phenomenon that causes MySQL to hang, although it was 4 years ago, please use it carefully.

What are the indicators of read write Fread fwrite Fsync in the above data output respectively? How much is normal?

Read/write/fsync:

1. Linux underlying operations;

2. Kernel invocation, which involves the switching of the process context, that is, the conversion of the user state to the kernel mentality, which is a performance-consuming operation.

Fread/fwrite/fflush:

1. The IO stream operation specified in the C language standard, based on the Read/write/fsync
2. At the user level, a layer of buffering is added to reduce the number of kernel calls, but an additional memory copy is added.

Relationship See:

1. For the input device, the call Fsync/fflush will empty the corresponding buffer and the data will be discarded;

2. For output devices or disk files, Fflush only guarantees that the data reaches the kernel buffer and does not guarantee that the data will reach the physical device, so you should call Fsync (Fileno (stream) after calling Fflush to ensure that the data is stored on disk.

Reference:

1. http://blog.yufeng.info/archives/995

2. http://blog.csdn.net/ybxuwei/article/details/22727565

Pt-ioprofile analysis to see the true IO situation of MySQL