Expose tmp table size in PERFORMANCE_SCHEMA
Bug #1737949 reported by
Sveta Smirnova
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Invalid
|
Undecided
|
Unassigned | |||
5.6 |
Triaged
|
Wishlist
|
Unassigned | |||
5.7 |
Triaged
|
Wishlist
|
Unassigned | |||
8.0 |
Triaged
|
Wishlist
|
Unassigned |
Bug Description
Please implement https:/
To post a comment you must log in.
Copying upstream report content.
Description: SCHEMA' s events_statements_* tables include columns CREATED_ TMP_DISK_ TABLES and CREATED_TMP_TABLES.
The PERFORMANCE_
This is great, but I'd like to request another column, for the size of the tmp table(s) created during a statement event.
This would be useful in the scenario when we know a tmp table is too large to fit in tmp_table_size, and has to go to disk. But we wonder how large would we have to increase tmp_table_size to fit the whole table in RAM and avoid going to disk? Currently we can only do this by experimentation. If the tmp table is an outlier that is really huge, this is repetitive and time-consuming.
A specific scenario when I used the tmp table size (it's included in the enhanced slow query log in Percona Server): a server was issuing intermittent disk full errors. Through the slow query log's information about tmp table size, I found that some queries were writing 1.5GB tmp tables to disk. The tmpdir only had 6GB free, so it only took four concurrent queries of this type to exhaust disk space. I might have guessed this was the cause, and I might have observed it happening if I were both quick and lucky, but having the info in the slow query log made it much easier to prove.
In cases when a query creates more than one tmp table, would it be better for the column should be the sum total of tmp table size, or the size of the largest individual tmp table? Of course, this is properly a job for a subordinate table, but in practice it would be useful enough simply to show the total size used by all tmp table(s) created by the statement.
How to repeat: schema) > select created_tmp_tables, created_ tmp_tables_ size from events_ statements_ current;
mysql [localhost] {msandbox} (performance_
ERROR 1054 (42S22): Unknown column 'created_ tmp_tables_ size' in 'field list'