Beware of Unstable Query Digests in MySQL

January 20, 2020

Author

Peter Zaitsev

Monitoring

MySQL

Percona Software

Share this Post:

Unstable Query Digests in MySQL If you’re using MySQL’s Performance Schema you may use “query digests” as IDs to identify specific query patterns in the events_statements_summary_by_digest Performance Schema Table.

You might assume these hashes are stable between different versions, so, for example, when upgrading from MySQL 5.7 to MySQL 8, you can compare the query response time and other execution details for the same hashes as part of your upgrade process and have confidence that queries run the same (or better) after the upgrade. Unfortunately, you can’t.

For some reason, the hashing algorithm is different between MySQL 5.6, MySQL 5.7, and MySQL 8 even for the most trivial queries:

MySQL 5.6

mysql> select digest,digest_text  from events_statements_summary_by_digest where digest_text='commit';<br>+----------------------------------+-------------+<br>| digest                           | digest_text |<br>+----------------------------------+-------------+<br>| 83e158a7b21d1f3f1579455e446139ec | COMMIT      |<br>+----------------------------------+-------------+<br>

1

mysql> select digest,digest_text from events_statements_summary_by_digest where digest_text='commit'; +----------------------------------+-------------+ | digest | digest_text | +----------------------------------+-------------+ | 83e158a7b21d1f3f1579455e446139ec | COMMIT | +----------------------------------+-------------+

MySQL 5.7

mysql> select digest,digest_text  from events_statements_summary_by_digest where digest_text='commit';<br>+----------------------------------+-------------+<br>| digest                           | digest_text |<br>+----------------------------------+-------------+<br>| 20381e49cdd9c4f151494275cec46e08 | COMMIT      |<br>+----------------------------------+-------------+<br>1 row in set (0.00 sec)<br>

1

mysql> select digest,digest_text from events_statements_summary_by_digest where digest_text='commit'; +----------------------------------+-------------+ | digest | digest_text | +----------------------------------+-------------+ | 20381e49cdd9c4f151494275cec46e08 | COMMIT | +----------------------------------+-------------+ 1 row in set (0.00 sec)

MySQL 8.0

mysql> select digest,digest_text  from events_statements_summary_by_digest where digest_text='commit';<br>+------------------------------------------------------------------+-------------+<br>| digest                                                           | digest_text |<br>+------------------------------------------------------------------+-------------+<br>| f2b9be5f0f32ca7507e4ddf133cd2fad0993a1c26fde9caa6f067b1af2b7603c | COMMIT      |<br>+------------------------------------------------------------------+-------------+<br>1 row in set (0.00 sec)<br>

1

mysql> select digest,digest_text from events_statements_summary_by_digest where digest_text='commit'; +------------------------------------------------------------------+-------------+ | digest | digest_text | +------------------------------------------------------------------+-------------+ | f2b9be5f0f32ca7507e4ddf133cd2fad0993a1c26fde9caa6f067b1af2b7603c | COMMIT | +------------------------------------------------------------------+-------------+ 1 row in set (0.00 sec)

MySQL 8.0 is employing a twice as long digest (in my opinion needlessly so, as 128bit already had a very low chance of collision) where MySQL 5.6 and MySQL 5.7 just have different hashes of the same length.

As a side effect of this behavior, you will see duplicate queries in Percona Monitoring and Management (PMM) if running multiple major MySQL versions and Performance Schema is used as a data source:

Unstable Query Digests in MySQL

You can clearly see three “commit” queries in this list, as well as some other likely duplicates.

If you want to avoid this problem in Percona Monitoring and Management, you can use Slow Query Log as your Query Data Source, which is best used with Query Sampling available in Percona Server for MySQL.