August 30, 2014

Hidden columns of query_review_history table

You can use pt-query-digest to process a MySQL slow query log and store historical values for review trend analysis into query_review_history table. According to its official documentation you can populate many columns in that table but there are other important ones such as ‘user’, ‘host’, ‘db’ which are not included by default. I will explain how to implement this.

Also the documentation says:

Any columns not mentioned above are inspected to see if they follow a certain naming convention. The column is special if the name ends with an underscore followed by any of these MAGIC_history_cols values:

pct|avt|cnt|sum|min|max|pct_95|stddev|median|rank

If the column ends with one of those values, then the prefix is interpreted as the event attribute to store in that column, and the suffix is interpreted as the metric to be stored.
For a full list of attributes, see http://code.google.com/p/maatkit/wiki/EventAttributes

pt-query-digest works on events, which are a collection of key/value pairs called attributes. You’ll recognize most of the attributes right away: Query_time, Lock_time, and so on. You can just look at a slow log and see them. However, there are some that don’t exist in the slow log, and slow logs may actually include different kinds of attributes (for example, you may have a server with the Percona patches).
With creative use of –filter, you can create new attributes derived from existing attributes.

Note: the event attributes list could be outdated as Percona Toolkit was moved from Google Code to Launchpad.

So according the above, you can manually add and have the following useful columns populated in query_review_history table:
* user_max varchar(64) DEFAULT NULL – User who executed the query.
* host_max varchar(64) DEFAULT NULL – Client host which executed the query.
* db_max varchar(64) DEFAULT NULL – Current database that comes from USE database statements.
* hostname_max varchar(64) NOT NULL – Hostname of the server where slow log is located or was copied from.

The first three columns could be populated automatically when you run pt-query-digest but in order to set the hostname you need to specify it in the –filter option. For example:

$HOSTNAME will reflect the current machine hostname. Replace $HOSTNAME with the necessary hostname if you copied slow query log from the another server. Having hostname set might be useful for filtering queries in the database aggregating slow queries from multiple servers.

About Roman Vynar

Roman is a Remote DBA in Percona. His duties include handling remote DBA ops, developing and improving of monitoring tools. He also maintains Percona Monitoring Plugins project.

Speak Your Mind

*