| Bug #93734 | MySQL 8.0 is 36 times slower than MySQL 5.7 | ||
|---|---|---|---|
| Submitted: | 24 Dec 2018 18:44 | Modified: | 2 Jun 2020 10:23 |
| Reporter: | Vadim Tkachenko | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | OS: | Ubuntu (18.04) | |
| Assigned to: | CPU Architecture: | x86 | |
[24 Dec 2018 18:44]
Vadim Tkachenko
[24 Dec 2018 18:49]
Vadim Tkachenko
I should add that the option innodb_flush_log_at_trx_commit= 1 is the one that makes the performance impact. When I use innodb_flush_log_at_trx_commit= 0 then MySQL 8.0 performance is roughly the same as MySQL 5.7
[24 Dec 2018 18:53]
Mark Callaghan
8.0.what? Is this using 36x more CPU?
[24 Dec 2018 18:55]
Vadim Tkachenko
MySQL 8.0.13 Vmstat for 8.0.13: procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 2 0 0 14814912 28384 10282952 0 0 1217 3199 85381 187052 4 5 90 0 0 0 0 0 14803844 28384 10289100 0 0 1196 2818 81898 179582 5 7 88 0 0 3 0 0 14793008 28384 10295088 0 0 1200 3007 84725 185657 5 6 89 0 0 vmstat for MySQl 5.7 procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 8 0 0 13856900 12504 13034624 0 0 0 205012 2338 4143 83 1 15 1 0 8 0 0 12861932 12504 13560592 0 0 0 202415 2303 4144 81 2 17 1 0 6 0 0 11894124 12504 14073404 0 0 0 194457 2371 4159 83 1 15 1 0 1 0 0 10964124 12512 14565408 0 0 0 187349 2250 3868 82 1 16 1 0
[25 Jan 2019 19:29]
Vadim Tkachenko
For MySQL 8.0.14 the result is time sysbench oltp_read_only --tables=8 --table-size=10000000 --rand-type=uniform --mysql-user=root --mysql-socket=/tmp/mysql.sock --threads=8 prepare real 76m26.892s user 6m24.992s sys 0m6.751s
[25 Jan 2019 19:43]
MySQL Verification Team
> > This type of the storage is critical because it is fast in random > > read/writes but slow in fsync(). > So, we just need to find out where the extra calls to fsync() are coming about and, if they're justified? I think it's fair to say MySQL 8.0 is not 36x slower, but reporter's disk's fsync() is the one who's 36x slower than normal ?
[13 Mar 2019 16:38]
MySQL Verification Team
Hi Vadim, Thank you for this report. I have setup the server as you specified. I do not have storage that you describe as "Samsung 970 Pro NVMe". Is that some kind of Flash Drive ??? Is it essential in some ways for the results ??? May be it has very slow fsync() ??? I used my iMac with Fusion drive, which has a 1 Tb flash drive as a primary storage. As my machine is not as fast as yours, I used only 1 million rows for each table. With 5.7.24, I have got the following results: real 12m14.404s user 0m13.832s sys 0m1.290s Now, I will be running it on 8.0.15.
[13 Mar 2019 16:42]
Vadim Tkachenko
Sinisa, "Samsung 970 Pro NVMe" is a PCIe SSD. It indeed seems to have a slow fsync() . The point here that even with slow fsync(), MySQL 5.7 performs fine, but MySQL 8.0 is much slower in inserting data.
[13 Mar 2019 17:45]
MySQL Verification Team
Vadim, My results with 8.0.15 are the following ones: real 12m7.974s user 0m15.085s sys 0m1.479s Hence, as you can see, I can't repeat your test case. And I do not think that we got that Samsung PCI SSD. But, I will check ... Can you let us know the family of devices that would repeat this behaviour ???? If we manage to repeat this behaviour, I have an idea how to diagnose it, but that is not a subject at this moment. Hence, your feedback would be truly welcome ......
[14 Mar 2019 0:15]
Sunny Bains
I used the config in the bug report. It's a low end machine, the FS is ext4 and I can't reproduce the problem.
Are you sure you weren't using a debug binary?
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 2
Core(s) per socket: 4
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 158
Model name: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz
Stepping: 9
CPU MHz: 804.715
CPU max MHz: 4200.0000
CPU min MHz: 800.0000
BogoMIPS: 7200.00
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 8192K
NUMA node0 CPU(s): 0-7
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc art arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc cpuid aperfmperf tsc_known_freq pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch cpuid_fault epb invpcid_single pti ssbd ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx rdseed adx smap clflushopt intel_pt xsaveopt xsavec xgetbv1 xsaves dtherm ida arat pln pts hwp hwp_notify hwp_act_window hwp_epp flush_l1d
It has an older SSD:
*-disk
description: ATA Disk
product: Samsung SSD 840
physical id: 0.0.0
bus info: scsi@2:0.0.0
logical name: /dev/sdb
version: 5B0Q
serial: S1AXNEAD605540R
size: 476GiB (512GB)
capabilities: partitioned partitioned:dos
configuration: ansiversion=5 logicalsectorsize=512 sectorsize=512 signature=0e627372
First is with mysql 8.0
time sysbench oltp_read_only --db-driver=mysql --tables=8 --table-size=10000000 --rand-type=uniform --mysql-user=root --mysql-socket=/tmp/mysql-sunny.sock --threads=8 prepare
sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating table 'sbtest8'...Creating table 'sbtest7'...
Creating table 'sbtest5'...
Creating table 'sbtest1'...
Creating table 'sbtest6'...
Creating table 'sbtest2'...
Creating table 'sbtest3'...
Creating table 'sbtest4'...
Inserting 10000000 records into 'sbtest1'
Inserting 10000000 records into 'sbtest3'
Inserting 10000000 records into 'sbtest4'
Inserting 10000000 records into 'sbtest6'
Inserting 10000000 records into 'sbtest7'
Inserting 10000000 records into 'sbtest5'
Inserting 10000000 records into 'sbtest2'
Inserting 10000000 records into 'sbtest8'
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest7'...
real 16m3.893s
user 4m17.926s
sys 0m6.921s
Second is with mysql 5.7.
time sysbench oltp_read_only --db-driver=mysql --tables=8 --table-size=10000000 --rand-type=uniform --mysql-user=root --mysql-socket=/tmp/mysql-sunny.sock --threads=8 prepare
sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating table 'sbtest6'...
Creating table 'sbtest8'...
Creating table 'sbtest2'...
Creating table 'sbtest3'...
Creating table 'sbtest7'...
Creating table 'sbtest4'...
Creating table 'sbtest1'...
Creating table 'sbtest5'...
Inserting 10000000 records into 'sbtest8'
Inserting 10000000 records into 'sbtest2'
Inserting 10000000 records into 'sbtest1'
Inserting 10000000 records into 'sbtest7'
Inserting 10000000 records into 'sbtest5'
Inserting 10000000 records into 'sbtest4'
Inserting 10000000 records into 'sbtest3'
Inserting 10000000 records into 'sbtest6'
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest3'...
real 23m43.940s
user 3m36.421s
sys 0m6.535s
[14 Mar 2019 13:56]
MySQL Verification Team
Hello Vadim, Actually, several of us were not able to reproduce this. We are waiting on some slower disks, so that we may attempt some new benchmarks later. Hence, no feedback is needed from. For the moment, this bug will remain in the stand-by status. Thanks again.
[14 Mar 2019 15:09]
Sunny Bains
I can reproduce the problem if I slow down the fsync() call by adding a sleep.
[14 Mar 2019 16:18]
MySQL Verification Team
Sunny, Thank you very much. I will transfer this bug into internal database and will leave some comments there.
[2 Jun 2020 10:23]
MySQL Verification Team
This was closed as a duplicate of the following bug, fixed in 8.0.22: BUG 30088404 - CONCURRENT LOG BUFFER OF 8.0 CAUSES REGRESSION OF CPU BOUND PERFORMANCE ON LOW-END ARCHITECTURE
[2 Jun 2020 12:15]
MySQL Verification Team
Hi Vadim, my friend, This is just to inform you that this bug is a duplicate of an internal bug. The internal bug was fixed in 8.0.22, so you will have to wait on that release .... Patientia mater virtuosis est ..........
[29 Sep 2020 6:05]
Imtiaz Shakil
Hi I'm also facing similar problems. I've two laptops. Laptop1 config: processor: corei7, ssd: samsung 970 evo 500gb nvme ssd, ram: 16gb, mysql: mysql Ver 8.0.21, os: linux mint 20.04 lts Laptop2 config: processor: corei5, ssd: Corsair Force LE200 240gb ssd, ram: 16gb, mysql: mysql Ver 14.14 Distrib 5.7.31, os: ubuntu budgie 18.04 Note: during ssd benchmark test nvme ssd performs almost 3 times faster than general corsair ssd. Problem: My mysql configuration is vanilla with no alteration. When I run a script that inserts 100000+ rows on multiple tables laptop2 (slower laptop) performs way faster than laptop1 (faster laptop).
[29 Sep 2020 12:33]
MySQL Verification Team
Hi Mr. Shakil, As I wrote already, please try out 8.0.22 whenever it is out. Nobody knows when will it be out.
[16 Dec 2020 16:30]
Rael G. C.
Just facing similar issue on Ubuntu 20.04 and with a SAMSUNG 970 EVO PLUS SSD. My rails test suite runs in: - 6 minutes on MySQL 5.7.32 - 19 minutes on MySQL 8.0.22
[16 Dec 2020 16:32]
Rael G. C.
One additional info: this is not a low end spec machine. It's a Ryzen 5 4650G, 64GB 3200MHz RAM, 1TB SSD.
[16 Dec 2020 17:04]
Sunny Bains
Please try with --innodb-writer-thread=off and report your experience.
[16 Dec 2020 21:31]
Rael G. C.
I edited `/etc/systemd/system/multi-user.target.wants/mysql.service` and added: ``` Environment=innodb_writer_thread=OFF ``` Reloaded systemd daemons and restarted mysqld. Now MySQL v8.0.22 performed in 13.30 minutes. Not bad like previous attempts (19 minutes), but far from 6 minutes from v5.7.
[18 Dec 2020 8:13]
Yasufumi Kinoshita
Current 8.0 behavior is not same for the same options at 5.7. At least, I use the followings to add to 8.0 when I compare with 5.7 ssl=0 (5.7 community (self built) might not use SSL for cases. 8.0 always use ssl by default, might be slower) innodb_doublewrite_pages=120 (if doublewrite enabled for recent 8.0.x "on HDD") innodb_undo_log_truncate=OFF innodb_log_writer_threads=OFF There may be another default behavior change related to performance still. innodb_log_writer_threads=OFF is intended to make 8.0 log io characteristics similar to 5.7 did. If not fixed enough, the difference might be the another problem.
[18 Dec 2020 13:53]
MySQL Verification Team
Thank you, Yasufumi !!!!!
[18 Dec 2020 14:02]
Rael G. C.
With the additional suggested changes: ssl=0 innodb_doublewrite_pages=120 innodb_undo_log_truncate=OFF I got better results, around 12 minutes. But still 2x slower than 5.7.
[18 Dec 2020 14:17]
MySQL Verification Team
Hi, MySQL 8.0 being slightly slower then 5.7 is a known fact. You can find many, many verified bugs on the subject, on this forum. We are in the process of improving this situation.
[22 Dec 2020 13:02]
Yasufumi Kinoshita
Please check your setting first. (https://mysqlserverteam.com/new-defaults-in-mysql-8-0/) 8.0 default is oriented to use SSD like storage. (5.7 default is to use HDD or small HDD RAID) Even with aligned options for your environment, if still the obvious regression exists, it should be another bug from this bug#. Please report as the another bug with the reproduce procedure. Thanks
[22 Dec 2020 14:11]
MySQL Verification Team
Thank you, Yasufumi.
[22 Dec 2020 21:25]
Rael G. C.
Like other users reported here, I'm using a SSD M.2 SAMSUNG 970 EVO PLUS SSD. But I'm no more in a rush now. I reverted production and test environments to 5.7.
