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: | |
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
[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