| Bug #93734 | MySQL 8.0 is 36 times slower than MySQL 5.7 | ||
|---|---|---|---|
| Submitted: | 24 Dec 2018 18:44 | Modified: | 6 Nov 2023 11:22 |
| Reporter: | Vadim Tkachenko | Email Updates: | |
| Status: | Closed | 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.
[5 Apr 2019 6:31]
MySQL Verification Team
Can the additional fsync()'s be due to this change? "As of MySQL 8.0.14, InnoDB calls fsync() after creating a new file, after increasing file size, and after closing a file, which permits O_DIRECT_NO_FSYNC mode to be safely used on EXT4 and XFS file systems."
[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 .....
[6 Oct 2022 8:46]
Rabee Nassar
I just upgrade from 5.7 to 8.0.29 and this issue still exist, write queries much slower than 5.7. Samsung 970 evo plus.
[6 Oct 2022 12:50]
MySQL Verification Team
Hi, You can all improve performance by using all new features in 8.0. Not only features, but new settings as well. You should also note that 8.0 default Unicode character set is changed to the slower one, so you could all examine those settings, which influence the width of all CHAR/VARCHAR - derived column domains. You can also use the advice from Mr. Mitic: I fixed slow MySQL performance when creating tables, by adding 'nobarrier=0' mount option. Link: https://urldefense.com/v3/__https://davidalger.com/posts/mysql-write-performance-and-io-ba...
[22 Nov 2022 17:38]
Paul Coene
This: innodb_flush_log_at_trx_commit= 0 Changed my average replace statement from taking 0.100070s to 0.000395s. Huge impact. Didn't need to set this prior to upgrading to 8.0.
[23 Nov 2022 12:57]
MySQL Verification Team
Hi, Do note that setting that variable will make any recovery practically undoable !!!
[23 Nov 2022 15:00]
Rael G. C.
I've noticed that at Ubuntu 22.04, which uses MySQL 8.0.31, this is fixed.
[23 Nov 2022 15:11]
MySQL Verification Team
Thank you for the feedback.
[10 Apr 2023 23:13]
adrian TNT
I want to confirm that from my tests, Samsung 980 PRO nvme AND Samsung 970 PRO nvme writes around 20 times slower than other SSDs, for example vs Kingston DC1000B enterprise nvme which in specs only writes at only 500MB/s vs Samsung which should write 10x faster at around 5000 MB/s
In below tests I am noticing a huge latency from the Samsung NVMEs, minimum 9.28 ms, vs minimum 0.54 ms on the Kingston DC1000B.
I am running MySQL 8.0.30 on Rocky Linux 9.1, 128 GB ram and AMD Ryzen 5 3600 6-Core, 12 threads cpu.
1) Samsung 980 PRO nvme
=======================
sysbench oltp_read_write --threads=16 --events=0 --time=60 --mysql-host=localhost --mysql-user=sbtest --mysql-password='****' --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable --report-interval=1 run
SQL statistics:
queries performed:
read: 115370
write: 46148
other: 23074
total: 184592
transactions: 11537 (188.20 per sec.)
queries: 184592 (3011.23 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 61.3007s
total number of events: 11537
Latency (ms):
min: 9.28 <<<<<<<<<<<<<<<< !!!!!!!!!!!!!!!
avg: 85.00
max: 2975.20
95th percentile: 147.61
sum: 980698.50
Threads fairness:
events (avg/stddev): 721.0625/9.10
execution time (avg/stddev): 61.2937/0.02
2) Kingston DC1000B NVME
========================
sysbench oltp_read_write --threads=16 --events=0 --time=60 --mysql-host=localhost --mysql-user=sbtest --mysql-password='****' --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable --report-interval=1 run
SQL statistics:
queries performed:
read: 2142950
write: 857180
other: 428590
total: 3428720
transactions: 214295 (3560.11 per sec.)
queries: 3428720 (56961.80 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.1927s
total number of events: 214295
Latency (ms):
min: 0.54 <<<<<<<<<<<<<< this is much faster
avg: 4.49
max: 623.81
95th percentile: 5.57
sum: 962847.61
Threads fairness:
events (avg/stddev): 13393.4375/145.71
execution time (avg/stddev): 60.1780/0.00
[11 Apr 2023 12:05]
MySQL Verification Team
Hi, There is truly no much that we can do with any external drive with such slow fsyncs ..... This is a performance problem, but caused by hardware and not by software ..... You can try mitigating it by using some of the O_DIRECT options, if that will work with the SSD in question. Some have fixed slow MySQL performance when creating tables, by adding 'nobarrier=0' mount option.
[6 Nov 2023 11:22]
MySQL Verification Team
Hi, Since the original bug is fixed in 8.0.22, this is now a closed report.
[26 Jan 20:58]
Jónatan Núñez
I had the same problem with a Samsung pro 990, but seems like the problem is with linux itself?
The file operations is very bad:
$ sysbench fileio --file-total-size=50G prepare
$ sysbench --file-total-size=50G --file-test-mode=rndrw --file-extra-flags=direct fileio run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Extra file open flags: directio
128 files, 400MiB each
50GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...
Threads started!
File operations:
reads/s: 846.57
writes/s: 564.38
fsyncs/s: 1812.67
Throughput:
read, MiB/s: 13.23
written, MiB/s: 8.82
General statistics:
total time: 10.0637s
total number of events: 32315
Latency (ms):
min: 0.02
avg: 0.31
max: 6.13
95th percentile: 0.46
sum: 9993.21
Threads fairness:
events (avg/stddev): 32315.0000/0.00
execution time (avg/stddev): 9.9932/0.00
[29 Jan 11:07]
MySQL Verification Team
Hi, This bug is fixed in 8.0.22, so this is no longer a problem.
