Introduction when the MySQL server has a short (5-30 seconds) performance fluctuations, the general performance monitoring tools are difficult to grasp the fault site, it is difficult to collect the corresponding more granular diagnostic information. In addition, if the frequency of such fluctuations is very low, such as in a few days, we are also difficult to artificially seize the scene, collect data. This is the problem that Pt-stalk solves.
Parameter –function: Set trigger condition, including status, Processlist, custom script, see the Trigger Condition section in detail. –dest: Sets the storage directory for collect information, default/var/lib/pt-stalk/.
In
addition, after setting the –dest/u01/mysql option to the MySQL directory, Pt-stalk will violently delete all files that have been modified beyond a certain date in the directory when it cleans up the log that exceeds the time limit. Therefore, you must be careful when setting up the Dest directory (if the settings are best for a separate directory, not with MySQL or other settings in the same directory). –dest default value is to view 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 r M-f ' {} ' \; |
–iterations: This parameter specifies that Pt-stalk exits after several fault sites have been collected. The default pt-stalk is always running. –run-time: After the collection is triggered, this parameter specifies how long the data is collected. The default is 30 seconds, such as show Processlist will be collected 30 times in a row. –sleep: To prevent the collection of data from being triggered, this parameter specifies that after a certain trigger, sleep must continue to observe and trigger the collection. The default is 300 seconds –interval: By default, Pt-stalk checks the status data every second to determine if a collection needs to be triggered. This parameter specifies the interval time, which is 1 seconds by default. –cycles: The collection is triggered by default Pt-stalk only if the five-time status value is observed to meet the trigger condition. This parameter control, need to meet the conditions several times, collection is triggered, the default is 5 times. –verbose: Set the output level of log, default is 2, first run can be set to 3, convenient to observe the situation. Level prints===== =====================================0 Errors1 WARNINGS2 Matching triggers and collection in Fo3 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.
Pt-stalk trigger conditions three trigger conditions, through the parameter function settings:
- status–function status–variable threads_connected–threshold 2500, which indicates that data collection is triggered when the MySQL status value threads_connected more than 2500. Commonly used trigger conditions can also use threads_running and so on.
- processlist
–function processlist–variable state–match Statistics–threshold 10, indicating that show The collection is triggered by more than 10 threads with a value of statistics in the State column in Processlist. The
- Custom script
contains the shell script for the Trg_plugin function, and the trg_plugin function must return an int value, such as the following script, because the status of slow log is an accumulative value and cannot parse the difference. So write a small script to get the difference and analyze the sharp spikes that slow suddenly appear.
| 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 $} ' 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 $} ' last_slow= ' cat Slow_queries|awk ' {print $} ' 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];then echo" 0 "else echo $diff _slow fi} |
Practical examples
| 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 to see the information collected is very comprehensive, including a large number of system status (IO, CPU, network card) and a lot of MySQL information (processlist, InnoDB status, variables, etc.)
Analyze MySQL performance fluctuations using pt-stalk (RPM)
