| 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.
[17 May 2021 6:27]
Aleksandar Pavic
It is still occurring on mysql 8.0.25 on vmware server...
[19 Nov 2021 17:24]
Aleksandar Mitic
I can confirm that this issue still exists. Mysql on Samsung SSD 980 PRO nvme 1TB disk is way slower then on older SSD disk on my laptop. The same clean install is creating tables 8x slower. Disk is 4x faster then older SSD, and anyway mysql on newer nvme disk is running really slow when creating tables.
[22 Nov 2021 13:02]
MySQL Verification Team
Hi, There are a number of settings that can improve performance on SSDs. For example: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_neighbo... https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_page_size https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_undo_tablespa... There are other references that you can find in our Reference Manual.
[22 Nov 2021 13:47]
Rael G. C.
> https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_neighbo... > > https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_page_size > > https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_undo_tablespa... I just checked the documentation for the above items. The first one (`innodb_flush_neighbors`), the documentation states that the default value (`0`) is the one good for SSDs. I.e., the default configuration (and this is the value used on Ubuntu) is already supposed to the the good one for SSDs. The third one (`innodb_undo_tablespaces`), the documention states: "The innodb_undo_tablespaces variable is deprecated and is no longer configurable as of MySQL 8.0.14". Ubuntu is using 8.0.27, so another option that will have no impact. The only one worth to try is the second (`innodb_page_size`), but after test it (reducing to 4096), I saw no difference at all.
[22 Nov 2021 15:14]
MySQL Verification Team
Hi, I have mentioned just a couple, but all of relevant tuning parameters for SSDs are fully documented in our Reference Manual, hence we do not understand why are you referring only to those that we mentioned. Also, this is a bug that is a duplicate of the bug that is not fixed yet ......
[22 Nov 2021 16:47]
Rael G. C.
Ok, thanks for the feedback. But one question: first this bug was flagged as duplicate of an internal issue, fixed on 8.0.22 (Ubuntu is using 8.0.24). Now you're saying it's not fixed. Is it fixed or not? Other problem: the internal issue is mentioning "low-end" architectures, which clearly is not the case.
[23 Nov 2021 7:36]
Aleksandar Mitic
Info for all of those experiencing this issue. I fixed slow MySQL performance when creating tables, by adding 'nobarrier=0' mount option. Link: https://davidalger.com/posts/mysql-write-performance-and-io-barriers/ After disabling nobarrier option, my database migrations were up to 20x times faster.
[23 Nov 2021 13:17]
MySQL Verification Team
It is fixed, actually, only 8.0 is still slower then 5.7, but to a much smaller degree .....
