Bug #102177 | TPCC performance worse in NVMe PCIE4.0 SSD than in SATA SSD | ||
---|---|---|---|
Submitted: | 7 Jan 2021 13:05 | Modified: | 21 Jan 2021 13:10 |
Reporter: | haochen he | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 8.0.22 | OS: | Ubuntu (20.10-Desktop) |
Assigned to: | CPU Architecture: | x86 (AMD Ryzen 9 3900XT) | |
Tags: | performance, SSD |
[7 Jan 2021 13:05]
haochen he
[11 Jan 2021 16:31]
Mark Callaghan
Questions I have: * what is fsync, read & write latency for the devices * are devices healthy (check dmesg) * what is device status ("nvme smart-log" for nvme, smartctl for sata) see http://smalldatum.blogspot.com/2017/10/wearing-out-ssd.html
[14 Jan 2021 1:55]
haochen he
Thanks for the reply. The result of smart-log & smartctl: ➜ ~ sudo nvme smart-log /dev/nvme1 Smart Log for NVME device:nvme1 namespace-id:ffffffff critical_warning : 0 temperature : 35 C available_spare : 100% available_spare_threshold : 10% percentage_used : 0% endurance group critical warning summary: 0 data_units_read : 302,863 data_units_written : 949,569 host_read_commands : 11,674,407 host_write_commands : 31,466,241 controller_busy_time : 255 power_cycles : 23 power_on_hours : 7 unsafe_shutdowns : 9 media_errors : 0 num_err_log_entries : 0 Warning Temperature Time : 0 Critical Composite Temperature Time : 0 Temperature Sensor 1 : 35 C Temperature Sensor 2 : 45 C Thermal Management T1 Trans Count : 0 Thermal Management T2 Trans Count : 0 Thermal Management T1 Total Time : 0 Thermal Management T2 Total Time : 0 ➜ ~ sudo smartctl --all /dev/sda smartctl 7.1 2019-12-30 r5022 [x86_64-linux-5.8.0-36-generic] (local build) Copyright (C) 2002-19, Bruce Allen, Christian Franke, www.smartmontools.org === START OF INFORMATION SECTION === Model Family: Samsung based SSDs Device Model: Samsung SSD 860 EVO 500GB Serial Number: S3Z3NB0NA09331Z LU WWN Device Id: 5 002538 e90a06b62 Firmware Version: RVT04B6Q User Capacity: 500,107,862,016 bytes [500 GB] Sector Size: 512 bytes logical/physical Rotation Rate: Solid State Device Form Factor: 2.5 inches Device is: In smartctl database [for details use: -P show] ATA Version is: ACS-4 T13/BSR INCITS 529 revision 5 SATA Version is: SATA 3.2, 6.0 Gb/s (current: 6.0 Gb/s) Local Time is: Thu Jan 14 09:16:51 2021 CST SMART support is: Available - device has SMART capability. SMART support is: Enabled === START OF READ SMART DATA SECTION === SMART overall-health self-assessment test result: PASSED General SMART Values: Offline data collection status: (0x00) Offline data collection activity was never started. Auto Offline Data Collection: Disabled. Self-test execution status: ( 0) The previous self-test routine completed without error or no self-test has ever been run. Total time to complete Offline data collection: ( 0) seconds. Offline data collection capabilities: (0x53) SMART execute Offline immediate. Auto Offline data collection on/off support. Suspend Offline collection upon new command. No Offline surface scan supported. Self-test supported. No Conveyance Self-test supported. Selective Self-test supported. SMART capabilities: (0x0003) Saves SMART data before entering power-saving mode. Supports SMART auto save timer. Error logging capability: (0x01) Error logging supported. General Purpose Logging supported. Short self-test routine recommended polling time: ( 2) minutes. Extended self-test routine recommended polling time: ( 85) minutes. SCT capabilities: (0x003d) SCT Status supported. SCT Error Recovery Control supported. SCT Feature Control supported. SCT Data Table supported. SMART Attributes Data Structure revision number: 1 Vendor Specific SMART Attributes with Thresholds: ID# ATTRIBUTE_NAME FLAG VALUE WORST THRESH TYPE UPDATED WHEN_FAILED RAW_VALUE 5 Reallocated_Sector_Ct 0x0033 100 100 010 Pre-fail Always - 0 9 Power_On_Hours 0x0032 099 099 000 Old_age Always - 463 12 Power_Cycle_Count 0x0032 099 099 000 Old_age Always - 19 177 Wear_Leveling_Count 0x0013 099 099 000 Pre-fail Always - 7 179 Used_Rsvd_Blk_Cnt_Tot 0x0013 100 100 010 Pre-fail Always - 0 181 Program_Fail_Cnt_Total 0x0032 100 100 010 Old_age Always - 0 182 Erase_Fail_Count_Total 0x0032 100 100 010 Old_age Always - 0 183 Runtime_Bad_Block 0x0013 100 100 010 Pre-fail Always - 0 187 Uncorrectable_Error_Cnt 0x0032 100 100 000 Old_age Always - 0 190 Airflow_Temperature_Cel 0x0032 072 049 000 Old_age Always - 28 195 ECC_Error_Rate 0x001a 200 200 000 Old_age Always - 0 199 CRC_Error_Count 0x003e 100 100 000 Old_age Always - 0 235 POR_Recovery_Count 0x0012 099 099 000 Old_age Always - 4 241 Total_LBAs_Written 0x0032 099 099 000 Old_age Always - 1696163175 SMART Error Log Version: 1 No Errors Logged SMART Self-test log structure revision number 1 No self-tests have been logged. [To run self-tests, use: smartctl -t] SMART Selective self-test log data structure revision number 1 SPAN MIN_LBA MAX_LBA CURRENT_TEST_STATUS 1 0 0 Not_testing 2 0 0 Not_testing 3 0 0 Not_testing 4 0 0 Not_testing 5 0 0 Not_testing Selective self-test flags (0x0): After scanning selected spans, do NOT read-scan remainder of disk. If Selective self-test is pending on power-up, resume after 0 minute delay.
[14 Jan 2021 2:06]
haochen he
The fsync latency: ````````````````The code I use to get fsync latency`````````````` #!/usr/bin/python import os, sys, mmap # Open a file fd = os.open( "testfile", os.O_RDWR|os.O_CREAT|os.O_DIRECT ) m = mmap.mmap(-1, 512) for i in range (1,1000): os.lseek(fd,os.SEEK_SET,0) m[1] = "1" os.write(fd, m) os.fsync(fd) # Close opened file os.close( fd ) ````````````````````````````````` Result of fsync latency (which is very STRANGE): 0.895 ms for Samsung 860 evo (SATA) 5.352 ms for Samsung 980 pro (NVMe) For read/write latency: from the official document of these 2 devices: https://s3.ap-northeast-2.amazonaws.com/global.semi.static/Samsung_SSD_860_EVO_Data_Sheet_... https://s3.ap-northeast-2.amazonaws.com/global.semi.static/Samsung_NVMe_SSD_980_PRO_Data_S... 980 pro is superior to 860 evo in ALL aspects (i.e. random read/write IOPS, sequential read/write throughput). What's more I noticed is when I disable the double-write buffer, the performance difference of the two devices is much more narrowed. Please refer to https://dba.stackexchange.com/questions/282927/using-double-write-buffer-is-8x-slower-in-s...
[14 Jan 2021 2:11]
haochen he
using dmesg, I do not see any error message about anything after some performance experiments on theses two devices. Actually, I have just bought these two devices from Samsung official mall for 2 weeks.
[21 Jan 2021 13:10]
MySQL Verification Team
Hi Mr. he, Thank you for your bug report. However, this is not bug in MySQL. Simply, you diagnosed yourself a latency which is totally independent of our server. Not a bug.
[24 Jun 2023 16:42]
adrian TNT
@haochen he: do you use a Ryzen CPU and what motherboard is it ? I experienced this with Ryzen 5 3600 CPU and Gigabyte B550M DS3H motherboard. I didn't test with other systems, but I found other people experiencing the same latency in MySQL with Samsung 980 pro and Ryzen CPUs. I am trying to see if the Ryzen or motherboard might be the cause. With a Kingston DC1000B NVME it didn't have this bad latency, but with many other NVMEs I seen same bad latency.