Generating Slow Query Log with MySQL Shell

Recently, I wrote three articles on how to analyze queries and generate a slow query log for MySQL Database Service on OCI:

• https://lefred.be/content/analyzing-queries-in-mysql-database-service/
• https://lefred.be/content/analyzing-queries-in-mysql-database-service-slow-query-log-part-1/
• https://lefred.be/content/analyzing-queries-in-mysql-database-service-slow-query-log-part-2/

In these post, we were generating a slow query log in text or JSON directly in Object Storage.

Today, we will see how we can generate a slow query log in text directly using MySQL Shell and form Performance Schema.

The generated log can be used to digest the queries with a tool like pt-query-digest.

The MySQL Plugin used is logs and is available on my GitHub repo dedicated to my MySQL Shell Plugins.

This is an example of an output using pt-query-digest:

# Query 3: 0.40 QPS, 0.00x concurrency, ID 0xF70E8D59DF2D27CB4C081956264E69AB at byte 104128
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2022-11-06T23:12:14 to 2022-11-06T23:12:19
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       2
# Exec time      8    13ms    87us    13ms     6ms    13ms     9ms     6ms
# Lock time      1     4us       0     4us     2us     4us     2us     2us
# Rows sent      0       1       0       1    0.50       1    0.71    0.50
# Rows examine   0       1       0       1    0.50       1    0.71    0.50
# Rows affecte   0       0       0       0       0       0       0       0
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Query size     1     420     210     210     210     210       0     210
# Cpu time       0       0       0       0       0       0       0       0
# Max memory     2   2.55M   1.15M   1.40M   1.28M   1.40M 178.87k   1.28M
# String:
# Bytes sent   n/a
# Databases    test
# Execution en PRIMARY
# Full join    no
# Full scan    no (1/50%), yes (1/50%)
# Hosts        n/a
# No index use no (1/50%), yes (1/50%)
# Tmp table    no
# Tmp table on no
# Tmp tbl size n/a
# Users        n/a
# Query_time distribution
#   1us
#  10us  ################################
# 100us
#   1ms
#  10ms  ################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `test` LIKE 'scott'\G
#    SHOW CREATE TABLE `test`.`scott`\G
# EXPLAIN /*!50100 PARTITIONS*/
select doc->"$.holeScores[6].number" hole_number, regexp_substr(json_search(replace(replace(replace(doc->"$.holeScores[*].score", ', ' , '","'), '[' ,'["'), ']', '"]'), 'one', 1), '[0-9]+') score_idx from scott\G

MySQL Shell for Visual Studio Code and OCI

It’s also possible to use this plugin with MySQL Shell for Visual Studio Code and generate a slow query log for an MySQL instance running on OCI.

You need to copy the plugins in ~/.mysqlsh-gui/plugin_data/gui_plugin/shell_instance_home/plugins.

For the Windows user, the path is C:\Users\<user_name>\AppData\Roaming\MySQL\mysqlsh-gui\plugin_data\gui_plugin\shell_instance_home\plugins.

Let’s connect using a Free Bastion Host:

Conclusion

Here is another solution to generate MySQL Slow Query Log directly from Performance Schema. The generated file is as close as possible to a real slow query log file with a few slight differences, some more information and some less.

As you could see, the plugin can be also used with MySQL Database Service in OCI where we don’t have access to the slow query log file.

Of course, using Performance Schema has some limitations, like the max amount of entries in the table and the possibility to miss some queries during the table truncation if we use the option.

But well used, this can be a very nice source to digest some queries during a limited time.

Enjoy MySQL and good hunting for query optimization candidates !