Query analysis is a fantastic path in the pursuit to achieve high performance. It’s also probably the most repeated part of a DBA’s daily adventure. For most of us, the weapon of choice is definitely pt-query-digest, which is one of the best tools for slow query analysis out there.
Why not use pt-query-digest? Well, sometimes getting the slow log can be a challenge, such as with RDS instances or when your database is running as part of a DBaaS, which is a common practice in certain organizations.
In those cases it’s good to have an alternative. And in this case, the chosen one is the Performance Schema. We have already talked about the events_statements_* tables; however, this is the moment for the events_statements_summary_by_digest. In this table each row summarizes events for given schema/digest values (note that before MySQL 5.6.9, there is no SCHEMA_NAME column and grouping is based on DIGEST values only).
In order for MySQL to start to aggregate information in summary tables, you must verify that the consumer statement_digest is enabled.
Now, the most straightforward way to get data is to simply query the table, like this:
1 2 3 4 5 6 7 8 | SELECT SCHEMA_NAME, digest, digest_text, round(sum_timer_wait/ 1000000000000, 6), count_star FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10; |
This will show you the picture of volume and frequency of SQL statements in your server. As simple as that. But there are some caveats:
- Statements are normalized to a digest text. Instead of seeing a query like SELECT age FROM population WHERE id BETWEEN 153 AND 153+69 you will have the fingerprint version: SELECT age FROM population WHERE id BETWEEN ? AND ? + ?
- The events_statements_summary_by_digest table has a limited maximum number of rows (200 by default, but MySQL 5.6.5 can be modified with the performance_schema_digests_size variable). As a consequence, when the table is full, statement digest values that have no already existing row will be added to a special “catch-all” row with DIGEST = NULL. In plain English: you won’t have meaningful info for those statements.
To solve the first issue, we can use the events_statements_history table to get complete queries for all the digests. I chose not to use events_statements_currents because of the short life the rows have on that table. With history, there are more chances to get more queries in the same amount of time.
Now, when using pt-query-digest, the first step is always to collect a representative amount of data, commonly from the slow log, and then process. With Performance Schema, let’s collect a representative amount of complete queries so we can have examples for every aggregated statement.
To address the second issue, we just need to TRUNCATE the events_statements_summary_by_digest table. This way the summary will start from scratch.
Since Performance Schema is available on all the platforms supported by MySQL, I chose to run the tests on an Amazon RDS MySQL instance. The only thing I didn’t like is that P_S is disabled by default on RDS and to enable it requires an instances reboot. Other than that, everything is the same as in a regular instance.
The steps are:
- Enable the events_statements_history consumer
- Create a MEMORY table to hold the data
- Truncate tables to have a fresh start
- Create a MySQL EVENT that will fill the table with data
- Once the event has ended, get the query digest.
The table schema is the following:
1 2 3 4 5 | CREATE TABLE IF NOT EXISTS percona.digest_seen (schema_name varchar(64) DEFAULT NULL, digest varchar(32) DEFAULT NULL, sql_text varchar(1024) DEFAULT NULL, PRIMARY KEY USING BTREE (schema_name,digest)) engine=memory; |
The original SQL_TEXT field from the events_statements_history table is defined as longtext, but unless you are using Percona Server (5.5+), you won’t be able to use longtext on a memory table. This is possible in Percona Server because the Improved Memory Engine permits the use of Blob and Text fields on the Memory Storage Engine. The workaround is to define that field as a varchar 1024. Why 1024? That’s another requirement from the table: The SQL_TEXT is fixed at 1024 chars. It’s only after MySQL 5.7.6 that the maximum number of bytes to display can be modified by changing the performance_schema_max_sql_text_length system variable at server startup.
Also, since we are going to use EVENTS on RDS, the “event_scheduler” variable has to be set to ON. Luckily, it is a dynamic variable so there’s no need to reboot the instance after modifying the Parameter Group. If using a non-RDS, it’s enough to execute “SET GLOBAL event_scheduler = ON;”
Here is the complete list of steps:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history'; SET SESSION max_heap_table_size = 1024*1024; CREATE DATABASE IF NOT EXISTS percona; USE percona; CREATE TABLE IF NOT EXISTS percona.digest_seen (schema_name varchar(64) DEFAULT NULL, digest varchar(32) DEFAULT NULL, sql_text varchar(24) DEFAULT NULL, PRIMARY KEY USING BTREE (schema_name,digest)) engine=memory; TRUNCATE TABLE percona.digest_seen; TRUNCATE performance_schema.events_statements_summary_by_digest; TRUNCATE performance_schema.events_statements_history; CREATE EVENT IF NOT EXISTS getDigest ON SCHEDULE EVERY 1 SECOND ENDS CURRENT_TIMESTAMP + INTERVAL 5 MINUTE ON COMPLETION NOT PRESERVE DO INSERT IGNORE INTO percona.digest_seen SELECT CURRENT_SCHEMA, DIGEST, SQL_TEXT FROM performance_schema.events_statements_history WHERE DIGEST IS NOT NULL GROUP BY current_schema, digest LIMIT 50; |
The event is defined to be run immediately, once per second, for 5 minutes. After the event is complete, it will be deleted.
When the event is done, we are in position to get the query digest. Simply execute this query:
1 2 3 4 5 6 7 8 9 10 11 | SELECT s.SCHEMA_NAME, s.SQL_TEXT, ROUND(d.SUM_TIMER_WAIT / 1000000000000, 6) as EXECUTION_TIME, ROUND(d.AVG_TIMER_WAIT / 1000000000000, 6) as AVERAGE_TIME, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest d LEFT JOIN percona.digest_seen s USING (digest) WHERE s.SCHEMA_NAME IS NOT NULL GROUP BY s.digest ORDER BY EXECUTION_TIME DESC LIMIT 10; |
The order by is similar to the one pt-query-digest does by default, but it could be any one you want.
The output is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------------+------------+ | SCHEMA_NAME | SQL_TEXT | EXECUTION_TIME | AVERAGE_TIME | COUNT_STAR | +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------------+------------+ | percona | UPDATE population SET age=age+1 WHERE id=148 | 202304.145758 | 1.949487 | 103773 | | percona | SELECT age FROM population WHERE id BETWEEN 153 AND 153+69 | 488.572609 | 0.000176 | 2771352 | | percona | SELECT sex,age,estimate2012 FROM population WHERE id BETWEEN 174 AND 174+69 ORDER BY sex | 108.841575 | 0.000236 | 461412 | | percona | SELECT census2010 FROM population WHERE id=153 | 62.742239 | 0.000090 | 693526 | | percona | SELECT SUM(estimate2014) FROM population WHERE id BETWEEN 154 AND 154+69 | 44.940020 | 0.000195 | 230810 | | percona | SELECT DISTINCT base2010 FROM population WHERE id BETWEEN 154 AND 154+69 ORDER BY base2010 | 33.909593 | 0.000294 | 115338 | | percona | UPDATE population SET estimate2011='52906609184-39278192019-93190587310-78276160274-48170779146-66415569224-40310027367-70054020251-87998206812-01032761541' WHERE id=154 | 8.231353 | 0.000303 | 27210 | | percona | COMMIT | 2.630153 | 0.002900 | 907 | | percona | BEGIN | 0.705435 | 0.000031 | 23127 | | | SELECT 1 | 0.422626 | 0.000102 | 4155 | +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------------+------------+ 10 rows in set (0.10 sec) |
Finally, you can do some cleanup:
1 2 3 4 | DROP event IF EXISTS getDigest; DROP TABLE IF EXISTS percona.digest_seen; SET SESSION max_heap_table_size = @@max_heap_table_size; UPDATE performance_schema.setup_consumers SET ENABLED = 'NO' WHERE NAME = 'events_statements_history'; |
Summary: Performance Schema is doing the query digest already for you. It is just a matter of how to access the data in a way that suits your requirements.
I also noticed that P_S is disabled on Amazon Aurora. A quick look over the various console settings didn’t reveal a way to enable it.
Hello !
1. Is possible to use SHOW PROFILES and store output from it into some variable/ or table, like in example:
SHOW PROFILES INTO @myOutput;
2. I know that now perfromance scheema is preferred instead of “SHOW PROFILE” but:
a.) do I need to change performance_schema=ON and restart MySql server ? With using SHOW PROFILE it is not needed AFAIK.
b.) is possible to get only queries from currect session using performance_schema and queries will be numbered starting at 1 ? With using SHOW PROFILE we have queries only for session and starting from queryid=1
Jakub