Bug #100285 Large "insert ... select" takes twice as long
Submitted: 22 Jul 2020 8:14 Modified: 13 Aug 2020 14:52
Reporter: Andras Janurik Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:8.0.21 OS:Windows (Windows 10, Windows Server 2012 R2)
Assigned to: CPU Architecture:x86

[22 Jul 2020 8:14] Andras Janurik
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
[22 Jul 2020 10:22] Andras Janurik
In the mean time I tested it with version 8.0.20 as well and that is slow as well, same as 8.0.21. (I know I put 8.0.20 in my original report accidentally as well).

So 8.0.19 is fast execution, 8.0.20 and above is double execution time.
[22 Jul 2020 14:00] MySQL Verification Team
Hi Mr. Janurik,

Thank you for your bug report.

I was not able to repeat your findings.

I have ran each benchmark 3 (three) times for each of the releases, so the presented times are the medium values.

This is a result for 8.0.18:

real	18m16.905s
user	0m0.010s
sys	0m0.006s

This is a result for 8.0.20:

real	19m52.296s
user	0m0.010s
sys	0m0.005s

This is a result for 8.0.21:

real	20m02.713s
user	0m0.012s
sys	0m0.007s

I also took A VERY GOOD CARE that nothing ran when the benchmarks when running, including other queries, screensaver, e-mail and everything else.

I have used command line tools and not Workbench. 

If you manage to get similar differences without using Workbench, then please file a separate bug, but not as MySQL Server bug, but as a Workbench bug.

Can't repeat.
[22 Jul 2020 14:14] Andras Janurik
Hi,

Sorry to hear.

Do you think it is possible that between 8.0.19 and 8.0.20 some cache or buffer attributes changed so that my current my.ini settings are not optimal any more? Maybe it requires some my.ini changes to avoid the performance drop?

Can you please show me how you executed the script using the command line tools? Maybe I will try it your way as well.
[22 Jul 2020 14:22] MySQL Verification Team
Hi,

Yes, of course that it is possible.

For online tool, just run:

mysql -uUSER -p  schema /* Replace with your database name */ < ./path_to_your_script

That is all.
[23 Jul 2020 7:58] Andras Janurik
Hi,

I am still trying to figure out what is causing this in my config.
Do you think you can spot it in my config file if I enclose it?
Or can you maybe show me the config file you were using to test my script?

Thanks
[23 Jul 2020 12:36] MySQL Verification Team
Sorry, Mr. Janurik,

This is a forum for bugs with fully repeatable test cases.

It is not a forum for free support.
[7 Aug 2020 9:11] Andras Janurik
Hi,

I am still debugging this issue and no matter what config I use I cannot replicate your results.
Can you please give me a config file you used for your tests?

Also please let me highlight something from my original test results.
If you compare the following lines you can see that with 8.0.19 run times grow proportionally with the number of records.
With every row the number of records double and the run times double as well.

===== Results for 8.0.19 =====
Records: 262144  Run time:	0.938 sec
Records: 524288  Run time:	1.860 sec
Records: 1048576  Run time:	3.859 sec
Records: 2097152  Run time:	8.531 sec
Records: 4194304  Run time:	15.750 sec
Records: 8388608  Run time:	31.328 sec

But with 8.0.20 something happens at 1048576 records. Between 524288 and 1048576 records the run time is not double but 4 times higher.
Between 1048576 and 2097152 it is double again but of coarse it is double of the original run time now.

===== Results for 8.0.20 =====
Records: 262144  Run time:	1.047 sec
Records: 524288  Run time:	2.281 sec
Records: 1048576  Run time:	8.015 sec
Records: 2097152  Run time:	17.421 sec
Records: 4194304  Run time:	34.422 sec
Records: 8388608  Run time:	68.625 sec

Up to 524288 resords the run times are actually very similar between 8.0.19 and 8.0.20.
But starting at 1048576 records run times are double.
Do you maybe have an explanation for that?

Thanks
[7 Aug 2020 12:31] MySQL Verification Team
Hi Mr. Janurik,

Performance scaling depends very much on the hardware, OS, tuning of OS and tuning of MySQL.

Can't repeat.
[7 Aug 2020 15:18] Andras Janurik
Hi,

Yes, you are right, but in this case the hardware, OS, tuning of OS and tuning of MySQL are exactly the same.
On the very same machine I am just switching between version 8.0.19 and 8.0.20.

So something has changed in 8.0.20 that is seriously affecting performance in certain situations.
It is either a bug or just a change that requires a configuration tuning.

I am just asking you as an insider who understands the 8.0.20 change log much better than me that could you please point me the direction on how to fix this or give me a config file that works properly for you.

Thanks
[7 Aug 2020 15:57] MySQL Verification Team
It just requires configuration tuning. This includes hardware, OS, MySQL .....
[9 Aug 2020 12:12] MySQL Verification Team
my tests

Attachment: bug100285_sbester_test.sql (application/sql, text), 8.73 KiB.

[13 Aug 2020 14:52] Andras Janurik
Thanks for the tests, helps a lot!