How to analyze MySQL slow query log using pt-query-digest

-
Sometimes you need to analyze slow queries from a MySQL database server that has been deployed as a Amazon RDS service. The slow queries are going into the table slow_log inside the mysql system database. This will happen when you have enabled logging of slow queries and log_output system variable is set to TABLE.

We will use pt-query-digest - a percona tool used to analyze MySQL queries from logs, process-list and tcpdump.

To export the slow queries, run the following command from a machine/jump-box that is allowed to connect to the target database and has mysql client installed. The data is exported into a MySQL slow-log format, which pt-query-digest can analyze:

 mysql -h your_amazon_rds_endpoint -u your_master_user_name -p -D mysql -s -r -e "SELECT CONCAT( '# Time: ', DATE_FORMAT(start_time, '%y%m%d %H%i%s'), '\n', '# User@Host: ', user_host, '\n', '# Query_time: ', TIME_TO_SEC(query_time),  '  Lock_time: ', TIME_TO_SEC(lock_time), '  Rows_sent: ', rows_sent, '  Rows_examined: ', rows_examined, '\n', sql_text, ';' ) FROM mysql.slow_log" > /tmp/mysql_slow_log.log

It will ask for password, Please enter the same and press Enter key.

The -s (–silent) option is used to suppress echoing of the query.

The -r (–raw) option is used to disable character escaping making \n into an actual newline, otherwise it’s echoed as ‘\n’.
  • Please substitute values for host and user according to your environment. The used account must have the privilege to SELECT from mysql.slow_log.
  • If you are coping the above command then please take care of the quotes if they get messed up while copy-paste from web.
  • /tmp is just the location and mysql_slow_log.log is just the file name that I choose here. You can choose any other location and file name of your choice. Same will hold true for any other location and file name used in the article ahead.
Once the export is complete, run pt-query-digest to do the analysis. If you already have a slow queries log file then directly analyze the log via pt-query-digest.

A simple review command is: (considering file name and locations from above context)

pt-query-digest --limit 100% /tmp/mysql.slow_log.log > /tmp/query-digest.txt

The query-digest.txt file is now ready for review.

 NOTE

This article outline the general knowledge required to analyze slow log using percona toolkit.

If you have understood the concept then you can use the same process in future, even if the used tool or mysql components get changed.

References

Enable slow query log in MySQL 5.5 

Percona pt-query-digest Tool 

Exporting the mysql.slow_log table into slow query log format 

To read and understand the pt-query-digest output - Identifying the load with the help of pt-query-digest and Percona Server

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can be on disk or
Read more