Description:
The enclosed test script creates a table and inserts 16 million rows.
The script was executed on 8.0.19 and 8.0.21.
8.0.21 execution times are more than double.
The following is the execution results from MySQL Workbench for the last 4 inserts.
8.0.19:
Records: 1048576 Duplicates: 0 Warnings: 0 3.859 sec
Records: 2097152 Duplicates: 0 Warnings: 0 8.531 sec
Records: 4194304 Duplicates: 0 Warnings: 0 15.750 sec
Records: 8388608 Duplicates: 0 Warnings: 0 31.328 sec
8.0.20:
Records: 1048576 Duplicates: 0 Warnings: 0 8.015 sec
Records: 2097152 Duplicates: 0 Warnings: 0 17.421 sec
Records: 4194304 Duplicates: 0 Warnings: 0 34.422 sec
Records: 8388608 Duplicates: 0 Warnings: 0 68.625 sec
How to repeat:
===== Script start =====
CREATE TABLE `test` (
`Key1` int NOT NULL AUTO_INCREMENT,
`Key2` date NOT NULL,
`Key3` varchar(20) NOT NULL,
`Value` float DEFAULT NULL,
PRIMARY KEY (`Key1`,`Key2`,`Key3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) VALUES ('2000-01-01', 'key3', '1.1');
-- Generate 16777216 rows
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test;
===== Full execution results for 8.0.19 =====
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) VALUES ('2000-01-01', 'key3', '1.1') 1 row(s) affected 0.015 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 1 row(s) affected Records: 1 Duplicates: 0 Warnings: 0 0.000 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 2 row(s) affected Records: 2 Duplicates: 0 Warnings: 0 0.000 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 4 row(s) affected Records: 4 Duplicates: 0 Warnings: 0 0.000 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 8 row(s) affected Records: 8 Duplicates: 0 Warnings: 0 0.000 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 16 row(s) affected Records: 16 Duplicates: 0 Warnings: 0 0.015 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 32 row(s) affected Records: 32 Duplicates: 0 Warnings: 0 0.000 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 64 row(s) affected Records: 64 Duplicates: 0 Warnings: 0 0.000 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 128 row(s) affected Records: 128 Duplicates: 0 Warnings: 0 0.000 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 256 row(s) affected Records: 256 Duplicates: 0 Warnings: 0 0.000 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 512 row(s) affected Records: 512 Duplicates: 0 Warnings: 0 0.016 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 1024 row(s) affected Records: 1024 Duplicates: 0 Warnings: 0 0.016 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 2048 row(s) affected Records: 2048 Duplicates: 0 Warnings: 0 0.016 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 4096 row(s) affected Records: 4096 Duplicates: 0 Warnings: 0 0.031 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 8192 row(s) affected Records: 8192 Duplicates: 0 Warnings: 0 0.093 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 16384 row(s) affected Records: 16384 Duplicates: 0 Warnings: 0 0.062 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 32768 row(s) affected Records: 32768 Duplicates: 0 Warnings: 0 0.125 sec
09:52:44 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 65536 row(s) affected Records: 65536 Duplicates: 0 Warnings: 0 0.250 sec
09:52:45 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 131072 row(s) affected Records: 131072 Duplicates: 0 Warnings: 0 0.453 sec
09:52:45 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 262144 row(s) affected Records: 262144 Duplicates: 0 Warnings: 0 0.938 sec
09:52:46 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 524288 row(s) affected Records: 524288 Duplicates: 0 Warnings: 0 1.860 sec
09:52:48 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 1048576 row(s) affected Records: 1048576 Duplicates: 0 Warnings: 0 3.859 sec
09:52:52 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 2097152 row(s) affected Records: 2097152 Duplicates: 0 Warnings: 0 8.531 sec
09:53:00 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 4194304 row(s) affected Records: 4194304 Duplicates: 0 Warnings: 0 15.750 sec
09:53:16 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 8388608 row(s) affected Records: 8388608 Duplicates: 0 Warnings: 0 31.328 sec
===== Full execution results for 8.0.21 =====
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) VALUES ('2000-01-01', 'key3', '1.1') 1 row(s) affected 0.016 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 1 row(s) affected Records: 1 Duplicates: 0 Warnings: 0 0.000 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 2 row(s) affected Records: 2 Duplicates: 0 Warnings: 0 0.000 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 4 row(s) affected Records: 4 Duplicates: 0 Warnings: 0 0.015 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 8 row(s) affected Records: 8 Duplicates: 0 Warnings: 0 0.000 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 16 row(s) affected Records: 16 Duplicates: 0 Warnings: 0 0.000 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 32 row(s) affected Records: 32 Duplicates: 0 Warnings: 0 0.000 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 64 row(s) affected Records: 64 Duplicates: 0 Warnings: 0 0.016 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 128 row(s) affected Records: 128 Duplicates: 0 Warnings: 0 0.000 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 256 row(s) affected Records: 256 Duplicates: 0 Warnings: 0 0.000 sec
09:57:12 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 512 row(s) affected Records: 512 Duplicates: 0 Warnings: 0 0.000 sec
09:57:13 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 1024 row(s) affected Records: 1024 Duplicates: 0 Warnings: 0 0.016 sec
09:57:13 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 2048 row(s) affected Records: 2048 Duplicates: 0 Warnings: 0 0.032 sec
09:57:13 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 4096 row(s) affected Records: 4096 Duplicates: 0 Warnings: 0 0.031 sec
09:57:13 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 8192 row(s) affected Records: 8192 Duplicates: 0 Warnings: 0 0.094 sec
09:57:13 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 16384 row(s) affected Records: 16384 Duplicates: 0 Warnings: 0 0.062 sec
09:57:13 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 32768 row(s) affected Records: 32768 Duplicates: 0 Warnings: 0 0.172 sec
09:57:13 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 65536 row(s) affected Records: 65536 Duplicates: 0 Warnings: 0 0.282 sec
09:57:13 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 131072 row(s) affected Records: 131072 Duplicates: 0 Warnings: 0 0.547 sec
09:57:14 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 262144 row(s) affected Records: 262144 Duplicates: 0 Warnings: 0 1.047 sec
09:57:15 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 524288 row(s) affected Records: 524288 Duplicates: 0 Warnings: 0 2.281 sec
09:57:17 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 1048576 row(s) affected Records: 1048576 Duplicates: 0 Warnings: 0 8.015 sec
09:57:25 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 2097152 row(s) affected Records: 2097152 Duplicates: 0 Warnings: 0 17.421 sec
09:57:43 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 4194304 row(s) affected Records: 4194304 Duplicates: 0 Warnings: 0 34.422 sec
09:58:17 INSERT INTO `test` (`Key2`, `Key3`, `Value`) select Key2, Key3, Value FROM test 8388608 row(s) affected Records: 8388608 Duplicates: 0 Warnings: 0 68.625 sec