A script for slow query log tracking based on pt-query-digest

A script for slow query log tracking based on pt-query-digest

Percona-toolkit is called MySQL DBA's "Switzerland ***", and its strength is self-evident. The slow query and analysis tool "pt-query-digest" provides many additional attributes, such as flexible filters, compared with the official "mysqldumpslow, database and table-based analysis and sorting.

Today, we will share a small script that automatically tracks MySQL slow query logs based on "pt-query-digest" and emails, and avoid repeated prompts for SQL statements of the same type.

Directly install the script (as long as percona-toolkit is installed, this script can be said to be silly, of course, it is best to take a look at the official documentation for a deeper understanding)

#! /Bin/shdate_ago = 'date + % Y-% m-% d \ % H: % M: % S -- date = '15minago '# obtain the time 15 minutes ago, this script is executed every 15 minutes in my crontab. aliasmysql_con = 'mysql-uuser-ppwd '# The following two rows will create query_history and query_review tables in the test Library. pt- query-digest -- historyu = user, p = pwd, D = test/mysqldata/dev-env-slow.log -- no-reportpt-query-digest -- reviewu = user, p = pwd, D = test/mysqldata/dev-env-slow.log -- no-reportrm-f/tmp/slow_query_inc2>/dev/null # Clear the file that stores the last result # query the checksumforiin time later than 15 minutes ago 'mysql _ con-N-s-e "SELECTchecksumFROMpercona_schema.query_reviewwherefirst_seen = last_seenandfirst_seen> '$ date_ago'" 2>/dev/null '; do # query related information from the query_history table and make some adjustments to the output mysql_con-e "SELECTsampleasStatment, ts_maxasTime, query_time_pct_95asQuery_Time, Rows_sent_pct_95asRows_Sent, outputs = '$ I' \ G "2>/dev/null | sed's/^ [\ t] * // G' | awk-F: '{print $1 ":" $2}'>/tmp/slow_query_incdoneif [-s/tmp/slow_query_inc]; then #/tmp/slow_query_inc. The file contains content before mail-s 'slow _ loginlast15min ---- fromDev '[email protected]

Then set the script to run every 15 minutes in the crontab. If a new slow query appears, you will receive an email, such:

Another point: it is best to start from the development environment in the SQL monitoring stage and kill slow SQL statements in the early stage.