MySQL Bugs: #98739: TempTable storage engine slow if mmap is required

Bug #98739	TempTable storage engine slow if mmap is required
Submitted:	25 Feb 2020 18:27	Modified:	11 Jun 2020 17:21
Reporter:	Marcelo Altmann (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Storage Engines	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If TempTable exceeds the temptable_max_ram and switches back to mmap algorithm, performance degrades a lot.

Query: select * from joinit union select * from joinit2;
Table size:

[root@marcelo-altmann-tmptable-standalone-1 test]# ls -lah
total 21M
drwxr-x---. 2 mysql mysql 4.0K Feb 25 18:18 .
drwxr-x--x. 7 mysql mysql 4.0K Feb 25 18:17 ..
-rw-r-----. 1 mysql mysql  10M Feb 25 18:18 joinit.ibd
-rw-r-----. 1 mysql mysql  10M Feb 25 18:18 joinit2.ibd

MMAP:
[root@marcelo-altmann-tmptable-standalone-1 ~]# time mysql -psekret -e "select * from joinit union select * from joinit2;" test > /dev/null
mysql: [Warning] Using a password on the command line interface can be insecure.

real	4m5.142s
user	0m0.060s
sys	0m0.008s

InnoDB:
 mysql -psekret -e "SET GLOBAL temptable_use_mmap = OFF;"
[root@marcelo-altmann-tmptable-standalone-1 ~]# time mysql -psekret -e "select * from joinit union select * from joinit2;" test > /dev/null
mysql: [Warning] Using a password on the command line interface can be insecure.

real	0m0.252s
user	0m0.062s
sys	0m0.005s

4 minutes to manipulate ˜30Mb of data seems a lot, especially that the default fallback algorithm is to use MMAP. 

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;

MMAP:
SET GLOBAL temptable_use_mmap = ON;
select * from joinit union select * from joinit2;

InnoDB:
SET GLOBAL temptable_use_mmap = OFF;
select * from joinit union select * from joinit2;

Thank you for the bug report.

Just did some further testing and something seems a bit broken with TempTable SE:

For the above reproducible test case, I ran it with strace attached to it:

Command:
strace -f -s8192 -ttt -p $(pidof mysqld) -e trace=recvfrom,open,mmap,close -o/tmp/temptable.trace

for each allocation, mysql does creates the file with:

open("/var/lib/mytmp/", O_RDWR|O_DIRECTORY|O_CLOEXEC|O_TMPFILE, 0600) = 51
lseek(51, 0, SEEK_END) = 0

Then it writes \n to it (https://github.com/mysql/mysql-server/blob/mysql-8.0.19/storage/temptable/include/temptabl...) in 4k chunk each time , until it reaches 8M
 write(51, "\n\n\n\n\n\....

Then i mmaps it and close
mmap(NULL, 8388608, PROT_READ|PROT_WRITE, MAP_SHARED, 51, 0) = 0x7f58f822b000
close(51)       = 0

The above process was repeated  26036 for the union select. 
Querying performance_schema.memory_summary_global_by_event_name it reports that more than 203G was been allocated in disk, which goes align with the 26036 * 8M mmap calls.

Fixed as of the upcoming 8.0.21 release, and here's the proposed changelog entry from the documentation team:

Use of memory-mapped files after exceeding the temptable_max_ram
threshold caused a performance degradation.