Description:
Internal Temp Tables used under heavy load cause the system to hang.
When there is lots of load on the system many queries using internal temp tables start taking an exceptionally long time.
Other queries continue to perform normally.
Queries using internal temp tables in a different database on the same instance of MySQL also start taking an exceptionally long time.
Queries using internal temp tables on a different instance of MySQL (using a different port) perform quickly at the same time (in under 1 second).
These tests are simulating exactly what we are seeing in the client’s production system every time there is unrecoverable slowness.
We have tested these in a few different Windows environments (both VM and bare metal).
We get the same results for each of them using MySQL 5.7.32 to MySQL 5.7.34.
We have also tested using MySQL 8.0.23, and get similar results.
First priority is to get this fixed for MySQL 5.7.
Later we will also need it fixed for MySQL 8.0.
The dependent query: SELECT * FROM t1 LIMIT 100000; always runs quickly.
The internal temp table query is the simplest one we could think of to demonstrate the issue: SELECT * FROM (SELECT * FROM t1 LIMIT 100000) t;
We will also attach some analysis demonstrating this.
It seems that MySQL is doing something very strange with these queries using internal temp tables.
Please get this fixed ASAP, as we don't have a workaround.
How to repeat:
1) Create the database:
DROP DATABASE IF EXISTS defect;
CREATE DATABASE defect;
USE defect;
CREATE TABLE `t1` (
`a` INT NOT NULL AUTO_INCREMENT,
`b` INT NOT NULL,
`c` INT NOT NULL,
`d` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`e` INT DEFAULT NULL,
`f` VARCHAR(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`g` BIT DEFAULT NULL,
`h` INT NOT NULL,
`i` INT NOT NULL,
`j` INT DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT;
INSERT INTO defect.t1 VALUES( (NULL),(a),(a),(now()),(a),(repeat(md5(a),5)),(NULL),(a),(a),(NULL));
INSERT INTO defect.t1 VALUES( (NULL),(a),(a),(now()),(a),(repeat(md5(a),5)),(NULL),(a),(a),(NULL));
INSERT INTO defect.t1 VALUES( (NULL),(a),(a),(now()),(a),(repeat(md5(a),5)),(NULL),(a),(a),(NULL));
INSERT INTO defect.t1 VALUES( (NULL),(a),(a),(now()),(a),(repeat(md5(a),5)),(NULL),(a),(a),(NULL));
INSERT INTO t1 (b,c,f,h,i ) SELECT t2.b,t2.c,t2.f,t2.h,t2.i FROM t1 AS t2,t1 AS t3,t1 AS t4,t1 AS t5,t1 AS t6;
INSERT INTO t1 (b,c,f,h,i ) SELECT t2.b,t2.c,t2.f,t2.h,t2.i FROM t1 AS t2,t1 AS t3;
UPDATE t1 SET b=a,c=a,e=a,f=repeat(md5(a),5),h=a,i=a,j=a;
2) Use mysqlslap to generate load:
mysqlslap.exe --verbose -utester -p --host=localhost --port=3306 --concurrency=100 --iterations=500 --create-schema=defect --query="SET transaction_isolation = 'READ-UNCOMMITTED';SET tmp_table_size = 1024 * 1024 * 1024;SET max_heap_table_size = 1024 * 1024 * 1024;SELECT * FROM (SELECT * FROM t1 LIMIT 100000) t;"
3) From a MySQL client call:
SET transaction_isolation = 'READ-UNCOMMITTED';
SET tmp_table_size = 1024 * 1024 * 1024;
SET max_heap_table_size = 1024 * 1024 * 1024;
SELECT * FROM (SELECT * FROM t1 LIMIT 100000) t;
This takes more than 1 minute on all of the tested systems.
It should be taking under 1 second.
When the client has 1,000s of queries coming in at the same time using internal temp tables, it brings the entire system to its knees.
