Hello,
I am using pt-query-digest to gather statistics about the execution performance of my queries. My database contains about 200 tables, some of them are large (> 5 million records). I run a daily backup which dumps all my tables to the disk with mysqldump. I am using Oracle MySQL 5.6.30 on a Debian 8 server with the slow query log turned on for queries running longer than 1 second.
In addition to queries which are actually taking longer, I can see all the backup queries as well showing up in the slow query log. Query logs are log rotated and compressed on the server. I copy the slow query log files with scp to my local machine and analyze them with pt-query-digest version 3.0.2. I am aware, that pt-query-digest expects the queries to be sorted in the log files from the oldest queries in the top of the file to the newer ones in the bottom. I am also aware that the slow query log writes digests of the queries, subsuming similar queries together.
The problem is that the query count does not match my expectations how often specific queries are executed. I run pt-query-digest on a single log file for testing. The original query log contains the name of the Table LargeTable exactly two times. One time it’s the backup, the second time it is a regular query. The tool pt-query-digest reports 24 calls to that table.
Profile
Rank Query ID Response time Calls R/Call V/M Item
==== ================== =============== ===== ======== ===== ===========
1 0x081547A2812914DF 3746.2346 77.8% 24 156.0931 20… SELECT LargeTable
As I only provide the slow log as input, this result is not possible. After investigating for a bit, I discovered that pt-query-digest adds all backup calls for all other tables to the digest of the LargeTable. I purged the log file for testing from all other queries and could reproduce the behaviour. All backup queries starting with “SELECT SQL_NO_CACHE” are ending up in the digest of LargeTable and therefore bias the statistics for this particular query. This is not what I expected, as MySQL computes different digests for these queries, which I verified with the performance_schema tables.
Sample query:
SELECT query, digest FROM sys.x$statement_analysis WHERE query like “SELECT SQL_NO_CACHE %”;
As a result I receive a list of the backup queries, each having a unique hash. I would expect the same behaviour from the pt-query-digest tool. Is there an explaination for this? MySQLdump which I use for the backups inserts MySQL specific comments:
SELECT /*!40001 SQL_NO_CACHE */ * FROM LargeTable;
Could these be the cause for this undesired aggregations? In the pt-query-digest documentation, I could not find a hint that the digest hash is limited to a specific amount of characters. It just says “This ID is a hex version of the query’s checksum in the database.”, so I would expect that the same method is used when digesting queries from log files, where no server connection exists. Is there a recommended work around?
Kind regards,
Stefan