How to use pt-stalk to analyze the performance fluctuation of MySQL

Editor to share with you how to use pt-stalk to analyze the performance fluctuations of MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Brief introduction

When the performance of the MySQL server fluctuates for a short time (5 seconds and 30 seconds), the general performance monitoring tools are difficult to catch the fault site, so it is difficult to collect the corresponding fine-grained diagnostic information. In addition, if the frequency of such fluctuations is very low, for example, only once a few days, it is also very difficult for us to artificially grasp the scene and collect data. This is the problem that pt-stalk solves.

Parameters.

-function: set trigger conditions, including status, processlist and custom scripts. For more information, please see the trigger conditions section.

-dest: sets the storage directory for collect information. Default is / var/lib/pt-stalk/.

In addition, after setting the-dest / u01/mysql option to the mysql directory, pt-stalk will violently delete all files in this directory that have been modified for more than a certain date when cleaning logs that have expired.

Therefore, you must be careful when setting up the dest directory (if the setting is preferably a separate directory, rather than the same directory as mysql or other settings). -dest default value is

Check the source code of pt-stalk:

# Delete collect files which more than-retention-time days old. Find "$dir"-warn-type f-mtime + $retention_time-exec rm-f'{}'\

-iterations: this parameter specifies that pt-stalk will exit after collecting the fault site several times. The default pt-stalk runs all the time.

-run-time: after the collection is triggered, this parameter specifies how long the data will be collected. The default is 30 seconds, for example, show processlist will collect 30 times in a row.

-sleep: to prevent data collection from being triggered all the time, this parameter specifies that after a certain trigger, it must be sleep for a period of time before continuing to observe and trigger the collection. The default is 300 seconds.

-interval: by default, pt-stalk checks the status data every other second to determine whether the collection needs to be triggered. This parameter specifies the interval, which defaults to 1 second.

-cycles: by default, pt-stalk triggers collection only when it is observed five times in a row that the status value meets the trigger condition. This parameter control, need to meet the condition several times in a row, the collection is triggered, the default is 5 times.

-verbose: set the output level of log. The default is 2. The first run can be set to 3 to facilitate observation.

LEVEL PRINTS

=

0 Errors

1 Warnings

2 Matching triggers and collection info

3 Non-matching triggers

-plugin: similar to the-function parameter, you can specify a shell script that contains functions such as before_collect, after_collect, and so on.

Trigger conditions of pt-stalk

Three trigger conditions are set by parameter function:

Status

-function status-variable Threads_connected-threshold 2500, indicating that data collection is triggered when the MySQL status value Threads_connected exceeds 2500. Commonly used trigger conditions can also use Threads_running and so on.

Processlist

-function processlist-variable State-match statistics-threshold 10, indicating that collection is triggered if the number of threads in the State column in show processlist with a value of statistics exceeds 10.

Custom script

For shell scripts that contain the trg_plugin function, the trg_plugin function must return an int value, such as the following script, because the status of slow log is a cumulative value and cannot analyze the difference, so write a small script to get the difference to analyze the sharp thorns that suddenly appear in slow.

Function trg_plugin () {current_slow= `MySQL-uroot-pxxx-P5002-S/opt/tmp/mysql5002.sock-e "show global status like 'Slow_queries'"-B-N | awk' {print $2} '`current_timestamp= `date' +% s' `# when first execute,the last_timestamp is empty if [!-e Slow_queries] Then echo "$current_timestamp $current_slow" > Slow_queries echo "0" exit 0 fi last_timestamp= `cat Slow_queries | awk'{print $1} '`last_slow= `cat Slow_queries | awk' {print $2} '`echo "$current_timestamp $current_slow" > Slow_queries let diff_timestamp=$current_timestamp-$last_timestamp let diff_slow=$current_slow-$last_slow # echo "$diff_timestamp $diff_slow" if [$diff_timestamp-gt 11]; then echo "0" else echo $diff_slow fi}

Practical example

Pt-stalk-daemonize-function=slow_log_status.sh-variable=my_custemed_condition-cycles=1-threshold=2-interval=10-socket=/opt/tmp/mysql5002.sock-verbose=3

Collect the results

You can see that the information collected is very comprehensive, including a large number of system status (IO, CPU, network card) and a large amount of MySQL information (processlist, innodb status, variables, etc.)

The above is all the content of the article "how to use pt-stalk to analyze the performance fluctuations of MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!