README.md
1# The MySQL sys schema
2
3A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.
4
5There are install files available for 5.6 and 5.7 respectively. To load these, you must position yourself within the directory that you downloaded to, as these top level files SOURCE individual files that are shared across versions in most cases (though not all).
6
7## Installation
8
9The objects should all be created as the root user (but run with the privileges of the invoker).
10
11For instance if you download to /tmp/mysql-sys/, and want to install the 5.6 version you should:
12
13 cd /tmp/mysql-sys/
14 mysql -u root -p < ./sys_56.sql
15
16Or if you would like to log in to the client, and install the 5.7 version:
17
18 cd /tmp/mysql-sys/
19 mysql -u root -p
20 SOURCE ./sys_57.sql
21
22Alternatively, you could just choose to load individual files based on your needs, but beware, certain objects have dependencies on other objects. You will need to ensure that these are also loaded.
23
24### Generating a single SQL file
25
26There is bash script within the root of the branch directory, called `generate_sql_file.sh`, that allows you to create a single SQL file from the branch.
27
28This includes substitution parameters for the MySQL user to use, and whether to include or exclude `SET sql_log_bin` commands from the scripts. This is particularly useful for installations such as Amazon RDS, which do not have the root@localhost user, or disallow setting sql_log_bin.
29
30When run, this outputs a file named such as `sys_<sys_version>_<mysql_version_identifier>_inline.sql`, i.e. `sys_1.2.0_56_inline.sql` is sys version 1.2.0, built for MySQL 5.6.
31
32#### Options
33
34* v: The version of MySQL to build the sys schema for, either '56' or '57'
35* b: Whether to omit any lines that deal with sql_log_bin (useful for RDS)
36* u: The user to set as the owner of the objects (useful for RDS)
37* m: Whether to generate a mysql_install_db / mysqld --initialize formatted file
38
39#### Examples
40
41Generate a MySQL 5.7 SQL file that uses the 'mark'@'localhost' user:
42
43 ./generate_sql_file.sh -v 57 -u "'mark'@'localhost'"
44
45Generate a MySQL 5.6 SQL file for RDS:
46
47 ./generate_sql_file.sh -v 56 -b -u CURRENT_USER
48
49Generate a MySQL 5.7 bootstrap file:
50
51 ./generate_sql_file.sh -v 57 -m
52
53## Overview of objects
54
55### Tables
56
57#### sys_config
58
59##### Description
60
61Holds configuration options for the sys schema. This is a persistent table (using the `InnoDB` storage engine), with the configuration persisting across upgrades (new options are added with `INSERT IGNORE`).
62
63The table also has two related triggers, which maintain the user that `INSERTs` or `UPDATEs` the configuration - `sys_config_insert_set_user` and `sys_config_update_set_user` respectively.
64
65Its structure is as follows:
66
67```SQL
68+----------+--------------+------+-----+-------------------+-----------------------------+
69| Field | Type | Null | Key | Default | Extra |
70+----------+--------------+------+-----+-------------------+-----------------------------+
71| variable | varchar(128) | NO | PRI | NULL | |
72| value | varchar(128) | YES | | NULL | |
73| set_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
74| set_by | varchar(128) | YES | | NULL | |
75+----------+--------------+------+-----+-------------------+-----------------------------+
76```
77
78Note, when functions check for configuration options, they first check whether a similar named user variable exists with a value, and if this is not set then pull the configuration option from this table in to that named user variable. This is done for performance reasons (to not continually `SELECT` from the table), however this comes with the side effect that once inited, the values last with the session, somewhat like how session variables are inited from global variables. If the values within this table are changed, they will not take effect until the user logs in again.
79
80##### Options included
81
82| Variable | Default Value | Description |
83| ------------------------------------ | ------------- | ------------------------------------------------------------------------------ |
84| statement_truncate_len | 64 | Sets the size to truncate statements to, for the `format_statement()` function. |
85| statement_performance_analyzer.limit | 100 | The maximum number of rows to include for the views that does not have a built-in limit (e.g. the 95th percentile view). If not set the limit is 100. |
86| statement_performance_analyzer.view | NULL | Used together with the 'custom' view. If the value contains a space, it is considered a query, otherwise it must be an existing view querying the performance_schema.events_statements_summary_by_digest table. |
87| diagnostics.allow_i_s_tables | OFF | Specifies whether it is allowed to do table scan queries on information_schema.TABLES for the `diagnostics` procedure. |
88| diagnostics.include_raw | OFF | Set to 'ON' to include the raw data (e.g. the original output of "SELECT * FROM sys.metrics") for the `diagnostics` procedure.|
89| ps_thread_trx_info.max_length | 65535 | Sets the maximum output length for JSON object output by the `ps_thread_trx_info()` function. |
90
91### Views
92
93Many of the views in the sys schema have both a command line user friendly format output, as well as tooling friendly versions of any view that contains formatted output duplicated as an x$ table.
94
95The examples below show output for only the formatted views, and note where there is an x$ counterpart available.
96
97#### host_summary / x$host_summary
98
99##### Description
100
101Summarizes statement activity, file IO and connections by host.
102
103When the host found is NULL, it is assumed to be a "background" thread.
104
105##### Structures (5.7)
106
107```SQL
108mysql> desc host_summary;
109+------------------------+---------------+------+-----+---------+-------+
110| Field | Type | Null | Key | Default | Extra |
111+------------------------+---------------+------+-----+---------+-------+
112| host | varchar(60) | YES | | NULL | |
113| statements | decimal(64,0) | YES | | NULL | |
114| statement_latency | text | YES | | NULL | |
115| statement_avg_latency | text | YES | | NULL | |
116| table_scans | decimal(65,0) | YES | | NULL | |
117| file_ios | decimal(64,0) | YES | | NULL | |
118| file_io_latency | text | YES | | NULL | |
119| current_connections | decimal(41,0) | YES | | NULL | |
120| total_connections | decimal(41,0) | YES | | NULL | |
121| unique_users | bigint(21) | NO | | 0 | |
122| current_memory | text | YES | | NULL | |
123| total_memory_allocated | text | YES | | NULL | |
124+------------------------+---------------+------+-----+---------+-------+
12512 rows in set (0.15 sec)
126
127mysql> desc x$host_summary;
128+------------------------+---------------+------+-----+---------+-------+
129| Field | Type | Null | Key | Default | Extra |
130+------------------------+---------------+------+-----+---------+-------+
131| host | varchar(60) | YES | | NULL | |
132| statements | decimal(64,0) | YES | | NULL | |
133| statement_latency | decimal(64,0) | YES | | NULL | |
134| statement_avg_latency | decimal(65,4) | YES | | NULL | |
135| table_scans | decimal(65,0) | YES | | NULL | |
136| file_ios | decimal(64,0) | YES | | NULL | |
137| file_io_latency | decimal(64,0) | YES | | NULL | |
138| current_connections | decimal(41,0) | YES | | NULL | |
139| total_connections | decimal(41,0) | YES | | NULL | |
140| unique_users | bigint(21) | NO | | 0 | |
141| current_memory | decimal(63,0) | YES | | NULL | |
142| total_memory_allocated | decimal(64,0) | YES | | NULL | |
143+------------------------+---------------+------+-----+---------+-------+
14412 rows in set (0.00 sec)
145```
146
147##### Example
148
149```SQL
150 mysql> select * from host_summary;
151 +------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
152 | host | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users |
153 +------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
154 | hal1 | 2924 | 00:03:59.53 | 81.92 ms | 82 | 54702 | 55.61 s | 1 | 1 | 1 |
155 +------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
156```
157
158#### host_summary_by_file_io / x$host_summary_by_file_io
159
160##### Description
161
162Summarizes file IO totals per host.
163
164When the host found is NULL, it is assumed to be a "background" thread.
165
166##### Structures
167
168```SQL
169mysql> desc host_summary_by_file_io;
170+------------+---------------+------+-----+---------+-------+
171| Field | Type | Null | Key | Default | Extra |
172+------------+---------------+------+-----+---------+-------+
173| host | varchar(60) | YES | | NULL | |
174| ios | decimal(42,0) | YES | | NULL | |
175| io_latency | text | YES | | NULL | |
176+------------+---------------+------+-----+---------+-------+
1773 rows in set (0.00 sec)
178
179mysql> desc x$host_summary_by_file_io;
180+------------+---------------+------+-----+---------+-------+
181| Field | Type | Null | Key | Default | Extra |
182+------------+---------------+------+-----+---------+-------+
183| host | varchar(60) | YES | | NULL | |
184| ios | decimal(42,0) | YES | | NULL | |
185| io_latency | decimal(42,0) | YES | | NULL | |
186+------------+---------------+------+-----+---------+-------+
1873 rows in set (0.06 sec)
188```
189
190##### Example
191
192```SQL
193 mysql> select * from host_summary_by_file_io;
194 +------------+-------+------------+
195 | host | ios | io_latency |
196 +------------+-------+------------+
197 | hal1 | 26457 | 21.58 s |
198 | hal2 | 1189 | 394.21 ms |
199 +------------+-------+------------+
200```
201
202#### host_summary_by_file_io_type / x$host_summary_by_file_io_type
203
204##### Description
205
206Summarizes file IO by event type per host.
207
208When the host found is NULL, it is assumed to be a "background" thread.
209
210##### Structures
211
212```SQL
213mysql> desc host_summary_by_file_io_type;
214+---------------+---------------------+------+-----+---------+-------+
215| Field | Type | Null | Key | Default | Extra |
216+---------------+---------------------+------+-----+---------+-------+
217| host | varchar(60) | YES | | NULL | |
218| event_name | varchar(128) | NO | | NULL | |
219| total | bigint(20) unsigned | NO | | NULL | |
220| total_latency | text | YES | | NULL | |
221| max_latency | text | YES | | NULL | |
222+---------------+---------------------+------+-----+---------+-------+
2235 rows in set (0.70 sec)
224
225mysql> desc x$host_summary_by_file_io_type;
226+---------------+---------------------+------+-----+---------+-------+
227| Field | Type | Null | Key | Default | Extra |
228+---------------+---------------------+------+-----+---------+-------+
229| host | varchar(60) | YES | | NULL | |
230| event_name | varchar(128) | NO | | NULL | |
231| total | bigint(20) unsigned | NO | | NULL | |
232| total_latency | bigint(20) unsigned | NO | | NULL | |
233| max_latency | bigint(20) unsigned | NO | | NULL | |
234+---------------+---------------------+------+-----+---------+-------+
2355 rows in set (0.01 sec)
236```
237
238##### Example
239
240```SQL
241 mysql> select * from host_summary_by_file_io_type;
242 +------------+--------------------------------------+-------+---------------+-------------+
243 | host | event_name | total | total_latency | max_latency |
244 +------------+--------------------------------------+-------+---------------+-------------+
245 | hal1 | wait/io/file/sql/FRM | 871 | 168.15 ms | 18.48 ms |
246 | hal1 | wait/io/file/innodb/innodb_data_file | 173 | 129.56 ms | 34.09 ms |
247 | hal1 | wait/io/file/innodb/innodb_log_file | 20 | 77.53 ms | 60.66 ms |
248 | hal1 | wait/io/file/myisam/dfile | 40 | 6.54 ms | 4.58 ms |
249 | hal1 | wait/io/file/mysys/charset | 3 | 4.79 ms | 4.71 ms |
250 | hal1 | wait/io/file/myisam/kfile | 67 | 4.38 ms | 300.04 us |
251 | hal1 | wait/io/file/sql/ERRMSG | 5 | 2.72 ms | 1.69 ms |
252 | hal1 | wait/io/file/sql/pid | 3 | 266.30 us | 185.47 us |
253 | hal1 | wait/io/file/sql/casetest | 5 | 246.81 us | 150.19 us |
254 | hal1 | wait/io/file/sql/global_ddl_log | 2 | 21.24 us | 18.59 us |
255 | hal2 | wait/io/file/sql/file_parser | 1422 | 4.80 s | 135.14 ms |
256 | hal2 | wait/io/file/sql/FRM | 865 | 85.82 ms | 9.81 ms |
257 | hal2 | wait/io/file/myisam/kfile | 1073 | 37.14 ms | 15.79 ms |
258 | hal2 | wait/io/file/myisam/dfile | 2991 | 25.53 ms | 5.25 ms |
259 | hal2 | wait/io/file/sql/dbopt | 20 | 1.07 ms | 153.07 us |
260 | hal2 | wait/io/file/sql/misc | 4 | 59.71 us | 33.75 us |
261 | hal2 | wait/io/file/archive/data | 1 | 13.91 us | 13.91 us |
262 +------------+--------------------------------------+-------+---------------+-------------+
263 ```
264
265#### host_summary_by_stages / x$host_summary_by_stages
266
267##### Description
268
269Summarizes stages by host, ordered by host and total latency per stage.
270
271When the host found is NULL, it is assumed to be a "background" thread.
272
273##### Structures
274
275```SQL
276mysql> desc host_summary_by_stages;
277+---------------+---------------------+------+-----+---------+-------+
278| Field | Type | Null | Key | Default | Extra |
279+---------------+---------------------+------+-----+---------+-------+
280| host | varchar(60) | YES | | NULL | |
281| event_name | varchar(128) | NO | | NULL | |
282| total | bigint(20) unsigned | NO | | NULL | |
283| total_latency | text | YES | | NULL | |
284| avg_latency | text | YES | | NULL | |
285+---------------+---------------------+------+-----+---------+-------+
2865 rows in set (0.06 sec)
287
288mysql> desc x$host_summary_by_stages;
289+---------------+---------------------+------+-----+---------+-------+
290| Field | Type | Null | Key | Default | Extra |
291+---------------+---------------------+------+-----+---------+-------+
292| host | varchar(60) | YES | | NULL | |
293| event_name | varchar(128) | NO | | NULL | |
294| total | bigint(20) unsigned | NO | | NULL | |
295| total_latency | bigint(20) unsigned | NO | | NULL | |
296| avg_latency | bigint(20) unsigned | NO | | NULL | |
297+---------------+---------------------+------+-----+---------+-------+
2985 rows in set (0.81 sec)
299```
300
301##### Example
302
303```SQL
304 mysql> select * from host_summary_by_stages;
305 +------+--------------------------------+-------+---------------+-------------+
306 | host | event_name | total | total_latency | avg_latency |
307 +------+--------------------------------+-------+---------------+-------------+
308 | hal | stage/sql/Opening tables | 889 | 1.97 ms | 2.22 us |
309 | hal | stage/sql/Creating sort index | 4 | 1.79 ms | 446.30 us |
310 | hal | stage/sql/init | 10 | 312.27 us | 31.23 us |
311 | hal | stage/sql/checking permissions | 10 | 300.62 us | 30.06 us |
312 | hal | stage/sql/freeing items | 5 | 85.89 us | 17.18 us |
313 | hal | stage/sql/statistics | 5 | 79.15 us | 15.83 us |
314 | hal | stage/sql/preparing | 5 | 69.12 us | 13.82 us |
315 | hal | stage/sql/optimizing | 5 | 53.11 us | 10.62 us |
316 | hal | stage/sql/Sending data | 5 | 44.66 us | 8.93 us |
317 | hal | stage/sql/closing tables | 5 | 37.54 us | 7.51 us |
318 | hal | stage/sql/System lock | 5 | 34.28 us | 6.86 us |
319 | hal | stage/sql/query end | 5 | 24.37 us | 4.87 us |
320 | hal | stage/sql/end | 5 | 8.60 us | 1.72 us |
321 | hal | stage/sql/Sorting result | 5 | 8.33 us | 1.67 us |
322 | hal | stage/sql/executing | 5 | 5.37 us | 1.07 us |
323 | hal | stage/sql/cleaning up | 5 | 4.60 us | 919.00 ns |
324 +------+--------------------------------+-------+---------------+-------------+
325```
326
327#### host_summary_by_statement_latency / x$host_summary_by_statement_latency
328
329##### Description
330
331Summarizes overall statement statistics by host.
332
333When the host found is NULL, it is assumed to be a "background" thread.
334
335##### Structures
336
337```SQL
338mysql> desc host_summary_by_statement_latency;
339+---------------+---------------+------+-----+---------+-------+
340| Field | Type | Null | Key | Default | Extra |
341+---------------+---------------+------+-----+---------+-------+
342| host | varchar(60) | YES | | NULL | |
343| total | decimal(42,0) | YES | | NULL | |
344| total_latency | text | YES | | NULL | |
345| max_latency | text | YES | | NULL | |
346| lock_latency | text | YES | | NULL | |
347| rows_sent | decimal(42,0) | YES | | NULL | |
348| rows_examined | decimal(42,0) | YES | | NULL | |
349| rows_affected | decimal(42,0) | YES | | NULL | |
350| full_scans | decimal(43,0) | YES | | NULL | |
351+---------------+---------------+------+-----+---------+-------+
3529 rows in set (0.29 sec)
353
354mysql> desc x$host_summary_by_statement_latency;
355+---------------+---------------+------+-----+---------+-------+
356| Field | Type | Null | Key | Default | Extra |
357+---------------+---------------+------+-----+---------+-------+
358| host | varchar(60) | YES | | NULL | |
359| total | decimal(42,0) | YES | | NULL | |
360| total_latency | decimal(42,0) | YES | | NULL | |
361| max_latency | decimal(42,0) | YES | | NULL | |
362| lock_latency | decimal(42,0) | YES | | NULL | |
363| rows_sent | decimal(42,0) | YES | | NULL | |
364| rows_examined | decimal(42,0) | YES | | NULL | |
365| rows_affected | decimal(42,0) | YES | | NULL | |
366| full_scans | decimal(43,0) | YES | | NULL | |
367+---------------+---------------+------+-----+---------+-------+
3689 rows in set (0.54 sec)
369```
370
371##### Example
372
373```SQL
374 mysql> select * from host_summary_by_statement_latency;
375 +------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
376 | host | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
377 +------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
378 | hal | 3381 | 00:02:09.13 | 1.48 s | 1.07 s | 1151 | 93947 | 150 | 91 |
379 +------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
380```
381
382#### host_summary_by_statement_type / x$host_summary_by_statement_type
383
384##### Description
385
386Summarizes the types of statements executed by each host.
387
388When the host found is NULL, it is assumed to be a "background" thread.
389
390##### Structures
391
392```SQL
393mysql> desc host_summary_by_statement_type;
394+---------------+---------------------+------+-----+---------+-------+
395| Field | Type | Null | Key | Default | Extra |
396+---------------+---------------------+------+-----+---------+-------+
397| host | varchar(60) | YES | | NULL | |
398| statement | varchar(128) | YES | | NULL | |
399| total | bigint(20) unsigned | NO | | NULL | |
400| total_latency | text | YES | | NULL | |
401| max_latency | text | YES | | NULL | |
402| lock_latency | text | YES | | NULL | |
403| rows_sent | bigint(20) unsigned | NO | | NULL | |
404| rows_examined | bigint(20) unsigned | NO | | NULL | |
405| rows_affected | bigint(20) unsigned | NO | | NULL | |
406| full_scans | bigint(21) unsigned | NO | | 0 | |
407+---------------+---------------------+------+-----+---------+-------+
40810 rows in set (0.30 sec)
409
410mysql> desc x$host_summary_by_statement_type;
411+---------------+---------------------+------+-----+---------+-------+
412| Field | Type | Null | Key | Default | Extra |
413+---------------+---------------------+------+-----+---------+-------+
414| host | varchar(60) | YES | | NULL | |
415| statement | varchar(128) | YES | | NULL | |
416| total | bigint(20) unsigned | NO | | NULL | |
417| total_latency | bigint(20) unsigned | NO | | NULL | |
418| max_latency | bigint(20) unsigned | NO | | NULL | |
419| lock_latency | bigint(20) unsigned | NO | | NULL | |
420| rows_sent | bigint(20) unsigned | NO | | NULL | |
421| rows_examined | bigint(20) unsigned | NO | | NULL | |
422| rows_affected | bigint(20) unsigned | NO | | NULL | |
423| full_scans | bigint(21) unsigned | NO | | 0 | |
424+---------------+---------------------+------+-----+---------+-------+
42510 rows in set (0.76 sec)
426```
427
428##### Example
429
430```SQL
431 mysql> select * from host_summary_by_statement_type;
432 +------+----------------------+--------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
433 | host | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
434 +------+----------------------+--------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
435 | hal | create_view | 2063 | 00:05:04.20 | 463.58 ms | 1.42 s | 0 | 0 | 0 | 0 |
436 | hal | select | 174 | 40.87 s | 28.83 s | 858.13 ms | 5212 | 157022 | 0 | 82 |
437 | hal | stmt | 6645 | 15.31 s | 491.78 ms | 0 ps | 0 | 0 | 7951 | 0 |
438 | hal | call_procedure | 17 | 4.78 s | 1.02 s | 37.94 ms | 0 | 0 | 19 | 0 |
439 | hal | create_table | 19 | 3.04 s | 431.71 ms | 0 ps | 0 | 0 | 0 | 0 |
440 ...
441 +------+----------------------+--------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
442```
443
444#### innodb_buffer_stats_by_schema / x$innodb_buffer_stats_by_schema
445
446##### Description
447
448Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, aggregating by schema.
449
450##### Structures
451
452```SQL
453mysql> desc innodb_buffer_stats_by_schema;
454+---------------+---------------+------+-----+---------+-------+
455| Field | Type | Null | Key | Default | Extra |
456+---------------+---------------+------+-----+---------+-------+
457| object_schema | text | YES | | NULL | |
458| allocated | text | YES | | NULL | |
459| data | text | YES | | NULL | |
460| pages | bigint(21) | NO | | 0 | |
461| pages_hashed | bigint(21) | NO | | 0 | |
462| pages_old | bigint(21) | NO | | 0 | |
463| rows_cached | decimal(44,0) | YES | | NULL | |
464+---------------+---------------+------+-----+---------+-------+
4657 rows in set (0.08 sec)
466
467mysql> desc x$innodb_buffer_stats_by_schema;
468+---------------+---------------+------+-----+---------+-------+
469| Field | Type | Null | Key | Default | Extra |
470+---------------+---------------+------+-----+---------+-------+
471| object_schema | text | YES | | NULL | |
472| allocated | decimal(43,0) | YES | | NULL | |
473| data | decimal(43,0) | YES | | NULL | |
474| pages | bigint(21) | NO | | 0 | |
475| pages_hashed | bigint(21) | NO | | 0 | |
476| pages_old | bigint(21) | NO | | 0 | |
477| rows_cached | decimal(44,0) | NO | | 0 | |
478+---------------+---------------+------+-----+---------+-------+
4797 rows in set (0.12 sec)
480````
481
482##### Example
483
484```SQL
485mysql> select * from innodb_buffer_stats_by_schema;
486+--------------------------+------------+------------+-------+--------------+-----------+-------------+
487| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
488+--------------------------+------------+------------+-------+--------------+-----------+-------------+
489| mem30_trunk__instruments | 1.69 MiB | 510.03 KiB | 108 | 108 | 108 | 3885 |
490| InnoDB System | 688.00 KiB | 351.62 KiB | 43 | 43 | 43 | 862 |
491| mem30_trunk__events | 80.00 KiB | 21.61 KiB | 5 | 5 | 5 | 229 |
492+--------------------------+------------+------------+-------+--------------+-----------+-------------+
493```
494
495#### innodb_buffer_stats_by_table / x$innodb_buffer_stats_by_table
496
497##### Description
498
499Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, aggregating by schema and table name.
500
501##### Structures
502
503```SQL
504mysql> desc innodb_buffer_stats_by_table;
505+---------------+---------------+------+-----+---------+-------+
506| Field | Type | Null | Key | Default | Extra |
507+---------------+---------------+------+-----+---------+-------+
508| object_schema | text | YES | | NULL | |
509| object_name | text | YES | | NULL | |
510| allocated | text | YES | | NULL | |
511| data | text | YES | | NULL | |
512| pages | bigint(21) | NO | | 0 | |
513| pages_hashed | bigint(21) | NO | | 0 | |
514| pages_old | bigint(21) | NO | | 0 | |
515| rows_cached | decimal(44,0) | YES | | NULL | |
516+---------------+---------------+------+-----+---------+-------+
5178 rows in set (0.09 sec)
518
519mysql> desc x$innodb_buffer_stats_by_table;
520+---------------+---------------+------+-----+---------+-------+
521| Field | Type | Null | Key | Default | Extra |
522+---------------+---------------+------+-----+---------+-------+
523| object_schema | text | YES | | NULL | |
524| object_name | text | YES | | NULL | |
525| allocated | decimal(43,0) | YES | | NULL | |
526| data | decimal(43,0) | YES | | NULL | |
527| pages | bigint(21) | NO | | 0 | |
528| pages_hashed | bigint(21) | NO | | 0 | |
529| pages_old | bigint(21) | NO | | 0 | |
530| rows_cached | decimal(44,0) | NO | | 0 | |
531+---------------+---------------+------+-----+---------+-------+
5328 rows in set (0.18 sec)
533```
534
535##### Example
536
537```SQL
538mysql> select * from innodb_buffer_stats_by_table;
539+--------------------------+------------------------------------+------------+-----------+-------+--------------+-----------+-------------+
540| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
541+--------------------------+------------------------------------+------------+-----------+-------+--------------+-----------+-------------+
542| InnoDB System | SYS_COLUMNS | 128.00 KiB | 98.97 KiB | 8 | 8 | 8 | 1532 |
543| InnoDB System | SYS_FOREIGN | 128.00 KiB | 55.48 KiB | 8 | 8 | 8 | 172 |
544| InnoDB System | SYS_TABLES | 128.00 KiB | 56.18 KiB | 8 | 8 | 8 | 365 |
545| InnoDB System | SYS_INDEXES | 112.00 KiB | 76.16 KiB | 7 | 7 | 7 | 1046 |
546| mem30_trunk__instruments | agentlatencytime | 96.00 KiB | 28.83 KiB | 6 | 6 | 6 | 252 |
547| mem30_trunk__instruments | binlogspaceusagedata | 96.00 KiB | 22.54 KiB | 6 | 6 | 6 | 196 |
548| mem30_trunk__instruments | connectionsdata | 96.00 KiB | 36.68 KiB | 6 | 6 | 6 | 276 |
549| mem30_trunk__instruments | connectionsmaxdata | 96.00 KiB | 31.88 KiB | 6 | 6 | 6 | 271 |
550| mem30_trunk__instruments | cpuaverage | 96.00 KiB | 14.32 KiB | 6 | 6 | 6 | 55 |
551| mem30_trunk__instruments | diskiototaldata | 96.00 KiB | 42.71 KiB | 6 | 6 | 6 | 152 |
552| mem30_trunk__instruments | innodbopenfilesdata | 96.00 KiB | 32.61 KiB | 6 | 6 | 6 | 266 |
553| mem30_trunk__instruments | innodbrowlocktimestatisticsdata | 96.00 KiB | 32.16 KiB | 6 | 6 | 6 | 261 |
554| mem30_trunk__instruments | myisamkeybufferusagedata | 96.00 KiB | 25.99 KiB | 6 | 6 | 6 | 232 |
555| mem30_trunk__instruments | mysqlprocessactivity | 96.00 KiB | 31.99 KiB | 6 | 6 | 6 | 252 |
556| mem30_trunk__instruments | querycacheaveragefreeblocksizedata | 96.00 KiB | 27.00 KiB | 6 | 6 | 6 | 237 |
557| mem30_trunk__instruments | querycacheaveragequerysizedata | 96.00 KiB | 38.29 KiB | 6 | 6 | 6 | 315 |
558| mem30_trunk__instruments | querycachefragmentationdata | 96.00 KiB | 27.00 KiB | 6 | 6 | 6 | 237 |
559| mem30_trunk__instruments | querycachememorydata | 96.00 KiB | 32.58 KiB | 6 | 6 | 6 | 278 |
560| mem30_trunk__instruments | querycachequeriesincachedata | 96.00 KiB | 27.15 KiB | 6 | 6 | 6 | 238 |
561| mem30_trunk__instruments | ramusagedata | 96.00 KiB | 15.02 KiB | 6 | 6 | 6 | 59 |
562| mem30_trunk__instruments | slaverelaylogspaceusagedata | 96.00 KiB | 28.28 KiB | 6 | 6 | 6 | 249 |
563| mem30_trunk__instruments | swapusagedata | 96.00 KiB | 15.02 KiB | 6 | 6 | 6 | 59 |
564| InnoDB System | SYS_FIELDS | 80.00 KiB | 49.78 KiB | 5 | 5 | 5 | 1147 |
565| InnoDB System | SYS_DATAFILES | 32.00 KiB | 3.97 KiB | 2 | 2 | 2 | 60 |
566| InnoDB System | SYS_FOREIGN_COLS | 32.00 KiB | 7.43 KiB | 2 | 2 | 2 | 83 |
567| InnoDB System | SYS_TABLESPACES | 32.00 KiB | 3.65 KiB | 2 | 2 | 2 | 56 |
568| InnoDB System | SYS_IBUF_TABLE | 16.00 KiB | 0 bytes | 1 | 1 | 1 | 0 |
569+--------------------------+------------------------------------+------------+-----------+-------+--------------+-----------+-------------+
570```
571
572#### innodb_lock_waits / x$innodb_lock_waits
573
574##### Description
575
576Gives a snapshot of which InnoDB locks transactions are waiting for.
577The lock waits are ordered by the age of the lock descending.
578
579##### Structures
580
581```SQL
582mysql> desc sys.innodb_lock_waits;
583+------------------------------+---------------------+------+-----+---------------------+-------+
584| Field | Type | Null | Key | Default | Extra |
585+------------------------------+---------------------+------+-----+---------------------+-------+
586| wait_started | datetime | YES | | NULL | |
587| wait_age | time | YES | | NULL | |
588| wait_age_secs | bigint(21) | YES | | NULL | |
589| locked_table | varchar(1024) | NO | | | |
590| locked_index | varchar(1024) | YES | | NULL | |
591| locked_type | varchar(32) | NO | | | |
592| waiting_trx_id | varchar(18) | NO | | | |
593| waiting_trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
594| waiting_trx_age | time | YES | | NULL | |
595| waiting_trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
596| waiting_trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
597| waiting_pid | bigint(21) unsigned | NO | | 0 | |
598| waiting_query | longtext | YES | | NULL | |
599| waiting_lock_id | varchar(81) | NO | | | |
600| waiting_lock_mode | varchar(32) | NO | | | |
601| blocking_trx_id | varchar(18) | NO | | | |
602| blocking_pid | bigint(21) unsigned | NO | | 0 | |
603| blocking_query | longtext | YES | | NULL | |
604| blocking_lock_id | varchar(81) | NO | | | |
605| blocking_lock_mode | varchar(32) | NO | | | |
606| blocking_trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
607| blocking_trx_age | time | YES | | NULL | |
608| blocking_trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
609| blocking_trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
610| sql_kill_blocking_query | varchar(32) | YES | | NULL | |
611| sql_kill_blocking_connection | varchar(26) | YES | | NULL | |
612+------------------------------+---------------------+------+-----+---------------------+-------+
61326 rows in set (0.01 sec)
614
615mysql> desc sys.x$innodb_lock_waits;
616+------------------------------+---------------------+------+-----+---------------------+-------+
617| Field | Type | Null | Key | Default | Extra |
618+------------------------------+---------------------+------+-----+---------------------+-------+
619| wait_started | datetime | YES | | NULL | |
620| wait_age | time | YES | | NULL | |
621| wait_age_secs | bigint(21) | YES | | NULL | |
622| locked_table | varchar(1024) | NO | | | |
623| locked_index | varchar(1024) | YES | | NULL | |
624| locked_type | varchar(32) | NO | | | |
625| waiting_trx_id | varchar(18) | NO | | | |
626| waiting_trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
627| waiting_trx_age | time | YES | | NULL | |
628| waiting_trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
629| waiting_trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
630| waiting_pid | bigint(21) unsigned | NO | | 0 | |
631| waiting_query | varchar(1024) | YES | | NULL | |
632| waiting_lock_id | varchar(81) | NO | | | |
633| waiting_lock_mode | varchar(32) | NO | | | |
634| blocking_trx_id | varchar(18) | NO | | | |
635| blocking_pid | bigint(21) unsigned | NO | | 0 | |
636| blocking_query | varchar(1024) | YES | | NULL | |
637| blocking_lock_id | varchar(81) | NO | | | |
638| blocking_lock_mode | varchar(32) | NO | | | |
639| blocking_trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
640| blocking_trx_age | time | YES | | NULL | |
641| blocking_trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
642| blocking_trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
643| sql_kill_blocking_query | varchar(32) | YES | | NULL | |
644| sql_kill_blocking_connection | varchar(26) | YES | | NULL | |
645+------------------------------+---------------------+------+-----+---------------------+-------+
64626 rows in set (0.02 sec)
647```
648
649##### Example
650
651```SQL
652mysql> SELECT * FROM innodb_lock_waits\G
653*************************** 1. row ***************************
654 wait_started: 2014-11-11 13:39:20
655 wait_age: 00:00:07
656 wait_age_secs: 7
657 locked_table: `db1`.`t1`
658 locked_index: PRIMARY
659 locked_type: RECORD
660 waiting_trx_id: 867158
661 waiting_trx_started: 2014-11-11 13:39:15
662 waiting_trx_age: 00:00:12
663 waiting_trx_rows_locked: 0
664 waiting_trx_rows_modified: 0
665 waiting_pid: 3
666 waiting_query: UPDATE t1 SET val = val + 1 WHERE id = 2
667 waiting_lock_id: 867158:2363:3:3
668 waiting_lock_mode: X
669 blocking_trx_id: 867157
670 blocking_pid: 4
671 blocking_query: UPDATE t1 SET val = val + 1 + SLEEP(10) WHERE id = 2
672 blocking_lock_id: 867157:2363:3:3
673 blocking_lock_mode: X
674 blocking_trx_started: 2014-11-11 13:39:11
675 blocking_trx_age: 00:00:16
676 blocking_trx_rows_locked: 1
677 blocking_trx_rows_modified: 1
678 sql_kill_blocking_query: KILL QUERY 4
679sql_kill_blocking_connection: KILL 4
680```
681
682#### io_by_thread_by_latency / x$io_by_thread_by_latency
683
684##### Description
685
686Shows the top IO consumers by thread, ordered by total latency.
687
688##### Structures
689
690```SQL
691mysql> desc io_by_thread_by_latency;
692+----------------+---------------------+------+-----+---------+-------+
693| Field | Type | Null | Key | Default | Extra |
694+----------------+---------------------+------+-----+---------+-------+
695| user | varchar(128) | YES | | NULL | |
696| total | decimal(42,0) | YES | | NULL | |
697| total_latency | text | YES | | NULL | |
698| min_latency | text | YES | | NULL | |
699| avg_latency | text | YES | | NULL | |
700| max_latency | text | YES | | NULL | |
701| thread_id | bigint(20) unsigned | NO | | NULL | |
702| processlist_id | bigint(20) unsigned | YES | | NULL | |
703+----------------+---------------------+------+-----+---------+-------+
7048 rows in set (0.14 sec)
705
706mysql> desc x$io_by_thread_by_latency;
707+----------------+---------------------+------+-----+---------+-------+
708| Field | Type | Null | Key | Default | Extra |
709+----------------+---------------------+------+-----+---------+-------+
710| user | varchar(128) | YES | | NULL | |
711| total | decimal(42,0) | YES | | NULL | |
712| total_latency | decimal(42,0) | YES | | NULL | |
713| min_latency | bigint(20) unsigned | YES | | NULL | |
714| avg_latency | decimal(24,4) | YES | | NULL | |
715| max_latency | bigint(20) unsigned | YES | | NULL | |
716| thread_id | bigint(20) unsigned | NO | | NULL | |
717| processlist_id | bigint(20) unsigned | YES | | NULL | |
718+----------------+---------------------+------+-----+---------+-------+
7198 rows in set (0.03 sec)
720```
721
722##### Example
723
724```SQL
725mysql> select * from io_by_thread_by_latency;
726+---------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
727| user | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
728+---------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
729| root@localhost | 11580 | 18.01 s | 429.78 ns | 1.12 ms | 181.07 ms | 25 | 6 |
730| main | 1358 | 1.31 s | 475.02 ns | 2.27 ms | 350.70 ms | 1 | NULL |
731| page_cleaner_thread | 654 | 147.44 ms | 588.12 ns | 225.44 us | 46.41 ms | 18 | NULL |
732| io_write_thread | 131 | 107.75 ms | 8.60 us | 822.55 us | 27.69 ms | 8 | NULL |
733| io_write_thread | 46 | 47.07 ms | 10.64 us | 1.02 ms | 16.90 ms | 9 | NULL |
734| io_write_thread | 71 | 46.99 ms | 9.11 us | 661.81 us | 17.04 ms | 11 | NULL |
735| io_log_thread | 20 | 21.01 ms | 14.25 us | 1.05 ms | 7.08 ms | 3 | NULL |
736| srv_master_thread | 13 | 17.60 ms | 8.49 us | 1.35 ms | 9.99 ms | 16 | NULL |
737| srv_purge_thread | 4 | 1.81 ms | 34.31 us | 452.45 us | 1.02 ms | 17 | NULL |
738| io_write_thread | 19 | 951.39 us | 9.75 us | 50.07 us | 297.47 us | 10 | NULL |
739| signal_handler | 3 | 218.03 us | 21.64 us | 72.68 us | 154.84 us | 19 | NULL |
740+---------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
741```
742
743#### io_global_by_file_by_bytes / x$io_global_by_file_by_bytes
744
745##### Description
746
747Shows the top global IO consumers by bytes usage by file.
748
749##### Structures
750
751```SQL
752mysql> desc io_global_by_file_by_bytes;
753+---------------+---------------------+------+-----+---------+-------+
754| Field | Type | Null | Key | Default | Extra |
755+---------------+---------------------+------+-----+---------+-------+
756| file | varchar(512) | YES | | NULL | |
757| count_read | bigint(20) unsigned | NO | | NULL | |
758| total_read | text | YES | | NULL | |
759| avg_read | text | YES | | NULL | |
760| count_write | bigint(20) unsigned | NO | | NULL | |
761| total_written | text | YES | | NULL | |
762| avg_write | text | YES | | NULL | |
763| total | text | YES | | NULL | |
764| write_pct | decimal(26,2) | NO | | 0.00 | |
765+---------------+---------------------+------+-----+---------+-------+
7669 rows in set (0.15 sec)
767
768mysql> desc x$io_global_by_file_by_bytes;
769+---------------+---------------------+------+-----+---------+-------+
770| Field | Type | Null | Key | Default | Extra |
771+---------------+---------------------+------+-----+---------+-------+
772| file | varchar(512) | NO | | NULL | |
773| count_read | bigint(20) unsigned | NO | | NULL | |
774| total_read | bigint(20) | NO | | NULL | |
775| avg_read | decimal(23,4) | NO | | 0.0000 | |
776| count_write | bigint(20) unsigned | NO | | NULL | |
777| total_written | bigint(20) | NO | | NULL | |
778| avg_write | decimal(23,4) | NO | | 0.0000 | |
779| total | bigint(21) | NO | | 0 | |
780| write_pct | decimal(26,2) | NO | | 0.00 | |
781+---------------+---------------------+------+-----+---------+-------+
7829 rows in set (0.14 sec)
783```
784
785##### Example
786
787```SQL
788mysql> SELECT * FROM io_global_by_file_by_bytes LIMIT 5;
789+--------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
790| file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct |
791+--------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
792| @@datadir/ibdata1 | 147 | 4.27 MiB | 29.71 KiB | 3 | 48.00 KiB | 16.00 KiB | 4.31 MiB | 1.09 |
793| @@datadir/mysql/proc.MYD | 347 | 85.35 KiB | 252 bytes | 111 | 19.08 KiB | 176 bytes | 104.43 KiB | 18.27 |
794| @@datadir/ib_logfile0 | 6 | 68.00 KiB | 11.33 KiB | 8 | 4.00 KiB | 512 bytes | 72.00 KiB | 5.56 |
795| /opt/mysql/5.5.33/share/english/errmsg.sys | 3 | 43.68 KiB | 14.56 KiB | 0 | 0 bytes | 0 bytes | 43.68 KiB | 0.00 |
796| /opt/mysql/5.5.33/share/charsets/Index.xml | 1 | 17.89 KiB | 17.89 KiB | 0 | 0 bytes | 0 bytes | 17.89 KiB | 0.00 |
797+--------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
798```
799
800#### io_global_by_file_by_latency / x$io_global_by_file_by_latency
801
802##### Description
803
804Shows the top global IO consumers by latency by file.
805
806##### Structures
807
808```SQL
809mysql> desc io_global_by_file_by_latency;
810+---------------+---------------------+------+-----+---------+-------+
811| Field | Type | Null | Key | Default | Extra |
812+---------------+---------------------+------+-----+---------+-------+
813| file | varchar(512) | YES | | NULL | |
814| total | bigint(20) unsigned | NO | | NULL | |
815| total_latency | text | YES | | NULL | |
816| count_read | bigint(20) unsigned | NO | | NULL | |
817| read_latency | text | YES | | NULL | |
818| count_write | bigint(20) unsigned | NO | | NULL | |
819| write_latency | text | YES | | NULL | |
820| count_misc | bigint(20) unsigned | NO | | NULL | |
821| misc_latency | text | YES | | NULL | |
822+---------------+---------------------+------+-----+---------+-------+
8239 rows in set (0.00 sec)
824
825mysql> desc x$io_global_by_file_by_latency;
826+---------------+---------------------+------+-----+---------+-------+
827| Field | Type | Null | Key | Default | Extra |
828+---------------+---------------------+------+-----+---------+-------+
829| file | varchar(512) | NO | | NULL | |
830| total | bigint(20) unsigned | NO | | NULL | |
831| total_latency | bigint(20) unsigned | NO | | NULL | |
832| count_read | bigint(20) unsigned | NO | | NULL | |
833| read_latency | bigint(20) unsigned | NO | | NULL | |
834| count_write | bigint(20) unsigned | NO | | NULL | |
835| write_latency | bigint(20) unsigned | NO | | NULL | |
836| count_misc | bigint(20) unsigned | NO | | NULL | |
837| misc_latency | bigint(20) unsigned | NO | | NULL | |
838+---------------+---------------------+------+-----+---------+-------+
8399 rows in set (0.07 sec)
840```
841
842##### Example
843
844```SQL
845mysql> select * from io_global_by_file_by_latency limit 5;
846+-----------------------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
847| file | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
848+-----------------------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
849| @@datadir/sys/wait_classes_global_by_avg_latency_raw.frm~ | 24 | 451.99 ms | 0 | 0 ps | 4 | 108.07 us | 20 | 451.88 ms |
850| @@datadir/sys/innodb_buffer_stats_by_schema_raw.frm~ | 24 | 379.84 ms | 0 | 0 ps | 4 | 108.88 us | 20 | 379.73 ms |
851| @@datadir/sys/io_by_thread_by_latency_raw.frm~ | 24 | 379.46 ms | 0 | 0 ps | 4 | 101.37 us | 20 | 379.36 ms |
852| @@datadir/ibtmp1 | 53 | 373.45 ms | 0 | 0 ps | 48 | 246.08 ms | 5 | 127.37 ms |
853| @@datadir/sys/statement_analysis_raw.frm~ | 24 | 353.14 ms | 0 | 0 ps | 4 | 94.96 us | 20 | 353.04 ms |
854+-----------------------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
855```
856
857#### io_global_by_wait_by_bytes / x$io_global_by_wait_by_bytes
858
859##### Description
860
861Shows the top global IO consumer classes by bytes usage.
862
863##### Structures
864
865```SQL
866mysql> desc io_global_by_wait_by_bytes;
867+-----------------+---------------------+------+-----+---------+-------+
868| Field | Type | Null | Key | Default | Extra |
869+-----------------+---------------------+------+-----+---------+-------+
870| event_name | varchar(128) | YES | | NULL | |
871| total | bigint(20) unsigned | NO | | NULL | |
872| total_latency | text | YES | | NULL | |
873| min_latency | text | YES | | NULL | |
874| avg_latency | text | YES | | NULL | |
875| max_latency | text | YES | | NULL | |
876| count_read | bigint(20) unsigned | NO | | NULL | |
877| total_read | text | YES | | NULL | |
878| avg_read | text | YES | | NULL | |
879| count_write | bigint(20) unsigned | NO | | NULL | |
880| total_written | text | YES | | NULL | |
881| avg_written | text | YES | | NULL | |
882| total_requested | text | YES | | NULL | |
883+-----------------+---------------------+------+-----+---------+-------+
88413 rows in set (0.02 sec)
885
886mysql> desc x$io_global_by_wait_by_bytes;
887+-----------------+---------------------+------+-----+---------+-------+
888| Field | Type | Null | Key | Default | Extra |
889+-----------------+---------------------+------+-----+---------+-------+
890| event_name | varchar(128) | YES | | NULL | |
891| total | bigint(20) unsigned | NO | | NULL | |
892| total_latency | bigint(20) unsigned | NO | | NULL | |
893| min_latency | bigint(20) unsigned | NO | | NULL | |
894| avg_latency | bigint(20) unsigned | NO | | NULL | |
895| max_latency | bigint(20) unsigned | NO | | NULL | |
896| count_read | bigint(20) unsigned | NO | | NULL | |
897| total_read | bigint(20) | NO | | NULL | |
898| avg_read | decimal(23,4) | NO | | 0.0000 | |
899| count_write | bigint(20) unsigned | NO | | NULL | |
900| total_written | bigint(20) | NO | | NULL | |
901| avg_written | decimal(23,4) | NO | | 0.0000 | |
902| total_requested | bigint(21) | NO | | 0 | |
903+-----------------+---------------------+------+-----+---------+-------+
90413 rows in set (0.01 sec)
905```
906
907##### Example
908
909```SQL
910mysql> select * from io_global_by_wait_by_bytes;
911+--------------------+--------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+
912| event_name | total | total_latency | min_latency | avg_latency | max_latency | count_read | total_read | avg_read | count_write | total_written | avg_written | total_requested |
913+--------------------+--------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+
914| myisam/dfile | 163681 | 983.13 ms | 379.08 ns | 6.01 us | 22.06 ms | 68737 | 127.31 MiB | 1.90 KiB | 1012221 | 121.52 MiB | 126 bytes | 248.83 MiB |
915| myisam/kfile | 1775 | 375.13 ms | 1.02 us | 211.34 µs | 35.15 ms | 54066 | 9.97 MiB | 193 bytes | 428257 | 12.40 MiB | 30 bytes | 22.37 MiB |
916| sql/FRM | 57889 | 8.40 s | 19.44 ns | 145.05 us | 336.71 ms | 8009 | 2.60 MiB | 341 bytes | 14675 | 2.91 MiB | 208 bytes | 5.51 MiB |
917| sql/global_ddl_log | 164 | 75.96 ms | 5.72 us | 463.19 µs | 7.43 ms | 20 | 80.00 KiB | 4.00 KiB | 76 | 304.00 KiB | 4.00 KiB | 384.00 KiB |
918| sql/file_parser | 419 | 601.37 ms | 1.96 us | 1.44 ms | 37.14 ms | 66 | 42.01 KiB | 652 bytes | 64 | 226.98 KiB | 3.55 KiB | 268.99 KiB |
919| sql/binlog | 190 | 6.79 s | 1.56 us | 35.76 ms | 4.21 s | 52 | 60.54 KiB | 1.16 KiB | 0 | 0 bytes | 0 bytes | 60.54 KiB |
920| sql/ERRMSG | 5 | 2.03 s | 8.61 us | 405.40 ms | 2.03 s | 3 | 51.82 KiB | 17.27 KiB | 0 | 0 bytes | 0 bytes | 51.82 KiB |
921| mysys/charset | 3 | 196.52 us | 17.61 µs | 65.51 µs | 137.33 µs | 1 | 17.83 KiB | 17.83 KiB | 0 | 0 bytes | 0 bytes | 17.83 KiB |
922| sql/partition | 81 | 18.87 ms | 888.08 ns | 232.92 us | 4.67 ms | 66 | 2.75 KiB | 43 bytes | 8 | 288 bytes | 36 bytes | 3.04 KiB |
923| sql/dbopt | 329166 | 26.95 s | 2.06 us | 81.89 µs | 178.71 ms | 0 | 0 bytes | 0 bytes | 9 | 585 bytes | 65 bytes | 585 bytes |
924| sql/relaylog | 7 | 1.18 ms | 838.84 ns | 168.30 us | 892.70 µs | 0 | 0 bytes | 0 bytes | 1 | 120 bytes | 120 bytes | 120 bytes |
925| mysys/cnf | 5 | 171.61 us | 303.26 ns | 34.32 µs | 115.21 µs | 3 | 56 bytes | 19 bytes | 0 | 0 bytes | 0 bytes | 56 bytes |
926| sql/pid | 3 | 220.55 us | 29.29 µs | 73.52 µs | 143.11 µs | 0 | 0 bytes | 0 bytes | 1 | 5 bytes | 5 bytes | 5 bytes |
927| sql/casetest | 1 | 121.19 us | 121.19 µs | 121.19 µs | 121.19 µs | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes | 0 bytes |
928| sql/binlog_index | 5 | 593.47 us | 1.07 µs | 118.69 µs | 535.90 µs | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes | 0 bytes |
929| sql/misc | 23 | 2.73 ms | 65.14 us | 118.50 µs | 255.31 µs | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes | 0 bytes |
930+--------------------+--------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+
931```
932
933#### io_global_by_wait_by_latency / x$io_global_by_wait_by_latency
934
935##### Description
936
937Shows the top global IO consumers by latency.
938
939##### Structures
940
941```SQL
942mysql> desc io_global_by_wait_by_latency;
943+---------------+---------------------+------+-----+---------+-------+
944| Field | Type | Null | Key | Default | Extra |
945+---------------+---------------------+------+-----+---------+-------+
946| event_name | varchar(128) | YES | | NULL | |
947| total | bigint(20) unsigned | NO | | NULL | |
948| total_latency | text | YES | | NULL | |
949| avg_latency | text | YES | | NULL | |
950| max_latency | text | YES | | NULL | |
951| read_latency | text | YES | | NULL | |
952| write_latency | text | YES | | NULL | |
953| misc_latency | text | YES | | NULL | |
954| count_read | bigint(20) unsigned | NO | | NULL | |
955| total_read | text | YES | | NULL | |
956| avg_read | text | YES | | NULL | |
957| count_write | bigint(20) unsigned | NO | | NULL | |
958| total_written | text | YES | | NULL | |
959| avg_written | text | YES | | NULL | |
960+---------------+---------------------+------+-----+---------+-------+
96114 rows in set (0.19 sec)
962
963mysql> desc x$io_global_by_wait_by_latency;
964+---------------+---------------------+------+-----+---------+-------+
965| Field | Type | Null | Key | Default | Extra |
966+---------------+---------------------+------+-----+---------+-------+
967| event_name | varchar(128) | YES | | NULL | |
968| total | bigint(20) unsigned | NO | | NULL | |
969| total_latency | bigint(20) unsigned | NO | | NULL | |
970| avg_latency | bigint(20) unsigned | NO | | NULL | |
971| max_latency | bigint(20) unsigned | NO | | NULL | |
972| read_latency | bigint(20) unsigned | NO | | NULL | |
973| write_latency | bigint(20) unsigned | NO | | NULL | |
974| misc_latency | bigint(20) unsigned | NO | | NULL | |
975| count_read | bigint(20) unsigned | NO | | NULL | |
976| total_read | bigint(20) | NO | | NULL | |
977| avg_read | decimal(23,4) | NO | | 0.0000 | |
978| count_write | bigint(20) unsigned | NO | | NULL | |
979| total_written | bigint(20) | NO | | NULL | |
980| avg_written | decimal(23,4) | NO | | 0.0000 | |
981+---------------+---------------------+------+-----+---------+-------+
98214 rows in set (0.01 sec)
983```
984
985##### Example
986
987```SQL
988mysql> SELECT * FROM io_global_by_wait_by_latency;
989+-------------------------+-------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
990| event_name | total | total_latency | avg_latency | max_latency | read_latency | write_latency | misc_latency | count_read | total_read | avg_read | count_write | total_written | avg_written |
991+-------------------------+-------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
992| sql/file_parser | 5433 | 30.20 s | 5.56 ms | 203.65 ms | 22.08 ms | 24.89 ms | 30.16 s | 24 | 6.18 KiB | 264 bytes | 737 | 2.15 MiB | 2.99 KiB |
993| innodb/innodb_data_file | 1344 | 1.52 s | 1.13 ms | 350.70 ms | 203.82 ms | 450.96 ms | 868.21 ms | 147 | 2.30 MiB | 16.00 KiB | 1001 | 53.61 MiB | 54.84 KiB |
994| innodb/innodb_log_file | 828 | 893.48 ms | 1.08 ms | 30.11 ms | 16.32 ms | 705.89 ms | 171.27 ms | 6 | 68.00 KiB | 11.33 KiB | 413 | 2.19 MiB | 5.42 KiB |
995| myisam/kfile | 7642 | 242.34 ms | 31.71 us | 19.27 ms | 73.60 ms | 23.48 ms | 145.26 ms | 758 | 135.63 KiB | 183 bytes | 4386 | 232.52 KiB | 54 bytes |
996| myisam/dfile | 12540 | 223.47 ms | 17.82 us | 32.50 ms | 87.76 ms | 16.97 ms | 118.74 ms | 5390 | 4.49 MiB | 873 bytes | 1448 | 2.65 MiB | 1.88 KiB |
997| csv/metadata | 8 | 28.98 ms | 3.62 ms | 20.15 ms | 399.27 us | 0 ps | 28.58 ms | 2 | 70 bytes | 35 bytes | 0 | 0 bytes | 0 bytes |
998| mysys/charset | 3 | 24.24 ms | 8.08 ms | 24.15 ms | 24.15 ms | 0 ps | 93.18 us | 1 | 17.31 KiB | 17.31 KiB | 0 | 0 bytes | 0 bytes |
999| sql/ERRMSG | 5 | 20.43 ms | 4.09 ms | 19.31 ms | 20.32 ms | 0 ps | 103.20 us | 3 | 58.97 KiB | 19.66 KiB | 0 | 0 bytes | 0 bytes |
1000| mysys/cnf | 5 | 11.37 ms | 2.27 ms | 11.28 ms | 11.29 ms | 0 ps | 78.22 us | 3 | 56 bytes | 19 bytes | 0 | 0 bytes | 0 bytes |
1001| sql/dbopt | 57 | 4.04 ms | 70.92 us | 843.70 us | 0 ps | 186.43 us | 3.86 ms | 0 | 0 bytes | 0 bytes | 7 | 431 bytes | 62 bytes |
1002| csv/data | 4 | 411.55 us | 102.89 us | 234.89 us | 0 ps | 0 ps | 411.55 us | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes |
1003| sql/misc | 22 | 340.38 us | 15.47 us | 33.77 us | 0 ps | 0 ps | 340.38 us | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes |
1004| archive/data | 39 | 277.86 us | 7.12 us | 16.18 us | 0 ps | 0 ps | 277.86 us | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes |
1005| sql/pid | 3 | 218.03 us | 72.68 us | 154.84 us | 0 ps | 21.64 us | 196.39 us | 0 | 0 bytes | 0 bytes | 1 | 6 bytes | 6 bytes |
1006| sql/casetest | 5 | 197.15 us | 39.43 us | 126.31 us | 0 ps | 0 ps | 197.15 us | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes |
1007| sql/global_ddl_log | 2 | 14.60 us | 7.30 us | 12.12 us | 0 ps | 0 ps | 14.60 us | 0 | 0 bytes | 0 bytes | 0 | 0 bytes | 0 bytes |
1008+-------------------------+-------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
1009```
1010
1011#### latest_file_io / x$latest_file_io
1012
1013##### Description
1014
1015Shows the latest file IO, by file / thread.
1016
1017##### Structures
1018
1019```SQL
1020mysql> desc latest_file_io;
1021+-----------+--------------+------+-----+---------+-------+
1022| Field | Type | Null | Key | Default | Extra |
1023+-----------+--------------+------+-----+---------+-------+
1024| thread | varchar(149) | YES | | NULL | |
1025| file | varchar(512) | YES | | NULL | |
1026| latency | text | YES | | NULL | |
1027| operation | varchar(32) | NO | | NULL | |
1028| requested | text | YES | | NULL | |
1029+-----------+--------------+------+-----+---------+-------+
10305 rows in set (0.10 sec)
1031
1032mysql> desc x$latest_file_io;
1033+-----------+---------------------+------+-----+---------+-------+
1034| Field | Type | Null | Key | Default | Extra |
1035+-----------+---------------------+------+-----+---------+-------+
1036| thread | varchar(149) | YES | | NULL | |
1037| file | varchar(512) | YES | | NULL | |
1038| latency | bigint(20) unsigned | YES | | NULL | |
1039| operation | varchar(32) | NO | | NULL | |
1040| requested | bigint(20) | YES | | NULL | |
1041+-----------+---------------------+------+-----+---------+-------+
10425 rows in set (0.05 sec)
1043```
1044
1045##### Example
1046
1047```SQL
1048mysql> select * from latest_file_io limit 5;
1049+----------------------+----------------------------------------+------------+-----------+-----------+
1050| thread | file | latency | operation | requested |
1051+----------------------+----------------------------------------+------------+-----------+-----------+
1052| msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 9.26 us | write | 124 bytes |
1053| msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 4.00 us | write | 2 bytes |
1054| msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 56.34 us | close | NULL |
1055| msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYD | 53.93 us | close | NULL |
1056| msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 104.05 ms | delete | NULL |
1057+----------------------+----------------------------------------+------------+-----------+-----------+
1058```
1059
1060#### memory_by_host_by_current_bytes / x$memory_by_host_by_current_bytes
1061
1062##### Description
1063
1064Summarizes memory use by host using the 5.7 Performance Schema instrumentation.
1065
1066When the host found is NULL, it is assumed to be a local "background" thread.
1067
1068##### Structures
1069
1070```SQL
1071mysql> desc memory_by_host_by_current_bytes;
1072+--------------------+---------------+------+-----+---------+-------+
1073| Field | Type | Null | Key | Default | Extra |
1074+--------------------+---------------+------+-----+---------+-------+
1075| host | varchar(60) | YES | | NULL | |
1076| current_count_used | decimal(41,0) | YES | | NULL | |
1077| current_allocated | text | YES | | NULL | |
1078| current_avg_alloc | text | YES | | NULL | |
1079| current_max_alloc | text | YES | | NULL | |
1080| total_allocated | text | YES | | NULL | |
1081+--------------------+---------------+------+-----+---------+-------+
10826 rows in set (0.24 sec)
1083
1084mysql> desc x$memory_by_host_by_current_bytes;
1085+--------------------+---------------+------+-----+---------+-------+
1086| Field | Type | Null | Key | Default | Extra |
1087+--------------------+---------------+------+-----+---------+-------+
1088| host | varchar(60) | YES | | NULL | |
1089| current_count_used | decimal(41,0) | YES | | NULL | |
1090| current_allocated | decimal(41,0) | YES | | NULL | |
1091| current_avg_alloc | decimal(45,4) | NO | | 0.0000 | |
1092| current_max_alloc | bigint(20) | YES | | NULL | |
1093| total_allocated | decimal(42,0) | YES | | NULL | |
1094+--------------------+---------------+------+-----+---------+-------+
10956 rows in set (0.28 sec)
1096```
1097
1098##### Example
1099
1100```SQL
1101mysql> select * from memory_by_host_by_current_bytes WHERE host IS NOT NULL;
1102 +------------+--------------------+-------------------+-------------------+-------------------+-----------------+
1103 | host | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
1104 +------------+--------------------+-------------------+-------------------+-------------------+-----------------+
1105 | background | 2773 | 10.84 MiB | 4.00 KiB | 8.00 MiB | 30.69 MiB |
1106 | localhost | 1509 | 809.30 KiB | 549 bytes | 176.38 KiB | 83.59 MiB |
1107 +------------+--------------------+-------------------+-------------------+-------------------+-----------------+
1108```
1109
1110#### memory_by_thread_by_current_bytes / x$memory_by_thread_by_current_bytes
1111
1112##### Description
1113
1114Summarizes memory use by user using the 5.7 Performance Schema instrumentation.
1115
1116The user columns shows either the background or foreground user name appropriately.
1117
1118##### Structures
1119
1120```SQL
1121mysql> desc memory_by_thread_by_current_bytes;
1122+--------------------+---------------------+------+-----+---------+-------+
1123| Field | Type | Null | Key | Default | Extra |
1124+--------------------+---------------------+------+-----+---------+-------+
1125| thread_id | bigint(20) unsigned | NO | | NULL | |
1126| user | varchar(128) | YES | | NULL | |
1127| current_count_used | decimal(41,0) | YES | | NULL | |
1128| current_allocated | text | YES | | NULL | |
1129| current_avg_alloc | text | YES | | NULL | |
1130| current_max_alloc | text | YES | | NULL | |
1131| total_allocated | text | YES | | NULL | |
1132+--------------------+---------------------+------+-----+---------+-------+
11337 rows in set (0.49 sec)
1134
1135mysql> desc x$memory_by_thread_by_current_bytes;
1136+--------------------+---------------------+------+-----+---------+-------+
1137| Field | Type | Null | Key | Default | Extra |
1138+--------------------+---------------------+------+-----+---------+-------+
1139| thread_id | bigint(20) unsigned | NO | | NULL | |
1140| user | varchar(128) | YES | | NULL | |
1141| current_count_used | decimal(41,0) | YES | | NULL | |
1142| current_allocated | decimal(41,0) | YES | | NULL | |
1143| current_avg_alloc | decimal(45,4) | NO | | 0.0000 | |
1144| current_max_alloc | bigint(20) | YES | | NULL | |
1145| total_allocated | decimal(42,0) | YES | | NULL | |
1146+--------------------+---------------------+------+-----+---------+-------+
11477 rows in set (0.25 sec)
1148```
1149
1150##### Example
1151
1152```SQL
1153mysql> select * from sys.memory_by_thread_by_current_bytes limit 5;
1154+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
1155| thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
1156+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
1157| 1 | sql/main | 29333 | 166.02 MiB | 5.80 KiB | 131.13 MiB | 196.00 MiB |
1158| 55 | root@localhost | 175 | 1.04 MiB | 6.09 KiB | 350.86 KiB | 67.37 MiB |
1159| 58 | root@localhost | 236 | 368.13 KiB | 1.56 KiB | 312.05 KiB | 130.34 MiB |
1160| 904 | root@localhost | 32 | 18.00 KiB | 576 bytes | 16.00 KiB | 6.68 MiB |
1161| 970 | root@localhost | 12 | 16.80 KiB | 1.40 KiB | 16.00 KiB | 1.20 MiB |
1162+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
1163```
1164
1165#### memory_by_user_by_current_bytes / x$memory_by_user_by_current_bytes
1166
1167##### Description
1168
1169Summarizes memory use by user using the 5.7 Performance Schema instrumentation.
1170
1171When the user found is NULL, it is assumed to be a "background" thread.
1172
1173##### Structures
1174
1175```SQL
1176mysql> desc memory_by_user_by_current_bytes;
1177+--------------------+---------------+------+-----+---------+-------+
1178| Field | Type | Null | Key | Default | Extra |
1179+--------------------+---------------+------+-----+---------+-------+
1180| user | varchar(32) | YES | | NULL | |
1181| current_count_used | decimal(41,0) | YES | | NULL | |
1182| current_allocated | text | YES | | NULL | |
1183| current_avg_alloc | text | YES | | NULL | |
1184| current_max_alloc | text | YES | | NULL | |
1185| total_allocated | text | YES | | NULL | |
1186+--------------------+---------------+------+-----+---------+-------+
11876 rows in set (0.06 sec)
1188
1189mysql> desc x$memory_by_user_by_current_bytes;
1190+--------------------+---------------+------+-----+---------+-------+
1191| Field | Type | Null | Key | Default | Extra |
1192+--------------------+---------------+------+-----+---------+-------+
1193| user | varchar(32) | YES | | NULL | |
1194| current_count_used | decimal(41,0) | YES | | NULL | |
1195| current_allocated | decimal(41,0) | YES | | NULL | |
1196| current_avg_alloc | decimal(45,4) | NO | | 0.0000 | |
1197| current_max_alloc | bigint(20) | YES | | NULL | |
1198| total_allocated | decimal(42,0) | YES | | NULL | |
1199+--------------------+---------------+------+-----+---------+-------+
12006 rows in set (0.12 sec)
1201```
1202
1203##### Example
1204
1205```SQL
1206mysql> select * from memory_by_user_by_current_bytes;
1207+------+--------------------+-------------------+-------------------+-------------------+-----------------+
1208| user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
1209+------+--------------------+-------------------+-------------------+-------------------+-----------------+
1210| root | 1401 | 1.09 MiB | 815 bytes | 334.97 KiB | 42.73 MiB |
1211| mark | 201 | 496.08 KiB | 2.47 KiB | 334.97 KiB | 5.50 MiB |
1212+------+--------------------+-------------------+-------------------+-------------------+-----------------+
1213```
1214
1215#### memory_global_by_current_bytes / x$memory_global_by_current_bytes
1216
1217##### Description
1218
1219Shows the current memory usage within the server globally broken down by allocation type.
1220
1221##### Structures
1222
1223```SQL
1224mysql> desc memory_global_by_current_bytes;
1225+-------------------+--------------+------+-----+---------+-------+
1226| Field | Type | Null | Key | Default | Extra |
1227+-------------------+--------------+------+-----+---------+-------+
1228| event_name | varchar(128) | NO | | NULL | |
1229| current_count | bigint(20) | NO | | NULL | |
1230| current_alloc | text | YES | | NULL | |
1231| current_avg_alloc | text | YES | | NULL | |
1232| high_count | bigint(20) | NO | | NULL | |
1233| high_alloc | text | YES | | NULL | |
1234| high_avg_alloc | text | YES | | NULL | |
1235+-------------------+--------------+------+-----+---------+-------+
12367 rows in set (0.08 sec)
1237
1238mysql> desc x$memory_global_by_current_bytes;
1239+-------------------+---------------+------+-----+---------+-------+
1240| Field | Type | Null | Key | Default | Extra |
1241+-------------------+---------------+------+-----+---------+-------+
1242| event_name | varchar(128) | NO | | NULL | |
1243| current_count | bigint(20) | NO | | NULL | |
1244| current_alloc | bigint(20) | NO | | NULL | |
1245| current_avg_alloc | decimal(23,4) | NO | | 0.0000 | |
1246| high_count | bigint(20) | NO | | NULL | |
1247| high_alloc | bigint(20) | NO | | NULL | |
1248| high_avg_alloc | decimal(23,4) | NO | | 0.0000 | |
1249+-------------------+---------------+------+-----+---------+-------+
12507 rows in set (0.16 sec)
1251```
1252
1253##### Example
1254
1255```SQL
1256mysql> select * from memory_global_by_current_bytes;
1257+----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
1258| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
1259+----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
1260| memory/sql/TABLE_SHARE::mem_root | 269 | 568.21 KiB | 2.11 KiB | 339 | 706.04 KiB | 2.08 KiB |
1261| memory/sql/TABLE | 214 | 366.56 KiB | 1.71 KiB | 245 | 481.13 KiB | 1.96 KiB |
1262| memory/sql/sp_head::main_mem_root | 32 | 334.97 KiB | 10.47 KiB | 421 | 9.73 MiB | 23.66 KiB |
1263| memory/sql/Filesort_buffer::sort_keys | 1 | 255.89 KiB | 255.89 KiB | 1 | 256.00 KiB | 256.00 KiB |
1264| memory/mysys/array_buffer | 82 | 121.66 KiB | 1.48 KiB | 1124 | 852.55 KiB | 777 bytes |
1265...
1266+----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
1267```
1268
1269#### memory_global_total / x$memory_global_total
1270
1271##### Description
1272
1273Shows the total memory usage within the server globally.
1274
1275##### Structures
1276
1277```SQL
1278mysql> desc memory_global_total;
1279+-----------------+------+------+-----+---------+-------+
1280| Field | Type | Null | Key | Default | Extra |
1281+-----------------+------+------+-----+---------+-------+
1282| total_allocated | text | YES | | NULL | |
1283+-----------------+------+------+-----+---------+-------+
12841 row in set (0.07 sec)
1285
1286mysql> desc x$memory_global_total;
1287+-----------------+---------------+------+-----+---------+-------+
1288| Field | Type | Null | Key | Default | Extra |
1289+-----------------+---------------+------+-----+---------+-------+
1290| total_allocated | decimal(41,0) | YES | | NULL | |
1291+-----------------+---------------+------+-----+---------+-------+
12921 row in set (0.00 sec)
1293```
1294
1295##### Example
1296
1297```SQL
1298mysql> select * from memory_global_total;
1299+-----------------+
1300| total_allocated |
1301+-----------------+
1302| 458.44 MiB |
1303+-----------------+
1304```
1305
1306#### metrics
1307
1308##### Description
1309
1310Creates a union of the following information:
1311
1312 * performance_schema.global_status (information_schema.GLOBAL_STATUS in MySQL 5.6)
1313 * information_schema.INNODB_METRICS
1314 * Performance Schema global memory usage information (only in MySQL 5.7)
1315 * Current time
1316
1317In MySQL 5.7 it is required that performance_schema = ON, though there is no requirements to which
1318instruments and consumers that are enabled. See also the description of the Enabled column below.
1319
1320For view has the following columns:
1321
1322 * Variable_name: The name of the variable
1323 * Variable_value: The value of the variable
1324 * Type: The type of the variable. This will depend on the source, e.g. Global Status, InnoDB Metrics - ..., etc.
1325 * Enabled: Whether the variable is enabled or not. Possible values are 'YES', 'NO', 'PARTIAL'.
1326 PARTIAL is currently only supported for the memory usage variables and means some but not all of the memory/% instruments are enabled.
1327
1328##### Structures
1329
1330```SQL
1331mysql> DESC metrics;
1332+----------------+--------------+------+-----+---------+-------+
1333| Field | Type | Null | Key | Default | Extra |
1334+----------------+--------------+------+-----+---------+-------+
1335| Variable_name | varchar(193) | YES | | NULL | |
1336| Variable_value | text | YES | | NULL | |
1337| Type | varchar(210) | YES | | NULL | |
1338| Enabled | varchar(7) | NO | | | |
1339+----------------+--------------+------+-----+---------+-------+
13404 rows in set (0.00 sec)
1341
1342mysq> DESC metrics_56;
1343+----------------+--------------+------+-----+---------+-------+
1344| Field | Type | Null | Key | Default | Extra |
1345+----------------+--------------+------+-----+---------+-------+
1346| Variable_name | varchar(193) | YES | | NULL | |
1347| Variable_value | text | YES | | NULL | |
1348| Type | varchar(210) | YES | | NULL | |
1349| Enabled | varchar(7) | NO | | | |
1350+----------------+--------------+------+-----+---------+-------+
13514 rows in set (0.01 sec)
1352```
1353
1354##### Example
1355
1356```SQL
1357mysql> SELECT * FROM metrics;
1358+-----------------------------------------------+-------------------------...+--------------------------------------+---------+
1359| Variable_name | Variable_value ...| Type | Enabled |
1360+-----------------------------------------------+-------------------------...+--------------------------------------+---------+
1361| aborted_clients | 0 ...| Global Status | YES |
1362| aborted_connects | 0 ...| Global Status | YES |
1363| binlog_cache_disk_use | 0 ...| Global Status | YES |
1364| binlog_cache_use | 0 ...| Global Status | YES |
1365| binlog_stmt_cache_disk_use | 0 ...| Global Status | YES |
1366| binlog_stmt_cache_use | 0 ...| Global Status | YES |
1367| bytes_received | 217081 ...| Global Status | YES |
1368| bytes_sent | 27257 ...| Global Status | YES |
1369...
1370| innodb_rwlock_x_os_waits | 0 ...| InnoDB Metrics - server | YES |
1371| innodb_rwlock_x_spin_rounds | 2723 ...| InnoDB Metrics - server | YES |
1372| innodb_rwlock_x_spin_waits | 1 ...| InnoDB Metrics - server | YES |
1373| trx_active_transactions | 0 ...| InnoDB Metrics - transaction | NO |
1374...
1375| trx_rseg_current_size | 0 ...| InnoDB Metrics - transaction | NO |
1376| trx_rseg_history_len | 4 ...| InnoDB Metrics - transaction | YES |
1377| trx_rw_commits | 0 ...| InnoDB Metrics - transaction | NO |
1378| trx_undo_slots_cached | 0 ...| InnoDB Metrics - transaction | NO |
1379| trx_undo_slots_used | 0 ...| InnoDB Metrics - transaction | NO |
1380| memory_current_allocated | 138244216 ...| Performance Schema | PARTIAL |
1381| memory_total_allocated | 138244216 ...| Performance Schema | PARTIAL |
1382| NOW() | 2015-05-31 13:27:50.382 ...| System Time | YES |
1383| UNIX_TIMESTAMP() | 1433042870.382 ...| System Time | YES |
1384+-----------------------------------------------+-------------------------...+--------------------------------------+---------+
1385412 rows in set (0.02 sec)
1386```
1387
1388#### processlist / x$processlist
1389
1390##### Description
1391
1392A detailed non-blocking processlist view to replace [INFORMATION_SCHEMA. | SHOW FULL] PROCESSLIST.
1393
1394Performs less locking than the legacy sources, whilst giving extra information.
1395
1396The output includes both background threads and user connections by default. See also `session` / `x$session`
1397for a view that contains only user session information.
1398
1399##### Structures (5.7)
1400
1401```SQL
1402mysql> desc processlist;
1403+------------------------+------------------------------------------+------+-----+---------+-------+
1404| Field | Type | Null | Key | Default | Extra |
1405+------------------------+------------------------------------------+------+-----+---------+-------+
1406| thd_id | bigint(20) unsigned | NO | | NULL | |
1407| conn_id | bigint(20) unsigned | YES | | NULL | |
1408| user | varchar(128) | YES | | NULL | |
1409| db | varchar(64) | YES | | NULL | |
1410| command | varchar(16) | YES | | NULL | |
1411| state | varchar(64) | YES | | NULL | |
1412| time | bigint(20) | YES | | NULL | |
1413| current_statement | longtext | YES | | NULL | |
1414| statement_latency | text | YES | | NULL | |
1415| progress | decimal(26,2) | YES | | NULL | |
1416| lock_latency | text | YES | | NULL | |
1417| rows_examined | bigint(20) unsigned | YES | | NULL | |
1418| rows_sent | bigint(20) unsigned | YES | | NULL | |
1419| rows_affected | bigint(20) unsigned | YES | | NULL | |
1420| tmp_tables | bigint(20) unsigned | YES | | NULL | |
1421| tmp_disk_tables | bigint(20) unsigned | YES | | NULL | |
1422| full_scan | varchar(3) | NO | | | |
1423| last_statement | longtext | YES | | NULL | |
1424| last_statement_latency | text | YES | | NULL | |
1425| current_memory | text | YES | | NULL | |
1426| last_wait | varchar(128) | YES | | NULL | |
1427| last_wait_latency | text | YES | | NULL | |
1428| source | varchar(64) | YES | | NULL | |
1429| trx_latency | text | YES | | NULL | |
1430| trx_state | enum('ACTIVE','COMMITTED','ROLLED BACK') | YES | | NULL | |
1431| trx_autocommit | enum('YES','NO') | YES | | NULL | |
1432| pid | varchar(1024) | YES | | NULL | |
1433| program_name | varchar(1024) | YES | | NULL | |
1434+------------------------+------------------------------------------+------+-----+---------+-------+
143528 rows in set (0.04 sec)
1436
1437mysql> desc x$processlist;
1438+------------------------+------------------------------------------+------+-----+---------+-------+
1439| Field | Type | Null | Key | Default | Extra |
1440+------------------------+------------------------------------------+------+-----+---------+-------+
1441| thd_id | bigint(20) unsigned | NO | | NULL | |
1442| conn_id | bigint(20) unsigned | YES | | NULL | |
1443| user | varchar(128) | YES | | NULL | |
1444| db | varchar(64) | YES | | NULL | |
1445| command | varchar(16) | YES | | NULL | |
1446| state | varchar(64) | YES | | NULL | |
1447| time | bigint(20) | YES | | NULL | |
1448| current_statement | longtext | YES | | NULL | |
1449| statement_latency | bigint(20) unsigned | YES | | NULL | |
1450| progress | decimal(26,2) | YES | | NULL | |
1451| lock_latency | bigint(20) unsigned | YES | | NULL | |
1452| rows_examined | bigint(20) unsigned | YES | | NULL | |
1453| rows_sent | bigint(20) unsigned | YES | | NULL | |
1454| rows_affected | bigint(20) unsigned | YES | | NULL | |
1455| tmp_tables | bigint(20) unsigned | YES | | NULL | |
1456| tmp_disk_tables | bigint(20) unsigned | YES | | NULL | |
1457| full_scan | varchar(3) | NO | | | |
1458| last_statement | longtext | YES | | NULL | |
1459| last_statement_latency | bigint(20) unsigned | YES | | NULL | |
1460| current_memory | decimal(41,0) | YES | | NULL | |
1461| last_wait | varchar(128) | YES | | NULL | |
1462| last_wait_latency | varchar(20) | YES | | NULL | |
1463| source | varchar(64) | YES | | NULL | |
1464| trx_latency | bigint(20) unsigned | YES | | NULL | |
1465| trx_state | enum('ACTIVE','COMMITTED','ROLLED BACK') | YES | | NULL | |
1466| trx_autocommit | enum('YES','NO') | YES | | NULL | |
1467| pid | varchar(1024) | YES | | NULL | |
1468| program_name | varchar(1024) | YES | | NULL | |
1469+------------------------+------------------------------------------+------+-----+---------+-------+
147028 rows in set (0.01 sec)
1471```
1472
1473##### Example
1474
1475```SQL
1476mysql> select * from sys.processlist where conn_id is not null and command != 'daemon' and conn_id != connection_id()\G
1477*************************** 1. row ***************************
1478 thd_id: 44524
1479 conn_id: 44502
1480 user: msandbox@localhost
1481 db: test
1482 command: Query
1483 state: alter table (flush)
1484 time: 18
1485 current_statement: alter table t1 add column g int
1486 statement_latency: 18.45 s
1487 progress: 98.84
1488 lock_latency: 265.43 ms
1489 rows_examined: 0
1490 rows_sent: 0
1491 rows_affected: 0
1492 tmp_tables: 0
1493 tmp_disk_tables: 0
1494 full_scan: NO
1495 last_statement: NULL
1496last_statement_latency: NULL
1497 current_memory: 664.06 KiB
1498 last_wait: wait/io/file/innodb/innodb_data_file
1499 last_wait_latency: 1.07 us
1500 source: fil0fil.cc:5146
1501 trx_latency: NULL
1502 trx_state: NULL
1503 trx_autocommit: NULL
1504 pid: 4212
1505 program_name: mysql
1506```
1507
1508#### ps_check_lost_instrumentation
1509
1510##### Description
1511
1512Used to check whether Performance Schema is not able to monitor all runtime data - only returns variables that have lost instruments
1513
1514##### Structure
1515
1516```SQL
1517mysql> desc ps_check_lost_instrumentation;
1518+----------------+---------------+------+-----+---------+-------+
1519| Field | Type | Null | Key | Default | Extra |
1520+----------------+---------------+------+-----+---------+-------+
1521| variable_name | varchar(64) | NO | | | |
1522| variable_value | varchar(1024) | YES | | NULL | |
1523+----------------+---------------+------+-----+---------+-------+
15242 rows in set (0.09 sec)
1525```
1526
1527##### Example
1528
1529```SQL
1530mysql> select * from ps_check_lost_instrumentation;
1531+----------------------------------------+----------------+
1532| variable_name | variable_value |
1533+----------------------------------------+----------------+
1534| Performance_schema_file_handles_lost | 101223 |
1535| Performance_schema_file_instances_lost | 1231 |
1536+----------------------------------------+----------------+
1537```
1538
1539#### schema_auto_increment_columns
1540
1541##### Description
1542
1543Present current auto_increment usage/capacity in all tables.
1544
1545##### Structures
1546
1547```SQL
1548mysql> desc schema_auto_increment_columns;
1549+----------------------+------------------------+------+-----+---------+-------+
1550| Field | Type | Null | Key | Default | Extra |
1551+----------------------+------------------------+------+-----+---------+-------+
1552| table_schema | varchar(64) | NO | | | |
1553| table_name | varchar(64) | NO | | | |
1554| column_name | varchar(64) | NO | | | |
1555| data_type | varchar(64) | NO | | | |
1556| column_type | longtext | NO | | NULL | |
1557| is_signed | int(1) | NO | | 0 | |
1558| is_unsigned | int(1) | NO | | 0 | |
1559| max_value | bigint(21) unsigned | YES | | NULL | |
1560| auto_increment | bigint(21) unsigned | YES | | NULL | |
1561| auto_increment_ratio | decimal(25,4) unsigned | YES | | NULL | |
1562+----------------------+------------------------+------+-----+---------+-------+
1563```
1564
1565##### Example
1566
1567```SQL
1568mysql> select * from schema_auto_increment_columns limit 5;
1569+-------------------+-------------------+-------------+-----------+-------------+-----------+-------------+---------------------+----------------+----------------------+
1570| table_schema | table_name | column_name | data_type | column_type | is_signed | is_unsigned | max_value | auto_increment | auto_increment_ratio |
1571+-------------------+-------------------+-------------+-----------+-------------+-----------+-------------+---------------------+----------------+----------------------+
1572| test | t1 | i | tinyint | tinyint(4) | 1 | 0 | 127 | 34 | 0.2677 |
1573| mem__advisor_text | template_meta | hib_id | int | int(11) | 1 | 0 | 2147483647 | 516 | 0.0000 |
1574| mem__advisors | advisor_schedules | schedule_id | int | int(11) | 1 | 0 | 2147483647 | 249 | 0.0000 |
1575| mem__advisors | app_identity_path | hib_id | int | int(11) | 1 | 0 | 2147483647 | 251 | 0.0000 |
1576| mem__bean_config | plists | id | bigint | bigint(20) | 1 | 0 | 9223372036854775807 | 1 | 0.0000 |
1577+-------------------+-------------------+-------------+-----------+-------------+-----------+-------------+---------------------+----------------+----------------------+
1578```
1579
1580#### schema_index_statistics / x$schema_index_statistics
1581
1582##### Description
1583
1584Statistics around indexes.
1585
1586Ordered by the total wait time descending - top indexes are most contended.
1587
1588##### Structures
1589
1590```SQL
1591mysql> desc schema_index_statistics;
1592+----------------+---------------------+------+-----+---------+-------+
1593| Field | Type | Null | Key | Default | Extra |
1594+----------------+---------------------+------+-----+---------+-------+
1595| table_schema | varchar(64) | YES | | NULL | |
1596| table_name | varchar(64) | YES | | NULL | |
1597| index_name | varchar(64) | YES | | NULL | |
1598| rows_selected | bigint(20) unsigned | NO | | NULL | |
1599| select_latency | text | YES | | NULL | |
1600| rows_inserted | bigint(20) unsigned | NO | | NULL | |
1601| insert_latency | text | YES | | NULL | |
1602| rows_updated | bigint(20) unsigned | NO | | NULL | |
1603| update_latency | text | YES | | NULL | |
1604| rows_deleted | bigint(20) unsigned | NO | | NULL | |
1605| delete_latency | text | YES | | NULL | |
1606+----------------+---------------------+------+-----+---------+-------+
160711 rows in set (0.17 sec)
1608
1609mysql> desc x$schema_index_statistics;
1610+----------------+---------------------+------+-----+---------+-------+
1611| Field | Type | Null | Key | Default | Extra |
1612+----------------+---------------------+------+-----+---------+-------+
1613| table_schema | varchar(64) | YES | | NULL | |
1614| table_name | varchar(64) | YES | | NULL | |
1615| index_name | varchar(64) | YES | | NULL | |
1616| rows_selected | bigint(20) unsigned | NO | | NULL | |
1617| select_latency | bigint(20) unsigned | NO | | NULL | |
1618| rows_inserted | bigint(20) unsigned | NO | | NULL | |
1619| insert_latency | bigint(20) unsigned | NO | | NULL | |
1620| rows_updated | bigint(20) unsigned | NO | | NULL | |
1621| update_latency | bigint(20) unsigned | NO | | NULL | |
1622| rows_deleted | bigint(20) unsigned | NO | | NULL | |
1623| delete_latency | bigint(20) unsigned | NO | | NULL | |
1624+----------------+---------------------+------+-----+---------+-------+
162511 rows in set (0.42 sec)
1626```
1627
1628##### Example
1629
1630```SQL
1631mysql> select * from schema_index_statistics limit 5;
1632+------------------+-------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
1633| table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
1634+------------------+-------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
1635| mem | mysqlserver | PRIMARY | 6208 | 108.27 ms | 0 | 0 ps | 5470 | 1.47 s | 0 | 0 ps |
1636| mem | innodb | PRIMARY | 4666 | 76.27 ms | 0 | 0 ps | 4454 | 571.47 ms | 0 | 0 ps |
1637| mem | connection | PRIMARY | 1064 | 20.98 ms | 0 | 0 ps | 1064 | 457.30 ms | 0 | 0 ps |
1638| mem | environment | PRIMARY | 5566 | 151.17 ms | 0 | 0 ps | 694 | 252.57 ms | 0 | 0 ps |
1639| mem | querycache | PRIMARY | 1698 | 27.99 ms | 0 | 0 ps | 1698 | 371.72 ms | 0 | 0 ps |
1640+------------------+-------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
1641```
1642
1643#### schema_object_overview
1644
1645##### Description
1646
1647Shows an overview of the types of objects within each schema
1648
1649Note: On instances with a large numbers of objects, this could take some time to execute, and may not be recommended.
1650
1651##### Structure
1652
1653```SQL
1654mysql> desc schema_object_overview;
1655+-------------+-------------+------+-----+---------+-------+
1656| Field | Type | Null | Key | Default | Extra |
1657+-------------+-------------+------+-----+---------+-------+
1658| db | varchar(64) | NO | | | |
1659| object_type | varchar(64) | NO | | | |
1660| count | bigint(21) | NO | | 0 | |
1661+-------------+-------------+------+-----+---------+-------+
16623 rows in set (0.08 sec)
1663```
1664
1665##### Example
1666
1667```SQL
1668mysql> select * from schema_object_overview;
1669+--------------------+---------------+-------+
1670| db | object_type | count |
1671+--------------------+---------------+-------+
1672| information_schema | SYSTEM VIEW | 60 |
1673| mysql | BASE TABLE | 31 |
1674| mysql | INDEX (BTREE) | 69 |
1675| performance_schema | BASE TABLE | 76 |
1676| sys | BASE TABLE | 1 |
1677| sys | FUNCTION | 12 |
1678| sys | INDEX (BTREE) | 1 |
1679| sys | PROCEDURE | 22 |
1680| sys | TRIGGER | 2 |
1681| sys | VIEW | 91 |
1682+--------------------+---------------+-------+
168310 rows in set (1.58 sec)
1684```
1685
1686#### schema_table_statistics / x$schema_table_statistics
1687
1688##### Description
1689
1690Statistics around tables.
1691
1692Ordered by the total wait time descending - top tables are most contended.
1693
1694Also includes the helper view (used by schema_table_statistics_with_buffer as well):
1695
1696* x$ps_schema_table_statistics_io
1697
1698##### Structures
1699
1700```SQL
1701mysql> desc schema_table_statistics;
1702+-------------------+---------------------+------+-----+---------+-------+
1703| Field | Type | Null | Key | Default | Extra |
1704+-------------------+---------------------+------+-----+---------+-------+
1705| table_schema | varchar(64) | YES | | NULL | |
1706| table_name | varchar(64) | YES | | NULL | |
1707| total_latency | text | YES | | NULL | |
1708| rows_fetched | bigint(20) unsigned | NO | | NULL | |
1709| fetch_latency | text | YES | | NULL | |
1710| rows_inserted | bigint(20) unsigned | NO | | NULL | |
1711| insert_latency | text | YES | | NULL | |
1712| rows_updated | bigint(20) unsigned | NO | | NULL | |
1713| update_latency | text | YES | | NULL | |
1714| rows_deleted | bigint(20) unsigned | NO | | NULL | |
1715| delete_latency | text | YES | | NULL | |
1716| io_read_requests | decimal(42,0) | YES | | NULL | |
1717| io_read | text | YES | | NULL | |
1718| io_read_latency | text | YES | | NULL | |
1719| io_write_requests | decimal(42,0) | YES | | NULL | |
1720| io_write | text | YES | | NULL | |
1721| io_write_latency | text | YES | | NULL | |
1722| io_misc_requests | decimal(42,0) | YES | | NULL | |
1723| io_misc_latency | text | YES | | NULL | |
1724+-------------------+---------------------+------+-----+---------+-------+
172519 rows in set (0.12 sec)
1726
1727mysql> desc x$schema_table_statistics;
1728+-------------------+---------------------+------+-----+---------+-------+
1729| Field | Type | Null | Key | Default | Extra |
1730+-------------------+---------------------+------+-----+---------+-------+
1731| table_schema | varchar(64) | YES | | NULL | |
1732| table_name | varchar(64) | YES | | NULL | |
1733| total_latency | bigint(20) unsigned | NO | | NULL | |
1734| rows_fetched | bigint(20) unsigned | NO | | NULL | |
1735| fetch_latency | bigint(20) unsigned | NO | | NULL | |
1736| rows_inserted | bigint(20) unsigned | NO | | NULL | |
1737| insert_latency | bigint(20) unsigned | NO | | NULL | |
1738| rows_updated | bigint(20) unsigned | NO | | NULL | |
1739| update_latency | bigint(20) unsigned | NO | | NULL | |
1740| rows_deleted | bigint(20) unsigned | NO | | NULL | |
1741| delete_latency | bigint(20) unsigned | NO | | NULL | |
1742| io_read_requests | decimal(42,0) | YES | | NULL | |
1743| io_read | decimal(41,0) | YES | | NULL | |
1744| io_read_latency | decimal(42,0) | YES | | NULL | |
1745| io_write_requests | decimal(42,0) | YES | | NULL | |
1746| io_write | decimal(41,0) | YES | | NULL | |
1747| io_write_latency | decimal(42,0) | YES | | NULL | |
1748| io_misc_requests | decimal(42,0) | YES | | NULL | |
1749| io_misc_latency | decimal(42,0) | YES | | NULL | |
1750+-------------------+---------------------+------+-----+---------+-------+
175119 rows in set (0.13 sec)
1752
1753mysql> desc x$ps_schema_table_statistics_io;
1754+---------------------------+---------------+------+-----+---------+-------+
1755| Field | Type | Null | Key | Default | Extra |
1756+---------------------------+---------------+------+-----+---------+-------+
1757| table_schema | varchar(64) | YES | | NULL | |
1758| table_name | varchar(64) | YES | | NULL | |
1759| count_read | decimal(42,0) | YES | | NULL | |
1760| sum_number_of_bytes_read | decimal(41,0) | YES | | NULL | |
1761| sum_timer_read | decimal(42,0) | YES | | NULL | |
1762| count_write | decimal(42,0) | YES | | NULL | |
1763| sum_number_of_bytes_write | decimal(41,0) | YES | | NULL | |
1764| sum_timer_write | decimal(42,0) | YES | | NULL | |
1765| count_misc | decimal(42,0) | YES | | NULL | |
1766| sum_timer_misc | decimal(42,0) | YES | | NULL | |
1767+---------------------------+---------------+------+-----+---------+-------+
176810 rows in set (0.10 sec)
1769```
1770
1771##### Example
1772
1773```SQL
1774mysql> select * from schema_table_statistics\G
1775*************************** 1. row ***************************
1776 table_schema: sys
1777 table_name: sys_config
1778 total_latency: 0 ps
1779 rows_fetched: 0
1780 fetch_latency: 0 ps
1781 rows_inserted: 0
1782 insert_latency: 0 ps
1783 rows_updated: 0
1784 update_latency: 0 ps
1785 rows_deleted: 0
1786 delete_latency: 0 ps
1787 io_read_requests: 8
1788 io_read: 2.28 KiB
1789 io_read_latency: 727.32 us
1790io_write_requests: 0
1791 io_write: 0 bytes
1792 io_write_latency: 0 ps
1793 io_misc_requests: 10
1794 io_misc_latency: 126.88 us
1795```
1796
1797#### schema_redundant_indexes / x$schema_flattened_keys
1798
1799##### Description
1800
1801Shows indexes which are made redundant (or duplicate) by other (dominant) keys.
1802
1803Also includes the the helper view `x$schema_flattened_keys`.
1804
1805##### Structures
1806
1807```SQL
1808mysql> desc sys.schema_redundant_indexes;
1809+----------------------------+--------------+------+-----+---------+-------+
1810| Field | Type | Null | Key | Default | Extra |
1811+----------------------------+--------------+------+-----+---------+-------+
1812| table_schema | varchar(64) | NO | | | |
1813| table_name | varchar(64) | NO | | | |
1814| redundant_index_name | varchar(64) | NO | | | |
1815| redundant_index_columns | text | YES | | NULL | |
1816| redundant_index_non_unique | bigint(1) | YES | | NULL | |
1817| dominant_index_name | varchar(64) | NO | | | |
1818| dominant_index_columns | text | YES | | NULL | |
1819| dominant_index_non_unique | bigint(1) | YES | | NULL | |
1820| subpart_exists | int(1) | NO | | 0 | |
1821| sql_drop_index | varchar(223) | YES | | NULL | |
1822+----------------------------+--------------+------+-----+---------+-------+
182310 rows in set (0.00 sec)
1824
1825mysql> desc sys.x$schema_flattened_keys;
1826+----------------+-------------+------+-----+---------+-------+
1827| Field | Type | Null | Key | Default | Extra |
1828+----------------+-------------+------+-----+---------+-------+
1829| table_schema | varchar(64) | NO | | | |
1830| table_name | varchar(64) | NO | | | |
1831| index_name | varchar(64) | NO | | | |
1832| non_unique | bigint(1) | YES | | NULL | |
1833| subpart_exists | bigint(1) | YES | | NULL | |
1834| index_columns | text | YES | | NULL | |
1835+----------------+-------------+------+-----+---------+-------+
18366 rows in set (0.00 sec)
1837```
1838
1839##### Example
1840
1841```SQL
1842mysql> select * from sys.schema_redundant_indexes\G
1843*************************** 1. row ***************************
1844 table_schema: test
1845 table_name: rkey
1846 redundant_index_name: j
1847 redundant_index_columns: j
1848redundant_index_non_unique: 1
1849 dominant_index_name: j_2
1850 dominant_index_columns: j,k
1851 dominant_index_non_unique: 1
1852 subpart_exists: 0
1853 sql_drop_index: ALTER TABLE `test`.`rkey` DROP INDEX `j`
18541 row in set (0.20 sec)
1855
1856mysql> SHOW CREATE TABLE test.rkey\G
1857*************************** 1. row ***************************
1858 Table: rkey
1859Create Table: CREATE TABLE `rkey` (
1860 `i` int(11) NOT NULL,
1861 `j` int(11) DEFAULT NULL,
1862 `k` int(11) DEFAULT NULL,
1863 PRIMARY KEY (`i`),
1864 KEY `j` (`j`),
1865 KEY `j_2` (`j`,`k`)
1866) ENGINE=InnoDB DEFAULT CHARSET=latin1
18671 row in set (0.06 sec)
1868```
1869
1870#### schema_table_lock_waits / x$schema_table_lock_waits
1871
1872##### Description
1873
1874Shows sessions that are blocked waiting on table metadata locks, and who is blocking them.
1875
1876##### Structures
1877
1878```SQL
1879mysql> desc schema_table_lock_waits;
1880+------------------------------+---------------------+------+-----+---------+-------+
1881| Field | Type | Null | Key | Default | Extra |
1882+------------------------------+---------------------+------+-----+---------+-------+
1883| object_schema | varchar(64) | YES | | NULL | |
1884| object_name | varchar(64) | YES | | NULL | |
1885| waiting_thread_id | bigint(20) unsigned | NO | | NULL | |
1886| waiting_pid | bigint(20) unsigned | YES | | NULL | |
1887| waiting_account | text | YES | | NULL | |
1888| waiting_lock_type | varchar(32) | NO | | NULL | |
1889| waiting_lock_duration | varchar(32) | NO | | NULL | |
1890| waiting_query | longtext | YES | | NULL | |
1891| waiting_query_secs | bigint(20) | YES | | NULL | |
1892| waiting_query_rows_affected | bigint(20) unsigned | YES | | NULL | |
1893| waiting_query_rows_examined | bigint(20) unsigned | YES | | NULL | |
1894| blocking_thread_id | bigint(20) unsigned | NO | | NULL | |
1895| blocking_pid | bigint(20) unsigned | YES | | NULL | |
1896| blocking_account | text | YES | | NULL | |
1897| blocking_lock_type | varchar(32) | NO | | NULL | |
1898| blocking_lock_duration | varchar(32) | NO | | NULL | |
1899| sql_kill_blocking_query | varchar(31) | YES | | NULL | |
1900| sql_kill_blocking_connection | varchar(25) | YES | | NULL | |
1901+------------------------------+---------------------+------+-----+---------+-------+
190218 rows in set (0.15 sec)
1903
1904mysql> desc x$schema_table_lock_waits;
1905+------------------------------+---------------------+------+-----+---------+-------+
1906| Field | Type | Null | Key | Default | Extra |
1907+------------------------------+---------------------+------+-----+---------+-------+
1908| object_schema | varchar(64) | YES | | NULL | |
1909| object_name | varchar(64) | YES | | NULL | |
1910| waiting_thread_id | bigint(20) unsigned | NO | | NULL | |
1911| waiting_pid | bigint(20) unsigned | YES | | NULL | |
1912| waiting_account | text | YES | | NULL | |
1913| waiting_lock_type | varchar(32) | NO | | NULL | |
1914| waiting_lock_duration | varchar(32) | NO | | NULL | |
1915| waiting_query | longtext | YES | | NULL | |
1916| waiting_query_secs | bigint(20) | YES | | NULL | |
1917| waiting_query_rows_affected | bigint(20) unsigned | YES | | NULL | |
1918| waiting_query_rows_examined | bigint(20) unsigned | YES | | NULL | |
1919| blocking_thread_id | bigint(20) unsigned | NO | | NULL | |
1920| blocking_pid | bigint(20) unsigned | YES | | NULL | |
1921| blocking_account | text | YES | | NULL | |
1922| blocking_lock_type | varchar(32) | NO | | NULL | |
1923| blocking_lock_duration | varchar(32) | NO | | NULL | |
1924| sql_kill_blocking_query | varchar(31) | YES | | NULL | |
1925| sql_kill_blocking_connection | varchar(25) | YES | | NULL | |
1926+------------------------------+---------------------+------+-----+---------+-------+
192718 rows in set (0.03 sec)
1928```
1929
1930##### Example
1931
1932```SQL
1933mysql> select * from sys.schema_table_lock_waits\G
1934*************************** 1. row ***************************
1935 object_schema: test
1936 object_name: t
1937 waiting_thread_id: 43
1938 waiting_pid: 21
1939 waiting_account: msandbox@localhost
1940 waiting_lock_type: SHARED_UPGRADABLE
1941 waiting_lock_duration: TRANSACTION
1942 waiting_query: alter table test.t add foo int
1943 waiting_query_secs: 988
1944 waiting_query_rows_affected: 0
1945 waiting_query_rows_examined: 0
1946 blocking_thread_id: 42
1947 blocking_pid: 20
1948 blocking_account: msandbox@localhost
1949 blocking_lock_type: SHARED_NO_READ_WRITE
1950 blocking_lock_duration: TRANSACTION
1951 sql_kill_blocking_query: KILL QUERY 20
1952sql_kill_blocking_connection: KILL 20
1953```
1954
1955#### schema_table_statistics_with_buffer / x$schema_table_statistics_with_buffer
1956
1957##### Description
1958
1959Statistics around tables.
1960
1961Ordered by the total wait time descending - top tables are most contended.
1962
1963More statistics such as caching stats for the InnoDB buffer pool with InnoDB tables
1964
1965Uses the x$ps_schema_table_statistics_io helper view from schema_table_statistics.
1966
1967##### Structures
1968
1969```SQL
1970mysql> desc schema_table_statistics_with_buffer;
1971+----------------------------+---------------------+------+-----+---------+-------+
1972| Field | Type | Null | Key | Default | Extra |
1973+----------------------------+---------------------+------+-----+---------+-------+
1974| table_schema | varchar(64) | YES | | NULL | |
1975| table_name | varchar(64) | YES | | NULL | |
1976| rows_fetched | bigint(20) unsigned | NO | | NULL | |
1977| fetch_latency | text | YES | | NULL | |
1978| rows_inserted | bigint(20) unsigned | NO | | NULL | |
1979| insert_latency | text | YES | | NULL | |
1980| rows_updated | bigint(20) unsigned | NO | | NULL | |
1981| update_latency | text | YES | | NULL | |
1982| rows_deleted | bigint(20) unsigned | NO | | NULL | |
1983| delete_latency | text | YES | | NULL | |
1984| io_read_requests | decimal(42,0) | YES | | NULL | |
1985| io_read | text | YES | | NULL | |
1986| io_read_latency | text | YES | | NULL | |
1987| io_write_requests | decimal(42,0) | YES | | NULL | |
1988| io_write | text | YES | | NULL | |
1989| io_write_latency | text | YES | | NULL | |
1990| io_misc_requests | decimal(42,0) | YES | | NULL | |
1991| io_misc_latency | text | YES | | NULL | |
1992| innodb_buffer_allocated | text | YES | | NULL | |
1993| innodb_buffer_data | text | YES | | NULL | |
1994| innodb_buffer_free | text | YES | | NULL | |
1995| innodb_buffer_pages | bigint(21) | YES | | 0 | |
1996| innodb_buffer_pages_hashed | bigint(21) | YES | | 0 | |
1997| innodb_buffer_pages_old | bigint(21) | YES | | 0 | |
1998| innodb_buffer_rows_cached | decimal(44,0) | YES | | 0 | |
1999+----------------------------+---------------------+------+-----+---------+-------+
200025 rows in set (0.05 sec)
2001
2002mysql> desc x$schema_table_statistics_with_buffer;
2003+----------------------------+---------------------+------+-----+---------+-------+
2004| Field | Type | Null | Key | Default | Extra |
2005+----------------------------+---------------------+------+-----+---------+-------+
2006| table_schema | varchar(64) | YES | | NULL | |
2007| table_name | varchar(64) | YES | | NULL | |
2008| rows_fetched | bigint(20) unsigned | NO | | NULL | |
2009| fetch_latency | bigint(20) unsigned | NO | | NULL | |
2010| rows_inserted | bigint(20) unsigned | NO | | NULL | |
2011| insert_latency | bigint(20) unsigned | NO | | NULL | |
2012| rows_updated | bigint(20) unsigned | NO | | NULL | |
2013| update_latency | bigint(20) unsigned | NO | | NULL | |
2014| rows_deleted | bigint(20) unsigned | NO | | NULL | |
2015| delete_latency | bigint(20) unsigned | NO | | NULL | |
2016| io_read_requests | decimal(42,0) | YES | | NULL | |
2017| io_read | decimal(41,0) | YES | | NULL | |
2018| io_read_latency | decimal(42,0) | YES | | NULL | |
2019| io_write_requests | decimal(42,0) | YES | | NULL | |
2020| io_write | decimal(41,0) | YES | | NULL | |
2021| io_write_latency | decimal(42,0) | YES | | NULL | |
2022| io_misc_requests | decimal(42,0) | YES | | NULL | |
2023| io_misc_latency | decimal(42,0) | YES | | NULL | |
2024| innodb_buffer_allocated | decimal(43,0) | YES | | NULL | |
2025| innodb_buffer_data | decimal(43,0) | YES | | NULL | |
2026| innodb_buffer_free | decimal(44,0) | YES | | NULL | |
2027| innodb_buffer_pages | bigint(21) | YES | | 0 | |
2028| innodb_buffer_pages_hashed | bigint(21) | YES | | 0 | |
2029| innodb_buffer_pages_old | bigint(21) | YES | | 0 | |
2030| innodb_buffer_rows_cached | decimal(44,0) | YES | | 0 | |
2031+----------------------------+---------------------+------+-----+---------+-------+
203225 rows in set (0.17 sec)
2033```
2034
2035##### Example
2036
2037```SQL
2038mysql> select * from schema_table_statistics_with_buffer limit 1\G
2039*************************** 1. row ***************************
2040 table_schema: mem
2041 table_name: mysqlserver
2042 rows_fetched: 27087
2043 fetch_latency: 442.72 ms
2044 rows_inserted: 2
2045 insert_latency: 185.04 us
2046 rows_updated: 5096
2047 update_latency: 1.39 s
2048 rows_deleted: 0
2049 delete_latency: 0 ps
2050 io_read_requests: 2565
2051 io_read_bytes: 1121627
2052 io_read_latency: 10.07 ms
2053 io_write_requests: 1691
2054 io_write_bytes: 128383
2055 io_write_latency: 14.17 ms
2056 io_misc_requests: 2698
2057 io_misc_latency: 433.66 ms
2058 innodb_buffer_pages: 19
2059 innodb_buffer_pages_hashed: 19
2060 innodb_buffer_pages_old: 19
2061innodb_buffer_bytes_allocated: 311296
2062 innodb_buffer_bytes_data: 1924
2063 innodb_buffer_rows_cached: 2
2064```
2065
2066#### schema_tables_with_full_table_scans / x$schema_tables_with_full_table_scans
2067
2068##### Description
2069
2070Finds tables that are being accessed by full table scans ordering by the number of rows scanned descending.
2071
2072##### Structures
2073
2074```SQL
2075mysql> desc schema_tables_with_full_table_scans;
2076+-------------------+---------------------+------+-----+---------+-------+
2077| Field | Type | Null | Key | Default | Extra |
2078+-------------------+---------------------+------+-----+---------+-------+
2079| object_schema | varchar(64) | YES | | NULL | |
2080| object_name | varchar(64) | YES | | NULL | |
2081| rows_full_scanned | bigint(20) unsigned | NO | | NULL | |
2082| latency | text | YES | | NULL | |
2083+-------------------+---------------------+------+-----+---------+-------+
20844 rows in set (0.02 sec)
2085
2086mysql> desc x$schema_tables_with_full_table_scans;
2087+-------------------+---------------------+------+-----+---------+-------+
2088| Field | Type | Null | Key | Default | Extra |
2089+-------------------+---------------------+------+-----+---------+-------+
2090| object_schema | varchar(64) | YES | | NULL | |
2091| object_name | varchar(64) | YES | | NULL | |
2092| rows_full_scanned | bigint(20) unsigned | NO | | NULL | |
2093| latency | bigint(20) unsigned | NO | | NULL | |
2094+-------------------+---------------------+------+-----+---------+-------+
20954 rows in set (0.03 sec)
2096```
2097
2098##### Example
2099
2100```SQL
2101mysql> select * from schema_tables_with_full_table_scans limit 5;
2102+--------------------+--------------------------------+-------------------+-----------+
2103| object_schema | object_name | rows_full_scanned | latency |
2104+--------------------+--------------------------------+-------------------+-----------+
2105| mem30__instruments | fsstatistics | 10207042 | 13.10 s |
2106| mem30__instruments | preparedstatementapidata | 436428 | 973.27 ms |
2107| mem30__instruments | mysqlprocessactivity | 411702 | 282.07 ms |
2108| mem30__instruments | querycachequeriesincachedata | 374011 | 767.15 ms |
2109| mem30__instruments | rowaccessesdata | 322321 | 1.55 s |
2110+--------------------+--------------------------------+-------------------+-----------+
2111```
2112
2113#### schema_unused_indexes
2114
2115##### Description
2116
2117Finds indexes that have had no events against them (and hence, no usage).
2118
2119To trust whether the data from this view is representative of your workload, you should ensure that the server has been up for a representative amount of time before using it.
2120
2121PRIMARY (key) indexes are ignored.
2122
2123##### Structure
2124
2125```SQL
2126mysql> desc schema_unused_indexes;
2127+---------------+-------------+------+-----+---------+-------+
2128| Field | Type | Null | Key | Default | Extra |
2129+---------------+-------------+------+-----+---------+-------+
2130| object_schema | varchar(64) | YES | | NULL | |
2131| object_name | varchar(64) | YES | | NULL | |
2132| index_name | varchar(64) | YES | | NULL | |
2133+---------------+-------------+------+-----+---------+-------+
21343 rows in set (0.09 sec)
2135```
2136
2137##### Example
2138
2139```SQL
2140mysql> select * from schema_unused_indexes limit 5;
2141+--------------------+---------------------+--------------------+
2142| object_schema | object_name | index_name |
2143+--------------------+---------------------+--------------------+
2144| mem30__bean_config | plists | path |
2145| mem30__config | group_selections | name |
2146| mem30__config | notification_groups | name |
2147| mem30__config | user_form_defaults | FKC1AEF1F9E7EE2CFB |
2148| mem30__enterprise | whats_new_entries | entryId |
2149+--------------------+---------------------+--------------------+
2150```
2151
2152#### session / x$session
2153
2154##### Description
2155
2156A detailed non-blocking processlist view to replace [INFORMATION_SCHEMA. | SHOW FULL] PROCESSLIST.
2157
2158Performs less locking than the legacy sources, whilst giving extra information.
2159
2160The output of this view is restricted to threads from user sessions. See also processlist / x$processlist which contains both user and background threads.
2161
2162##### Structures (5.7)
2163
2164```SQL
2165mysql> desc session;
2166+------------------------+------------------------------------------+------+-----+---------+-------+
2167| Field | Type | Null | Key | Default | Extra |
2168+------------------------+------------------------------------------+------+-----+---------+-------+
2169| thd_id | bigint(20) unsigned | NO | | NULL | |
2170| conn_id | bigint(20) unsigned | YES | | NULL | |
2171| user | varchar(128) | YES | | NULL | |
2172| db | varchar(64) | YES | | NULL | |
2173| command | varchar(16) | YES | | NULL | |
2174| state | varchar(64) | YES | | NULL | |
2175| time | bigint(20) | YES | | NULL | |
2176| current_statement | longtext | YES | | NULL | |
2177| statement_latency | text | YES | | NULL | |
2178| progress | decimal(26,2) | YES | | NULL | |
2179| lock_latency | text | YES | | NULL | |
2180| rows_examined | bigint(20) unsigned | YES | | NULL | |
2181| rows_sent | bigint(20) unsigned | YES | | NULL | |
2182| rows_affected | bigint(20) unsigned | YES | | NULL | |
2183| tmp_tables | bigint(20) unsigned | YES | | NULL | |
2184| tmp_disk_tables | bigint(20) unsigned | YES | | NULL | |
2185| full_scan | varchar(3) | NO | | | |
2186| last_statement | longtext | YES | | NULL | |
2187| last_statement_latency | text | YES | | NULL | |
2188| current_memory | text | YES | | NULL | |
2189| last_wait | varchar(128) | YES | | NULL | |
2190| last_wait_latency | text | YES | | NULL | |
2191| source | varchar(64) | YES | | NULL | |
2192| trx_latency | text | YES | | NULL | |
2193| trx_state | enum('ACTIVE','COMMITTED','ROLLED BACK') | YES | | NULL | |
2194| trx_autocommit | enum('YES','NO') | YES | | NULL | |
2195| pid | varchar(1024) | YES | | NULL | |
2196| program_name | varchar(1024) | YES | | NULL | |
2197+------------------------+------------------------------------------+------+-----+---------+-------+
219828 rows in set (0.00 sec)
2199
2200mysql> desc x$session;
2201+------------------------+------------------------------------------+------+-----+---------+-------+
2202| Field | Type | Null | Key | Default | Extra |
2203+------------------------+------------------------------------------+------+-----+---------+-------+
2204| thd_id | bigint(20) unsigned | NO | | NULL | |
2205| conn_id | bigint(20) unsigned | YES | | NULL | |
2206| user | varchar(128) | YES | | NULL | |
2207| db | varchar(64) | YES | | NULL | |
2208| command | varchar(16) | YES | | NULL | |
2209| state | varchar(64) | YES | | NULL | |
2210| time | bigint(20) | YES | | NULL | |
2211| current_statement | longtext | YES | | NULL | |
2212| statement_latency | bigint(20) unsigned | YES | | NULL | |
2213| progress | decimal(26,2) | YES | | NULL | |
2214| lock_latency | bigint(20) unsigned | YES | | NULL | |
2215| rows_examined | bigint(20) unsigned | YES | | NULL | |
2216| rows_sent | bigint(20) unsigned | YES | | NULL | |
2217| rows_affected | bigint(20) unsigned | YES | | NULL | |
2218| tmp_tables | bigint(20) unsigned | YES | | NULL | |
2219| tmp_disk_tables | bigint(20) unsigned | YES | | NULL | |
2220| full_scan | varchar(3) | NO | | | |
2221| last_statement | longtext | YES | | NULL | |
2222| last_statement_latency | bigint(20) unsigned | YES | | NULL | |
2223| current_memory | decimal(41,0) | YES | | NULL | |
2224| last_wait | varchar(128) | YES | | NULL | |
2225| last_wait_latency | varchar(20) | YES | | NULL | |
2226| source | varchar(64) | YES | | NULL | |
2227| trx_latency | bigint(20) unsigned | YES | | NULL | |
2228| trx_state | enum('ACTIVE','COMMITTED','ROLLED BACK') | YES | | NULL | |
2229| trx_autocommit | enum('YES','NO') | YES | | NULL | |
2230| pid | varchar(1024) | YES | | NULL | |
2231| program_name | varchar(1024) | YES | | NULL | |
2232+------------------------+------------------------------------------+------+-----+---------+-------+
223328 rows in set (0.00 sec)
2234```
2235
2236##### Example
2237
2238```SQL
2239mysql> select * from sys.session\G
2240*************************** 1. row ***************************
2241 thd_id: 24
2242 conn_id: 2
2243 user: root@localhost
2244 db: sys
2245 command: Query
2246 state: Sending data
2247 time: 0
2248 current_statement: select * from sys.session
2249 statement_latency: 137.22 ms
2250 progress: NULL
2251 lock_latency: 33.75 ms
2252 rows_examined: 0
2253 rows_sent: 0
2254 rows_affected: 0
2255 tmp_tables: 4
2256 tmp_disk_tables: 1
2257 full_scan: YES
2258 last_statement: NULL
2259last_statement_latency: NULL
2260 current_memory: 3.26 MiB
2261 last_wait: wait/synch/mutex/innodb/file_format_max_mutex
2262 last_wait_latency: 64.09 ns
2263 source: trx0sys.cc:778
2264 trx_latency: 7.88 s
2265 trx_state: ACTIVE
2266 trx_autocommit: NO
2267 pid: 4212
2268 program_name: mysql
2269```
2270
2271#### session_ssl_status
2272
2273##### Description
2274
2275Shows SSL version, cipher and the count of re-used SSL sessions per connection
2276
2277##### Structures
2278
2279```SQL
2280mysql> desc sys.session_ssl_status;
2281+---------------------+---------------------+------+-----+---------+-------+
2282| Field | Type | Null | Key | Default | Extra |
2283+---------------------+---------------------+------+-----+---------+-------+
2284| thread_id | bigint(20) unsigned | NO | | NULL | |
2285| ssl_version | varchar(1024) | YES | | NULL | |
2286| ssl_cipher | varchar(1024) | YES | | NULL | |
2287| ssl_sessions_reused | varchar(1024) | YES | | NULL | |
2288+---------------------+---------------------+------+-----+---------+-------+
22894 rows in set (0.00 sec)
2290```
2291
2292##### Example
2293
2294```SQL
2295mysql> select * from session_ssl_status;
2296+-----------+-------------+--------------------+---------------------+
2297| thread_id | ssl_version | ssl_cipher | ssl_sessions_reused |
2298+-----------+-------------+--------------------+---------------------+
2299| 26 | TLSv1 | DHE-RSA-AES256-SHA | 0 |
2300| 27 | TLSv1 | DHE-RSA-AES256-SHA | 0 |
2301| 28 | TLSv1 | DHE-RSA-AES256-SHA | 0 |
2302+-----------+-------------+--------------------+---------------------+
23033 rows in set (0.00 sec)
2304```
2305
2306#### statement_analysis / x$statement_analysis
2307
2308##### Description
2309
2310Lists a normalized statement view with aggregated statistics, mimics the MySQL Enterprise Monitor Query Analysis view, ordered by the total execution time per normalized statement
2311
2312##### Structures
2313
2314```SQL
2315mysql> desc statement_analysis;
2316+-------------------+---------------------+------+-----+---------------------+-------+
2317| Field | Type | Null | Key | Default | Extra |
2318+-------------------+---------------------+------+-----+---------------------+-------+
2319| query | longtext | YES | | NULL | |
2320| db | varchar(64) | YES | | NULL | |
2321| full_scan | varchar(1) | NO | | | |
2322| exec_count | bigint(20) unsigned | NO | | NULL | |
2323| err_count | bigint(20) unsigned | NO | | NULL | |
2324| warn_count | bigint(20) unsigned | NO | | NULL | |
2325| total_latency | text | YES | | NULL | |
2326| max_latency | text | YES | | NULL | |
2327| avg_latency | text | YES | | NULL | |
2328| lock_latency | text | YES | | NULL | |
2329| rows_sent | bigint(20) unsigned | NO | | NULL | |
2330| rows_sent_avg | decimal(21,0) | NO | | 0 | |
2331| rows_examined | bigint(20) unsigned | NO | | NULL | |
2332| rows_examined_avg | decimal(21,0) | NO | | 0 | |
2333| rows_affected | bigint(20) unsigned | NO | | NULL | |
2334| rows_affected_avg | decimal(21,0) | NO | | 0 | |
2335| tmp_tables | bigint(20) unsigned | NO | | NULL | |
2336| tmp_disk_tables | bigint(20) unsigned | NO | | NULL | |
2337| rows_sorted | bigint(20) unsigned | NO | | NULL | |
2338| sort_merge_passes | bigint(20) unsigned | NO | | NULL | |
2339| digest | varchar(32) | YES | | NULL | |
2340| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2341| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2342+-------------------+---------------------+------+-----+---------------------+-------+
234323 rows in set (0.26 sec)
2344
2345mysql> desc x$statement_analysis;
2346+-------------------+---------------------+------+-----+---------------------+-------+
2347| Field | Type | Null | Key | Default | Extra |
2348+-------------------+---------------------+------+-----+---------------------+-------+
2349| query | longtext | YES | | NULL | |
2350| db | varchar(64) | YES | | NULL | |
2351| full_scan | varchar(1) | NO | | | |
2352| exec_count | bigint(20) unsigned | NO | | NULL | |
2353| err_count | bigint(20) unsigned | NO | | NULL | |
2354| warn_count | bigint(20) unsigned | NO | | NULL | |
2355| total_latency | bigint(20) unsigned | NO | | NULL | |
2356| max_latency | bigint(20) unsigned | NO | | NULL | |
2357| avg_latency | bigint(20) unsigned | NO | | NULL | |
2358| lock_latency | bigint(20) unsigned | NO | | NULL | |
2359| rows_sent | bigint(20) unsigned | NO | | NULL | |
2360| rows_sent_avg | decimal(21,0) | NO | | 0 | |
2361| rows_examined | bigint(20) unsigned | NO | | NULL | |
2362| rows_examined_avg | decimal(21,0) | NO | | 0 | |
2363| rows_affected | bigint(20) unsigned | NO | | NULL | |
2364| rows_affected_avg | decimal(21,0) | NO | | 0 | |
2365| tmp_tables | bigint(20) unsigned | NO | | NULL | |
2366| tmp_disk_tables | bigint(20) unsigned | NO | | NULL | |
2367| rows_sorted | bigint(20) unsigned | NO | | NULL | |
2368| sort_merge_passes | bigint(20) unsigned | NO | | NULL | |
2369| digest | varchar(32) | YES | | NULL | |
2370| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2371| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2372+-------------------+---------------------+------+-----+---------------------+-------+
237323 rows in set (0.27 sec)
2374```
2375
2376##### Example
2377
2378```SQL
2379mysql> select * from statement_analysis limit 1\G
2380*************************** 1. row ***************************
2381 query: SELECT * FROM `schema_object_o ... MA` , `information_schema` ...
2382 db: sys
2383 full_scan: *
2384 exec_count: 2
2385 err_count: 0
2386 warn_count: 0
2387 total_latency: 16.75 s
2388 max_latency: 16.57 s
2389 avg_latency: 8.38 s
2390 lock_latency: 16.69 s
2391 rows_sent: 84
2392 rows_sent_avg: 42
2393 rows_examined: 20012
2394rows_examined_avg: 10006
2395 rows_affected: 0
2396rows_affected_avg: 0
2397 tmp_tables: 378
2398 tmp_disk_tables: 66
2399 rows_sorted: 168
2400sort_merge_passes: 0
2401 digest: 54f9bd520f0bbf15db0c2ed93386bec9
2402 first_seen: 2014-03-07 13:13:41
2403 last_seen: 2014-03-07 13:13:48
2404```
2405
2406#### statements_with_errors_or_warnings / x$statements_with_errors_or_warnings
2407
2408##### Description
2409
2410Lists all normalized statements that have raised errors or warnings.
2411
2412##### Structures
2413
2414```SQL
2415mysql> desc statements_with_errors_or_warnings;
2416+-------------+---------------------+------+-----+---------------------+-------+
2417| Field | Type | Null | Key | Default | Extra |
2418+-------------+---------------------+------+-----+---------------------+-------+
2419| query | longtext | YES | | NULL | |
2420| db | varchar(64) | YES | | NULL | |
2421| exec_count | bigint(20) unsigned | NO | | NULL | |
2422| errors | bigint(20) unsigned | NO | | NULL | |
2423| error_pct | decimal(27,4) | NO | | 0.0000 | |
2424| warnings | bigint(20) unsigned | NO | | NULL | |
2425| warning_pct | decimal(27,4) | NO | | 0.0000 | |
2426| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2427| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2428| digest | varchar(32) | YES | | NULL | |
2429+-------------+---------------------+------+-----+---------------------+-------+
243010 rows in set (0.55 sec)
2431
2432mysql> desc x$statements_with_errors_or_warnings;
2433+-------------+---------------------+------+-----+---------------------+-------+
2434| Field | Type | Null | Key | Default | Extra |
2435+-------------+---------------------+------+-----+---------------------+-------+
2436| query | longtext | YES | | NULL | |
2437| db | varchar(64) | YES | | NULL | |
2438| exec_count | bigint(20) unsigned | NO | | NULL | |
2439| errors | bigint(20) unsigned | NO | | NULL | |
2440| error_pct | decimal(27,4) | NO | | 0.0000 | |
2441| warnings | bigint(20) unsigned | NO | | NULL | |
2442| warning_pct | decimal(27,4) | NO | | 0.0000 | |
2443| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2444| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2445| digest | varchar(32) | YES | | NULL | |
2446+-------------+---------------------+------+-----+---------------------+-------+
244710 rows in set (0.25 sec)
2448```
2449
2450##### Example
2451
2452```SQL
2453mysql> select * from statements_with_errors_or_warnings LIMIT 1\G
2454*************************** 1. row ***************************
2455 query: CREATE OR REPLACE ALGORITHM = ... _delete` AS `rows_deleted` ...
2456 db: sys
2457 exec_count: 2
2458 errors: 1
2459 error_pct: 50.0000
2460 warnings: 0
2461warning_pct: 0.0000
2462 first_seen: 2014-03-07 12:56:54
2463 last_seen: 2014-03-07 13:01:01
2464 digest: 943a788859e623d5f7798ba0ae0fd8a9
2465```
2466
2467#### statements_with_full_table_scans / x$statements_with_full_table_scans
2468
2469##### Description
2470
2471Lists all normalized statements that use have done a full table scan ordered by number the percentage of times a full scan was done, then by the statement latency.
2472
2473This view ignores SHOW statements, as these always cause a full table scan, and there is nothing that can be done about this.
2474
2475##### Structures
2476
2477```SQL
2478mysql> desc statements_with_full_table_scans;
2479+--------------------------+------------------------+------+-----+---------------------+-------+
2480| Field | Type | Null | Key | Default | Extra |
2481+--------------------------+------------------------+------+-----+---------------------+-------+
2482| query | longtext | YES | | NULL | |
2483| db | varchar(64) | YES | | NULL | |
2484| exec_count | bigint(20) unsigned | NO | | NULL | |
2485| total_latency | text | YES | | NULL | |
2486| no_index_used_count | bigint(20) unsigned | NO | | NULL | |
2487| no_good_index_used_count | bigint(20) unsigned | NO | | NULL | |
2488| no_index_used_pct | decimal(24,0) | NO | | 0 | |
2489| rows_sent | bigint(20) unsigned | NO | | NULL | |
2490| rows_examined | bigint(20) unsigned | NO | | NULL | |
2491| rows_sent_avg | decimal(21,0) unsigned | YES | | NULL | |
2492| rows_examined_avg | decimal(21,0) unsigned | YES | | NULL | |
2493| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2494| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2495| digest | varchar(32) | YES | | NULL | |
2496+--------------------------+------------------------+------+-----+---------------------+-------+
249714 rows in set (0.04 sec)
2498
2499mysql> desc x$statements_with_full_table_scans;
2500+--------------------------+------------------------+------+-----+---------------------+-------+
2501| Field | Type | Null | Key | Default | Extra |
2502+--------------------------+------------------------+------+-----+---------------------+-------+
2503| query | longtext | YES | | NULL | |
2504| db | varchar(64) | YES | | NULL | |
2505| exec_count | bigint(20) unsigned | NO | | NULL | |
2506| total_latency | bigint(20) unsigned | NO | | NULL | |
2507| no_index_used_count | bigint(20) unsigned | NO | | NULL | |
2508| no_good_index_used_count | bigint(20) unsigned | NO | | NULL | |
2509| no_index_used_pct | decimal(24,0) | NO | | 0 | |
2510| rows_sent | bigint(20) unsigned | NO | | NULL | |
2511| rows_examined | bigint(20) unsigned | NO | | NULL | |
2512| rows_sent_avg | decimal(21,0) unsigned | YES | | NULL | |
2513| rows_examined_avg | decimal(21,0) unsigned | YES | | NULL | |
2514| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2515| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2516| digest | varchar(32) | YES | | NULL | |
2517+--------------------------+------------------------+------+-----+---------------------+-------+
251814 rows in set (0.14 sec)
2519```
2520
2521##### Example
2522
2523```SQL
2524mysql> select * from statements_with_full_table_scans limit 1\G
2525*************************** 1. row ***************************
2526 query: SELECT * FROM `schema_tables_w ... ex_usage` . `COUNT_READ` DESC
2527 db: sys
2528 exec_count: 1
2529 total_latency: 88.20 ms
2530 no_index_used_count: 1
2531no_good_index_used_count: 0
2532 no_index_used_pct: 100
2533 rows_sent: 0
2534 rows_examined: 1501
2535 rows_sent_avg: 0
2536 rows_examined_avg: 1501
2537 first_seen: 2014-03-07 13:58:20
2538 last_seen: 2014-03-07 13:58:20
2539 digest: 64baecd5c1e1e1651a6b92e55442a288
2540```
2541
2542#### statements_with_runtimes_in_95th_percentile / x$statements_with_runtimes_in_95th_percentile
2543
2544##### Description
2545
2546Lists all statements whose average runtime, in microseconds, is in the top 95th percentile.
2547
2548Also includes two helper views:
2549
2550* x$ps_digest_avg_latency_distribution
2551* x$ps_digest_95th_percentile_by_avg_us
2552
2553##### Structures
2554
2555```SQL
2556mysql> desc statements_with_runtimes_in_95th_percentile;
2557+-------------------+---------------------+------+-----+---------------------+-------+
2558| Field | Type | Null | Key | Default | Extra |
2559+-------------------+---------------------+------+-----+---------------------+-------+
2560| query | longtext | YES | | NULL | |
2561| db | varchar(64) | YES | | NULL | |
2562| full_scan | varchar(1) | NO | | | |
2563| exec_count | bigint(20) unsigned | NO | | NULL | |
2564| err_count | bigint(20) unsigned | NO | | NULL | |
2565| warn_count | bigint(20) unsigned | NO | | NULL | |
2566| total_latency | text | YES | | NULL | |
2567| max_latency | text | YES | | NULL | |
2568| avg_latency | text | YES | | NULL | |
2569| rows_sent | bigint(20) unsigned | NO | | NULL | |
2570| rows_sent_avg | decimal(21,0) | NO | | 0 | |
2571| rows_examined | bigint(20) unsigned | NO | | NULL | |
2572| rows_examined_avg | decimal(21,0) | NO | | 0 | |
2573| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2574| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2575| digest | varchar(32) | YES | | NULL | |
2576+-------------------+---------------------+------+-----+---------------------+-------+
257716 rows in set (0.11 sec)
2578
2579mysql> desc x$statements_with_runtimes_in_95th_percentile;
2580+-------------------+---------------------+------+-----+---------------------+-------+
2581| Field | Type | Null | Key | Default | Extra |
2582+-------------------+---------------------+------+-----+---------------------+-------+
2583| query | longtext | YES | | NULL | |
2584| db | varchar(64) | YES | | NULL | |
2585| full_scan | varchar(1) | NO | | | |
2586| exec_count | bigint(20) unsigned | NO | | NULL | |
2587| err_count | bigint(20) unsigned | NO | | NULL | |
2588| warn_count | bigint(20) unsigned | NO | | NULL | |
2589| total_latency | bigint(20) unsigned | NO | | NULL | |
2590| max_latency | bigint(20) unsigned | NO | | NULL | |
2591| avg_latency | bigint(20) unsigned | NO | | NULL | |
2592| rows_sent | bigint(20) unsigned | NO | | NULL | |
2593| rows_sent_avg | decimal(21,0) | NO | | 0 | |
2594| rows_examined | bigint(20) unsigned | NO | | NULL | |
2595| rows_examined_avg | decimal(21,0) | NO | | 0 | |
2596| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2597| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2598| digest | varchar(32) | YES | | NULL | |
2599+-------------------+---------------------+------+-----+---------------------+-------+
260016 rows in set (0.00 sec)
2601
2602mysql> desc x$ps_digest_avg_latency_distribution;
2603+--------+---------------+------+-----+---------+-------+
2604| Field | Type | Null | Key | Default | Extra |
2605+--------+---------------+------+-----+---------+-------+
2606| cnt | bigint(21) | NO | | 0 | |
2607| avg_us | decimal(21,0) | YES | | NULL | |
2608+--------+---------------+------+-----+---------+-------+
26092 rows in set (0.10 sec)
2610
2611mysql> desc x$ps_digest_95th_percentile_by_avg_us;
2612+------------+---------------+------+-----+---------+-------+
2613| Field | Type | Null | Key | Default | Extra |
2614+------------+---------------+------+-----+---------+-------+
2615| avg_us | decimal(21,0) | YES | | NULL | |
2616| percentile | decimal(46,4) | NO | | 0.0000 | |
2617+------------+---------------+------+-----+---------+-------+
26182 rows in set (0.15 sec)
2619```
2620
2621##### Example
2622
2623```SQL
2624mysql> select * from statements_with_runtimes_in_95th_percentile\G
2625*************************** 1. row ***************************
2626 query: SELECT * FROM `schema_object_o ... MA` , `information_schema` ...
2627 db: sys
2628 full_scan: *
2629 exec_count: 2
2630 err_count: 0
2631 warn_count: 0
2632 total_latency: 16.75 s
2633 max_latency: 16.57 s
2634 avg_latency: 8.38 s
2635 rows_sent: 84
2636 rows_sent_avg: 42
2637 rows_examined: 20012
2638rows_examined_avg: 10006
2639 first_seen: 2014-03-07 13:13:41
2640 last_seen: 2014-03-07 13:13:48
2641 digest: 54f9bd520f0bbf15db0c2ed93386bec9
2642```
2643
2644#### statements_with_sorting / x$statements_with_sorting
2645
2646##### Description
2647
2648Lists all normalized statements that have done sorts, ordered by total_latency descending.
2649
2650##### Structures
2651
2652```SQL
2653mysql> desc statements_with_sorting;
2654+-------------------+---------------------+------+-----+---------------------+-------+
2655| Field | Type | Null | Key | Default | Extra |
2656+-------------------+---------------------+------+-----+---------------------+-------+
2657| query | longtext | YES | | NULL | |
2658| db | varchar(64) | YES | | NULL | |
2659| exec_count | bigint(20) unsigned | NO | | NULL | |
2660| total_latency | text | YES | | NULL | |
2661| sort_merge_passes | bigint(20) unsigned | NO | | NULL | |
2662| avg_sort_merges | decimal(21,0) | NO | | 0 | |
2663| sorts_using_scans | bigint(20) unsigned | NO | | NULL | |
2664| sort_using_range | bigint(20) unsigned | NO | | NULL | |
2665| rows_sorted | bigint(20) unsigned | NO | | NULL | |
2666| avg_rows_sorted | decimal(21,0) | NO | | 0 | |
2667| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2668| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2669| digest | varchar(32) | YES | | NULL | |
2670+-------------------+---------------------+------+-----+---------------------+-------+
267113 rows in set (0.01 sec)
2672
2673mysql> desc x$statements_with_sorting;
2674+-------------------+---------------------+------+-----+---------------------+-------+
2675| Field | Type | Null | Key | Default | Extra |
2676+-------------------+---------------------+------+-----+---------------------+-------+
2677| query | longtext | YES | | NULL | |
2678| db | varchar(64) | YES | | NULL | |
2679| exec_count | bigint(20) unsigned | NO | | NULL | |
2680| total_latency | bigint(20) unsigned | NO | | NULL | |
2681| sort_merge_passes | bigint(20) unsigned | NO | | NULL | |
2682| avg_sort_merges | decimal(21,0) | NO | | 0 | |
2683| sorts_using_scans | bigint(20) unsigned | NO | | NULL | |
2684| sort_using_range | bigint(20) unsigned | NO | | NULL | |
2685| rows_sorted | bigint(20) unsigned | NO | | NULL | |
2686| avg_rows_sorted | decimal(21,0) | NO | | 0 | |
2687| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2688| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2689| digest | varchar(32) | YES | | NULL | |
2690+-------------------+---------------------+------+-----+---------------------+-------+
269113 rows in set (0.04 sec)
2692```
2693
2694##### Example
2695
2696```SQL
2697mysql> select * from statements_with_sorting limit 1\G
2698*************************** 1. row ***************************
2699 query: SELECT * FROM `schema_object_o ... MA` , `information_schema` ...
2700 db: sys
2701 exec_count: 2
2702 total_latency: 16.75 s
2703sort_merge_passes: 0
2704 avg_sort_merges: 0
2705sorts_using_scans: 12
2706 sort_using_range: 0
2707 rows_sorted: 168
2708 avg_rows_sorted: 84
2709 first_seen: 2014-03-07 13:13:41
2710 last_seen: 2014-03-07 13:13:48
2711 digest: 54f9bd520f0bbf15db0c2ed93386bec9
2712```
2713
2714#### statements_with_temp_tables / x$statements_with_temp_tables
2715
2716##### Description
2717
2718Lists all normalized statements that use temporary tables ordered by number of on disk temporary tables descending first, then by the number of memory tables.
2719
2720##### Structures
2721
2722```SQL
2723mysql> desc statements_with_temp_tables;
2724+--------------------------+---------------------+------+-----+---------------------+-------+
2725| Field | Type | Null | Key | Default | Extra |
2726+--------------------------+---------------------+------+-----+---------------------+-------+
2727| query | longtext | YES | | NULL | |
2728| db | varchar(64) | YES | | NULL | |
2729| exec_count | bigint(20) unsigned | NO | | NULL | |
2730| total_latency | text | YES | | NULL | |
2731| memory_tmp_tables | bigint(20) unsigned | NO | | NULL | |
2732| disk_tmp_tables | bigint(20) unsigned | NO | | NULL | |
2733| avg_tmp_tables_per_query | decimal(21,0) | NO | | 0 | |
2734| tmp_tables_to_disk_pct | decimal(24,0) | NO | | 0 | |
2735| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2736| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2737| digest | varchar(32) | YES | | NULL | |
2738+--------------------------+---------------------+------+-----+---------------------+-------+
273911 rows in set (0.30 sec)
2740
2741mysql> desc x$statements_with_temp_tables;
2742+--------------------------+---------------------+------+-----+---------------------+-------+
2743| Field | Type | Null | Key | Default | Extra |
2744+--------------------------+---------------------+------+-----+---------------------+-------+
2745| query | longtext | YES | | NULL | |
2746| db | varchar(64) | YES | | NULL | |
2747| exec_count | bigint(20) unsigned | NO | | NULL | |
2748| total_latency | bigint(20) unsigned | NO | | NULL | |
2749| memory_tmp_tables | bigint(20) unsigned | NO | | NULL | |
2750| disk_tmp_tables | bigint(20) unsigned | NO | | NULL | |
2751| avg_tmp_tables_per_query | decimal(21,0) | NO | | 0 | |
2752| tmp_tables_to_disk_pct | decimal(24,0) | NO | | 0 | |
2753| first_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2754| last_seen | timestamp | NO | | 0000-00-00 00:00:00 | |
2755| digest | varchar(32) | YES | | NULL | |
2756+--------------------------+---------------------+------+-----+---------------------+-------+
275711 rows in set (0.05 sec)
2758```
2759
2760##### Example
2761
2762```SQL
2763mysql> select * from statements_with_temp_tables limit 1\G
2764*************************** 1. row ***************************
2765 query: SELECT * FROM `schema_object_o ... MA` , `information_schema` ...
2766 db: sys
2767 exec_count: 2
2768 total_latency: 16.75 s
2769 memory_tmp_tables: 378
2770 disk_tmp_tables: 66
2771avg_tmp_tables_per_query: 189
2772 tmp_tables_to_disk_pct: 17
2773 first_seen: 2014-03-07 13:13:41
2774 last_seen: 2014-03-07 13:13:48
2775 digest: 54f9bd520f0bbf15db0c2ed93386bec9
2776```
2777
2778#### user_summary / x$user_summary
2779
2780##### Description
2781
2782Summarizes statement activity, file IO and connections by user.
2783
2784When the user found is NULL, it is assumed to be a "background" thread.
2785
2786##### Structures (5.7)
2787
2788```SQL
2789mysql> desc user_summary;
2790+------------------------+---------------+------+-----+---------+-------+
2791| Field | Type | Null | Key | Default | Extra |
2792+------------------------+---------------+------+-----+---------+-------+
2793| user | varchar(32) | YES | | NULL | |
2794| statements | decimal(64,0) | YES | | NULL | |
2795| statement_latency | text | YES | | NULL | |
2796| statement_avg_latency | text | YES | | NULL | |
2797| table_scans | decimal(65,0) | YES | | NULL | |
2798| file_ios | decimal(64,0) | YES | | NULL | |
2799| file_io_latency | text | YES | | NULL | |
2800| current_connections | decimal(41,0) | YES | | NULL | |
2801| total_connections | decimal(41,0) | YES | | NULL | |
2802| unique_hosts | bigint(21) | NO | | 0 | |
2803| current_memory | text | YES | | NULL | |
2804| total_memory_allocated | text | YES | | NULL | |
2805+------------------------+---------------+------+-----+---------+-------+
280612 rows in set (0.00 sec)
2807
2808mysql> desc x$user_summary;
2809+------------------------+---------------+------+-----+---------+-------+
2810| Field | Type | Null | Key | Default | Extra |
2811+------------------------+---------------+------+-----+---------+-------+
2812| user | varchar(32) | YES | | NULL | |
2813| statements | decimal(64,0) | YES | | NULL | |
2814| statement_latency | decimal(64,0) | YES | | NULL | |
2815| statement_avg_latency | decimal(65,4) | NO | | 0.0000 | |
2816| table_scans | decimal(65,0) | YES | | NULL | |
2817| file_ios | decimal(64,0) | YES | | NULL | |
2818| file_io_latency | decimal(64,0) | YES | | NULL | |
2819| current_connections | decimal(41,0) | YES | | NULL | |
2820| total_connections | decimal(41,0) | YES | | NULL | |
2821| unique_hosts | bigint(21) | NO | | 0 | |
2822| current_memory | decimal(63,0) | YES | | NULL | |
2823| total_memory_allocated | decimal(64,0) | YES | | NULL | |
2824+------------------------+---------------+------+-----+---------+-------+
282512 rows in set (0.01 sec)
2826```
2827
2828##### Example
2829
2830```SQL
2831mysql> select * from user_summary\G
2832*************************** 1. row ***************************
2833 user: root
2834 statements: 4981
2835 statement_latency: 26.54 s
2836 statement_avg_latency: 5.33 ms
2837 table_scans: 74
2838 file_ios: 7792
2839 file_io_latency: 40.08 s
2840 current_connections: 1
2841 total_connections: 2
2842 unique_hosts: 1
2843 current_memory: 3.57 MiB
2844total_memory_allocated: 83.37 MiB
2845*************************** 2. row ***************************
2846 user: background
2847 statements: 0
2848 statement_latency: 0 ps
2849 statement_avg_latency: 0 ps
2850 table_scans: 0
2851 file_ios: 1618
2852 file_io_latency: 4.78 s
2853 current_connections: 21
2854 total_connections: 23
2855 unique_hosts: 0
2856 current_memory: 165.94 MiB
2857total_memory_allocated: 197.29 MiB
2858```
2859
2860#### user_summary_by_file_io / x$user_summary_by_file_io
2861
2862##### Description
2863
2864Summarizes file IO totals per user.
2865
2866When the user found is NULL, it is assumed to be a "background" thread.
2867
2868##### Structures
2869
2870```SQL
2871mysql> desc user_summary_by_file_io;
2872+------------+---------------+------+-----+---------+-------+
2873| Field | Type | Null | Key | Default | Extra |
2874+------------+---------------+------+-----+---------+-------+
2875| user | varchar(32) | YES | | NULL | |
2876| ios | decimal(42,0) | YES | | NULL | |
2877| io_latency | text | YES | | NULL | |
2878+------------+---------------+------+-----+---------+-------+
28793 rows in set (0.20 sec)
2880
2881mysql> desc x$user_summary_by_file_io;
2882+------------+---------------+------+-----+---------+-------+
2883| Field | Type | Null | Key | Default | Extra |
2884+------------+---------------+------+-----+---------+-------+
2885| user | varchar(32) | YES | | NULL | |
2886| ios | decimal(42,0) | YES | | NULL | |
2887| io_latency | decimal(42,0) | YES | | NULL | |
2888+------------+---------------+------+-----+---------+-------+
28893 rows in set (0.02 sec)
2890```
2891
2892##### Example
2893
2894```SQL
2895mysql> select * from user_summary_by_file_io;
2896+------------+-------+------------+
2897| user | ios | io_latency |
2898+------------+-------+------------+
2899| root | 26457 | 21.58 s |
2900| background | 1189 | 394.21 ms |
2901+------------+-------+------------+
2902```
2903
2904#### user_summary_by_file_io_type / x$user_summary_by_file_io_type
2905
2906##### Description
2907
2908Summarizes file IO by event type per user.
2909
2910When the user found is NULL, it is assumed to be a "background" thread.
2911
2912##### Structures
2913
2914```SQL
2915mysql> desc user_summary_by_file_io_type;
2916+-------------+---------------------+------+-----+---------+-------+
2917| Field | Type | Null | Key | Default | Extra |
2918+-------------+---------------------+------+-----+---------+-------+
2919| user | varchar(32) | YES | | NULL | |
2920| event_name | varchar(128) | NO | | NULL | |
2921| total | bigint(20) unsigned | NO | | NULL | |
2922| latency | text | YES | | NULL | |
2923| max_latency | text | YES | | NULL | |
2924+-------------+---------------------+------+-----+---------+-------+
29255 rows in set (0.02 sec)
2926
2927mysql> desc x$user_summary_by_file_io_type;
2928+-------------+---------------------+------+-----+---------+-------+
2929| Field | Type | Null | Key | Default | Extra |
2930+-------------+---------------------+------+-----+---------+-------+
2931| user | varchar(32) | YES | | NULL | |
2932| event_name | varchar(128) | NO | | NULL | |
2933| total | bigint(20) unsigned | NO | | NULL | |
2934| latency | bigint(20) unsigned | NO | | NULL | |
2935| max_latency | bigint(20) unsigned | NO | | NULL | |
2936+-------------+---------------------+------+-----+---------+-------+
29375 rows in set (0.00 sec)
2938```
2939
2940##### Example
2941
2942```SQL
2943mysql> select * from user_summary_by_file_io_type;
2944+------------+--------------------------------------+-------+-----------+-------------+
2945| user | event_name | total | latency | max_latency |
2946+------------+--------------------------------------+-------+-----------+-------------+
2947| background | wait/io/file/innodb/innodb_data_file | 1434 | 3.29 s | 147.56 ms |
2948| background | wait/io/file/sql/FRM | 910 | 286.61 ms | 32.92 ms |
2949| background | wait/io/file/sql/relaylog | 9 | 252.28 ms | 144.17 ms |
2950| background | wait/io/file/sql/binlog | 56 | 193.73 ms | 153.72 ms |
2951| background | wait/io/file/sql/binlog_index | 22 | 183.02 ms | 81.83 ms |
2952| background | wait/io/file/innodb/innodb_log_file | 20 | 117.17 ms | 36.53 ms |
2953| background | wait/io/file/sql/relaylog_index | 9 | 50.15 ms | 48.04 ms |
2954| background | wait/io/file/sql/ERRMSG | 5 | 35.41 ms | 31.78 ms |
2955| background | wait/io/file/myisam/kfile | 67 | 18.14 ms | 9.00 ms |
2956| background | wait/io/file/mysys/charset | 3 | 7.46 ms | 4.13 ms |
2957| background | wait/io/file/sql/casetest | 5 | 6.01 ms | 5.86 ms |
2958| background | wait/io/file/sql/pid | 3 | 5.96 ms | 3.06 ms |
2959| background | wait/io/file/myisam/dfile | 43 | 980.38 us | 152.46 us |
2960| background | wait/io/file/mysys/cnf | 5 | 154.97 us | 58.87 us |
2961| background | wait/io/file/sql/global_ddl_log | 2 | 18.64 us | 16.40 us |
2962| root | wait/io/file/sql/file_parser | 11048 | 48.79 s | 201.11 ms |
2963| root | wait/io/file/innodb/innodb_data_file | 4699 | 3.02 s | 46.93 ms |
2964| root | wait/io/file/sql/FRM | 10403 | 2.38 s | 61.72 ms |
2965| root | wait/io/file/myisam/dfile | 22143 | 726.77 ms | 308.79 ms |
2966| root | wait/io/file/myisam/kfile | 6213 | 435.35 ms | 88.76 ms |
2967| root | wait/io/file/sql/dbopt | 159 | 130.86 ms | 15.46 ms |
2968| root | wait/io/file/csv/metadata | 8 | 86.60 ms | 50.32 ms |
2969| root | wait/io/file/sql/binlog | 15 | 38.79 ms | 9.40 ms |
2970| root | wait/io/file/sql/misc | 21 | 22.33 ms | 15.30 ms |
2971| root | wait/io/file/csv/data | 4 | 297.46 us | 111.93 us |
2972| root | wait/io/file/archive/data | 3 | 54.10 us | 40.74 us |
2973+------------+--------------------------------------+-------+-----------+-------------+
2974```
2975
2976#### user_summary_by_stages / x$user_summary_by_stages
2977
2978##### Description
2979
2980Summarizes stages by user, ordered by user and total latency per stage.
2981
2982When the user found is NULL, it is assumed to be a "background" thread.
2983
2984##### Structures
2985
2986```SQL
2987mysql> desc user_summary_by_stages;
2988+---------------+---------------------+------+-----+---------+-------+
2989| Field | Type | Null | Key | Default | Extra |
2990+---------------+---------------------+------+-----+---------+-------+
2991| user | varchar(32) | YES | | NULL | |
2992| event_name | varchar(128) | NO | | NULL | |
2993| total | bigint(20) unsigned | NO | | NULL | |
2994| total_latency | text | YES | | NULL | |
2995| avg_latency | text | YES | | NULL | |
2996+---------------+---------------------+------+-----+---------+-------+
29975 rows in set (0.01 sec)
2998
2999mysql> desc x$user_summary_by_stages;
3000+---------------+---------------------+------+-----+---------+-------+
3001| Field | Type | Null | Key | Default | Extra |
3002+---------------+---------------------+------+-----+---------+-------+
3003| user | varchar(16) | YES | | NULL | |
3004| event_name | varchar(128) | NO | | NULL | |
3005| total | bigint(20) unsigned | NO | | NULL | |
3006| total_latency | bigint(20) unsigned | NO | | NULL | |
3007| avg_latency | bigint(20) unsigned | NO | | NULL | |
3008+---------------+---------------------+------+-----+---------+-------+
30095 rows in set (0.05 sec)
3010```
3011
3012##### Example
3013
3014```SQL
3015mysql> select * from user_summary_by_stages;
3016+------+--------------------------------+-------+---------------+-------------+
3017| user | event_name | total | total_latency | avg_latency |
3018+------+--------------------------------+-------+---------------+-------------+
3019| root | stage/sql/Opening tables | 889 | 1.97 ms | 2.22 us |
3020| root | stage/sql/Creating sort index | 4 | 1.79 ms | 446.30 us |
3021| root | stage/sql/init | 10 | 312.27 us | 31.23 us |
3022| root | stage/sql/checking permissions | 10 | 300.62 us | 30.06 us |
3023| root | stage/sql/freeing items | 5 | 85.89 us | 17.18 us |
3024| root | stage/sql/statistics | 5 | 79.15 us | 15.83 us |
3025| root | stage/sql/preparing | 5 | 69.12 us | 13.82 us |
3026| root | stage/sql/optimizing | 5 | 53.11 us | 10.62 us |
3027| root | stage/sql/Sending data | 5 | 44.66 us | 8.93 us |
3028| root | stage/sql/closing tables | 5 | 37.54 us | 7.51 us |
3029| root | stage/sql/System lock | 5 | 34.28 us | 6.86 us |
3030| root | stage/sql/query end | 5 | 24.37 us | 4.87 us |
3031| root | stage/sql/end | 5 | 8.60 us | 1.72 us |
3032| root | stage/sql/Sorting result | 5 | 8.33 us | 1.67 us |
3033| root | stage/sql/executing | 5 | 5.37 us | 1.07 us |
3034| root | stage/sql/cleaning up | 5 | 4.60 us | 919.00 ns |
3035+------+--------------------------------+-------+---------------+-------------+
3036```
3037
3038#### user_summary_by_statement_latency / x$user_summary_by_statement_latency
3039
3040##### Description
3041
3042Summarizes overall statement statistics by user.
3043
3044When the user found is NULL, it is assumed to be a "background" thread.
3045
3046##### Structures
3047
3048```SQL
3049mysql> desc user_summary_by_statement_latency;
3050+---------------+---------------+------+-----+---------+-------+
3051| Field | Type | Null | Key | Default | Extra |
3052+---------------+---------------+------+-----+---------+-------+
3053| user | varchar(32) | YES | | NULL | |
3054| total | decimal(42,0) | YES | | NULL | |
3055| total_latency | text | YES | | NULL | |
3056| max_latency | text | YES | | NULL | |
3057| lock_latency | text | YES | | NULL | |
3058| rows_sent | decimal(42,0) | YES | | NULL | |
3059| rows_examined | decimal(42,0) | YES | | NULL | |
3060| rows_affected | decimal(42,0) | YES | | NULL | |
3061| full_scans | decimal(43,0) | YES | | NULL | |
3062+---------------+---------------+------+-----+---------+-------+
30639 rows in set (0.00 sec)
3064
3065mysql> desc x$user_summary_by_statement_latency;
3066+---------------+---------------+------+-----+---------+-------+
3067| Field | Type | Null | Key | Default | Extra |
3068+---------------+---------------+------+-----+---------+-------+
3069| user | varchar(32) | YES | | NULL | |
3070| total | decimal(42,0) | YES | | NULL | |
3071| total_latency | decimal(42,0) | YES | | NULL | |
3072| max_latency | decimal(42,0) | YES | | NULL | |
3073| lock_latency | decimal(42,0) | YES | | NULL | |
3074| rows_sent | decimal(42,0) | YES | | NULL | |
3075| rows_examined | decimal(42,0) | YES | | NULL | |
3076| rows_affected | decimal(42,0) | YES | | NULL | |
3077| full_scans | decimal(43,0) | YES | | NULL | |
3078+---------------+---------------+------+-----+---------+-------+
30799 rows in set (0.28 sec)
3080```
3081
3082##### Example
3083
3084```SQL
3085mysql> select * from user_summary_by_statement_latency;
3086+------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3087| user | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
3088+------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3089| root | 3381 | 00:02:09.13 | 1.48 s | 1.07 s | 1151 | 93947 | 150 | 91 |
3090+------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3091```
3092
3093#### user_summary_by_statement_type / x$user_summary_by_statement_type
3094
3095##### Description
3096
3097Summarizes the types of statements executed by each user.
3098
3099When the user found is NULL, it is assumed to be a "background" thread.
3100
3101##### Structures
3102
3103```SQL
3104mysql> desc user_summary_by_statement_type;
3105+---------------+---------------------+------+-----+---------+-------+
3106| Field | Type | Null | Key | Default | Extra |
3107+---------------+---------------------+------+-----+---------+-------+
3108| user | varchar(32) | YES | | NULL | |
3109| statement | varchar(128) | YES | | NULL | |
3110| total | bigint(20) unsigned | NO | | NULL | |
3111| total_latency | text | YES | | NULL | |
3112| max_latency | text | YES | | NULL | |
3113| lock_latency | text | YES | | NULL | |
3114| rows_sent | bigint(20) unsigned | NO | | NULL | |
3115| rows_examined | bigint(20) unsigned | NO | | NULL | |
3116| rows_affected | bigint(20) unsigned | NO | | NULL | |
3117| full_scans | bigint(21) unsigned | NO | | 0 | |
3118+---------------+---------------------+------+-----+---------+-------+
311910 rows in set (0.21 sec)
3120
3121mysql> desc x$user_summary_by_statement_type;
3122+---------------+---------------------+------+-----+---------+-------+
3123| Field | Type | Null | Key | Default | Extra |
3124+---------------+---------------------+------+-----+---------+-------+
3125| user | varchar(32) | YES | | NULL | |
3126| statement | varchar(128) | YES | | NULL | |
3127| total | bigint(20) unsigned | NO | | NULL | |
3128| total_latency | bigint(20) unsigned | NO | | NULL | |
3129| max_latency | bigint(20) unsigned | NO | | NULL | |
3130| lock_latency | bigint(20) unsigned | NO | | NULL | |
3131| rows_sent | bigint(20) unsigned | NO | | NULL | |
3132| rows_examined | bigint(20) unsigned | NO | | NULL | |
3133| rows_affected | bigint(20) unsigned | NO | | NULL | |
3134| full_scans | bigint(21) unsigned | NO | | 0 | |
3135+---------------+---------------------+------+-----+---------+-------+
313610 rows in set (0.37 sec)
3137```
3138
3139##### Example
3140
3141```SQL
3142mysql> select * from user_summary_by_statement_type;
3143+------+------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3144| user | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
3145+------+------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3146| root | create_view | 1332 | 00:03:39.08 | 677.76 ms | 494.56 ms | 0 | 0 | 0 | 0 |
3147| root | select | 88 | 20.13 s | 16.57 s | 17.40 s | 1804 | 77285 | 0 | 48 |
3148| root | drop_db | 16 | 6.83 s | 1.14 s | 5.73 s | 0 | 0 | 953 | 0 |
3149| root | drop_view | 392 | 1.70 s | 739.49 ms | 0 ps | 0 | 0 | 0 | 0 |
3150| root | show_databases | 16 | 1.37 s | 587.44 ms | 1.31 ms | 400 | 400 | 0 | 16 |
3151| root | show_tables | 34 | 676.78 ms | 167.04 ms | 3.46 ms | 1087 | 1087 | 0 | 34 |
3152| root | create_db | 22 | 334.90 ms | 38.93 ms | 0 ps | 0 | 0 | 22 | 0 |
3153| root | create_procedure | 352 | 250.02 ms | 21.90 ms | 165.17 ms | 0 | 0 | 0 | 0 |
3154| root | drop_function | 176 | 122.44 ms | 69.18 ms | 87.24 ms | 0 | 0 | 0 | 0 |
3155| root | create_function | 176 | 76.12 ms | 1.36 ms | 49.50 ms | 0 | 0 | 0 | 0 |
3156| root | drop_procedure | 352 | 67.41 ms | 1.57 ms | 36.22 ms | 0 | 0 | 0 | 0 |
3157| root | update | 2 | 41.75 ms | 35.96 ms | 35.52 ms | 0 | 557 | 338 | 0 |
3158| root | error | 3 | 17.22 ms | 17.05 ms | 0 ps | 0 | 0 | 0 | 0 |
3159| root | set_option | 88 | 8.02 ms | 1.63 ms | 0 ps | 0 | 0 | 0 | 0 |
3160| root | call_procedure | 2 | 2.98 ms | 2.29 ms | 95.00 us | 0 | 0 | 0 | 0 |
3161| root | Init DB | 22 | 1.07 ms | 117.65 us | 0 ps | 0 | 0 | 0 | 0 |
3162| root | show_status | 1 | 408.69 us | 408.69 us | 102.00 us | 23 | 23 | 0 | 1 |
3163+------+------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3164```
3165
3166#### wait_classes_global_by_avg_latency / x$wait_classes_global_by_avg_latency
3167
3168##### Description
3169
3170Lists the top wait classes by average latency, ignoring idle (this may be very large).
3171
3172##### Structures
3173
3174```SQL
3175mysql> desc wait_classes_global_by_avg_latency;
3176+---------------+---------------+------+-----+---------+-------+
3177| Field | Type | Null | Key | Default | Extra |
3178+---------------+---------------+------+-----+---------+-------+
3179| event_class | varchar(128) | YES | | NULL | |
3180| total | decimal(42,0) | YES | | NULL | |
3181| total_latency | text | YES | | NULL | |
3182| min_latency | text | YES | | NULL | |
3183| avg_latency | text | YES | | NULL | |
3184| max_latency | text | YES | | NULL | |
3185+---------------+---------------+------+-----+---------+-------+
31866 rows in set (0.11 sec)
3187
3188mysql> desc x$wait_classes_global_by_avg_latency;
3189+---------------+---------------------+------+-----+---------+-------+
3190| Field | Type | Null | Key | Default | Extra |
3191+---------------+---------------------+------+-----+---------+-------+
3192| event_class | varchar(128) | YES | | NULL | |
3193| total | decimal(42,0) | YES | | NULL | |
3194| total_latency | decimal(42,0) | YES | | NULL | |
3195| min_latency | bigint(20) unsigned | YES | | NULL | |
3196| avg_latency | decimal(46,4) | NO | | 0.0000 | |
3197| max_latency | bigint(20) unsigned | YES | | NULL | |
3198+---------------+---------------------+------+-----+---------+-------+
31996 rows in set (0.02 sec)
3200```
3201
3202##### Example
3203
3204```SQL
3205mysql> select * from wait_classes_global_by_avg_latency where event_class != 'idle';
3206+-------------------+--------+---------------+-------------+-------------+-------------+
3207| event_class | total | total_latency | min_latency | avg_latency | max_latency |
3208+-------------------+--------+---------------+-------------+-------------+-------------+
3209| wait/io/file | 543123 | 44.60 s | 19.44 ns | 82.11 us | 4.21 s |
3210| wait/io/table | 22002 | 766.60 ms | 148.72 ns | 34.84 us | 44.97 ms |
3211| wait/io/socket | 79613 | 967.17 ms | 0 ps | 12.15 us | 27.10 ms |
3212| wait/lock/table | 35409 | 18.68 ms | 65.45 ns | 527.51 ns | 969.88 us |
3213| wait/synch/rwlock | 37935 | 4.61 ms | 21.38 ns | 121.61 ns | 34.65 us |
3214| wait/synch/mutex | 390622 | 18.60 ms | 19.44 ns | 47.61 ns | 10.32 us |
3215+-------------------+--------+---------------+-------------+-------------+-------------+
3216```
3217
3218#### wait_classes_global_by_latency / x$wait_classes_global_by_latency
3219
3220##### Description
3221
3222Lists the top wait classes by total latency, ignoring idle (this may be very large).
3223
3224##### Structures
3225
3226```SQL
3227mysql> desc wait_classes_global_by_latency;
3228+---------------+---------------+------+-----+---------+-------+
3229| Field | Type | Null | Key | Default | Extra |
3230+---------------+---------------+------+-----+---------+-------+
3231| event_class | varchar(128) | YES | | NULL | |
3232| total | decimal(42,0) | YES | | NULL | |
3233| total_latency | text | YES | | NULL | |
3234| min_latency | text | YES | | NULL | |
3235| avg_latency | text | YES | | NULL | |
3236| max_latency | text | YES | | NULL | |
3237+---------------+---------------+------+-----+---------+-------+
32386 rows in set (0.00 sec)
3239
3240mysql> desc x$wait_classes_global_by_latency;
3241+---------------+---------------------+------+-----+---------+-------+
3242| Field | Type | Null | Key | Default | Extra |
3243+---------------+---------------------+------+-----+---------+-------+
3244| event_class | varchar(128) | YES | | NULL | |
3245| total | decimal(42,0) | YES | | NULL | |
3246| total_latency | decimal(42,0) | YES | | NULL | |
3247| min_latency | bigint(20) unsigned | YES | | NULL | |
3248| avg_latency | decimal(46,4) | NO | | 0.0000 | |
3249| max_latency | bigint(20) unsigned | YES | | NULL | |
3250+---------------+---------------------+------+-----+---------+-------+
32516 rows in set (0.02 sec)
3252```
3253
3254##### Example
3255
3256```SQL
3257mysql> select * from wait_classes_global_by_latency;
3258+-------------------+--------+---------------+-------------+-------------+-------------+
3259| event_class | total | total_latency | min_latency | avg_latency | max_latency |
3260+-------------------+--------+---------------+-------------+-------------+-------------+
3261| wait/io/file | 550470 | 46.01 s | 19.44 ns | 83.58 us | 4.21 s |
3262| wait/io/socket | 228833 | 2.71 s | 0 ps | 11.86 us | 29.93 ms |
3263| wait/io/table | 64063 | 1.89 s | 99.79 ns | 29.43 us | 68.07 ms |
3264| wait/lock/table | 76029 | 47.19 ms | 65.45 ns | 620.74 ns | 969.88 us |
3265| wait/synch/mutex | 635925 | 34.93 ms | 19.44 ns | 54.93 ns | 107.70 us |
3266| wait/synch/rwlock | 61287 | 7.62 ms | 21.38 ns | 124.37 ns | 34.65 us |
3267+-------------------+--------+---------------+-------------+-------------+-------------+
3268```
3269
3270#### waits_by_user_by_latency / x$waits_by_user_by_latency
3271
3272##### Description
3273
3274Lists the top wait events per user by their total latency, ignoring idle (this may be very large) per user.
3275
3276##### Structures
3277
3278```SQL
3279mysql> desc waits_by_user_by_latency;
3280+---------------+---------------------+------+-----+---------+-------+
3281| Field | Type | Null | Key | Default | Extra |
3282+---------------+---------------------+------+-----+---------+-------+
3283| user | varchar(32) | YES | | NULL | |
3284| event | varchar(128) | NO | | NULL | |
3285| total | bigint(20) unsigned | NO | | NULL | |
3286| total_latency | text | YES | | NULL | |
3287| avg_latency | text | YES | | NULL | |
3288| max_latency | text | YES | | NULL | |
3289+---------------+---------------------+------+-----+---------+-------+
32906 rows in set (0.00 sec)
3291
3292mysql> desc x$waits_by_user_by_latency;
3293+---------------+---------------------+------+-----+---------+-------+
3294| Field | Type | Null | Key | Default | Extra |
3295+---------------+---------------------+------+-----+---------+-------+
3296| user | varchar(32) | YES | | NULL | |
3297| event | varchar(128) | NO | | NULL | |
3298| total | bigint(20) unsigned | NO | | NULL | |
3299| total_latency | bigint(20) unsigned | NO | | NULL | |
3300| avg_latency | bigint(20) unsigned | NO | | NULL | |
3301| max_latency | bigint(20) unsigned | NO | | NULL | |
3302+---------------+---------------------+------+-----+---------+-------+
33036 rows in set (0.30 sec)
3304```
3305
3306##### Example
3307
3308```SQL
3309mysql> select * from waits_by_user_by_latency;
3310+------+-----------------------------------------------------+--------+---------------+-------------+-------------+
3311| user | event | total | total_latency | avg_latency | max_latency |
3312+------+-----------------------------------------------------+--------+---------------+-------------+-------------+
3313| root | wait/io/file/sql/file_parser | 13743 | 00:01:00.46 | 4.40 ms | 231.88 ms |
3314| root | wait/io/file/innodb/innodb_data_file | 4699 | 3.02 s | 643.38 us | 46.93 ms |
3315| root | wait/io/file/sql/FRM | 11462 | 2.60 s | 226.83 us | 61.72 ms |
3316| root | wait/io/file/myisam/dfile | 26776 | 746.70 ms | 27.89 us | 308.79 ms |
3317| root | wait/io/file/myisam/kfile | 7126 | 462.66 ms | 64.93 us | 88.76 ms |
3318| root | wait/io/file/sql/dbopt | 179 | 137.58 ms | 768.59 us | 15.46 ms |
3319| root | wait/io/file/csv/metadata | 8 | 86.60 ms | 10.82 ms | 50.32 ms |
3320| root | wait/synch/mutex/mysys/IO_CACHE::append_buffer_lock | 798080 | 66.46 ms | 82.94 ns | 161.03 us |
3321| root | wait/io/file/sql/binlog | 19 | 49.11 ms | 2.58 ms | 9.40 ms |
3322| root | wait/io/file/sql/misc | 26 | 22.38 ms | 860.80 us | 15.30 ms |
3323| root | wait/io/file/csv/data | 4 | 297.46 us | 74.37 us | 111.93 us |
3324| root | wait/synch/rwlock/sql/MDL_lock::rwlock | 944 | 287.86 us | 304.62 ns | 874.64 ns |
3325| root | wait/io/file/archive/data | 4 | 82.71 us | 20.68 us | 40.74 us |
3326| root | wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock | 60 | 12.21 us | 203.20 ns | 512.72 ns |
3327| root | wait/synch/mutex/innodb/trx_mutex | 81 | 5.93 us | 73.14 ns | 252.59 ns |
3328+------+-----------------------------------------------------+--------+---------------+-------------+-------------+
3329```
3330
3331#### waits_by_host_by_latency / x$waits_by_host_by_latency
3332
3333##### Description
3334
3335Lists the top wait events per host by their total latency, ignoring idle (this may be very large) per host.
3336
3337##### Structures
3338
3339```SQL
3340mysql> desc waits_by_host_by_latency;
3341+---------------+---------------------+------+-----+---------+-------+
3342| Field | Type | Null | Key | Default | Extra |
3343+---------------+---------------------+------+-----+---------+-------+
3344| host | varchar(60) | YES | | NULL | |
3345| event | varchar(128) | NO | | NULL | |
3346| total | bigint(20) unsigned | NO | | NULL | |
3347| total_latency | text | YES | | NULL | |
3348| avg_latency | text | YES | | NULL | |
3349| max_latency | text | YES | | NULL | |
3350+---------------+---------------------+------+-----+---------+-------+
33516 rows in set (0.36 sec)
3352
3353mysql> desc x$waits_by_host_by_latency;
3354+---------------+---------------------+------+-----+---------+-------+
3355| Field | Type | Null | Key | Default | Extra |
3356+---------------+---------------------+------+-----+---------+-------+
3357| host | varchar(60) | YES | | NULL | |
3358| event | varchar(128) | NO | | NULL | |
3359| total | bigint(20) unsigned | NO | | NULL | |
3360| total_latency | bigint(20) unsigned | NO | | NULL | |
3361| avg_latency | bigint(20) unsigned | NO | | NULL | |
3362| max_latency | bigint(20) unsigned | NO | | NULL | |
3363+---------------+---------------------+------+-----+---------+-------+
33646 rows in set (0.25 sec)
3365```
3366
3367##### Example
3368
3369```SQL
3370 mysql> select * from waits_by_host_by_latency;
3371 +------+-----------------------------------------------------+--------+---------------+-------------+-------------+
3372 | host | event | total | total_latency | avg_latency | max_latency |
3373 +------+-----------------------------------------------------+--------+---------------+-------------+-------------+
3374 | hal1 | wait/io/file/sql/file_parser | 13743 | 00:01:00.46 | 4.40 ms | 231.88 ms |
3375 | hal1 | wait/io/file/innodb/innodb_data_file | 4699 | 3.02 s | 643.38 us | 46.93 ms |
3376 | hal1 | wait/io/file/sql/FRM | 11462 | 2.60 s | 226.83 us | 61.72 ms |
3377 | hal1 | wait/io/file/myisam/dfile | 26776 | 746.70 ms | 27.89 us | 308.79 ms |
3378 | hal1 | wait/io/file/myisam/kfile | 7126 | 462.66 ms | 64.93 us | 88.76 ms |
3379 | hal1 | wait/io/file/sql/dbopt | 179 | 137.58 ms | 768.59 us | 15.46 ms |
3380 | hal1 | wait/io/file/csv/metadata | 8 | 86.60 ms | 10.82 ms | 50.32 ms |
3381 | hal1 | wait/synch/mutex/mysys/IO_CACHE::append_buffer_lock | 798080 | 66.46 ms | 82.94 ns | 161.03 us |
3382 | hal1 | wait/io/file/sql/binlog | 19 | 49.11 ms | 2.58 ms | 9.40 ms |
3383 | hal1 | wait/io/file/sql/misc | 26 | 22.38 ms | 860.80 us | 15.30 ms |
3384 | hal1 | wait/io/file/csv/data | 4 | 297.46 us | 74.37 us | 111.93 us |
3385 | hal1 | wait/synch/rwlock/sql/MDL_lock::rwlock | 944 | 287.86 us | 304.62 ns | 874.64 ns |
3386 | hal1 | wait/io/file/archive/data | 4 | 82.71 us | 20.68 us | 40.74 us |
3387 | hal1 | wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock | 60 | 12.21 us | 203.20 ns | 512.72 ns |
3388 | hal1 | wait/synch/mutex/innodb/trx_mutex | 81 | 5.93 us | 73.14 ns | 252.59 ns |
3389 +------+-----------------------------------------------------+--------+---------------+-------------+-------------+
3390```
3391
3392#### waits_global_by_latency / x$waits_global_by_latency
3393
3394##### Description
3395
3396Lists the top wait events by their total latency, ignoring idle (this may be very large).
3397
3398##### Structures
3399
3400```SQL
3401mysql> desc waits_global_by_latency;
3402+---------------+---------------------+------+-----+---------+-------+
3403| Field | Type | Null | Key | Default | Extra |
3404+---------------+---------------------+------+-----+---------+-------+
3405| events | varchar(128) | NO | | NULL | |
3406| total | bigint(20) unsigned | NO | | NULL | |
3407| total_latency | text | YES | | NULL | |
3408| avg_latency | text | YES | | NULL | |
3409| max_latency | text | YES | | NULL | |
3410+---------------+---------------------+------+-----+---------+-------+
34115 rows in set (0.01 sec)
3412
3413mysql> desc x$waits_global_by_latency;
3414+---------------+---------------------+------+-----+---------+-------+
3415| Field | Type | Null | Key | Default | Extra |
3416+---------------+---------------------+------+-----+---------+-------+
3417| events | varchar(128) | NO | | NULL | |
3418| total | bigint(20) unsigned | NO | | NULL | |
3419| total_latency | bigint(20) unsigned | NO | | NULL | |
3420| avg_latency | bigint(20) unsigned | NO | | NULL | |
3421| max_latency | bigint(20) unsigned | NO | | NULL | |
3422+---------------+---------------------+------+-----+---------+-------+
34235 rows in set (0.03 sec)
3424```
3425
3426##### Example
3427
3428```SQL
3429mysql> select * from waits_global_by_latency;
3430+-----------------------------------------------------+---------+---------------+-------------+-------------+
3431| events | total | total_latency | avg_latency | max_latency |
3432+-----------------------------------------------------+---------+---------------+-------------+-------------+
3433| wait/io/file/sql/file_parser | 14936 | 00:01:06.64 | 4.46 ms | 231.88 ms |
3434| wait/io/file/innodb/innodb_data_file | 6133 | 6.31 s | 1.03 ms | 147.56 ms |
3435| wait/io/file/sql/FRM | 12677 | 2.83 s | 223.37 us | 40.86 ms |
3436| wait/io/file/myisam/dfile | 28446 | 754.40 ms | 26.52 us | 308.79 ms |
3437| wait/io/file/myisam/kfile | 7572 | 491.17 ms | 64.87 us | 88.76 ms |
3438| wait/io/file/sql/relaylog | 9 | 252.28 ms | 28.03 ms | 144.17 ms |
3439| wait/io/file/sql/binlog | 76 | 242.87 ms | 3.20 ms | 153.72 ms |
3440| wait/io/file/sql/binlog_index | 21 | 173.07 ms | 8.24 ms | 81.83 ms |
3441| wait/io/file/sql/dbopt | 184 | 149.52 ms | 812.62 us | 15.46 ms |
3442| wait/io/file/innodb/innodb_log_file | 20 | 117.17 ms | 5.86 ms | 36.53 ms |
3443| wait/synch/mutex/mysys/IO_CACHE::append_buffer_lock | 1197128 | 99.27 ms | 82.56 ns | 161.03 us |
3444| wait/io/file/csv/metadata | 8 | 86.60 ms | 10.82 ms | 50.32 ms |
3445| wait/io/file/sql/relaylog_index | 10 | 60.10 ms | 6.01 ms | 48.04 ms |
3446| wait/io/file/sql/ERRMSG | 5 | 35.41 ms | 7.08 ms | 31.78 ms |
3447| wait/io/file/sql/misc | 28 | 22.40 ms | 800.06 us | 15.30 ms |
3448| wait/io/file/mysys/charset | 3 | 7.46 ms | 2.49 ms | 4.13 ms |
3449| wait/io/file/sql/casetest | 5 | 6.01 ms | 1.20 ms | 5.86 ms |
3450| wait/io/file/sql/pid | 3 | 5.96 ms | 1.99 ms | 3.06 ms |
3451| wait/synch/rwlock/sql/MDL_lock::rwlock | 1396 | 420.58 us | 301.22 ns | 874.64 ns |
3452| wait/io/file/csv/data | 4 | 297.46 us | 74.37 us | 111.93 us |
3453| wait/io/file/mysys/cnf | 5 | 154.97 us | 30.99 us | 58.87 us |
3454| wait/io/file/archive/data | 4 | 82.71 us | 20.68 us | 40.74 us |
3455| wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock | 90 | 19.23 us | 213.38 ns | 576.81 ns |
3456| wait/io/file/sql/global_ddl_log | 2 | 18.64 us | 9.32 us | 16.40 us |
3457| wait/synch/mutex/innodb/trx_mutex | 108 | 8.23 us | 76.15 ns | 365.69 ns |
3458+-----------------------------------------------------+---------+---------------+-------------+-------------+
3459```
3460
3461### Functions
3462
3463#### extract_schema_from_file_name
3464
3465##### Description
3466
3467Takes a raw file path, and attempts to extract the schema name from it.
3468
3469Useful for when interacting with Performance Schema data concerning IO statistics, for example.
3470
3471Currently relies on the fact that a table data file will be within a specified database directory (will not work with partitions or tables that specify an individual DATA_DIRECTORY).
3472
3473##### Parameters
3474
3475* path (VARCHAR(512)): The full file path to a data file to extract the schema name from.
3476
3477##### Returns
3478
3479VARCHAR(64)
3480
3481##### Example
3482```SQL
3483mysql> SELECT sys.extract_schema_from_file_name('/var/lib/mysql/employees/employee.ibd');
3484+----------------------------------------------------------------------------+
3485| sys.extract_schema_from_file_name('/var/lib/mysql/employees/employee.ibd') |
3486+----------------------------------------------------------------------------+
3487| employees |
3488+----------------------------------------------------------------------------+
34891 row in set (0.00 sec)
3490```
3491
3492#### extract_table_from_file_name
3493
3494##### Description
3495
3496Takes a raw file path, and extracts the table name from it.
3497
3498Useful for when interacting with Performance Schema data concerning IO statistics, for example.
3499
3500##### Parameters
3501
3502* path (VARCHAR(512)): The full file path to a data file to extract the table name from.
3503
3504##### Returns
3505
3506VARCHAR(64)
3507
3508##### Example
3509```SQL
3510mysql> SELECT sys.extract_table_from_file_name('/var/lib/mysql/employees/employee.ibd');
3511+---------------------------------------------------------------------------+
3512| sys.extract_table_from_file_name('/var/lib/mysql/employees/employee.ibd') |
3513+---------------------------------------------------------------------------+
3514| employee |
3515+---------------------------------------------------------------------------+
35161 row in set (0.02 sec)
3517```
3518
3519#### format_bytes
3520
3521##### Description
3522
3523Takes a raw bytes value, and converts it to a human readable format.
3524
3525##### Parameters
3526
3527* bytes (TEXT): A raw bytes value.
3528
3529##### Returns
3530
3531TEXT
3532
3533##### Example
3534```SQL
3535mysql> SELECT sys.format_bytes(2348723492723746) AS size;
3536+----------+
3537| size |
3538+----------+
3539| 2.09 PiB |
3540+----------+
35411 row in set (0.00 sec)
3542
3543mysql> SELECT sys.format_bytes(2348723492723) AS size;
3544+----------+
3545| size |
3546+----------+
3547| 2.14 TiB |
3548+----------+
35491 row in set (0.00 sec)
3550
3551mysql> SELECT sys.format_bytes(23487234) AS size;
3552+-----------+
3553| size |
3554+-----------+
3555| 22.40 MiB |
3556+-----------+
35571 row in set (0.00 sec)
3558```
3559
3560#### format_path
3561
3562##### Description
3563
3564Takes a raw path value, and strips out the datadir or tmpdir replacing with @@datadir and @@tmpdir respectively.
3565
3566Also normalizes the paths across operating systems, so backslashes on Windows are converted to forward slashes.
3567
3568##### Parameters
3569
3570* path (VARCHAR(512)): The raw file path value to format.
3571
3572##### Returns
3573
3574VARCHAR(512) CHARSET UTF8
3575
3576##### Example
3577```SQL
3578mysql> select @@datadir;
3579+-----------------------------------------------+
3580| @@datadir |
3581+-----------------------------------------------+
3582| /Users/mark/sandboxes/SmallTree/AMaster/data/ |
3583+-----------------------------------------------+
35841 row in set (0.06 sec)
3585
3586mysql> select format_path('/Users/mark/sandboxes/SmallTree/AMaster/data/mysql/proc.MYD') AS path;
3587+--------------------------+
3588| path |
3589+--------------------------+
3590| @@datadir/mysql/proc.MYD |
3591+--------------------------+
35921 row in set (0.03 sec)
3593```
3594
3595#### format_statement
3596
3597##### Description
3598
3599Formats a normalized statement, truncating it if it is > 64 characters long by default.
3600
3601To configure the length to truncate the statement to by default, update the `statement_truncate_len` variable with `sys_config` table to a different value. Alternatively, to change it just for just your particular session, use `SET @sys.statement_truncate_len := <some new value>`.
3602
3603Useful for printing statement related data from Performance Schema from the command line.
3604
3605##### Parameters
3606
3607* statement (LONGTEXT): The statement to format.
3608
3609##### Returns
3610
3611VARCHAR(65)
3612
3613##### Example
3614```SQL
3615mysql> SELECT sys.format_statement(digest_text)
3616 -> FROM performance_schema.events_statements_summary_by_digest
3617 -> ORDER by sum_timer_wait DESC limit 5;
3618+-------------------------------------------------------------------+
3619| sys.format_statement(digest_text) |
3620+-------------------------------------------------------------------+
3621| CREATE SQL SECURITY INVOKER VI ... KE ? AND `variable_value` > ? |
3622| CREATE SQL SECURITY INVOKER VI ... ait` IS NOT NULL , `esc` . ... |
3623| CREATE SQL SECURITY INVOKER VI ... ait` IS NOT NULL , `sys` . ... |
3624| CREATE SQL SECURITY INVOKER VI ... , `compressed_size` ) ) DESC |
3625| CREATE SQL SECURITY INVOKER VI ... LIKE ? ORDER BY `timer_start` |
3626+-------------------------------------------------------------------+
36275 rows in set (0.00 sec)
3628```
3629
3630#### format_time
3631
3632##### Description
3633
3634Takes a raw picoseconds value, and converts it to a human readable form.
3635
3636Picoseconds are the precision that all latency values are printed in within Performance Schema, however are not user friendly when wanting to scan output from the command line.
3637
3638##### Parameters
3639
3640* picoseconds (TEXT): The raw picoseconds value to convert.
3641
3642##### Returns
3643
3644TEXT
3645
3646##### Example
3647```SQL
3648mysql> select format_time(342342342342345);
3649+------------------------------+
3650| format_time(342342342342345) |
3651+------------------------------+
3652| 00:05:42 |
3653+------------------------------+
36541 row in set (0.00 sec)
3655
3656mysql> select format_time(342342342);
3657+------------------------+
3658| format_time(342342342) |
3659+------------------------+
3660| 342.34 us |
3661+------------------------+
36621 row in set (0.00 sec)
3663
3664mysql> select format_time(34234);
3665 +--------------------+
3666| format_time(34234) |
3667+--------------------+
3668| 34.23 ns |
3669+--------------------+
36701 row in set (0.00 sec)
3671```
3672
3673#### list_add
3674
3675##### Description
3676
3677Takes a list, and a value to add to the list, and returns the resulting list.
3678
3679Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.
3680
3681##### Parameters
3682
3683in_list (TEXT): The comma separated list to add a value to
3684
3685in_add_value (TEXT): The value to add to the input list
3686
3687##### Returns
3688
3689TEXT
3690
3691##### Example
3692
3693```SQL
3694mysql> select @@sql_mode;
3695+-----------------------------------------------------------------------------------+
3696| @@sql_mode |
3697+-----------------------------------------------------------------------------------+
3698| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
3699+-----------------------------------------------------------------------------------+
37001 row in set (0.00 sec)
3701
3702mysql> set sql_mode = sys.list_add(@@sql_mode, 'ANSI_QUOTES');
3703Query OK, 0 rows affected (0.06 sec)
3704
3705mysql> select @@sql_mode;
3706+-----------------------------------------------------------------------------------------------+
3707| @@sql_mode |
3708+-----------------------------------------------------------------------------------------------+
3709| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
3710+-----------------------------------------------------------------------------------------------+
37111 row in set (0.00 sec)
3712```
3713
3714#### list_drop
3715
3716##### Description
3717
3718Takes a list, and a value to attempt to remove from the list, and returns the resulting list.
3719
3720Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.
3721
3722##### Parameters
3723
3724in_list (TEXT): The comma separated list to drop a value from
3725
3726in_drop_value (TEXT): The value to drop from the input list
3727
3728##### Returns
3729
3730TEXT
3731
3732##### Example
3733
3734```SQL
3735mysql> select @@sql_mode;
3736+-----------------------------------------------------------------------------------------------+
3737| @@sql_mode |
3738+-----------------------------------------------------------------------------------------------+
3739| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
3740+-----------------------------------------------------------------------------------------------+
37411 row in set (0.00 sec)
3742
3743mysql> set sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');
3744Query OK, 0 rows affected (0.03 sec)
3745
3746mysql> select @@sql_mode;
3747+----------------------------------------------------------------------------+
3748| @@sql_mode |
3749+----------------------------------------------------------------------------+
3750| ANSI_QUOTES,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
3751+----------------------------------------------------------------------------+
37521 row in set (0.00 sec)
3753```
3754
3755#### ps_is_account_enabled
3756
3757##### Description
3758
3759Determines whether instrumentation of an account is enabled within Performance Schema.
3760
3761##### Parameters
3762
3763* in_host VARCHAR(60): The hostname of the account to check.
3764* in_user VARCHAR(32): The username of the account to check.
3765
3766##### Returns
3767
3768ENUM('YES', 'NO')
3769
3770##### Example
3771```SQL
3772mysql> SELECT sys.ps_is_account_enabled('localhost', 'root');
3773+------------------------------------------------+
3774| sys.ps_is_account_enabled('localhost', 'root') |
3775+------------------------------------------------+
3776| YES |
3777+------------------------------------------------+
37781 row in set (0.01 sec)
3779```
3780
3781#### ps_is_consumer_enabled
3782
3783##### Description
3784
3785Determines whether a consumer is enabled (taking the consumer hierarchy into consideration) within the Performance Schema.
3786
3787##### Parameters
3788
3789* in_consumer VARCHAR(64): The name of the consumer to check.
3790
3791##### Returns
3792
3793ENUM('YES', 'NO')
3794
3795##### Example
3796```SQL
3797mysql> SELECT sys.ps_is_consumer_enabled('events_stages_history');
3798+-----------------------------------------------------+
3799| sys.ps_is_consumer_enabled('events_stages_history') |
3800+-----------------------------------------------------+
3801| NO |
3802+-----------------------------------------------------+
38031 row in set (0.00 sec)
3804```
3805
3806#### ps_is_instrument_default_enabled
3807
3808##### Description
3809
3810Returns whether an instrument is enabled by default in this version of MySQL.
3811
3812##### Parameters
3813
3814* in_instrument VARCHAR(128): The instrument to check.
3815
3816##### Returns
3817
3818ENUM('YES', 'NO')
3819
3820##### Example
3821```SQL
3822mysql> SELECT sys.ps_is_instrument_default_enabled('statement/sql/select');
3823+--------------------------------------------------------------+
3824| sys.ps_is_instrument_default_enabled('statement/sql/select') |
3825+--------------------------------------------------------------+
3826| YES |
3827+--------------------------------------------------------------+
38281 row in set (0.00 sec)
3829```
3830
3831#### ps_is_instrument_default_timed
3832
3833##### Description
3834
3835Returns whether an instrument is timed by default in this version of MySQL.
3836
3837##### Parameters
3838
3839* in_instrument VARCHAR(128): The instrument to check.
3840
3841##### Returns
3842
3843ENUM('YES', 'NO')
3844
3845##### Example
3846```SQL
3847mysql> SELECT sys.ps_is_instrument_default_timed('statement/sql/select');
3848+------------------------------------------------------------+
3849| sys.ps_is_instrument_default_timed('statement/sql/select') |
3850+------------------------------------------------------------+
3851| YES |
3852+------------------------------------------------------------+
38531 row in set (0.00 sec)
3854```
3855
3856#### ps_is_thread_instrumented
3857
3858##### Description
3859
3860Checks whether the provided connection id is instrumented within Performance Schema.
3861
3862##### Parameters
3863
3864* in_connection_id (BIGINT UNSIGNED): the id of the connection to check.
3865
3866##### Returns
3867
3868ENUM('YES', 'NO', 'UNKNOWN')
3869
3870##### Example
3871```SQL
3872mysql> SELECT sys.ps_is_thread_instrumented(CONNECTION_ID());
3873+------------------------------------------------+
3874| sys.ps_is_thread_instrumented(CONNECTION_ID()) |
3875+------------------------------------------------+
3876| YES |
3877+------------------------------------------------+
38781 row in set (0.10 sec)
3879```
3880
3881#### ps_thread_id
3882
3883##### Description
3884
3885Return the Performance Schema THREAD_ID for the specified connection ID.
3886
3887##### Parameters
3888
3889* in_connection_id (BIGINT UNSIGNED): The id of the connection to return the thread id for. If NULL, the current connection thread id is returned.
3890
3891##### Returns
3892
3893BIGINT UNSIGNED
3894
3895##### Example
3896```SQL
3897mysql> SELECT sys.ps_thread_id(79);
3898+----------------------+
3899| sys.ps_thread_id(79) |
3900+----------------------+
3901| 98 |
3902+----------------------+
39031 row in set (0.00 sec)
3904
3905mysql> SELECT sys.ps_thread_id(CONNECTION_ID());
3906+-----------------------------------+
3907| sys.ps_thread_id(CONNECTION_ID()) |
3908+-----------------------------------+
3909| 98 |
3910+-----------------------------------+
39111 row in set (0.00 sec)
3912```
3913
3914#### ps_thread_stack
3915
3916##### Description
3917
3918Outputs a JSON formatted stack of all statements, stages and events within Performance Schema for the specified thread.
3919
3920##### Parameters
3921
3922* thd_id (BIGINT): The id of the thread to trace. This should match the thread_id column from the performance_schema.threads table.
3923
3924##### Example
3925
3926(line separation added for output)
3927
3928```SQL
3929 mysql> SELECT sys.ps_thread_stack(37, FALSE) AS thread_stack\G
3930*************************** 1. row ***************************
3931thread_stack: {"rankdir": "LR","nodesep": "0.10","stack_created": "2014-02-19 13:39:03",
3932"mysql_version": "5.7.3-m13","mysql_user": "root@localhost","events":
3933[{"nesting_event_id": "0", "event_id": "10", "timer_wait": 256.35, "event_info":
3934"sql/select", "wait_info": "select @@version_comment limit 1\nerrors: 0\nwarnings: 0\nlock time:
3935...
3936```
3937
3938#### ps_thread_trx_info
3939
3940##### Description
3941
3942Returns a JSON object with info on the given thread's current transaction, and the statements it has already executed, derived from the `performance_schema.events_transactions_current` and `performance_schema.events_statements_history` tables (so the consumers for these also have to be enabled within Performance Schema to get full data in the object).
3943
3944When the output exceeds the default truncation length (65535), a JSON error object is returned, such as:
3945
3946`{ "error": "Trx info truncated: Row 6 was cut by GROUP_CONCAT()" }`
3947
3948Similar error objects are returned for other warnings/and exceptions raised when calling the function.
3949
3950The max length of the output of this function can be controlled with the `ps_thread_trx_info.max_length` variable set via `sys_config`, or the `@sys.ps_thread_trx_info.max_length` user variable, as appropriate.
3951
3952##### Parameters
3953
3954* in_thread_id (BIGINT UNSIGNED): The id of the thread to return the transaction info for.
3955
3956##### Example
3957
3958```SQL
3959SELECT sys.ps_thread_trx_info(48)\G
3960*************************** 1. row ***************************
3961sys.ps_thread_trx_info(48): [
3962 {
3963 "time": "790.70 us",
3964 "state": "COMMITTED",
3965 "mode": "READ WRITE",
3966 "autocommitted": "NO",
3967 "gtid": "AUTOMATIC",
3968 "isolation": "REPEATABLE READ",
3969 "statements_executed": [
3970 {
3971 "sql_text": "INSERT INTO info VALUES (1, \'foo\')",
3972 "time": "471.02 us",
3973 "schema": "trx",
3974 "rows_examined": 0,
3975 "rows_affected": 1,
3976 "rows_sent": 0,
3977 "tmp_tables": 0,
3978 "tmp_disk_tables": 0,
3979 "sort_rows": 0,
3980 "sort_merge_passes": 0
3981 },
3982 {
3983 "sql_text": "COMMIT",
3984 "time": "254.42 us",
3985 "schema": "trx",
3986 "rows_examined": 0,
3987 "rows_affected": 0,
3988 "rows_sent": 0,
3989 "tmp_tables": 0,
3990 "tmp_disk_tables": 0,
3991 "sort_rows": 0,
3992 "sort_merge_passes": 0
3993 }
3994 ]
3995 },
3996 {
3997 "time": "426.20 us",
3998 "state": "COMMITTED",
3999 "mode": "READ WRITE",
4000 "autocommitted": "NO",
4001 "gtid": "AUTOMATIC",
4002 "isolation": "REPEATABLE READ",
4003 "statements_executed": [
4004 {
4005 "sql_text": "INSERT INTO info VALUES (2, \'bar\')",
4006 "time": "107.33 us",
4007 "schema": "trx",
4008 "rows_examined": 0,
4009 "rows_affected": 1,
4010 "rows_sent": 0,
4011 "tmp_tables": 0,
4012 "tmp_disk_tables": 0,
4013 "sort_rows": 0,
4014 "sort_merge_passes": 0
4015 },
4016 {
4017 "sql_text": "COMMIT",
4018 "time": "213.23 us",
4019 "schema": "trx",
4020 "rows_examined": 0,
4021 "rows_affected": 0,
4022 "rows_sent": 0,
4023 "tmp_tables": 0,
4024 "tmp_disk_tables": 0,
4025 "sort_rows": 0,
4026 "sort_merge_passes": 0
4027 }
4028 ]
4029 }
4030]
40311 row in set (0.03 sec)
4032```
4033
4034#### sys_get_config
4035
4036##### Description
4037
4038Returns the value for the requested variable using the following logic:
4039
40401. If the option exists in sys.sys_config return the value from there.
40412. Else fall back on the provided default value.
4042
4043Notes for using sys_get_config():
4044
4045* If the default value argument to sys_get_config() is NULL and case 2. is reached, NULL is returned.
4046 It is then expected that the caller is able to handle NULL for the given configuration option.
4047* The convention is to name the user variables @sys.<name of variable>. It is <name of variable> that
4048 is stored in the sys_config table and is what is expected as the argument to sys_get_config().
4049* If you want to check whether the configuration option has already been set and if not assign with
4050 the return value of sys_get_config() you can use IFNULL(...) (see example below). However this should
4051 not be done inside a loop (e.g. for each row in a result set) as for repeated calls where assignment
4052 is only needed in the first iteration using IFNULL(...) is expected to be significantly slower than
4053 using an IF (...) THEN ... END IF; block (see example below).
4054
4055##### Parameters
4056
4057* in_variable_name (VARCHAR(128)): The name of the config option to return the value for.
4058* in_default_value (VARCHAR(128)): The default value to return if neither a use variable exists nor the variable exists in sys.sys_config.
4059
4060##### Returns
4061
4062VARCHAR(128)
4063
4064##### Example
4065```SQL
4066-- Get the configuration value from sys.sys_config falling back on 128 if the option is not present in the table.
4067mysql> SELECT sys.sys_get_config('statement_truncate_len', 128) AS Value;
4068+-------+
4069| Value |
4070+-------+
4071| 64 |
4072+-------+
40731 row in set (0.00 sec)
4074
4075-- Check whether the option is already set, if not assign - IFNULL(...) one liner example.
4076mysql> SET @sys.statement_truncate_len = IFNULL(@sys.statement_truncate_len, sys.sys_get_config('statement_truncate_len', 64));
4077Query OK, 0 rows affected (0.00 sec)
4078
4079-- Check whether the option is already set, if not assign - IF ... THEN ... END IF example.
4080IF (@sys.statement_truncate_len IS NULL) THEN
4081 SET @sys.statement_truncate_len = sys.sys_get_config('statement_truncate_len', 64);
4082END IF;
4083```
4084
4085#### version_major
4086
4087##### Description
4088
4089Returns the major version of MySQL Server.
4090
4091##### Returns
4092
4093TINYINT UNSIGNED
4094
4095##### Example
4096```SQL
4097mysql> SELECT VERSION(), sys.version_major();
4098+--------------------------------------+---------------------+
4099| VERSION() | sys.version_major() |
4100+--------------------------------------+---------------------+
4101| 5.7.9-enterprise-commercial-advanced | 5 |
4102+--------------------------------------+---------------------+
41031 row in set (0.00 sec)
4104```
4105
4106#### version_minor
4107
4108##### Description
4109
4110Returns the minor (release series) version of MySQL Server.
4111
4112##### Returns
4113
4114TINYINT UNSIGNED
4115
4116##### Example
4117```SQL
4118mysql> SELECT VERSION(), sys.server_minor();
4119+--------------------------------------+---------------------+
4120| VERSION() | sys.version_minor() |
4121+--------------------------------------+---------------------+
4122| 5.7.9-enterprise-commercial-advanced | 7 |
4123+--------------------------------------+---------------------+
41241 row in set (0.00 sec)
4125```
4126
4127#### version_patch
4128
4129##### Description
4130
4131Returns the patch release version of MySQL Server.
4132
4133##### Returns
4134
4135TINYINT UNSIGNED
4136
4137##### Example
4138```SQL
4139mysql> SELECT VERSION(), sys.version_patch();
4140+--------------------------------------+---------------------+
4141| VERSION() | sys.version_patch() |
4142+--------------------------------------+---------------------+
4143| 5.7.9-enterprise-commercial-advanced | 9 |
4144+--------------------------------------+---------------------+
41451 row in set (0.00 sec)
4146```
4147
4148
4149### Procedures
4150
4151#### create_synonym_db
4152
4153##### Description
4154
4155Takes a source database name and synonym name, and then creates the synonym database with views that point to all of the tables within the source database.
4156
4157Useful for creating a "ps" synonym for "performance_schema", or "is" instead of "information_schema", for example.
4158
4159##### Parameters
4160
4161* in_db_name (VARCHAR(64)):
4162** The database name that you would like to create a synonym for.
4163* in_synonym (VARCHAR(64)):
4164** The database synonym name.
4165
4166##### Example
4167```SQL
4168mysql> SHOW DATABASES;
4169+--------------------+
4170| Database |
4171+--------------------+
4172| information_schema |
4173| mysql |
4174| performance_schema |
4175| sys |
4176| test |
4177+--------------------+
41785 rows in set (0.00 sec)
4179
4180mysql> CALL sys.create_synonym_db('performance_schema', 'ps');
4181+-------------------------------------+
4182| summary |
4183+-------------------------------------+
4184| Created 74 views in the ps database |
4185+-------------------------------------+
41861 row in set (8.57 sec)
4187
4188Query OK, 0 rows affected (8.57 sec)
4189
4190mysql> SHOW DATABASES;
4191+--------------------+
4192| Database |
4193+--------------------+
4194| information_schema |
4195| mysql |
4196| performance_schema |
4197| ps |
4198| sys |
4199| test |
4200+--------------------+
42016 rows in set (0.00 sec)
4202
4203mysql> SHOW FULL TABLES FROM ps;
4204+-----------------------------------------+------------+
4205| Tables_in_ps | Table_type |
4206+-----------------------------------------+------------+
4207| accounts | VIEW |
4208| cond_instances | VIEW |
4209| events_stages_current | VIEW |
4210| events_stages_history | VIEW |
4211...
4212```
4213
4214#### execute_prepared_stmt
4215
4216##### Description
4217
4218Takes the query in the argument and executes it using a prepared statement. The prepared statement is deallocated,
4219so the procedure is mainly useful for executing one off dynamically created queries.
4220
4221The sys_execute_prepared_stmt prepared statement name is used for the query and is required not to exist.
4222
4223##### Parameters
4224
4225* in_query (longtext CHARACTER SET UTF8):
4226** The query to execute.
4227
4228The following configuration option is supported:
4229
4230 * sys.debug
4231 Whether to provide debugging output.
4232 Default is 'OFF'. Set to 'ON' to include.
4233
4234##### Example
4235```SQL
4236mysql> CALL sys.execute_prepared_stmt(''SELECT * FROM sys.sys_config'');
4237+------------------------+-------+---------------------+--------+
4238| variable | value | set_time | set_by |
4239+------------------------+-------+---------------------+--------+
4240| statement_truncate_len | 64 | 2015-06-30 13:06:00 | NULL |
4241+------------------------+-------+---------------------+--------+
42421 row in set (0.00 sec)
4243
4244Query OK, 0 rows affected (0.00 sec)
4245```
4246
4247#### diagnostics
4248
4249##### Description
4250
4251Create a report of the current status of the server for diagnostics purposes. Data collected includes (some items depends on versions and settings):
4252
4253* The GLOBAL VARIABLES
4254* Several sys schema views including metrics or equivalent (depending on version and settings)
4255* Queries in the 95th percentile
4256* Several ndbinfo views for MySQL Cluster
4257* Replication (both master and slave) information.
4258
4259Some of the sys schema views are calculated as initial (optional), overall, delta:
4260
4261* The initial view is the content of the view at the start of this procedure.
4262 This output will be the same as the the start values used for the delta view.
4263 The initial view is included if @sys.diagnostics.include_raw = 'ON'.
4264* The overall view is the content of the view at the end of this procedure.
4265 This output is the same as the end values used for the delta view.
4266 The overall view is always included.
4267* The delta view is the difference from the beginning to the end. Note that for min and max values
4268 they are simply the min or max value from the end view respectively, so does not necessarily reflect
4269 the minimum/maximum value in the monitored period.
4270 Note: except for the metrics view the delta is only calculation between the first and last outputs.
4271
4272Requires the SUPER privilege for "SET sql_log_bin = 0;".
4273
4274Versions supported:
4275* MySQL 5.6: 5.6.10 and later
4276* MySQL 5.7: 5.7.9 and later
4277
4278Some configuration options are supported:
4279
4280* sys.diagnostics.allow_i_s_tables
4281 Specifies whether it is allowed to do table scan queries on information_schema.TABLES. This can be expensive if there
4282 are many tables. Set to 'ON' to allow, 'OFF' to not allow.
4283 Default is 'OFF'.
4284
4285* sys.diagnostics.include_raw
4286 Set to 'ON' to include the raw data (e.g. the original output of "SELECT * FROM sys.metrics").
4287 Use this to get the initial values of the various views.
4288 Default is 'OFF'.
4289
4290* sys.statement_truncate_len
4291 How much of queries in the process list output to include.
4292 Default is 64.
4293
4294* sys.debug
4295 Whether to provide debugging output.
4296 Default is 'OFF'. Set to 'ON' to include.
4297
4298##### Parameters
4299
4300* in_max_runtime (INT UNSIGNED):
4301The maximum time to keep collecting data.
4302Use NULL to get the default which is 60 seconds, otherwise enter a value greater than 0.
4303* in_interval (INT UNSIGNED):
4304How long to sleep between data collections.
4305Use NULL to get the default which is 30 seconds, otherwise enter a value greater than 0.
4306* in_auto_config (ENUM('current', 'medium', 'full'))
4307Automatically enable Performance Schema instruments and consumers.
4308NOTE: The more that are enabled, the more impact on the performance.
4309If another setting the 'current' is chosen, the current settings
4310are restored at the end of the procedure.
4311Supported values are:
4312** current - use the current settings.
4313** medium - enable some settings. This requires the SUPER privilege.
4314** full - enables all settings. This will have a big impact on the
4315 performance - be careful using this option. This requires the SUPER privilege.
4316
4317##### Example
4318```SQL
4319mysql> TEE diag.out;
4320mysql> CALL sys.diagnostics(120, 30, 'current');
4321...
4322mysql> NOTEE;
4323```
4324
4325#### ps_setup_disable_background_threads
4326
4327##### Description
4328
4329Disable all background thread instrumentation within Performance Schema.
4330
4331##### Parameters
4332
4333None.
4334
4335##### Example
4336```SQL
4337mysql> CALL sys.ps_setup_disable_background_threads();
4338+--------------------------------+
4339| summary |
4340+--------------------------------+
4341| Disabled 18 background threads |
4342+--------------------------------+
43431 row in set (0.00 sec)
4344```
4345
4346#### ps_setup_disable_instrument
4347
4348##### Description
4349
4350Disables instruments within Performance Schema matching the input pattern.
4351
4352##### Parameters
4353
4354* in_pattern (VARCHAR(128)): A LIKE pattern match (using "%in_pattern%") of events to disable
4355
4356##### Example
4357
4358To disable all mutex instruments:
4359```SQL
4360mysql> CALL sys.ps_setup_disable_instrument('wait/synch/mutex');
4361+--------------------------+
4362| summary |
4363+--------------------------+
4364| Disabled 155 instruments |
4365+--------------------------+
43661 row in set (0.02 sec)
4367```
4368To disable just a the scpecific TCP/IP based network IO instrument:
4369```SQL
4370mysql> CALL sys.ps_setup_disable_instrument('wait/io/socket/sql/server_tcpip_socket');
4371+------------------------+
4372| summary |
4373+------------------------+
4374| Disabled 1 instruments |
4375+------------------------+
43761 row in set (0.00 sec)
4377```
4378To enable all instruments:
4379```SQL
4380mysql> CALL sys.ps_setup_disable_instrument('');
4381+--------------------------+
4382| summary |
4383+--------------------------+
4384| Disabled 547 instruments |
4385+--------------------------+
43861 row in set (0.01 sec)
4387```
4388
4389#### ps_setup_disable_consumer
4390
4391##### Description
4392
4393Disables consumers within Performance Schema matching the input pattern.
4394
4395##### Parameters
4396
4397* consumer (VARCHAR(128)): A LIKE pattern match (using "%consumer%") of consumers to disable
4398
4399##### Example
4400
4401To disable all consumers:
4402```SQL
4403mysql> CALL sys.ps_setup_disable_consumer('');
4404+--------------------------+
4405| summary |
4406+--------------------------+
4407| Disabled 15 consumers |
4408+--------------------------+
44091 row in set (0.02 sec)
4410```
4411
4412To disable just the event_stage consumers:
4413```SQL
4414mysql> CALL sys.ps_setup_disable_consumer('stage');
4415+------------------------+
4416| summary |
4417+------------------------+
4418| Disabled 3 consumers |
4419+------------------------+
44201 row in set (0.00 sec)
4421```
4422
4423#### ps_setup_disable_thread
4424
4425##### Description
4426
4427Disable the given connection/thread in Performance Schema.
4428
4429##### Parameters
4430
4431* in_connection_id (BIGINT): The connection ID (PROCESSLIST_ID from performance_schema.threads or the ID shown within SHOW PROCESSLIST)
4432
4433##### Example
4434```SQL
4435mysql> CALL sys.ps_setup_disable_thread(3);
4436+-------------------+
4437| summary |
4438+-------------------+
4439| Disabled 1 thread |
4440+-------------------+
44411 row in set (0.01 sec)
4442```
4443To disable the current connection:
4444```SQL
4445mysql> CALL sys.ps_setup_disable_thread(CONNECTION_ID());
4446+-------------------+
4447| summary |
4448+-------------------+
4449| Disabled 1 thread |
4450+-------------------+
44511 row in set (0.00 sec)
4452```
4453
4454#### ps_setup_enable_background_threads
4455
4456##### Description
4457
4458Enable all background thread instrumentation within Performance Schema.
4459
4460##### Parameters
4461
4462None.
4463
4464##### Example
4465```SQL
4466mysql> CALL sys.ps_setup_enable_background_threads();
4467+-------------------------------+
4468| summary |
4469+-------------------------------+
4470| Enabled 18 background threads |
4471+-------------------------------+
44721 row in set (0.00 sec)
4473```
4474
4475#### ps_setup_enable_consumer
4476
4477##### Description
4478
4479Enables consumers within Performance Schema matching the input pattern.
4480
4481##### Parameters
4482
4483* consumer (VARCHAR(128)): A LIKE pattern match (using "%consumer%") of consumers to enable
4484
4485##### Example
4486
4487To enable all consumers:
4488```SQL
4489mysql> CALL sys.ps_setup_enable_consumer('');
4490+-------------------------+
4491| summary |
4492+-------------------------+
4493| Enabled 10 consumers |
4494+-------------------------+
44951 row in set (0.02 sec)
4496```
4497
4498To enable just "waits" consumers:
4499```SQL
4500mysql> CALL sys.ps_setup_enable_consumer('waits');
4501+-----------------------+
4502| summary |
4503+-----------------------+
4504| Enabled 3 consumers |
4505+-----------------------+
45061 row in set (0.00 sec)
4507```
4508
4509#### ps_setup_enable_instrument
4510
4511##### Description
4512
4513Enables instruments within Performance Schema matching the input pattern.
4514
4515##### Parameters
4516
4517
4518* in_pattern (VARCHAR(128)): A LIKE pattern match (using "%in_pattern%") of events to enable
4519
4520##### Example
4521
4522To enable all mutex instruments:
4523```SQL
4524mysql> CALL sys.ps_setup_enable_instrument('wait/synch/mutex');
4525+-------------------------+
4526| summary |
4527+-------------------------+
4528| Enabled 155 instruments |
4529+-------------------------+
45301 row in set (0.02 sec)
4531```
4532To enable just a the scpecific TCP/IP based network IO instrument:
4533```SQL
4534mysql> CALL sys.ps_setup_enable_instrument('wait/io/socket/sql/server_tcpip_socket');
4535+-----------------------+
4536| summary |
4537+-----------------------+
4538| Enabled 1 instruments |
4539+-----------------------+
45401 row in set (0.00 sec)
4541```
4542To enable all instruments:
4543```SQL
4544mysql> CALL sys.ps_setup_enable_instrument('');
4545+-------------------------+
4546| summary |
4547+-------------------------+
4548| Enabled 547 instruments |
4549+-------------------------+
45501 row in set (0.01 sec)
4551```
4552
4553#### ps_setup_enable_thread
4554
4555##### Description
4556
4557Enable the given connection/thread in Performance Schema.
4558
4559##### Parameters
4560
4561
4562* in_connection_id (BIGINT): The connection ID (PROCESSLIST_ID from performance_schema.threads or the ID shown within SHOW PROCESSLIST)
4563
4564##### Example
4565```SQL
4566mysql> CALL sys.ps_setup_enable_thread(3);
4567+------------------+
4568| summary |
4569+------------------+
4570| Enabled 1 thread |
4571+------------------+
45721 row in set (0.01 sec)
4573```
4574To enable the current connection:
4575```SQL
4576mysql> CALL sys.ps_setup_enable_thread(CONNECTION_ID());
4577+------------------+
4578| summary |
4579+------------------+
4580| Enabled 1 thread |
4581+------------------+
45821 row in set (0.00 sec)
4583```
4584
4585#### ps_setup_reload_saved
4586
4587##### Description
4588
4589Reloads a saved Performance Schema configuration, so that you can alter the setup for debugging purposes, but restore it to a previous state.
4590
4591Use the companion procedure - ps_setup_save(), to save a configuration.
4592
4593Requires the SUPER privilege for "SET sql_log_bin = 0;".
4594
4595##### Parameters
4596
4597None.
4598
4599##### Example
4600```SQL
4601mysql> CALL sys.ps_setup_save();
4602Query OK, 0 rows affected (0.08 sec)
4603
4604mysql> UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
4605Query OK, 547 rows affected (0.40 sec)
4606Rows matched: 784 Changed: 547 Warnings: 0
4607
4608/* Run some tests that need more detailed instrumentation here */
4609
4610mysql> CALL sys.ps_setup_reload_saved();
4611Query OK, 0 rows affected (0.32 sec)
4612```
4613
4614#### ps_setup_reset_to_default
4615
4616##### Description
4617
4618Resets the Performance Schema setup to the default settings.
4619
4620##### Parameters
4621
4622* in_verbose (BOOLEAN): Whether to print each setup stage (including the SQL) whilst running.
4623
4624##### Example
4625```SQL
4626mysql> CALL sys.ps_setup_reset_to_default(true)\G
4627*************************** 1. row ***************************
4628status: Resetting: setup_actors
4629DELETE
4630FROM performance_schema.setup_actors
4631WHERE NOT (HOST = '%' AND USER = '%' AND ROLE = '%')
46321 row in set (0.00 sec)
4633
4634*************************** 1. row ***************************
4635status: Resetting: setup_actors
4636INSERT IGNORE INTO performance_schema.setup_actors
4637VALUES ('%', '%', '%')
46381 row in set (0.00 sec)
4639...
4640
4641mysql> CALL sys.ps_setup_reset_to_default(false)G
4642Query OK, 0 rows affected (0.00 sec)
4643```
4644
4645#### ps_setup_save
4646
4647##### Description
4648
4649Saves the current configuration of Performance Schema, so that you can alter the setup for debugging purposes, but restore it to a previous state.
4650
4651Use the companion procedure - ps_setup_reload_saved(), to restore the saved config.
4652
4653Requires the SUPER privilege for "SET sql_log_bin = 0;".
4654
4655##### Parameters
4656
4657None.
4658
4659##### Example
4660```SQL
4661mysql> CALL sys.ps_setup_save();
4662Query OK, 0 rows affected (0.08 sec)
4663
4664mysql> UPDATE performance_schema.setup_instruments
4665 -> SET enabled = 'YES', timed = 'YES';
4666Query OK, 547 rows affected (0.40 sec)
4667Rows matched: 784 Changed: 547 Warnings: 0
4668
4669/* Run some tests that need more detailed instrumentation here */
4670
4671mysql> CALL sys.ps_setup_reload_saved();
4672Query OK, 0 rows affected (0.32 sec)
4673```
4674
4675#### ps_setup_show_disabled
4676
4677##### Description
4678
4679Shows all currently disable Performance Schema configuration.
4680
4681##### Parameters
4682
4683* in_in_show_instruments (BOOLEAN): Whether to print disabled instruments (can print many items)
4684* in_in_show_threads (BOOLEAN): Whether to print disabled threads
4685
4686##### Example
4687```SQL
4688mysql> CALL sys.ps_setup_show_disabled(TRUE, TRUE);
4689+----------------------------+
4690| performance_schema_enabled |
4691+----------------------------+
4692| 1 |
4693+----------------------------+
46941 row in set (0.00 sec)
4695
4696+--------------------+
4697| enabled_users |
4698+--------------------+
4699| 'mark'@'localhost' |
4700+--------------------+
47011 row in set (0.00 sec)
4702
4703+-------------+----------------------+---------+-------+
4704| object_type | objects | enabled | timed |
4705+-------------+----------------------+---------+-------+
4706| EVENT | mysql.% | NO | NO |
4707| EVENT | performance_schema.% | NO | NO |
4708| EVENT | information_schema.% | NO | NO |
4709| FUNCTION | mysql.% | NO | NO |
4710| FUNCTION | performance_schema.% | NO | NO |
4711| FUNCTION | information_schema.% | NO | NO |
4712| PROCEDURE | mysql.% | NO | NO |
4713| PROCEDURE | performance_schema.% | NO | NO |
4714| PROCEDURE | information_schema.% | NO | NO |
4715| TABLE | mysql.% | NO | NO |
4716| TABLE | performance_schema.% | NO | NO |
4717| TABLE | information_schema.% | NO | NO |
4718| TRIGGER | mysql.% | NO | NO |
4719| TRIGGER | performance_schema.% | NO | NO |
4720| TRIGGER | information_schema.% | NO | NO |
4721+-------------+----------------------+---------+-------+
472215 rows in set (0.00 sec)
4723
4724+----------------------------------+
4725| disabled_consumers |
4726+----------------------------------+
4727| events_stages_current |
4728| events_stages_history |
4729| events_stages_history_long |
4730| events_statements_history |
4731| events_statements_history_long |
4732| events_transactions_history |
4733| events_transactions_history_long |
4734| events_waits_current |
4735| events_waits_history |
4736| events_waits_history_long |
4737+----------------------------------+
473810 rows in set (0.00 sec)
4739
4740Empty set (0.00 sec)
4741
4742+---------------------------------------------------------------------------------------+-------+
4743| disabled_instruments | timed |
4744+---------------------------------------------------------------------------------------+-------+
4745| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO |
4746| wait/synch/mutex/sql/LOCK_des_key_file | NO |
4747| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO |
4748...
4749| memory/sql/servers_cache | NO |
4750| memory/sql/udf_mem | NO |
4751| wait/lock/metadata/sql/mdl | NO |
4752+---------------------------------------------------------------------------------------+-------+
4753547 rows in set (0.00 sec)
4754
4755Query OK, 0 rows affected (0.01 sec)
4756```
4757
4758#### ps_setup_show_disabled_consumers
4759
4760##### Description
4761
4762Shows all currently disabled consumers.
4763
4764##### Parameters
4765
4766None
4767
4768##### Example
4769
4770```SQL
4771mysql> CALL sys.ps_setup_show_disabled_consumers();
4772
4773+---------------------------+
4774| disabled_consumers |
4775+---------------------------+
4776| events_statements_current |
4777| global_instrumentation |
4778| thread_instrumentation |
4779| statements_digest |
4780+---------------------------+
47814 rows in set (0.05 sec)
4782```
4783
4784#### ps_setup_show_disabled_instruments
4785
4786##### Description
4787
4788Shows all currently disabled instruments.
4789
4790##### Parameters
4791
4792None
4793
4794##### Example
4795
4796```SQL
4797mysql> CALL sys.ps_setup_show_disabled_instruments();
4798```
4799
4800#### ps_setup_show_enabled
4801
4802##### Description
4803
4804Shows all currently enabled Performance Schema configuration.
4805
4806##### Parameters
4807
4808* in_show_instruments (BOOLEAN): Whether to print enabled instruments (can print many items)
4809* in_show_threads (BOOLEAN): Whether to print enabled threads
4810
4811##### Example
4812```SQL
4813mysql> CALL sys.ps_setup_show_enabled(TRUE, TRUE);
4814+----------------------------+
4815| performance_schema_enabled |
4816+----------------------------+
4817| 1 |
4818+----------------------------+
48191 row in set (0.00 sec)
4820
4821+---------------+
4822| enabled_users |
4823+---------------+
4824| '%'@'%' |
4825+---------------+
48261 row in set (0.01 sec)
4827
4828+----------------------+---------+-------+
4829| objects | enabled | timed |
4830+----------------------+---------+-------+
4831| mysql.% | NO | NO |
4832| performance_schema.% | NO | NO |
4833| information_schema.% | NO | NO |
4834| %.% | YES | YES |
4835+----------------------+---------+-------+
48364 rows in set (0.01 sec)
4837
4838+---------------------------+
4839| enabled_consumers |
4840+---------------------------+
4841| events_statements_current |
4842| global_instrumentation |
4843| thread_instrumentation |
4844| statements_digest |
4845+---------------------------+
48464 rows in set (0.05 sec)
4847
4848+--------------------------+-------------+
4849| enabled_threads | thread_type |
4850+--------------------------+-------------+
4851| innodb/srv_master_thread | BACKGROUND |
4852| root@localhost | FOREGROUND |
4853| root@localhost | FOREGROUND |
4854| root@localhost | FOREGROUND |
4855| root@localhost | FOREGROUND |
4856+--------------------------+-------------+
48575 rows in set (0.03 sec)
4858
4859+-------------------------------------+-------+
4860| enabled_instruments | timed |
4861+-------------------------------------+-------+
4862| wait/io/file/sql/map | YES |
4863| wait/io/file/sql/binlog | YES |
4864...
4865| statement/com/Error | YES |
4866| statement/com/ | YES |
4867| idle | YES |
4868+-------------------------------------+-------+
4869210 rows in set (0.08 sec)
4870
4871Query OK, 0 rows affected (0.89 sec)
4872```
4873
4874#### ps_setup_show_enabled_consumers
4875
4876##### Description
4877
4878Shows all currently enabled consumers.
4879
4880##### Parameters
4881
4882None
4883
4884##### Example
4885
4886```SQL
4887mysql> CALL sys.ps_setup_show_enabled_consumers();
4888
4889+---------------------------+
4890| enabled_consumers |
4891+---------------------------+
4892| events_statements_current |
4893| global_instrumentation |
4894| thread_instrumentation |
4895| statements_digest |
4896+---------------------------+
48974 rows in set (0.05 sec)
4898```
4899
4900#### ps_setup_show_enabled_instruments
4901
4902##### Description
4903
4904Shows all currently enabled instruments.
4905
4906##### Parameters
4907
4908None
4909
4910##### Example
4911
4912```SQL
4913mysql> CALL sys.ps_setup_show_enabled_instruments();
4914```
4915
4916#### ps_statement_avg_latency_histogram
4917
4918##### Description
4919
4920Outputs a textual histogram graph of the average latency values across all normalized queries tracked within the Performance Schema events_statements_summary_by_digest table.
4921
4922Can be used to show a very high level picture of what kind of latency distribution statements running within this instance have.
4923
4924##### Parameters
4925
4926None.
4927
4928##### Example
4929```SQL
4930mysql> CALL sys.ps_statement_avg_latency_histogram()G
4931*************************** 1. row ***************************
4932Performance Schema Statement Digest Average Latency Histogram:
4933
4934 . = 1 unit
4935 * = 2 units
4936 # = 3 units
4937
4938(0 - 38ms) 240 | ################################################################################
4939(38 - 77ms) 38 | ......................................
4940(77 - 115ms) 3 | ...
4941(115 - 154ms) 62 | *******************************
4942(154 - 192ms) 3 | ...
4943(192 - 231ms) 0 |
4944(231 - 269ms) 0 |
4945(269 - 307ms) 0 |
4946(307 - 346ms) 0 |
4947(346 - 384ms) 1 | .
4948(384 - 423ms) 1 | .
4949(423 - 461ms) 0 |
4950(461 - 499ms) 0 |
4951(499 - 538ms) 0 |
4952(538 - 576ms) 0 |
4953(576 - 615ms) 1 | .
4954
4955 Total Statements: 350; Buckets: 16; Bucket Size: 38 ms;
4956```
4957
4958#### ps_trace_statement_digest
4959
4960##### Description
4961
4962Traces all instrumentation within Performance Schema for a specific Statement Digest.
4963
4964When finding a statement of interest within the performance_schema.events_statements_summary_by_digest table, feed the DIGEST MD5 value in to this procedure, set how long to poll for, and at what interval to poll, and it will generate a report of all statistics tracked within Performance Schema for that digest for the interval.
4965
4966It will also attempt to generate an EXPLAIN for the longest running example of the digest during the interval.
4967
4968Note this may fail, as Performance Schema truncates long SQL_TEXT values (and hence the EXPLAIN will fail due to parse errors).
4969
4970Requires the SUPER privilege for "SET sql_log_bin = 0;".
4971
4972##### Parameters
4973
4974* in_digest VARCHAR(32): The statement digest identifier you would like to analyze
4975* in_runtime (INT): The number of seconds to run analysis for (defaults to a minute)
4976* in_interval (DECIMAL(2,2)): The interval (in seconds, may be fractional) at which to try and take snapshots (defaults to a second)
4977* in_start_fresh (BOOLEAN): Whether to TRUNCATE the events_statements_history_long and events_stages_history_long tables before starting (default false)
4978* in_auto_enable (BOOLEAN): Whether to automatically turn on required consumers (default false)
4979
4980##### Example
4981```SQL
4982mysql> call ps_analyze_statement_digest('891ec6860f98ba46d89dd20b0c03652c', 10, 0.1, true, true);
4983+--------------------+
4984| SUMMARY STATISTICS |
4985+--------------------+
4986| SUMMARY STATISTICS |
4987+--------------------+
49881 row in set (9.11 sec)
4989
4990+------------+-----------+-----------+-----------+---------------+------------+------------+
4991| executions | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scans |
4992+------------+-----------+-----------+-----------+---------------+------------+------------+
4993| 21 | 4.11 ms | 2.00 ms | 0 | 21 | 0 | 0 |
4994+------------+-----------+-----------+-----------+---------------+------------+------------+
49951 row in set (9.11 sec)
4996
4997+------------------------------------------+-------+-----------+
4998| event_name | count | latency |
4999+------------------------------------------+-------+-----------+
5000| stage/sql/checking query cache for query | 16 | 724.37 us |
5001| stage/sql/statistics | 16 | 546.92 us |
5002| stage/sql/freeing items | 18 | 520.11 us |
5003| stage/sql/init | 51 | 466.80 us |
5004...
5005| stage/sql/cleaning up | 18 | 11.92 us |
5006| stage/sql/executing | 16 | 6.95 us |
5007+------------------------------------------+-------+-----------+
500817 rows in set (9.12 sec)
5009
5010+---------------------------+
5011| LONGEST RUNNING STATEMENT |
5012+---------------------------+
5013| LONGEST RUNNING STATEMENT |
5014+---------------------------+
50151 row in set (9.16 sec)
5016
5017+-----------+-----------+-----------+-----------+---------------+------------+-----------+
5018| thread_id | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scan |
5019+-----------+-----------+-----------+-----------+---------------+------------+-----------+
5020| 166646 | 618.43 us | 1.00 ms | 0 | 1 | 0 | 0 |
5021+-----------+-----------+-----------+-----------+---------------+------------+-----------+
50221 row in set (9.16 sec)
5023
5024// Truncated for clarity...
5025+-----------------------------------------------------------------+
5026| sql_text |
5027+-----------------------------------------------------------------+
5028| select hibeventhe0_.id as id1382_, hibeventhe0_.createdTime ... |
5029+-----------------------------------------------------------------+
50301 row in set (9.17 sec)
5031
5032+------------------------------------------+-----------+
5033| event_name | latency |
5034+------------------------------------------+-----------+
5035| stage/sql/init | 8.61 us |
5036| stage/sql/Waiting for query cache lock | 453.23 us |
5037| stage/sql/init | 331.07 ns |
5038| stage/sql/checking query cache for query | 43.04 us |
5039...
5040| stage/sql/freeing items | 30.46 us |
5041| stage/sql/cleaning up | 662.13 ns |
5042+------------------------------------------+-----------+
5043 18 rows in set (9.23 sec)
5044
5045+----+-------------+--------------+-------+---------------+-----------+---------+-------------+------+-------+
5046| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
5047+----+-------------+--------------+-------+---------------+-----------+---------+-------------+------+-------+
5048| 1 | SIMPLE | hibeventhe0_ | const | fixedTime | fixedTime | 775 | const,const | 1 | NULL |
5049+----+-------------+--------------+-------+---------------+-----------+---------+-------------+------+-------+
50501 row in set (9.27 sec)
5051
5052Query OK, 0 rows affected (9.28 sec)
5053```
5054
5055#### ps_trace_thread
5056
5057##### Description
5058
5059Dumps all data within Performance Schema for an instrumented thread, to create a DOT formatted graph file.
5060
5061Each resultset returned from the procedure should be used for a complete graph
5062
5063Requires the SUPER privilege for "SET sql_log_bin = 0;".
5064
5065##### Parameters
5066
5067* in_thread_id (INT): The thread that you would like a stack trace for
5068* in_outfile (VARCHAR(255)): The filename the dot file will be written to
5069* in_max_runtime (DECIMAL(20,2)): The maximum time to keep collecting data. Use NULL to get the default which is 60 seconds.
5070* in_interval (DECIMAL(20,2)): How long to sleep between data collections. Use NULL to get the default which is 1 second.
5071* in_start_fresh (BOOLEAN): Whether to reset all Performance Schema data before tracing.
5072* in_auto_setup (BOOLEAN): Whether to disable all other threads and enable all consumers/instruments. This will also reset the settings at the end of the run.
5073* in_debug (BOOLEAN): Whether you would like to include file:lineno in the graph
5074
5075##### Example
5076```SQL
5077mysql> CALL sys.ps_trace_thread(25, CONCAT('/tmp/stack-', REPLACE(NOW(), ' ', '-'), '.dot'), NULL, NULL, TRUE, TRUE, TRUE);
5078+-------------------+
5079| summary |
5080+-------------------+
5081| Disabled 1 thread |
5082+-------------------+
50831 row in set (0.00 sec)
5084
5085+---------------------------------------------+
5086| Info |
5087+---------------------------------------------+
5088| Data collection starting for THREAD_ID = 25 |
5089+---------------------------------------------+
50901 row in set (0.03 sec)
5091
5092+-----------------------------------------------------------+
5093| Info |
5094+-----------------------------------------------------------+
5095| Stack trace written to /tmp/stack-2014-02-16-21:18:41.dot |
5096+-----------------------------------------------------------+
50971 row in set (60.07 sec)
5098
5099+-------------------------------------------------------------------+
5100| Convert to PDF |
5101+-------------------------------------------------------------------+
5102| dot -Tpdf -o /tmp/stack_25.pdf /tmp/stack-2014-02-16-21:18:41.dot |
5103+-------------------------------------------------------------------+
51041 row in set (60.07 sec)
5105
5106+-------------------------------------------------------------------+
5107| Convert to PNG |
5108+-------------------------------------------------------------------+
5109| dot -Tpng -o /tmp/stack_25.png /tmp/stack-2014-02-16-21:18:41.dot |
5110+-------------------------------------------------------------------+
51111 row in set (60.07 sec)
5112
5113+------------------+
5114| summary |
5115+------------------+
5116| Enabled 1 thread |
5117+------------------+
51181 row in set (60.32 sec)
5119```
5120
5121#### ps_truncate_all_tables
5122
5123##### Description
5124
5125Truncates all summary tables within Performance Schema, resetting all aggregated instrumentation as a snapshot.
5126
5127##### Parameters
5128
5129* in_verbose (BOOLEAN): Whether to print each TRUNCATE statement before running
5130
5131##### Example
5132```SQL
5133mysql> CALL sys.ps_truncate_all_tables(false);
5134+---------------------+
5135| summary |
5136+---------------------+
5137| Truncated 44 tables |
5138+---------------------+
51391 row in set (0.10 sec)
5140```
5141
5142#### statement_performance_analyzer
5143
5144##### Description
5145Create a report of the statements running on the server.
5146
5147The views are calculated based on the overall and/or delta activity.
5148
5149Requires the SUPER privilege for "SET sql_log_bin = 0;".
5150
5151The following configuration options are supported:
5152
5153 * sys.statement_performance_analyzer.limit
5154 The maximum number of rows to include for the views that does not have a built-in limit (e.g. the 95th percentile view).
5155 If not set the limit is 100.
5156
5157 * sys.statement_performance_analyzer.view
5158 Used together with the 'custom' view. If the value contains a space, it is considered a query, otherwise it must be
5159 an existing view querying the performance_schema.events_statements_summary_by_digest table. There cannot be any limit
5160 clause including in the query or view definition if @sys.statement_performance_analyzer.limit > 0.
5161 If specifying a view, use the same format as for in_table.
5162
5163 * sys.debug
5164 Whether to provide debugging output.
5165 Default is 'OFF'. Set to 'ON' to include.
5166
5167##### Parameters
5168
5169* in_action (ENUM('snapshot', 'overall', 'delta', 'create_tmp', 'create_table', 'save', 'cleanup')):
5170 The action to take. Supported actions are:
5171 - snapshot Store a snapshot. The default is to make a snapshot of the current content of
5172 performance_schema.events_statements_summary_by_digest, but by setting in_table
5173 this can be overwritten to copy the content of the specified table.
5174 The snapshot is stored in the sys.tmp_digests temporary table.
5175 - overall Generate analyzis based on the content specified by in_table. For the overall analyzis,
5176 in_table can be NOW() to use a fresh snapshot. This will overwrite an existing snapshot.
5177 Use NULL for in_table to use the existing snapshot. If in_table IS NULL and no snapshot
5178 exists, a new will be created.
5179 See also in_views and @sys.statement_performance_analyzer.limit.
5180 - delta Generate a delta analysis. The delta will be calculated between the reference table in
5181 in_table and the snapshot. An existing snapshot must exist.
5182 The action uses the sys.tmp_digests_delta temporary table.
5183 See also in_views and @sys.statement_performance_analyzer.limit.
5184 - create_table Create a regular table suitable for storing the snapshot for later use, e.g. for
5185 calculating deltas.
5186 - create_tmp Create a temporary table suitable for storing the snapshot for later use, e.g. for
5187 calculating deltas.
5188 - save Save the snapshot in the table specified by in_table. The table must exists and have
5189 the correct structure.
5190 If no snapshot exists, a new is created.
5191 - cleanup Remove the temporary tables used for the snapshot and delta.
5192
5193* in_table (VARCHAR(129)):
5194 The table argument used for some actions. Use the format 'db1.t1' or 't1' without using any backticks (`)
5195 for quoting. Periods (.) are not supported in the database and table names.
5196
5197 The meaning of the table for each action supporting the argument is:
5198
5199 - snapshot The snapshot is created based on the specified table. Set to NULL or NOW() to use
5200 the current content of performance_schema.events_statements_summary_by_digest.
5201 - overall The table with the content to create the overall analyzis for. The following values
5202 can be used:
5203 - A table name - use the content of that table.
5204 - NOW() - create a fresh snapshot and overwrite the existing snapshot.
5205 - NULL - use the last stored snapshot.
5206 - delta The table name is mandatory and specified the reference view to compare the currently
5207 stored snapshot against. If no snapshot exists, a new will be created.
5208 - create_table The name of the regular table to create.
5209 - create_tmp The name of the temporary table to create.
5210 - save The name of the table to save the currently stored snapshot into.
5211
5212* in_views (SET ('with_runtimes_in_95th_percentile', 'analysis', 'with_errors_or_warnings',
5213 'with_full_table_scans', 'with_sorting', 'with_temp_tables', 'custom'))
5214 Which views to include:
5215
5216 - with_runtimes_in_95th_percentile Based on the sys.statements_with_runtimes_in_95th_percentile view
5217 - analysis Based on the sys.statement_analysis view
5218 - with_errors_or_warnings Based on the sys.statements_with_errors_or_warnings view
5219 - with_full_table_scans Based on the sys.statements_with_full_table_scans view
5220 - with_sorting Based on the sys.statements_with_sorting view
5221 - with_temp_tables Based on the sys.statements_with_temp_tables view
5222 - custom Use a custom view. This view must be specified in @sys.statement_performance_analyzer.view to an existing view or a query
5223
5224 Default is to include all except 'custom'.
5225
5226##### Example
5227```SQL
5228-- To create a report with the queries in the 95th percentile since last truncate of performance_schema.events_statements_summary_by_digest and the delta for a 1 minute period:
5229--
5230-- 1. Create a temporary table to store the initial snapshot.
5231-- 2. Create the initial snapshot.
5232-- 3. Save the initial snapshot in the temporary table.
5233-- 4. Wait one minute.
5234-- 5. Create a new snapshot.
5235-- 6. Perform analyzis based on the new snapshot.
5236-- 7. Perform analyzis based on the delta between the initial and new snapshots.
5237
5238mysql> CALL sys.statement_performance_analyzer('create_tmp', 'mydb.tmp_digests_ini', NULL);
5239Query OK, 0 rows affected (0.08 sec)
5240
5241mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
5242Query OK, 0 rows affected (0.02 sec)
5243
5244mysql> CALL sys.statement_performance_analyzer('save', 'mydb.tmp_digests_ini', NULL);
5245Query OK, 0 rows affected (0.00 sec)
5246
5247mysql> DO SLEEP(60);
5248Query OK, 0 rows affected (1 min 0.00 sec)
5249
5250mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
5251Query OK, 0 rows affected (0.02 sec)
5252
5253mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile');
5254+-----------------------------------------+
5255| Next Output |
5256+-----------------------------------------+
5257| Queries with Runtime in 95th Percentile |
5258+-----------------------------------------+
52591 row in set (0.05 sec)
5260
5261...
5262
5263mysql> CALL sys.statement_performance_analyzer('delta', 'mydb.tmp_digests_ini', 'with_runtimes_in_95th_percentile');
5264+-----------------------------------------+
5265| Next Output |
5266+-----------------------------------------+
5267| Queries with Runtime in 95th Percentile |
5268+-----------------------------------------+
52691 row in set (0.03 sec)
5270
5271...
5272
5273
5274-- To create an overall report of the 95th percentile queries and the top 10 queries with full table scans:
5275
5276mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
5277Query OK, 0 rows affected (0.01 sec)
5278
5279mysql> SET @sys.statement_performance_analyzer.limit = 10;
5280Query OK, 0 rows affected (0.00 sec)
5281
5282mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile,with_full_table_scans');
5283+-----------------------------------------+
5284| Next Output |
5285+-----------------------------------------+
5286| Queries with Runtime in 95th Percentile |
5287+-----------------------------------------+
52881 row in set (0.01 sec)
5289
5290...
5291
5292+-------------------------------------+
5293| Next Output |
5294+-------------------------------------+
5295| Top 10 Queries with Full Table Scan |
5296+-------------------------------------+
52971 row in set (0.09 sec)
5298
5299...
5300
5301
5302-- Use a custom view showing the top 10 query sorted by total execution time refreshing the view every minute using
5303-- the watch command in Linux.
5304
5305mysql> CREATE OR REPLACE VIEW mydb.my_statements AS
5306 -> SELECT sys.format_statement(DIGEST_TEXT) AS query,
5307 -> SCHEMA_NAME AS db,
5308 -> COUNT_STAR AS exec_count,
5309 -> sys.format_time(SUM_TIMER_WAIT) AS total_latency,
5310 -> sys.format_time(AVG_TIMER_WAIT) AS avg_latency,
5311 -> ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
5312 -> ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
5313 -> ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
5314 -> DIGEST AS digest
5315 -> FROM performance_schema.events_statements_summary_by_digest
5316 -> ORDER BY SUM_TIMER_WAIT DESC;
5317Query OK, 0 rows affected (0.01 sec)
5318
5319mysql> CALL sys.statement_performance_analyzer('create_table', 'mydb.digests_prev', NULL);
5320Query OK, 0 rows affected (0.10 sec)
5321
5322shell$ watch -n 60 "mysql sys --table -e \"
5323> SET @sys.statement_performance_analyzer.view = 'mydb.my_statements';
5324> SET @sys.statement_performance_analyzer.limit = 10;
5325> CALL statement_performance_analyzer('snapshot', NULL, NULL);
5326> CALL statement_performance_analyzer('delta', 'mydb.digests_prev', 'custom');
5327> CALL statement_performance_analyzer('save', 'mydb.digests_prev', NULL);
5328> \""
5329
5330Every 60.0s: mysql sys --table -e " ... Mon Dec 22 10:58:51 2014
5331
5332+----------------------------------+
5333| Next Output |
5334+----------------------------------+
5335| Top 10 Queries Using Custom View |
5336+----------------------------------+
5337+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
5338| query | db | exec_count | total_latency | avg_latency | rows_sent_avg | rows_examined_avg | rows_affected_avg | digest |
5339+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
5340...
5341```
5342
5343#### table_exists
5344
5345##### Description
5346Tests whether the table specified in in_db and in_table exists either as a regular
5347table, or as a temporary table. The returned value corresponds to the table that
5348will be used, so if there's both a temporary and a permanent table with the given
5349name, then 'TEMPORARY' will be returned.
5350
5351##### Parameters
5352
5353* in_db (VARCHAR(64)): The database name to check for the existance of the table in.
5354
5355* in_table (VARCHAR(64)): The name of the table to check the existance of.
5356
5357* out_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY'): The return value: whether the table exists. The value is one of:
5358 - '' - the table does not exist neither as a base table, view, nor temporary table.
5359 - 'BASE TABLE' - the table name exists as a permanent base table table.
5360 - 'VIEW' - the table name exists as a view.
5361 - 'TEMPORARY' - the table name exists as a temporary table.
5362
5363##### Example
5364```SQL
5365mysql> CREATE DATABASE db1;
5366Query OK, 1 row affected (0.07 sec)
5367
5368mysql> use db1;
5369Database changed
5370mysql> CREATE TABLE t1 (id INT PRIMARY KEY);
5371Query OK, 0 rows affected (0.08 sec)
5372
5373mysql> CREATE TABLE t2 (id INT PRIMARY KEY);
5374Query OK, 0 rows affected (0.08 sec)
5375
5376mysql> CREATE view v_t1 AS SELECT * FROM t1;
5377Query OK, 0 rows affected (0.00 sec)
5378
5379mysql> CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
5380Query OK, 0 rows affected (0.00 sec)
5381
5382mysql> CALL sys.table_exists('db1', 't1', @exists); SELECT @exists;
5383Query OK, 0 rows affected (0.00 sec)
5384
5385+------------+
5386| @exists |
5387+------------+
5388| TEMPORARY |
5389+------------+
53901 row in set (0.00 sec)
5391
5392mysql> CALL sys.table_exists('db1', 't2', @exists); SELECT @exists;
5393Query OK, 0 rows affected (0.00 sec)
5394
5395+------------+
5396| @exists |
5397+------------+
5398| BASE TABLE |
5399+------------+
54001 row in set (0.01 sec)
5401
5402mysql> CALL sys.table_exists('db1', 'v_t1', @exists); SELECT @exists;
5403Query OK, 0 rows affected (0.00 sec)
5404
5405+---------+
5406| @exists |
5407+---------+
5408| VIEW |
5409+---------+
54101 row in set (0.00 sec)
5411
5412mysql> CALL sys.table_exists('db1', 't3', @exists); SELECT @exists;
5413Query OK, 0 rows affected (0.01 sec)
5414
5415+---------+
5416| @exists |
5417+---------+
5418| |
5419+---------+
54201 row in set (0.00 sec)
5421```
5422
