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:

Shell

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 LIMIT10;

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:

Shell

1

2

3

4

5

CREATE TABLE IFNOTEXISTS percona.digest_seen

(schema_name varchar(64)DEFAULTNULL,

digest varchar(32)DEFAULTNULL,

sql_text varchar(1024)DEFAULTNULL,

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;”

|percona|UPDATE population SET age=age+1WHERE id=148|202304.145758|1.949487|103773|

|percona|SELECT age FROM population WHERE idBETWEEN153AND153+69|488.572609|0.000176|2771352|

|percona|SELECT sex,age,estimate2012 FROM population WHERE idBETWEEN174AND174+69ORDER 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 idBETWEEN154AND154+69|44.940020|0.000195|230810|

|percona|SELECT DISTINCT base2010 FROM population WHERE idBETWEEN154AND154+69ORDER 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|

Related

Author

Daniel studied Electronic Engineering, but quickly becomes interested in all data things. He has worked as a DBA since 2007 for several companies including one of the major social gaming company at that time in Latin America. Daniel lives in Cali, Colombia and in his free time he loves to spend time with his family and go out to eat at any kind of restaurant. Working for Percona since 2014, he is part of the Consulting team.

Comments (2)

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