You are investigating the performance of a query which selects data from an InnoDB table. Consider this Performance Schema diagnostics output for the query: mysql> SELECT event_id, event_name…
The correct answer is B. The query did not find its table in the table definition cache. Option B is correct because the presence of stage/sql/checking tables (event 8947) is a diagnostic indicator that MySQL could not locate the table in the table definition cache (TDC). When a table IS found in the TDC, MySQL skips that verification stage and proceeds directly to…
Performance Tuning
Question
You are investigating the performance of a query which selects data from an InnoDB table. Consider this Performance Schema diagnostics output for the query:
mysql> SELECT event_id, event_name, timer_wait, nesting_event_id, source
FROM (SELECT thread_id, event_id, event_name, timer_wait, nesting_event_id,
source
FROM
performance_schema.events_statements_history_long
WHERE event_id = 8945
UNION ALL
SELECT thread_id, event_id, event_name, timer_wait, nesting_event_id,
source
FROM
performance_schema.events_stages_history_long
WHERE event_id = 8945
UNION ALL
SELECT thread_id, event_id, event_name, timer_wait, nesting_event_id,
source
FROM
performance_schema.events_waits_history_long
WHERE
event_id = 8945 OR event_name <> 'Idle' AND thread_id =
) AS a
ORDER BY
event_id;
+----------+-------------------------------------------------+------------+--------------------+-------------------------------------------+
| event_id | event_name | timer_wait | nesting_event_id | source |
+----------+-------------------------------------------------+------------+--------------------+-------------------------------------------+
| 8944 | stage/sql/select | 960916000 | NULL | sql_parse.cc:1931 |
| 8945 | stage/sql/init | 90045000 | 8944 | sql_parse.cc:1845 |
| 8946 | stage/sql/checking permissions | 47290000 | 8944 | sql_parse.cc:2020 |
| 8947 | stage/sql/checking tables | 3826000 | 8944 | sql_base.cc:4911 |
| 8948 | stage/sql/open | 57268000 | 8944 | sql_parse.cc:3874 |
| 8949 | stage/sql/system lock | 22660000 | 8944 | lock.cc:315 |
| 8950 | stage/sql/optimizing | 1849000 | 8944 | sql_optimizer.cc:1150 |
| 8951 | stage/sql/init/session | 13395156 | 8949 | sql_optimizer.cc:1443 |
| 8952 | wait/synch/mutex/mysys/THR_LOCK::mutex | 294408 | 8951 | /home/pb2/mysql/sql/mysys/thr_mutex.cc:145|
| 8953 | stage/sql/optimizing | 1387000 | 8944 | sql_optimizer.cc:1150 |
| 8954 | stage/sql/init | 345439000 | 8944 | sql_parse.cc:1845 |
| 8955 | wait/io/table/sql/handler | 302137776 | 8954 | handler.cc:202 |
| 8956 | wait/synch/mutex/mysys/THR_LOCK::mutex | 256240 | 8954 | /home/pb2/mysql/sql/mysys/thr_mutex.cc:145|
| 8957 | stage/sql/preparing | 70460000 | 8944 | sql_select.cc:846 |
| 8958 | stage/sql/executing | 7350000 | 8944 | sql_select.cc:1107 |
| 8959 | stage/sql/sending data | 14570000 | 8944 | sql_select.cc:1157 |
| 8960 | stage/sql/end | 4927000 | 8944 | sql_select.cc:1165 |
| 8961 | stage/sql/query end | 1387000 | 8944 | sql_parse.cc:4012 |
| 8962 | stage/sql/closing tables | 6128000 | 8944 | sql_parse.cc:3996 |
| 8963 | stage/sql/freeing items | 5896000 | 8944 | sql_parse.cc:4283 |
| 8964 | stage/sql/cleaning up | 1187000 | 8944 | sql_parse.cc:4404 |
+----------+-------------------------------------------------+------------+--------------------+-------------------------------------------+
Which statement is true about the output?
Options
AThe time the query took is the sum of all timer_wait values.
BThe query did not find its table in the table definition cache.
CThe query read data from the data file rather than directly from the buffer pool.
DThe event with event_id = 8945 is a child of the event with event_id=8944.
How the community answered
(47 responses)
A
6% (3)
B
49% (23)
C
17% (8)
D
28% (13)
Explanation
Option B is correct because the presence of stage/sql/checking tables (event 8947) is a diagnostic indicator that MySQL could not locate the table in the table definition cache (TDC). When a table IS found in the TDC, MySQL skips that verification stage and proceeds directly to locking; its appearance here means MySQL had to read the table definition from disk to open it, which is further corroborated by the stage/sql/open stage (event 8948) following immediately after.
A is wrong because the events are hierarchically nested - note that events 8945–8964 all have nesting_event_id = 8944, meaning their times are already rolled up into the parent event's timer_wait. Summing all timer_wait values would massively double-count time.
C is wrong because physical disk reads from an InnoDB data file would appear as wait/io/file/innodb/innodb_data_file events. What appears instead (event 8955) is wait/io/table/sql/handler, which is a logical row-access wait - consistent with data being served from the InnoDB buffer pool, not the data file.
D is wrong as a standalone insight - while event 8945's nesting_event_id = 8944 does confirm 8944 is its parent, this relationship is trivially readable from the table and event 8944 (nesting_event_id = NULL) is simply the root statement event that parents all the stages; it reveals nothing diagnostic.
Memory tip: Think of the TDC like a schema cache. If MySQL needs to "check the table", it's a cache miss - just like an HTTP 404 forces a disk lookup. stage/sql/checking tables = TDC miss.
