| 1 |
########### suite/sysschema/t/pr_statement_performance_analyzer.test ############# |
| 2 |
# # |
| 3 |
# Testing of of the sys.statement_performance_analyzer() procedure # |
| 4 |
# # |
| 5 |
# Creation: # |
| 6 |
# 2015-07-28 jkrogh Implement this test as part of # |
| 7 |
# WL#7804 REPORT FOR SUPPORT # |
| 8 |
# # |
| 9 |
################################################################################## |
| 10 |
|
| 11 |
# Performance schema tracks prepared statements separately, and does not |
| 12 |
# yet have a summary view that we can use for this view. |
| 13 |
# Until then, we disable this test with --ps-protocol |
| 14 |
# Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled |
| 15 |
-- source include/no_protocol.inc |
| 16 |
# Bug #23290879 - For reasons unknown to man this test fails randomly only on Windows |
| 17 |
-- source include/not_windows.inc |
| 18 |
|
| 19 |
# Thread stack overrun in debug mode on sparc |
| 20 |
--source include/not_sparc_debug.inc |
| 21 |
|
| 22 |
use test; |
| 23 |
--disable_warnings |
| 24 |
DROP TABLE IF EXISTS t1; |
| 25 |
DROP TEMPORARY TABLE IF EXISTS tmp_digests_ini; |
| 26 |
DROP VIEW IF EXISTS view_digests; |
| 27 |
--enable_warnings |
| 28 |
|
| 29 |
# Create a table |
| 30 |
CREATE TABLE t1 (id INT PRIMARY KEY, val int); |
| 31 |
|
| 32 |
# Create a new connection to make the actual changes |
| 33 |
# Create the connection now to ensure queries like "SELECT @@`version_comment` LIMIT 1" are excluded |
| 34 |
connect (con1,localhost,root,,); |
| 35 |
connection con1; |
| 36 |
use test; |
| 37 |
--let $con1_thread_id= `SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID()` |
| 38 |
|
| 39 |
# Disable instrumentation for all other threads other than the one that will |
| 40 |
# my monitored for this test ($con1_thread_id) |
| 41 |
connection default; |
| 42 |
--disable_result_log |
| 43 |
--replace_result $con1_thread_id CON1_THREAD_ID |
| 44 |
eval UPDATE performance_schema.threads SET INSTRUMENTED = IF(THREAD_ID = $con1_thread_id, 'YES', 'NO'); |
| 45 |
CALL sys.ps_setup_enable_consumer('events_statements_history_long'); |
| 46 |
CALL sys.ps_truncate_all_tables(FALSE); |
| 47 |
--enable_result_log |
| 48 |
|
| 49 |
# Start with some initial queries |
| 50 |
# Don't rely on the digests being constant across MySQL releases and versions, so find the |
| 51 |
# digest for each of the three queries by getting the last event from performance_schema.events_statements_current |
| 52 |
# for the con1 connection. |
| 53 |
connection con1; |
| 54 |
INSERT INTO t1 VALUES (1, 0); |
| 55 |
connection default; |
| 56 |
--let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'INSERT ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL |
| 57 |
--source include/wait_condition.inc |
| 58 |
--let $digest_insert= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` |
| 59 |
--let $query_insert= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` |
| 60 |
connection con1; |
| 61 |
UPDATE t1 SET val = 1 WHERE id = 1; |
| 62 |
connection default; |
| 63 |
--let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'UPDATE ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL |
| 64 |
--source include/wait_condition.inc |
| 65 |
--let $digest_update= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` |
| 66 |
--let $query_update= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` |
| 67 |
connection con1; |
| 68 |
SELECT t1a.* FROM t1 AS t1a LEFT OUTER JOIN (SELECT * FROM t1 AS t1b1 INNER JOIN t1 AS t1b2 USING (id, val)) AS t1b ON t1b.id > t1a.id ORDER BY t1a.val, t1a.id; |
| 69 |
connection default; |
| 70 |
--let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'SELECT ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL |
| 71 |
--source include/wait_condition.inc |
| 72 |
--let $digest_select= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` |
| 73 |
--let $query_select= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` |
| 74 |
|
| 75 |
# Enable to debug if some digests are not found |
| 76 |
# --output /tmp/digest |
| 77 |
# --eval SELECT '$digest_insert' AS DigestInsert, '$digest_update' AS DigestUpdate, '$digest_select' AS DigestSelect |
| 78 |
# --output /tmp/digest_text |
| 79 |
# --eval SELECT '$query_insert' AS DigestInsert, '$query_update' AS DigestUpdate, '$query_select' AS DigestSelect |
| 80 |
# --output /tmp/ps_history |
| 81 |
# SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, DIGEST, SQL_TEXT FROM performance_schema.events_statements_history_long ORDER BY EVENT_ID; |
| 82 |
|
| 83 |
# Start collecting data |
| 84 |
CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL); |
| 85 |
CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); |
| 86 |
CALL sys.statement_performance_analyzer('save', 'test.tmp_digests_ini', NULL); |
| 87 |
|
| 88 |
# Make sure the last_seen times will be different from the SELECT statement's first_seen. |
| 89 |
DO SLEEP(1.2); |
| 90 |
|
| 91 |
# Make some more changes |
| 92 |
connection con1; |
| 93 |
INSERT INTO t1 VALUES (2, 0); |
| 94 |
UPDATE t1 SET val = 1 WHERE id = 2; |
| 95 |
SELECT t1a.* FROM t1 AS t1a LEFT OUTER JOIN (SELECT * FROM t1 AS t1b1 INNER JOIN t1 AS t1b2 USING (id, val)) AS t1b ON t1b.id > t1a.id ORDER BY t1a.val, t1a.id; |
| 96 |
disconnect con1; |
| 97 |
|
| 98 |
# Make the second collection of data and create the output |
| 99 |
connection default; |
| 100 |
# Make sure all of the queries executing in con1 has been recorded in performance_schema.events_statements_summary_by_digest |
| 101 |
# before continuing with the actual tests of pr_statement_performance_analyzer() |
| 102 |
--let $wait_condition= SELECT COUNT_STAR = 2 FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST = '$digest_select' |
| 103 |
--source include/wait_condition.inc |
| 104 |
|
| 105 |
# Get all values to be used in replacements now to minimize the risk of the |
| 106 |
# values disappearing out of the performance_schema tables |
| 107 |
--let $o_upd_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` |
| 108 |
--let $o_upd_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` |
| 109 |
--let $o_upd_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` |
| 110 |
--let $o_upd_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` |
| 111 |
--let $o_upd_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` |
| 112 |
--let $o_sel_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` |
| 113 |
--let $o_sel_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` |
| 114 |
--let $o_sel_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` |
| 115 |
--let $o_sel_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` |
| 116 |
--let $o_sel_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` |
| 117 |
--let $o_ins_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` |
| 118 |
--let $o_ins_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` |
| 119 |
--let $o_ins_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` |
| 120 |
--let $o_ins_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` |
| 121 |
--let $o_ins_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` |
| 122 |
--let $o_upd_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` |
| 123 |
--let $o_sel_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` |
| 124 |
--let $o_ins_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` |
| 125 |
--let $o_upd_cpu_latency= `SELECT cpu_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` |
| 126 |
--let $o_sel_cpu_latency= `SELECT cpu_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` |
| 127 |
--let $o_ins_cpu_latency= `SELECT cpu_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` |
| 128 |
--let $o_upd_controlled_mem= `SELECT max_controlled_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` |
| 129 |
--let $o_sel_controlled_mem= `SELECT max_controlled_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` |
| 130 |
--let $o_ins_controlled_mem= `SELECT max_controlled_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` |
| 131 |
--let $o_upd_total_mem= `SELECT max_total_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` |
| 132 |
--let $o_sel_total_mem= `SELECT max_total_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` |
| 133 |
--let $o_ins_total_mem= `SELECT max_total_memory FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` |
| 134 |
--let $d_upd_total_latency= `SELECT format_pico_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1` |
| 135 |
--let $d_sel_total_latency= `SELECT format_pico_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1` |
| 136 |
--let $d_ins_total_latency= `SELECT format_pico_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1` |
| 137 |
--let $d_upd_tock_latency= `SELECT format_pico_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1` |
| 138 |
--let $d_sel_tock_latency= `SELECT format_pico_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1` |
| 139 |
--let $d_ins_tock_latency= `SELECT format_pico_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1` |
| 140 |
--let $d_upd_cpu_latency= `SELECT format_pico_time(CPU_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1` |
| 141 |
--let $d_sel_cpu_latency= `SELECT format_pico_time(CPU_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1` |
| 142 |
--let $d_ins_cpu_latency= `SELECT format_pico_time(CPU_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1` |
| 143 |
--let $d_upd_controlled_mem= `SELECT format_bytes(MAX_CONTROLLED_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1` |
| 144 |
--let $d_sel_controlled_mem= `SELECT format_bytes(MAX_CONTROLLED_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1` |
| 145 |
--let $d_ins_controlled_mem= `SELECT format_bytes(MAX_CONTROLLED_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1` |
| 146 |
--let $d_upd_total_mem= `SELECT format_bytes(MAX_TOTAL_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1` |
| 147 |
--let $d_sel_total_mem= `SELECT format_bytes(MAX_TOTAL_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1` |
| 148 |
--let $d_ins_total_mem= `SELECT format_bytes(MAX_TOTAL_MEMORY) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1` |
| 149 |
|
| 150 |
# DEBUG |
| 151 |
# --echo $o_upd_controlled_mem $o_sel_controlled_mem $o_ins_controlled_mem |
| 152 |
# --echo $o_upd_total_mem $o_sel_total_mem $o_ins_total_mem |
| 153 |
# --echo $d_upd_controlled_mem $d_sel_controlled_mem $d_ins_controlled_mem |
| 154 |
# --echo $d_upd_total_mem $d_sel_total_mem $d_ins_total_mem |
| 155 |
|
| 156 |
CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); |
| 157 |
|
| 158 |
# Do a sanity check to ensure we are assuming the queries has the digests they have and that there is nothing else in the events_statements_summary_by_digest than there actually is. |
| 159 |
--replace_result $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT |
| 160 |
--sorted_result |
| 161 |
SELECT DIGEST, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest; |
| 162 |
|
| 163 |
# with_runtimes_in_95th_percentile |
| 164 |
# It is unknown what the result will be since the execution times for each query are unknown |
| 165 |
# So just check that no warning or error occurs |
| 166 |
--disable_result_log |
| 167 |
CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile'); |
| 168 |
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_runtimes_in_95th_percentile'); |
| 169 |
--enable_result_log |
| 170 |
|
| 171 |
# analysis - as there's no control of the various latencies, it may be the same for two or more of the queries. |
| 172 |
# So replace_result cannot be used to give it a unique value. Instead just use LATENCY for all rows. |
| 173 |
--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $o_upd_total_latency LATENCY $o_upd_max_latency LATENCY $o_upd_avg_latency LATENCY $o_upd_lock_latency LATENCY $o_upd_cpu_latency LATENCY $o_upd_controlled_mem MEMORY $o_upd_total_mem MEMORY $o_upd_first_seen FIRST_SEEN $o_upd_last_seen LAST_SEEN $o_sel_total_latency LATENCY $o_sel_max_latency LATENCY $o_sel_avg_latency LATENCY $o_sel_lock_latency LATENCY $o_sel_cpu_latency LATENCY $o_sel_controlled_mem MEMORY $o_sel_total_mem MEMORY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN $o_ins_total_latency LATENCY $o_ins_max_latency LATENCY $o_ins_avg_latency LATENCY $o_ins_lock_latency LATENCY $o_ins_cpu_latency LATENCY $o_ins_controlled_mem MEMORY $o_ins_total_mem MEMORY $o_ins_first_seen FIRST_SEEN $o_ins_last_seen LAST_SEEN |
| 174 |
--sorted_result |
| 175 |
CALL sys.statement_performance_analyzer('overall', NULL, 'analysis'); |
| 176 |
|
| 177 |
--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $d_upd_total_latency LATENCY $o_upd_max_latency LATENCY $o_upd_first_seen FIRST_SEEN $o_upd_last_seen LAST_SEEN $d_upd_tock_latency LATENCY $d_upd_cpu_latency LATENCY $d_upd_controlled_mem MEMORY $o_upd_controlled_mem MEMORY $d_upd_total_mem MEMORY $o_upd_total_mem MEMORY $d_sel_total_latency LATENCY $o_sel_max_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN $d_sel_tock_latency LATENCY $d_sel_cpu_latency LATENCY $d_sel_controlled_mem MEMORY $o_sel_controlled_mem MEMORY $d_sel_total_mem MEMORY $o_sel_total_mem MEMORY $d_ins_total_latency LATENCY $o_ins_max_latency LATENCY $o_ins_first_seen FIRST_SEEN $o_ins_last_seen LAST_SEEN $d_ins_tock_latency LATENCY $d_ins_cpu_latency LATENCY $d_ins_controlled_mem MEMORY $o_ins_controlled_mem MEMORY $d_ins_total_mem MEMORY $o_ins_total_mem MEMORY |
| 178 |
--sorted_result |
| 179 |
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis'); |
| 180 |
|
| 181 |
# Should give an empty result except for the banner generated by the procedure |
| 182 |
CALL sys.statement_performance_analyzer('overall', NULL, 'with_errors_or_warnings'); |
| 183 |
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_errors_or_warnings'); |
| 184 |
|
| 185 |
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN |
| 186 |
CALL sys.statement_performance_analyzer('overall', NULL, 'with_full_table_scans'); |
| 187 |
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN |
| 188 |
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_full_table_scans'); |
| 189 |
|
| 190 |
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN |
| 191 |
CALL sys.statement_performance_analyzer('overall', NULL, 'with_sorting'); |
| 192 |
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN |
| 193 |
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_sorting'); |
| 194 |
|
| 195 |
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN |
| 196 |
CALL sys.statement_performance_analyzer('overall', NULL, 'with_temp_tables'); |
| 197 |
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN |
| 198 |
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_temp_tables'); |
| 199 |
|
| 200 |
# Try a custom view |
| 201 |
# Sort by the query, then we know the order will be INSERT, SELECT, UPDATE |
| 202 |
CREATE VIEW test.view_digests AS |
| 203 |
SELECT sys.format_statement(DIGEST_TEXT) AS query, |
| 204 |
SCHEMA_NAME AS db, |
| 205 |
COUNT_STAR AS exec_count, |
| 206 |
format_pico_time(SUM_TIMER_WAIT) AS total_latency, |
| 207 |
format_pico_time(AVG_TIMER_WAIT) AS avg_latency, |
| 208 |
ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg, |
| 209 |
ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg, |
| 210 |
ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg, |
| 211 |
DIGEST AS digest |
| 212 |
FROM performance_schema.events_statements_summary_by_digest |
| 213 |
ORDER BY SUBSTRING(query, 1, 6); |
| 214 |
SET @sys.statement_performance_analyzer.view = 'test.view_digests'; |
| 215 |
--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $o_upd_total_latency LATENCY $o_upd_avg_latency LATENCY $o_sel_total_latency LATENCY $o_sel_avg_latency LATENCY $o_ins_total_latency LATENCY $o_ins_avg_latency LATENCY |
| 216 |
CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); |
| 217 |
--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $d_upd_total_latency LATENCY $d_sel_total_latency LATENCY $d_ins_total_latency LATENCY |
| 218 |
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'custom'); |
| 219 |
|
| 220 |
# Verify that setting a limit works |
| 221 |
SET @sys.statement_performance_analyzer.limit = 2; |
| 222 |
--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $digest_insert DIGEST_INSERT $digest_select DIGEST_SELECT $o_ins_total_latency LATENCY $o_ins_avg_latency LATENCY $o_sel_total_latency LATENCY $o_sel_avg_latency LATENCY |
| 223 |
CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); |
| 224 |
|
| 225 |
# Test for error conditions - some of the errors depend on the sql_mode so set it explicitly |
| 226 |
# Which sql_modes are deprecated depends on the release, so disable warnings while setting the sql_mode |
| 227 |
|
| 228 |
|
| 229 |
# Use non-strict sql_mode |
| 230 |
SET SESSION sql_mode = ''; |
| 231 |
# Ask for a non-supported action - since strict more is not used, this will give the error: ERROR 1644 (45000): Unknown action: '' |
| 232 |
# Note: the action passed to the procedure is actually lost because it's truncated. |
| 233 |
--error ER_SIGNAL_EXCEPTION |
| 234 |
CALL sys.statement_performance_analyzer('do magic', NULL, NULL); |
| 235 |
|
| 236 |
|
| 237 |
# Use 5.7.9+ default: |
| 238 |
--disable_warnings |
| 239 |
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; |
| 240 |
--enable_warnings |
| 241 |
# Ask for a non-supported action - since strict mode is used, this will give the error: ERROR 1265 (01000): Data truncated for column 'in_action' at row 1 |
| 242 |
--error 1265 |
| 243 |
CALL sys.statement_performance_analyzer('do magic', NULL, NULL); |
| 244 |
# Try to create reserved table name |
| 245 |
-- error S45000 |
| 246 |
CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests', NULL); |
| 247 |
-- error S45000 |
| 248 |
CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests_delta', NULL); |
| 249 |
-- error S45000 |
| 250 |
CALL sys.statement_performance_analyzer('create_tmp', 'tmp_digests', NULL); |
| 251 |
# This should succeed |
| 252 |
CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests', NULL); |
| 253 |
CREATE TABLE test.tmp_unsupported LIKE test.tmp_digests_ini; |
| 254 |
# Try to create a temporary table that already exists |
| 255 |
-- error S45000 |
| 256 |
CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL); |
| 257 |
# Try to create a base table that already exists |
| 258 |
-- error S45000 |
| 259 |
CALL sys.statement_performance_analyzer('create_table', 'test.tmp_digests_ini', NULL); |
| 260 |
-- error S45000 |
| 261 |
CALL sys.statement_performance_analyzer('create_table', 'test.tmp_unsupported', NULL); |
| 262 |
# Verify that the sanity check for the input table e.g. for saving snapshots works |
| 263 |
ALTER TABLE test.tmp_unsupported ADD COLUMN myvar int DEFAULT 0; |
| 264 |
-- error S45000 |
| 265 |
CALL sys.statement_performance_analyzer('save', 'test.tmp_unsupported', NULL); |
| 266 |
# Try to create a snapshot or overall output with the wrong in_table |
| 267 |
-- error S45000 |
| 268 |
CALL sys.statement_performance_analyzer('snapshot', 'test.new_table', NULL); |
| 269 |
-- error S45000 |
| 270 |
CALL sys.statement_performance_analyzer('overall', 'test.new_table', 'analysis'); |
| 271 |
# Try to create a delta output or save a snapshot specifying a non-existing table. |
| 272 |
-- error S45000 |
| 273 |
CALL sys.statement_performance_analyzer('delta', 'test.new_table', 'analysis'); |
| 274 |
-- error S45000 |
| 275 |
CALL sys.statement_performance_analyzer('save', 'test.new_table', NULL); |
| 276 |
# Verify custom views doesn't work without the user variable set |
| 277 |
SET @sys.statement_performance_analyzer.view = NULL; |
| 278 |
DELETE FROM sys.sys_config WHERE variable = 'statement_performance_analyzer.view'; |
| 279 |
-- error S45000 |
| 280 |
CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); |
| 281 |
# Set the custom view to a regular table - should not work |
| 282 |
SET @sys.statement_performance_analyzer.view = 'test.tmp_unsupported'; |
| 283 |
-- error S45000 |
| 284 |
CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); |
| 285 |
|
| 286 |
|
| 287 |
# Remove the temporary tables created by the procedure |
| 288 |
# First ensure the tables actually exists (to avoid passing the test if the table names are changed) |
| 289 |
CALL sys.table_exists('sys', 'tmp_digests', @exists); |
| 290 |
SELECT @exists; |
| 291 |
CALL sys.table_exists('sys', 'tmp_digests_delta', @exists); |
| 292 |
SELECT @exists; |
| 293 |
CALL sys.statement_performance_analyzer('cleanup', NULL, NULL); |
| 294 |
# Verify the internal tables really were removed |
| 295 |
-- error ER_BAD_TABLE_ERROR |
| 296 |
DROP TEMPORARY TABLE sys.tmp_digests; |
| 297 |
-- error ER_BAD_TABLE_ERROR |
| 298 |
DROP TEMPORARY TABLE sys.tmp_digests_delta; |
| 299 |
|
| 300 |
# An attempt to create a delta view should fail now as there's no longer an existing snapshot. |
| 301 |
-- error S45000 |
| 302 |
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis'); |
| 303 |
# Ensure no delta table was created. |
| 304 |
-- error ER_BAD_TABLE_ERROR |
| 305 |
DROP TEMPORARY TABLE sys.tmp_digests_delta; |
| 306 |
|
| 307 |
# Try and use a table.db name > 129 |
| 308 |
SET @identifier := REPEAT('a', 65); |
| 309 |
-- error 1406 |
| 310 |
CALL sys.statement_performance_analyzer('snapshot', CONCAT(@identifier, '.', @identifier), NULL); |
| 311 |
|
| 312 |
# Clean up |
| 313 |
DROP TEMPORARY TABLE test.tmp_digests_ini; |
| 314 |
DROP TEMPORARY TABLE test.tmp_digests; |
| 315 |
DROP TABLE test.tmp_unsupported; |
| 316 |
DROP TABLE test.t1; |
| 317 |
DROP VIEW view_digests; |
| 318 |
SET @identifier := NULL; |
| 319 |
|
| 320 |
SET SESSION sql_mode = @@global.sql_mode; |
| 321 |
SET @sys.statement_performance_analyzer.limit = NULL; |
| 322 |
SET @sys.statement_performance_analyzer.view = NULL; |
| 323 |
--source ../include/ps_setup_consumers_cleanup.inc |
| 324 |
--source ../include/ps_threads_cleanup.inc |
| 325 |
--source ../include/sys_config_cleanup.inc |