Use pt-stalk to diagnose MySQL Problems

Transient appearance on the MySQL server (5 ~ 30 seconds) when the performance fluctuates, it is difficult for general performance monitoring tools to grasp the fault site, and it is difficult to collect the corresponding fine-grained diagnosis information. In addition, if the frequency of such fluctuations is very low, for example, only once in a few days, it is hard for us to capture the scene and collect data. This is exactly the problem solved by pt-stalk.

Pt-stalk is part of Percona-Toolkit (formerly part of Aspersa ). After Percona-Toolkit is installed, you can use man pt-stalk to learn how to use the tool. This article describes a subset of man pt-stalk. We strongly recommend that you read man pt-stalk directly. Additionally, this document provides the pt-stalk sample command for your reference.

1. Use pt-stalkPt-stalk-collect-tcpdump-function status \-variable Threads_connected-threshold 2500 \-daemonize--user = root-password = YOURPASSWORD

The above command indicates to run the pt-stalk background (-daemonize) and monitor the Threads_connected STATUS value in show global status. If the value exceeds 2500, collects the performance and status information of the host and MySQL. Pt-stalk checks the status value every second. If the trigger conditions are met five times in a row, collection starts.

-Collect-tcpdump: In addition to collecting basic information, tcpdump will be used to collect the current network package. Similarly,-collect-gdb can be used.

2. How to connect pt-stalk to MySQL

In the preceding command, "--user = root-password = YOURPASSWORD" indicates that all parameters following "-" will be used for mysql and mysqladmin commands, make sure that you have the correct user name and password. The syntax in man pt-stalk is as follows:

SYNOPSIS Usage: pt-stalk [OPTIONS] [-- mysql options]

The preceding [OPTIONS] is the parameter used by pt-stalk, and [-- mysql options] is the parameter used by mysql and mysqladmin.

3. pt-stalk working status

Pt-stalk is a background program. By default, we can view the running status of pt-stalk through the file/var/log/pt-stalk.log:

Tail-f/var/log/pt-stalk.log plugin Check results: Threads_connected = 1641, matched = no plugin Check results: Threads_connected = 1641, matched = no 2012_06_05_00_00_37 Check results: Threads_connected = 1641, matched = no duplicate Check results: Threads_connected = 1641, matched = no duplicate Check results: Threads_connected = 1641, matched = no duplicate Check results: Threads_connected = 1641, matched = no duplicate Check results: threads_connected= 1641, matched = no

You can also specify a desired log directory and file through the-log parameter.

4. Performance and status data collected by pt-stalk

By default, pt-stalk stores the collected data in the/var/lib/pt-stalk directory. You can use the parameter-dest to specify the directory you want. The following is a data file triggered by pt-stalk after collection:

These data are raw data. We can analyze whether MySQL or the host encountered exceptions at that time.

5. trigger conditions for pt-stalk

In the preceding example, the trigger parameter is-function status-variable Threads_connected-threshold 2500, indicating that data collection is triggered when the MySQL status value Threads_connected exceeds 2500. You can also use Threads_running for common trigger conditions.

You can also use the result in show processlist to trigger the event, for example, "-function processlist-variable State-match statistics-threshold 10, if the value of the State column in show processlist is statistics and the number of threads exceeds 10, collection is triggered.

6. Some other useful parameters

-Iterations: this parameter specifies that pt-stalk will exit after several fault locations are collected. By default, pt-stalk runs continuously.

-Run-time: Specifies the collection parameter after collection is triggered.How Long. The default value is 30 seconds.

-Sleep: to prevent data collection from being triggered all the time, this parameter specifies that after a trigger, you must sleep for a while before continuing to observe and trigger the collection. The default value is 300 seconds.

-Interval: by default, pt-stalk checks the status data every second to determine whether to trigger collection. This parameter specifies the interval. The default value is 1 second.

-Cycles: by default, the collection is triggered only when the trigger condition is observed for five consecutive times. This parameter is controlled. The collection is triggered several times in a row. The default value is 5 times.

References: man pt-stalk; man percona-toolkit