MySQL Bugs: #98741: Created_tmp_disk_tables not incrementing for TempTable storage engine

Bug #98741	Created_tmp_disk_tables not incrementing for TempTable storage engine
Submitted:	25 Feb 2020 20:11	Modified:	30 May 2020 8:06
Reporter:	Marcelo Altmann (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If your temporary tables use TempTable as the storage engine and you exceed temptable_max_ram.

mysql> show global status like 'Created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6692  |
| Created_tmp_tables      | 10    |
+-------------------------+-------+
3 rows in set (2.71 sec)

Each MMAP allocation is counting against Created_tmp_files, however this includes any temporary file, not only the ones from TempTable. Looking into  Created_tmp_disk_tables one may think that all temporary tables are kept in memory.

How to repeat:
create database test;
use test;
set global temptable_max_ram = 12 * 1024 * 1024;

CREATE TABLE `joinit` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
CREATE TABLE `joinit2` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
INSERT INTO joinit VALUES (NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )));
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit2 SELECT * FROM joinit;

show global status like 'Created_tmp%';
select * from joinit union select * from joinit2;
show global status like 'Created_tmp%';

Suggested fix:
Many monitoring tools rely on Created_tmp_disk_tables as a measure for disk tempory tables. It will be really nice if TempTable engine will follow MEMORY engine and increment this counter when a chuck was MMAP'ed due to temptable_max_ram been exceeded.

Thank you for the bug report.

Copying internal note.

*** DEBANERJ  debarun.banerjee Thu Apr 30 2020 07:53:30 GMT+0530 (IST)***
Here is my opinion on this bug.

"Created_tmp_disk_tables": Counts the number of internal on-disk temp tables
created e.g temp table created in Innodb Storage Engine (disk based SE).

When temptable_use_mmap=OFF,

1. A temp table is first created in "TempTable" SE.

2. Once the memory usage exceeds "tmp_table_size", another table is created
in "Innodb" SE and all in-memory data is moved to it.
"Created_tmp_disk_tables" counter is incremented at this point. Once a temp
table is moved to "Innodb" SE, it is never moved back to "TempTable" SE even
if the table size is reduced later.

This mechanism is also similar in earlier versions where MEMORY SE was the
default for in-memory temp table.

When temptable_use_mmap=ON (default), there is a difference in step[2]. When
"tmp_table_size" exceeds, "TempTable" SE starts creating memory-mapped files
for the overflow data. If the table size is reduced later, then it becomes a
complete in-memory table again.

So, in contrast with the other model the state of the table changes from
memory <-> disk within "TempTable" SE. IMHO, it is going to be confusing to
use the same counter "Created_tmp_disk_tables" here. A point to note here is
the MEMORY(HEAP) SE never had this capability.

What is the way to track the disk usage by these temp tables then ? It can be
done today by Performance Schema instruments "memory/temptable/physical_ram"
and "memory/temptable/physical_disk".

e.g.
SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE
event_name = 'memory/temptable/physical_disk';

If this is not enough, then please suggest if any specific new counters could
help ? Perhaps, something like counting the number of temp tables that have
ever used memory-mapped files. In that case I would suggest to file an
enhancement request as it would need new variable(s).

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".