USE performance_schema;<\/code><\/p><\/blockquote>\nWhere to Begin With<\/h3>\n
We want to boost cluster performance but aren\u2019t sure where to begin.<\/p>\n
\n
- \n
\n
- Why Queries are Slow<\/a><\/li>\n
- Top 10 Time-Consuming Queries<\/a><\/li>\n
- EXPLAIN ANALYZE<\/a><\/li>\n
- Finding Candidate Queries for Optimisation<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
SQL Layer<\/p><\/div>\n
<\/div>\nWhy Queries are Slow<\/strong><\/h3>\n
<\/div>\n\n
- Queries are tasks, and they are composed of subtasks, and those subtasks consume time.\n
\n
- To optimize the query, we must optimize the subtasks by eliminating them, making them happen fewer times, or making them happen more quickly.<\/li>\n
- Talking about the Query life cycle, it\u2019s first parsed, then planned, executed, and finally sent back to the client.<\/li>\n
- It involves a lot of calls to the storage engine to retrieve rows and post-retrieval executions such as grouping and sorting.<\/li>\n<\/ul>\n<\/li>\n
- While accomplishing the above tasks, the query spends time on the network in the CPU on operations such as statistics, locking, and most importantly, calls to the storage engine to retrieve rows.<\/li>\n
- This call consumes time in the memory and the CPU, and especially I\/O, if the data is not in memory. Excessive time is consumed because transactions are performed needlessly and too many times, so our goal is to eliminate and reduce the operation by making them faster.<\/li>\n<\/ul>\n
Top 10 Time-Consuming Queries<\/strong><\/h3>\n
To optimize the DB, we don’t really need to find the query that is taking the maximum time, but we have to find the query that is taking more time and that is getting called more frequently.<\/p>\n
\n\n
- \n
\n
SELECT (100 * SUM_TIMER_WAIT \/ sum(SUM_TIMER_WAIT) OVER ()) percent, SUM_TIMER_WAIT AS total, COUNT_STAR AS calls, AVG_TIMER_WAIT AS mean, substring(DIGEST_TEXT, 1, 75) FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
\n Projection<\/strong><\/td>\n Field Name<\/strong><\/td>\n Description<\/strong><\/td>\n<\/tr>\n \n \u00a0 \u00a0 total<\/td>\n \u00a0 SUM_TIMER_WAIT<\/td>\n Number of times a query has been executed (COUNT_STAR)<\/p>\n multiplied by<\/p>\n
The average execution time. (AVG_TIMER_WAIT)<\/td>\n<\/tr>\n
\n \u00a0 \u00a0 mean<\/td>\n \u00a0 AVG_TIMER_WAIT<\/td>\n \u00a0 Average Response Time<\/td>\n<\/tr>\n \n \u00a0 \u00a0 substring(DIGEST_TEXT, 1, 75)<\/td>\n \u00a0 Query<\/td>\n \u00a0 Query<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/blockquote>\n <\/p>\n
<\/h3>\n
EXPLAIN<\/h3>\n
List of things expected to happen when the query is executed (list of subtasks).<\/p>\n
EXPLAIN<\/p><\/div>\n
EXPLAIN ANALYZE<\/h3>\n
You will get both the estimation of what the planner expected and what happened when the query ran.<\/p>\n
EXPLAIN DELETE from the table won’t delete the actual rows; therefore, it is safe to get the query plan, but EXPLAIN ANALYZE is not just going to show the query plan; it is going to delete those rows.<\/p><\/blockquote>\n
Understanding how to ANALYZE a query<\/strong><\/h3>\n
If we execute the query with EXPLAIN ANALYZE, we will seek all the sub tasks, executing that query. We have to observe which sub task has the biggest jump in the execution time and see if it is justified.<\/p>\n
The red flag in EXPLAIN ANALYZE is a large difference between the estimated number of rows and the actual number of rows.<\/p>\n
Finding Candidate Queries for the Optimization<\/h3>\n
\n
- Large Number of SUM_ROWS_EXAMINED, in comparison to SUM_ROWS_SENT\n
\n
- This may suggest poor index usage since a large number of rows are sent to the client but discarded afterward.<\/li>\n
- If the number of full joins is high, this suggests that either an index is needed or there is a joint condition missing. A full table scan will occur when there is no index for the joint condition and when there is no join condition.\n
\n
SELECT * FROM sys.statements_with_full_table_scans ORDER BY no_index_used_count DESC\\G<\/code><\/li>\n<\/ul>\n<\/li>\n- \n
SUM_ROWS_SENT<\/p><\/div><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
<\/p>\n
\n
- If the number of full joins is high, this suggests that either an index is needed or there is a joint condition missing. A full table scan will occur when there is no index for the joint condition and when there is no join condition.\n
\n
- \n
SELECT * FROM sys.statements_with_full_table_scans ORDER BY no_index_used_count DESC\\G<\/code><\/p><\/blockquote>\n<\/li>\n<\/ul>\n<\/li>\n- When the number of SUM_SELECT_RANGE_CHECK is high, this may suggest that we need to change the indexes on the tables.<\/li>\n
- If the number of internal temporary tables created on disk is high, we need to consider which indexes are used for sorting and grouping and the amount of memory allowed for internal temporary tables.<\/li>\n
- Writing on disk is still more expensive than internal memory. If you see that the number SUM_SORT_MERGE_PASSES is high for a query (at least as the number of times the query was executed), then increase the sort_buffer_size value only for the sessions that need a larger size.<\/li>\n<\/ul>\n
\nCheck Index Sizes<\/strong><\/h3>\n
\n
SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) \/ 1024 \/ 1024) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = \"test\" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;<\/code><\/li>\n<\/ul>\n<\/blockquote>\nSome interesting facts that are good to knowledge about<\/strong><\/h3>\n
\n
- MySQL’s design separates query processing and other server tasks from data storage and retrieval.<\/li>\n
- When we fetch rows from MySQL when using a library, by default the rows are buffered in the library’s memory and then fetched from there.<\/li>\n
- The optimizer does not care what storage engine a particular table uses. Still, the storage engine does affect how the server optimizes the query. For example, the optimizer asks the storage engine about some of its capabilities and the cost of certain operations, and for statistics on the table data.<\/li>\n<\/ul>\n
<\/h2>\n
Queries that are creating deadlocks<\/strong><\/h3>\n
\n\n
SELECT * FROM performance_schema.events_errors_summary_by_account_by_error WHERE error_name = 'ER_LOCK_DEADLOCK' \\G<\/code><\/li>\n<\/ul>\n<\/blockquote>\nConclusion<\/h3>\n
\nevents_statements_summary_by_digest in performance_schema is the gold mine when looking for queries that may have performance issues.<\/p>\n
Other than performance, we should also consider queries that cause errors and lock time out.<\/p>\n<\/blockquote>\n
References<\/strong><\/h3>\n
![\"SQL](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2024\/09\/SQL-1024x489.jpeg\")
![\"EXLAIN\"](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2024\/09\/EXPLAIN-1024x534.jpeg\")
![\"SUM_ROWS_SENT\"](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2024\/09\/SUM_ROWS_SENT-1024x562.jpeg\")