I'm seeing a large number of internal temporary disk tables being written. I can see the count with SHOW GLOBAL STATUS where Variable_name like 'Created_tmp_disk_tables'.
I know I can update max_heap_table_size and tmp_table_size to help prevent this, but without knowing the size of the tables getting written to disk, it's difficult to know what values to use.
Does anyone know how to go about finding this value?
This is not easy to get. In Percona Server, there are options to add additional information in the slow query log that shows the size of temp tables (see https://www.percona.com/doc/percona-server/5.7/diagnostics/slow_extended.html)
# User@Host: mailboxer[mailboxer] @ [192.168.10.165]
# Thread_id: 11167745 Schema: board
# Query_time: 1.009400 Lock_time: 0.000190 Rows_sent: 4 Rows_examined: 1543719 Rows_affected: 0 Rows_read: 4
# Bytes_sent: 278 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
(The example above, taken from the Percona documentation, shows the extended fields, although the example is for a query that did not create temp tables, so the size is shown as 0.)
In Oracle MySQL, some of the same extended information is available in query events in the PERFORMANCE_SCHEMA—but not the temp table sizes.
In 2014, I logged a feature request to supply this information: https://bugs.mysql.com/bug.php?id=74484 and this bug has been acknowledged, but this has not been implemented as far as I know.
It's a little bit unclear how this would be implemented, since it's possible for any given query to create multiple temp tables of different sizes. I believe the Percona feature shows the sum total of the temp table sizes in such cases.
All I can offer as a suggestion is to increase the max_heap_table_size and tmp_table_size in increments, and monitor the rate of increase of the Created_tmp_disk_tables reported by SHOW GLOBAL STATUS, compared to Created_tmp_tables (temp tables that did not use disk). As the allowed tmp table size is able to hold a greater percentage of temp tables created, you should start to see the ratio of on-disk temp tables to in-memory temp tables decrease.
It's typically not necessary to increase tmp_table_size to hold every possible temp table, no matter how large. You want the largest outliers to use the disk. But as long as the temp tables use memory 98% of the time, you should be fine. That would mean that the ratio of Created_tmp_disk_tables to Created_tmp_tables should be 1:50 or more.