Bug #103938 TempTable use more space when overflow to InnoDB on-disk interna temporary table
Submitted: 8 Jun 2021 8:02 Modified: 11 Jun 2021 6:43
Reporter: Lei Zeng Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Storage Engines Severity:S3 (Non-critical)
Version:8.0 OS:Linux
Assigned to: CPU Architecture:x86
Tags: temptable

[8 Jun 2021 8:02] Lei Zeng
Description:
When TempTable engine overflows in-memory internal temporary table to disk, it uses much more space if it overflows to InnoDB on disk internal temporary table than to memory-mapped temporary files. This is behavior change in space allocation, and at the same time, has impact on query performance.

This can be noticed by enable/disable parameter temptable_use_mmap. Starting from 8.0.23, parameter temptable_max_mmap is introduced, making this behavior change and query performance impact more visible. 

The following is a comparison of a GROUP BY query space usage and latency between 8.0.23 and 8.0.21

mysql>  select @@version, @@internal_tmp_mem_storage_engine, @@temptable_max_ram, @@temptable_use_mmap, @@temptable_max_mmap ;
+-----------+-----------------------------------+---------------------+----------------------+----------------------+
| @@version | @@internal_tmp_mem_storage_engine | @@temptable_max_ram | @@temptable_use_mmap | @@temptable_max_mmap |
+-----------+-----------------------------------+---------------------+----------------------+----------------------+
| 8.0.23    | TempTable                         |          1073741824 |                    1 |           1073741824 |
+-----------+-----------------------------------+---------------------+----------------------+----------------------+

mysql> SELECT * FROM sys.statements_with_temp_tables WHERE query like '%sbtest1%'\G
*************************** 1. row ***************************
                   query: SELECT `pad` , COUNT ( * ) FROM `sbtest1` GROUP BY `pad`
                      db: db1
              exec_count: 1
           total_latency: 4.85 min
       memory_tmp_tables: 1
         disk_tmp_tables: 1
avg_tmp_tables_per_query: 1
  tmp_tables_to_disk_pct: 100

mysql> select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ;
+----+------------+----------------------------+------------+----------+-----------+
| ID | SPACE      | PATH                       | SIZE       | STATE    | PURPOSE   |
+----+------------+----------------------------+------------+----------+-----------+
| 11 | 4243767290 | ./#innodb_temp/temp_10.ibt |      81920 | ACTIVE   | INTRINSIC |
| 12 | 4243767289 | ./#innodb_temp/temp_9.ibt  | 4714397696 | ACTIVE   | INTRINSIC | 

mysql> select THREAD_ID, event_name, SUM_NUMBER_OF_BYTES_ALLOC  from performance_schema.memory_summary_by_thread_by_event_name  where thread_id=(SELECT THREAD_ID FROM performance_schema.threads where PROCESSLIST_ID = 12) order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10; 
+-----------+---------------------------------------+---------------------------+
| THREAD_ID | event_name                            | SUM_NUMBER_OF_BYTES_ALLOC |
+-----------+---------------------------------------+---------------------------+
|        51 | memory/temptable/physical_disk        |                1073741824 | 
|        51 | memory/innodb/memory                  |                 598807296 |
|        51 | memory/sql/thd::main_mem_root         |                   1290488 |
|        51 | memory/mysqld_openssl/openssl_malloc  |                    105929 |
|        51 | memory/innodb/btr0btr                 |                    105120 |
|        51 | memory/sql/Filesort_buffer::sort_keys |                     71454 |
|        51 | memory/sql/String::value              |                     35168 |
|        51 | memory/innodb/ha_innodb               |                     31264 |
|        51 | memory/sql/test_quick_select          |                     16416 |
|        51 | memory/sql/hash_join                  |                     16392 |
+-----------+---------------------------------------+---------------------------+

mysql> select @@version, @@internal_tmp_mem_storage_engine, @@temptable_max_ram, @@temptable_use_mmap;
+-----------+-----------------------------------+---------------------+----------------------+
| @@version | @@internal_tmp_mem_storage_engine | @@temptable_max_ram | @@temptable_use_mmap |
+-----------+-----------------------------------+---------------------+----------------------+
| 8.0.21    | TempTable                         |          1073741824 |                    1 |
+-----------+-----------------------------------+---------------------+----------------------+

mysql> SELECT * FROM sys.statements_with_temp_tables WHERE query like '%sbtest1%'\G
*************************** 1. row ***************************
                   query: SELECT `pad` , COUNT ( * ) FROM `sbtest1` GROUP BY `pad`
                      db: db1
              exec_count: 1
           total_latency: 28.24 s
       memory_tmp_tables: 1
         disk_tmp_tables: 0
avg_tmp_tables_per_query: 1
  tmp_tables_to_disk_pct: 0

mysql> select THREAD_ID, event_name, SUM_NUMBER_OF_BYTES_ALLOC  from performance_schema.memory_summary_by_thread_by_event_name  where thread_id=(SELECT THREAD_ID FROM performance_schema.threads where PROCESSLIST_ID = 13) order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;
+-----------+---------------------------------------+---------------------------+
| THREAD_ID | event_name                            | SUM_NUMBER_OF_BYTES_ALLOC |
+-----------+---------------------------------------+---------------------------+
|        53 | memory/temptable/physical_disk        |                2147483648 |
|        53 | memory/sql/thd::main_mem_root         |                   1159880 |
|        53 | memory/innodb/memory                  |                    312024 |
|        53 | memory/mysqld_openssl/openssl_malloc  |                    105434 |
|        53 | memory/sql/Filesort_buffer::sort_keys |                     65536 |
|        53 | memory/innodb/ha_innodb               |                     36552 |
|        53 | memory/sql/String::value              |                     35008 |
|        53 | memory/sql/hash_join                  |                     16392 |
|        53 | memory/innodb/row0sel                 |                      8680 |
|        53 | memory/sql/test_quick_select          |                      8208 |
+-----------+---------------------------------------+---------------------------+

no space usage in INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES

How to repeat:
1. create a sysbench table of size 2GB

sysbench /usr/local/share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-user=${username} --mysql-password=${password}  --mysql-db=db1 --mysql-host=${host}  --mysql-port=${port} --tables=1 --table-size=10000000 --forced-shutdown --rand-type=uniform --db-ps-mode=disable --threads=1 prepare

[root@ip-172-31-26-254 db1]# ls -lart sbtest1.*
-rw-r----- 1 mysql mysql 2436890624 Jun  8 06:32 sbtest1.ibd

2. run a GROUP BY query

select pad, count(*) from sbtest1 group by pad;
[10 Jun 2021 15:23] Jusufadis Bakamovic
Hi Lei,

What you're describing is intended and expected behavior. As you have already noticed, with 8.0.23 release new TempTable system variable has been introduced, namely temptable_max_mmap. temptable_max_mmap is there to define the upper limit of how much (virtual) memory through MMAP file-backed mechanism TempTable can consume. In the event that both temptable_max_ram and temptable_max_mmap thresholds are reached, optimizer will have to fallback to InnoDB tables and that, of course, can be more costly in terms of performance. Perhaps disk space too given the format.

Prior to 8.0.23, there was no such mechanism so it was possible for queries to consume as much as they would like to from the MMAP-ed virtual memory. In the context of more fine grained resource control this is not feasible because it makes it possible to end up hitting the hard limit (either virtual memory wastage or more likely running out of the disk partition space where MMAP file ends up being (defined by --tmpdir)). Most innocent consequence in such situation would be a failed query.

So, reason why you're observing is by design. To avoid such situations, users will need to tweak temptable_max_ram and temptable_max_mmap to fit their needs and workflows.

In this particular case, and from your report

+-----------+---------------------------------------+---------------------------+
| THREAD_ID | event_name                            | SUM_NUMBER_OF_BYTES_ALLOC |
+-----------+---------------------------------------+---------------------------+
|        53 | memory/temptable/physical_disk        |                2147483648 

I'd say that increasing temptable_max_mmap by roughly 1GiB should do the job and bring back the performance. To do it in more generic way, one could set the temptable_max_mmap to some_factor * tmpfs_partition_size.
[10 Jun 2021 22:34] Lei Zeng
My question is why InnoDB on-disk internal temporary table in MySQL 8.0 uses three times as much space as those in MySQL 5.7

The base table is ~2GB, the GROUP BY query consumes less than 1.4 GB space on 5.7, but need ~ 4.4 GB on 8.0. This is behavior change and unexpected.

8.0.23

mysql> select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ;
+----+------------+----------------------------+------------+----------+-----------+
| ID | SPACE      | PATH                       | SIZE       | STATE    | PURPOSE   |
+----+------------+----------------------------+------------+----------+-----------+
| 12 | 4243767289 | ./#innodb_temp/temp_9.ibt  | 4714397696 | ACTIVE   | INTRINSIC | 

5.7.34

mysql> select @@internal_tmp_disk_storage_engine;
+------------------------------------+
| @@internal_tmp_disk_storage_engine |
+------------------------------------+
| InnoDB                             |
+------------------------------------+

ls -lart sbtest1.*
-rw-r----- 1 mysql mysql       8632 Jun 10 22:08 sbtest1.frm
-rw-r----- 1 mysql mysql 2441084928 Jun 10 22:09 sbtest1.ibd

ls -lart ibtmp1
-rw-r-----  1 mysql mysql 1488977920 Jun 10 22:23 ibtmp1

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE AS InitialSizeBytes, TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE AS FreeSizeBytes, MAXIMUM_SIZE AS MaxSizeBytes FROM information_schema.FILES WHERE TABLESPACE_NAME = 'innodb_temporary';
+-----------+------------------+--------+------------------+----------------+---------------+--------------+
| FILE_NAME | TABLESPACE_NAME  | ENGINE | InitialSizeBytes | TotalSizeBytes | FreeSizeBytes | MaxSizeBytes |
+-----------+------------------+--------+------------------+----------------+---------------+--------------+
| ./ibtmp1  | innodb_temporary | InnoDB |         12582912 |     1488977920 |    1464860672 |         NULL |
+-----------+------------------+--------+------------------+----------------+---------------+--------------+
[11 Jun 2021 6:43] MySQL Verification Team
Thank you, Adi for the detailed explanation.
Since this is intended and expected behavior hence marking this as a !bg.

regards,
Umesh