+---------------------------+--------------------+------------+--------------+-------------+-----------------------+------------------+-----------+-----------------+
|
| event_name | source | timer_wait | object_name | object_type | object_instance_begin | nesting_event_id | operation | number_of_bytes |
|
+---------------------------+--------------------+------------+--------------+-------------+-----------------------+------------------+-----------+-----------------+
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 36268436 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 26239308 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25965756 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24482904 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25177904 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 26441136 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25628820 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24544620 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25093392 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 26585696 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24908800 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24778696 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25735016 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 26504520 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25397524 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24916028 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25496492 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 27547576 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24461776 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 23730636 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25158444 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 27067192 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25512616 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24772024 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 29405172 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 27908420 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25396968 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 28189200 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25007212 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
|
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 6350632 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 2304 |
|
| wait/io/file/sql/io_cache | uniques.cc:124 | 225208912 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | read | 2097156 |
|
| wait/io/file/sql/io_cache | uniques.cc:124 | 200659288 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | read | 1902844 |
|
| wait/io/file/sql/io_cache | mf_cache.cc:93 | 398505772 | /tmp/MYfd=45 | FILE | 140737121288512 | 1535991 | close | NULL |
|
+---------------------------+--------------------+------------+--------------+-------------+-----------------------+------------------+-----------+-----------------+
|
just an observation: unlike in MariaDB, in MySQL performance schema instruments temporary file operations, so one can monitor temp file usage.
Using a {{SELECT COUNT(DISTINCT) }} query as an example:
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
select
event_name, source, timer_wait, object_name, object_type,
object_instance_begin, nesting_event_id, operation, number_of_bytes
from
events_waits_history_long
where
event_name='wait/io/file/sql/io_cache';
+---------------------------+--------------------+------------+--------------+-------------+-----------------------+------------------+-----------+-----------------+
| event_name | source | timer_wait | object_name | object_type | object_instance_begin | nesting_event_id | operation | number_of_bytes |
+---------------------------+--------------------+------------+--------------+-------------+-----------------------+------------------+-----------+-----------------+
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 36268436 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 26239308 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25965756 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24482904 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25177904 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 26441136 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25628820 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24544620 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25093392 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 26585696 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24908800 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24778696 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25735016 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 26504520 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25397524 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24916028 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25496492 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 27547576 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24461776 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 23730636 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25158444 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 27067192 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25512616 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 24772024 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 29405172 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 27908420 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25396968 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 28189200 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 25007212 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 65536 |
| wait/io/file/sql/io_cache | mf_iocache.cc:1690 | 6350632 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | write | 2304 |
| wait/io/file/sql/io_cache | uniques.cc:124 | 225208912 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | read | 2097156 |
| wait/io/file/sql/io_cache | uniques.cc:124 | 200659288 | /tmp/MYfd=45 | FILE | 140737121288512 | 1225845 | read | 1902844 |
| wait/io/file/sql/io_cache | mf_cache.cc:93 | 398505772 | /tmp/MYfd=45 | FILE | 140737121288512 | 1535991 | close | NULL |
+---------------------------+--------------------+------------+--------------+-------------+-----------------------+------------------+-----------+-----------------+