Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. Working on a weekend project,  I became familiar with the SQL keyword “LATERAL”, which I had not used before because it was only introduced recently in MySQL 8.0.14,  so I wanted to share how it can be used. Some references on this topic: Lateral Derived Tables LATERAL Derived Tables in MySQL 8.0 The keyword “LATERAL” comes into play when you work with derived tables. The derived tables have been available in MySQL for a long time, and schematically they look like this: SELECT t1.foo, t2.bar FROM t1, (SELECT bar FROM table2 WHERE <condition> ) t2 WHERE t1.id=t2.id; The table “(SELECT bar FROM table1 WHERE <condition> ) t2” is used in FROM condition is a derived table, and you can join regular and derived tables normally. Derived tables are typically executed as materialized (ONCE per query and stored as “cached” in temporary tables) or via an outer table. So what’s the problem with derived tables? You will find a limitation when you will try to use a condition inside a derived table that references an outside table. For example: SELECT t1.foo, t2.bar FROM t1, (SELECT bar FROM table2 WHERE <condition> AND table2.date=t1.date ) t2 WHERE t1.id=t2.id; This is not allowed, and to be able to use this query you need to specify a keyword LATERAL, so the query will look like: SELECT t1.foo, t2.bar FROM t1, LATERAL (SELECT bar FROM table2 WHERE <condition> AND table2.date=t1.date ) t2 WHERE t1.id=t2.id; What is a drawback? Well, the derived query can’t be executed now ONCE per query and will be executed FOR EACH row from table t1, so obviously, it will come with a performance penalty and should be used carefully. You may ask, then, when do we need to use LATERAL as we were just fine before MySQL 8.0.14? Actually I came to this query working with timeseries and forecast (prediction data). For example, for each day, we have a metric prediction for each of 30 days ahead. E.g. Prediction date Date in the future Metric prediction 3/28/2020 3/29/2020 5 3/28/2020 3/30/2020 8 3/28/2020 3/31/2020 10 … 27 more rows for 3/28/2020 … 3/29/2020 3/30/2020 3 3/29/2020 3/31/2020 7 3/29/2020 4/1/2020 4 … 27 more rows for 3/29/2020 For each prediction date in the table, I need to find the metric prediction ONLY for the next day. There are probably multiple ways to write this query, and also I was looking to handle this in the application, but with the LATERAL derived table you can write this as: SELECT * FROM prediction_table q , LATERAL ( SELECT date_in_future,DATEDIFF(date_in_future, date_prediction) df FROM prediction_table q1 WHERE q1.date_prediction=q.date_prediction ORDER BY df ASC LIMIT 1) q2 WHERE q2.date_in_future=q.date_in_future; And if you need not exactly tomorrow, but rather for N days ahead, you can use WINDOW function RANK() OVER ( ORDER BY DATEDIFF(expiration,tradedate)) r. So, the query will look like: SELECT * FROM prediction_table q , LATERAL ( SELECT date_in_future,RANK() OVER ( ORDER BY DATEDIFF(expiration,tradedate)) r FROM prediction_table q1 WHERE q1.date_prediction=q.date_prediction) q2 WHERE q2.date_in_future=q.date_in_future AND r=N; I am happy to see that MySQL 8 comes with a new rich set for SQL functions, which makes working with queries much easier.
  2. For this next blog in our Continuent MySQL Use Case series, we’re diving into a sub-series on the topic of ‘multi-master MySQL’. We’ll cover three (3) multi-master MySQL use cases as part of this sub-series focusing first on e-commerce to start with, and then following up with use cases from financial services and telecommunications. Multi-master replication for MySQL typically means that a user can write to any master node knowing that the write will be eventually consistent for all nodes in the cluster; unlike regular MySQL replication, where writes have to be applied to the sole master to ensure that it will be synched to all the slaves. The First Multi-master Customer The first Continuent multi-master customer is a leading fashion e-commerce company with sites servicing customers across the globe. More specifically, it has four multi-brand online stores and several online flagship stores serving millions of customers in 180 countries around the world. The Challenge The challenge this customer faced had to do with managing a single sign-on e-commerce portal for their millions of worldwide customers as well as their need to reduce contention in a single location by balancing traffic to each cluster based on the source website. The Solution Multi-master Tungsten Clustering topology (active/active) – Two or more master MySQL clusters In the Tungsten multi-master cluster topology (active/active), there is one writeable master node per cluster, across two or more clusters. All writes are directed to an available (usually local) master by the Tungsten Connectors / Proxies serving the client requests. The Connectors/Proxies are able to use any other site in the event of a local outage; both sites are write-able at all times, and each cluster replicates from all other member clusters. Our customer’s current Tungsten Clustering deployment consists of two (2) 3-node MySQL clusters configured in a multi-master topology. The Tungsten MySQL clusters are situated in two (2) separate data centers, geographically located in different sites, providing remote redundancy and disaster recovery. They also utilize Tungsten Replicator to replicate out in real-time from each cluster for management reporting. The Benefits Multi-master, Availability, Disaster Recovery About Tungsten Clustering Tungsten Clustering allows enterprises running business-critical MySQL database applications to cost-effectively achieve continuous operations with commercial-grade high availability (HA), geographically redundant disaster recovery (DR) and global scaling. To find out more, visit our Tungsten Clustering product page.
  3. MySQL has come up with window function in latest GA MySQL 8.0 . It is a major leap in SQL for MySQL. This presentation provides an overview to window function in MySQL 8.0. Window functions in MySQL 8.0 from Mydbops Window Function in MySQL 8.0
  4. Mysqldump is the most popular logical backup tool for MySQL. It is included in the MySQL distribution, so it’s ready for use on all of the MySQL instances.  Logical backups are not, however, the fastest nor the most space-efficient way of backing up MySQL databases, but they have a huge advantage over physical backups.  Physical backups are usually all or nothing type of backups. While it might be possible to create partial backup with Xtrabackup (we described this in one of our previous blog posts), restoring such backup is tricky and time-consuming.  Basically, if we want to restore a single table, we have to stop the whole replication chain and perform the recovery on all of the nodes at once. This is a major issue - these days you rarely can afford to stop all of the databases.  Another problem is the table level is the lowest granularity level you can achieve with Xtrabackup: you can restore a single table but you cannot restore part of it. Logical backup, though, can be restored in the way of running SQL statements, therefore it can easily be performed on a running cluster and you can (we wouldn’t call it easily, but still) pick which SQL statements to run so you can do a partial restore of a table.  Let’s take a look at how this can be done in the real world. Restoring a Single MySQL Table Using mysqldump At the beginning, please keep in mind that partial backups do not provide a consistent view of the data. When you take backups of separate tables, you cannot restore such backup to a known position in time (for example, to provision the replication slave) even if you would restore all of the data from the backup. Having this behind us, let’s proceed. We have a master and a slave: Dataset contains of one schema and several tables: mysql> SHOW SCHEMAS; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sbtest | | sys | +--------------------+ 5 rows in set (0.01 sec) mysql> SHOW TABLES FROM sbtest; +------------------+ | Tables_in_sbtest | +------------------+ | sbtest1 | | sbtest10 | | sbtest11 | | sbtest12 | | sbtest13 | | sbtest14 | | sbtest15 | | sbtest16 | | sbtest17 | | sbtest18 | | sbtest19 | | sbtest2 | | sbtest20 | | sbtest21 | | sbtest22 | | sbtest23 | | sbtest24 | | sbtest25 | | sbtest26 | | sbtest27 | | sbtest28 | | sbtest29 | | sbtest3 | | sbtest30 | | sbtest31 | | sbtest32 | | sbtest4 | | sbtest5 | | sbtest6 | | sbtest7 | | sbtest8 | | sbtest9 | +------------------+ 32 rows in set (0.00 sec) Now, we have to take a backup. There are several ways in which we can approach this issue. We can just take a consistent backup of the whole dataset but this will generate a large, single file with all the data. To restore the single table we would have to extract data for the table from that file. It is of course possible, but it is quite time-consuming and it’s pretty much manual operation that can be scripted but if you do not have proper scripts in place, writing ad hoc code when your database is down and you are under heavy pressure is not necessarily the safest idea. Instead of that we can prepare backup in a way that every table will be stored in a separate file: root@vagrant:~/backup# d=$(date +%Y%m%d) ; db='sbtest'; for tab in $(mysql -uroot -ppass -h127.0.0.1 -e "SHOW TABLES FROM ${db}" | grep -v Tables_in_${db}) ; do mysqldump --set-gtid-purged=OFF --routines --events --triggers ${db} ${tab} > ${d}_${db}.${tab}.sql ; done Please note that we set --set-gtid-purged=OFF. We need it if we’d be loading this data later to the database. Otherwise MySQL will attempt to set @@GLOBAL.GTID_PURGED, which will, most likely, fail. MySQL would as well set SET @@SESSION.SQL_LOG_BIN= 0; which is definitely not what we want. Those settings are required if we’d make a consistent backup of the whole data set and we’d like to use it to provision a new node. In our case we know it is not a consistent backup and there is no way we can rebuild anything from it. All we want is to generate a dump that we can load on the master and let it replicate to slaves. That command generated a nice list of sql files that can be uploaded to the production cluster: root@vagrant:~/backup# ls -alh total 605M drwxr-xr-x 2 root root 4.0K Mar 18 14:10 . drwx------ 9 root root 4.0K Mar 18 14:08 .. -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest10.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest11.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest12.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest13.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest14.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest15.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest16.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest17.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest18.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest19.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest1.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest20.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest21.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest22.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest23.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest24.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest25.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest26.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest27.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest28.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest29.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest2.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest30.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest31.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest32.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest3.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest4.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest5.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest6.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest7.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest8.sql -rw-r--r-- 1 root root 19M Mar 18 14:10 20200318_sbtest.sbtest9.sql When you would like to restore the data, all you need to do is to load the SQL file into the master node: root@vagrant:~/backup# mysql -uroot -ppass sbtest < 20200318_sbtest.sbtest11.sql Data will be loaded into the database and replicated to all of the slaves. How to Restore a Single MySQL Table Using ClusterControl? Currently ClusterControl does not provide an easy way of restoring just a single table but it is still possible to do it with just a few manual actions. There are two options you can use. First, suitable for small number of tables, you can basically create schedule where you perform partial backups of a separate tables one by one: Here, we are taking a backup of sbtest.sbtest1 table. We can easily schedule another backup for sbtest2 table: Alternatively we can perform a backup and put data from a single schema into a separate file: Now you can either find the missing data by hand in the file, restore this backup to a separate server or let ClusterControl do it: You keep the server up and running and you can extract the data that you wanted to restore using either mysqldump or SELECT … INTO OUTFILE. Such extracted data will be ready to be applied on the production cluster.   Tags:  MySQL mysqldump restore backup
  5. In my Webinar on Using Percona Monitoring and Management (PMM) for MySQL Troubleshooting, I showed how to use direct queries to ClickHouse for advanced query analysis tasks. In the followup Webinar Q&A, I promised to describe it in more detail and share some queries, so here it goes. PMM uses ClickHouse to store query performance data which gives us great performance and a very high compression ratio. ClickHouse stores data in column-store format so it handles denormalized data very well. As a result, all query performance data is stored in one simple “metrics” table: Column Name Comment queryid hash of query fingerprint service_name Name of service (IP or hostname of DB server by default) database PostgreSQL: database schema MySQL: database; PostgreSQL: schema username client user name client_host client IP or hostname replication_set Name of replication set cluster Cluster name service_type Type of service service_id Service identifier environment Environment name az Availability zone region Region name node_model Node model node_id Node identifier node_name Node name node_type Node type machine_id Machine identifier container_name Container name container_id Container identifier labels.key Custom labels names labels.value Custom labels values agent_id Identifier of agent that collect and send metrics agent_type qan-agent-type-invalid = 0 qan-mysql-perfschema-agent,= 1 qan-mysql-slowlog-agent,= 2 qan-mongodb-profiler-agent,= 3 qan-postgresql-pgstatements-agent,= 4 Agent Type that collect of metrics: slowlog,perf schema,etc. period_start Time when collection of bucket started period_length Duration of collection bucket fingerprint mysql digest_text; query without data example One of query example from set found in bucket example_format EXAMPLE_FORMAT_INVALID = 0 EXAMPLE = 1 FINGERPRINT = 2 Indicates that collect real query examples is prohibited is_truncated Indicates if query examples is too long and was truncated example_type EXAMPLE_TYPE_INVALID = 0 RANDOM = 1 SLOWEST = 2 FASTEST = 3 WITH_ERROR = 4 Indicates what query example was picked up example_metrics Metrics of query example in JSON format. num_queries_with_warnings How many queries was with warnings in bucket warnings.code List of warnings warnings.count Count of each warnings in bucket num_queries_with_errors How many queries was with error in bucket errors.code List of Last_errno errors.count Count of each Last_errno in bucket num_queries Amount queries in this bucket m_query_time_cnt The statement execution time in seconds was met. m_query_time_sum The statement execution time in seconds. m_query_time_min Smallest value of query_time in bucket m_query_time_max Biggest value of query_time in bucket m_query_time_p99 99 percentile of value of query_time in bucket m_lock_time_cnt m_lock_time_sum The time to acquire locks in seconds. m_lock_time_min m_lock_time_max m_lock_time_p99 m_rows_sent_cnt m_rows_sent_sum The number of rows sent to the client. m_rows_sent_min m_rows_sent_max m_rows_sent_p99 m_rows_examined_cnt m_rows_examined_sum Number of rows scanned – SELECT. m_rows_examined_min m_rows_examined_max m_rows_examined_p99 m_rows_affected_cnt m_rows_affected_sum Number of rows changed – UPDATE m_rows_affected_min m_rows_affected_max m_rows_affected_p99 m_rows_read_cnt m_rows_read_sum The number of rows read from tables. m_rows_read_min m_rows_read_max m_rows_read_p99 m_merge_passes_cnt m_merge_passes_sum The number of merge passes that the sort algorithm has had to do. m_merge_passes_min m_merge_passes_max m_merge_passes_p99 m_innodb_io_r_ops_cnt m_innodb_io_r_ops_sum Counts the number of page read operations scheduled. m_innodb_io_r_ops_min m_innodb_io_r_ops_max m_innodb_io_r_ops_p99 m_innodb_io_r_bytes_cnt m_innodb_io_r_bytes_sum Similar to innodb_IO_r_ops m_innodb_io_r_bytes_min m_innodb_io_r_bytes_max m_innodb_io_r_bytes_p99 m_innodb_io_r_wait_cnt m_innodb_io_r_wait_sum Shows how long (in seconds) it took InnoDB to actually read the data from storage. m_innodb_io_r_wait_min m_innodb_io_r_wait_max m_innodb_io_r_wait_p99 m_innodb_rec_lock_wait_cnt m_innodb_rec_lock_wait_sum Shows how long (in seconds) the query waited for row locks. m_innodb_rec_lock_wait_min m_innodb_rec_lock_wait_max m_innodb_rec_lock_wait_p99 m_innodb_queue_wait_cnt m_innodb_queue_wait_sum Shows how long (in seconds) the query spent either waiting to enter the InnoDB queue or inside that queue waiting for execution. m_innodb_queue_wait_min m_innodb_queue_wait_max m_innodb_queue_wait_p99 m_innodb_pages_distinct_cnt m_innodb_pages_distinct_sum Counts approximately the number of unique pages the query accessed. m_innodb_pages_distinct_min m_innodb_pages_distinct_max m_innodb_pages_distinct_p99 m_query_length_cnt m_query_length_sum Shows how long the query is. m_query_length_min m_query_length_max m_query_length_p99 m_bytes_sent_cnt m_bytes_sent_sum The number of bytes sent to all clients. m_bytes_sent_min m_bytes_sent_max m_bytes_sent_p99 m_tmp_tables_cnt m_tmp_tables_sum Number of temporary tables created on memory for the query. m_tmp_tables_min m_tmp_tables_max m_tmp_tables_p99 m_tmp_disk_tables_cnt m_tmp_disk_tables_sum Number of temporary tables created on disk for the query. m_tmp_disk_tables_min m_tmp_disk_tables_max m_tmp_disk_tables_p99 m_tmp_table_sizes_cnt m_tmp_table_sizes_sum Total Size in bytes for all temporary tables used in the query. m_tmp_table_sizes_min m_tmp_table_sizes_max m_tmp_table_sizes_p99 m_qc_hit_cnt m_qc_hit_sum Query Cache hits. m_full_scan_cnt m_full_scan_sum The query performed a full table scan. m_full_join_cnt m_full_join_sum The query performed a full join (a join without indexes). m_tmp_table_cnt m_tmp_table_sum The query created an implicit internal temporary table. m_tmp_table_on_disk_cnt m_tmp_table_on_disk_sum The querys temporary table was stored on disk. m_filesort_cnt m_filesort_sum The query used a filesort. m_filesort_on_disk_cnt m_filesort_on_disk_sum The filesort was performed on disk. m_select_full_range_join_cnt m_select_full_range_join_sum The number of joins that used a range search on a reference table. m_select_range_cnt m_select_range_sum The number of joins that used ranges on the first table. m_select_range_check_cnt m_select_range_check_sum The number of joins without keys that check for key usage after each row. m_sort_range_cnt m_sort_range_sum The number of sorts that were done using ranges. m_sort_rows_cnt m_sort_rows_sum The number of sorted rows. m_sort_scan_cnt m_sort_scan_sum The number of sorts that were done by scanning the table. m_no_index_used_cnt m_no_index_used_sum The number of queries without index. m_no_good_index_used_cnt m_no_good_index_used_sum The number of queries without good index. m_docs_returned_cnt m_docs_returned_sum The number of returned documents. m_docs_returned_min m_docs_returned_max m_docs_returned_p99 m_response_length_cnt m_response_length_sum The response length of the query result in bytes. m_response_length_min m_response_length_max m_response_length_p99 m_docs_scanned_cnt m_docs_scanned_sum The number of scanned documents. m_docs_scanned_min m_docs_scanned_max m_docs_scanned_p99 m_shared_blks_hit_cnt m_shared_blks_hit_sum Total number of shared blocks cache hits by the statement m_shared_blks_read_cnt m_shared_blks_read_sum Total number of shared blocks read by the statement. m_shared_blks_dirtied_cnt m_shared_blks_dirtied_sum Total number of shared blocks dirtied by the statement. m_shared_blks_written_cnt m_shared_blks_written_sum Total number of shared blocks written by the statement. m_local_blks_hit_cnt m_local_blks_hit_sum Total number of local block cache hits by the statement m_local_blks_read_cnt m_local_blks_read_sum Total number of local blocks read by the statement. m_local_blks_dirtied_cnt m_local_blks_dirtied_sum Total number of local blocks dirtied by the statement. m_local_blks_written_cnt m_local_blks_written_sum Total number of local blocks written by the statement. m_temp_blks_read_cnt m_temp_blks_read_sum Total number of temp blocks read by the statement. m_temp_blks_written_cnt m_temp_blks_written_sum Total number of temp blocks written by the statement. m_blk_read_time_cnt m_blk_read_time_sum Total time the statement spent reading blocks m_blk_write_time_cnt m_blk_write_time_sum Total time the statement spent writing blocks I provided the whole table structure here as it includes a description for many columns. Note not all columns will contain data for all database engines in all configurations, and some are not yet used at all. Before we get to queries let me explain some general design considerations for this table. We do not store performance information for every single query; it is not always available to begin with (for example, if using MySQL Performance Schema). Even if it was available with modern database engines capable of serving 1M+ QPS, it would still be a lot of data to store and process. Instead, we aggregate statistics by “buckets”  which can be seen as sort key in the “metrics” table: ORDER BY (queryid,service_name,database,schema,username,client_host,period_start) You can think about Sort Key as similar to Clustered  Index in MySQL. Basically, for every period (1 minute by default) we store information for every queried, service_name, database, schema, username, and client_host combination. Period_Start   is stored in the UTC timezone. QueryID – is a  hash which identifies unique query pattern, such as “select c from sbtest1 where id=?” Service_Name is the name of the database instance  Database  – is the database or Catalog.  We use it in PostgreSQL terminology, not MySQL one Schema – this is Schema, which also can be referred to as Database in MySQL  UserName –  The Database level  User Name, which ran this given query. Client_Host –  HostName or IP of the Client This data storage format allows us to provide a very detailed workload analysis, for example, you can see if there is a difference in performance profile between different schemas, which is very valuable for many applications that use the “tenant per schema” approach. Or you can see specific workloads that different users generate on your database fleet.  Another thing you may notice is that each metric for each grouping bucket stores several statistical values, such as:  `m_query_time_cnt` Float32 COMMENT 'The statement execution time in seconds was met.', `m_query_time_sum` Float32 COMMENT 'The statement execution time in seconds.', `m_query_time_min` Float32 COMMENT 'Smallest value of query_time in bucket', `m_query_time_max` Float32 COMMENT 'Biggest value of query_time in bucket', `m_query_time_p99` Float32 COMMENT '99 percentile of value of query_time in bucket', The  _cnt  value is the number of times this metric was reported.  Every Query should have query_time available but many other measurements may not be available for every engine and any configuration.  The _sum value is the sum for the metric among all _cnt  queries. So if you want to compute _avg you should divide   _sum by _cnt. _min, _max and _p99  store the minimum, maximum, and 99 percentile value. How to Access ClickHouse To access ClickHouse on PMM Server you should run the “clickhouse-client”  command line tool. If you’re deploying MySQL with Docker you can just run: docker exec -it pmm2-server clickhouse-client Where pmm2-server is the name of the container you’re using for PMM. Run  “use pmm”  to select the current schema to PMM. Query Examples ClickHouse uses SQL-like language as its query language. I call it SQL-like as it does not implement SQL standard fully, yet it has many additional and very useful extensions. You can find the complete ClickHouse Query Language reference here.  # Number of Queries for the period select sum(num_queries) from metrics where period_start>'2020-03-18 00:00:00'; # Average Query Execution Time for Last 6 hours select avg(m_query_time_sum/m_query_time_cnt) from metrics where period_start>subtractHours(now(),6); # What are most frequent query ids ? Also calculate total number of queries in the same query select queryid,sum(num_queries) cnt from metrics where period_start>subtractHours(now(),6) group by queryid with totals order by cnt desc limit 10; # How do actual queries look for those IDs ? select any(example),sum(num_queries) cnt from metrics where period_start>subtractHours(now(),6) group by queryid order by cnt desc limit 10 \G # queries hitting particular host select any(example),sum(num_queries) cnt from metrics where period_start>subtractHours(now(),6) and node_name='mysql1' group by queryid order by cnt desc limit 10 \G # slowest instances of the queries select any(example),sum(num_queries) cnt, max(m_query_time_max) slowest from metrics where period_start>subtractHours(now(),6) group by queryid order by slowest desc limit 10 \G # Query pattern which resulted in the largest temporary table created select example, m_tmp_table_sizes_max from metrics where period_start>subtractHours(now(),6) order by m_tmp_table_sizes_max desc limit 1 \G # Slowest Queries Containing Delete in the text select any(example),sum(num_queries) cnt, max(m_query_time_max) slowest from metrics where period_start>subtractHours(now(),6) and lowerUTF8(example) like '%delete%' group by queryid order by slowest desc limit 10 \G I hope this gets you started! If you create some other queries which you find particularly helpful, please feel free to leave them in the comments for others to enjoy!