Expose tmp table size in PERFORMANCE_SCHEMA

[Sveta Smirnova]

Please implement https://bugs.mysql.com/bug.php?id=74484

[Sveta Smirnova (svetasmirnova)]

Copying upstream report content.

Description:
The PERFORMANCE_SCHEMA's events_statements_* tables include columns CREATED_TMP_DISK_TABLES and CREATED_TMP_TABLES.

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:
mysql [localhost] {msandbox} (performance_schema) > select created_tmp_tables, created_tmp_tables_size from events_statements_current;

ERROR 1054 (42S22): Unknown column 'created_tmp_tables_size' in 'field list'

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2513