6

While configuring our new servers (IBM X3650M4, 2x Intel Xeon E5-2620, 64G RAM, RAID 10 (4x 240G SSD)) we bumped into a strange problem. We're trying to import dumps (mydumper) from our (old) production environment into our new one, but we're hardly able to do so.

The dump is 47G (compressed), +/- 4500 tables, the biggest tables are 7.1G (54285914 rows), 2.2G, 1.7G and 1.7G.

We're trying to run myloader with 12 (or 8) threads, but most of the times that fails. After a few minutes myloader comes across the large tables and just stalls. Several tools show no more activity (htop no cpu load, iostat no writes or reads), mytop shows 4 (long running) queries that never finish, most innotop screens are frozen (because SHOW INNODB STATUS does not return any results anymore). When I try to restart mysql that fails too, because mysql is not able to kill a number of threads. The only remedy is to use killall -9 mysqld mysql.

We are using the following mysql configuration:

# cat /etc/mysql/my.cnf 
# Ansible managed

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

ssl_cert = /etc/mysql/client-cert.pem
ssl_key = /etc/mysql/client-key.pem

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
pid_file = /var/run/mysqld/mysqld.pid
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql

general_log = 0
general_log_file = /var/log/mysql/mysql.log
log_error = /var/log/mysql/error.log
log_queries_not_using_indexes = 0
slow_query_log = 0
slow_query_log_file = /var/log/mysql/mysql-slow.log

skip_external_locking = 1
skip_name_resolve = 1

max_connections = 1000
wait_timeout = 28800
interactive_timeout = 28800

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server = utf8
collation_server = utf8_general_ci
skip-character-set-client-handshake
init_connect='SET collation_connection = utf8_general_ci; SET NAMES utf8;'

default_storage_engine = InnoDB

key_buffer_size = 32M
myisam_recover_options = FORCE,BACKUP

thread_stack = 256K
thread_cache_size = 500

query_cache_type = 0
query_cache_limit = 2M
query_cache_size = 64M

max_allowed_packet = 256M
group_concat_max_len = 256M

tmp_table_size = 256M
max_heap_table_size = 64M

open_files_limit = 65535
innodb_open_files = 8192
table_definition_cache = 8192
table_open_cache = 8192

expire_logs_days = 7
max_binlog_size = 1G

innodb_buffer_pool_size = 32G
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 25000

innodb_file_per_table
innodb_stats_on_metadata = 0

innodb_adaptive_flushing_method = keep_average
innodb_flush_neighbor_pages = none
log_warnings_suppress = 1592
query_response_time_stats = 1

ssl_ca = /etc/mysql/ca-cert.pem
ssl_cert = /etc/mysql/server-cert.pem
ssl_key = /etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet = 256M

[mysql]

[isamchk]
key_buffer_size = 32M

!includedir /etc/mysql/conf.d/

We are using the following ext4 mount options:

defaults,noatime,nobarrier

We are using the following raid (controller) settings:

# ./MegaCli64 -LDInfo -Lall -aAll


Adapter 0 -- Virtual Drive Information:
Virtual Drive: 0 (Target Id: 0)
Name                :
RAID Level          : Primary-1, Secondary-0, RAID Level Qualifier-0
Size                : 445.171 GB
Sector Size         : 512
Is VD emulated      : No
Mirror Data         : 445.171 GB
State               : Optimal
Strip Size          : 128 KB
Number Of Drives per span:2
Span Depth          : 2
Default Cache Policy: WriteThrough, ReadAhead, Direct, No Write Cache if Bad BBU
Current Cache Policy: WriteThrough, ReadAhead, Direct, No Write Cache if Bad BBU
Default Access Policy: Read/Write
Current Access Policy: Read/Write
Disk Cache Policy   : Disabled
Encryption Type     : None
PI type: No PI

Is VD Cached: No


Virtual Drive: 1 (Target Id: 1)
Name                :
RAID Level          : Primary-1, Secondary-0, RAID Level Qualifier-0
Size                : 930.390 GB
Sector Size         : 512
Is VD emulated      : No
Mirror Data         : 930.390 GB
State               : Optimal
Strip Size          : 128 KB
Number Of Drives    : 2
Span Depth          : 1
Default Cache Policy: WriteThrough, ReadAhead, Direct, No Write Cache if Bad BBU
Current Cache Policy: WriteThrough, ReadAhead, Direct, No Write Cache if Bad BBU
Default Access Policy: Read/Write
Current Access Policy: Read/Write
Disk Cache Policy   : Disabled
Encryption Type     : None
PI type: No PI

Is VD Cached: No



Exit Code: 0x00

and

# ./MegaCli64 -AdpAllInfo -a0

Adapter #0

==============================================================================
                    Versions
                ================
Product Name    : ServeRAID M5110e
Serial No       : 31R0NE          
FW Package Build: 23.22.0-0024

                    Mfg. Data
                ================
Mfg. Date       : 01/24/13
Rework Date     : 01/24/13
Revision No     :  00W2671
Battery FRU     :         

                Image Versions in Flash:
                ================
BIOS Version       : 5.45.07.2_4.16.08.00_0x06030503
FW Version         : 3.340.75-3372
NVDATA Version     : 2.1312.03-0126
WebBIOS Version    : 6.1-71-e_71-Rel
Preboot CLI Version: 05.07-00:#%00011
Boot Block Version : 2.05.00.00-0010
BOOT Version       : 07.26.26.219

                Pending Images in Flash
                ================
None

                PCI Info
                ================
Controller Id   : 0000
Vendor Id       : 1000
Device Id       : 005b
SubVendorId     : 1014
SubDeviceId     : 0412

Host Interface  : PCIE

ChipRevision    : D1

Link Speed       : 0 
Number of Frontend Port: 0 
Device Interface  : PCIE

Number of Backend Port: 8 
Port  :  Address
0        4433221100000000 
1        4433221101000000 
2        4433221102000000 
3        4433221103000000 
4        4433221104000000 
5        4433221105000000 
6        0000000000000000 
7        0000000000000000 

                HW Configuration
                ================
SAS Address      : 500507605803cfa8
BBU              : Present
Alarm            : Present
NVRAM            : Present
Serial Debugger  : Present
Memory           : Present
Flash            : Present
Memory Size      : 512MB
TPM              : Absent
On board Expander: Absent
Upgrade Key      : Present
Temperature sensor for ROC    : Present
Temperature sensor for controller    : Absent

ROC temperature : 59  degree Celsius

                Settings
                ================
Current Time                     : 20:52:13 11/12, 2015
Predictive Fail Poll Interval    : 300sec
Interrupt Throttle Active Count  : 16
Interrupt Throttle Completion    : 50us
Rebuild Rate                     : 30%
PR Rate                          : 30%
BGI Rate                         : 30%
Check Consistency Rate           : 30%
Reconstruction Rate              : 30%
Cache Flush Interval             : 4s
Max Drives to Spinup at One Time : 2
Delay Among Spinup Groups        : 12s
Physical Drive Coercion Mode     : 1GB
Cluster Mode                     : Disabled
Alarm                            : Disabled
Auto Rebuild                     : Enabled
Battery Warning                  : Enabled
Ecc Bucket Size                  : 15
Ecc Bucket Leak Rate             : 1440 Minutes
Restore HotSpare on Insertion    : Disabled
Expose Enclosure Devices         : Enabled
Maintain PD Fail History         : Enabled
Host Request Reordering          : Enabled
Auto Detect BackPlane Enabled    : SGPIO/i2c SEP
Load Balance Mode                : Auto
Use FDE Only                     : Yes
Security Key Assigned            : No
Security Key Failed              : No
Security Key Not Backedup        : No
Default LD PowerSave Policy      : Controller Defined
Maximum number of direct attached drives to spin up in 1 min : 10 
Auto Enhanced Import             : No
Any Offline VD Cache Preserved   : No
Allow Boot with Preserved Cache  : No
Disable Online Controller Reset  : No
PFK in NVRAM                     : Yes
Use disk activity for locate     : No
POST delay           : 90 seconds
BIOS Error Handling              : Stop On Errors
Current Boot Mode         :Normal
                Capabilities
                ================
RAID Level Supported             : RAID0, RAID1, RAID5, RAID6, RAID00, RAID10, RAID50, RAID60, PRL 11, PRL 11 with spanning, SRL 3 supported, PRL11-RLQ0 DDF layout with no span, PRL11-RLQ0 DDF layout with span
Supported Drives                 : SAS, SATA

Allowed Mixing:

Mix in Enclosure Allowed

                Status
                ================
ECC Bucket Count                 : 0

                Limitations
                ================
Max Arms Per VD          : 32 
Max Spans Per VD         : 8 
Max Arrays               : 128 
Max Number of VDs        : 64 
Max Parallel Commands    : 1008 
Max SGE Count            : 60 
Max Data Transfer Size   : 8192 sectors 
Max Strips PerIO         : 42 
Max LD per array         : 16 
Min Strip Size           : 8 KB
Max Strip Size           : 1.0 MB
Max Configurable CacheCade Size: 0 GB
Current Size of CacheCade      : 0 GB
Current Size of FW Cache       : 368 MB

                Device Present
                ================
Virtual Drives    : 2 
  Degraded        : 0 
  Offline         : 0 
Physical Devices  : 7 
  Disks           : 6 
  Critical Disks  : 0 
  Failed Disks    : 0 

                Supported Adapter Operations
                ================
Rebuild Rate                    : Yes
CC Rate                         : Yes
BGI Rate                        : Yes
Reconstruct Rate                : Yes
Patrol Read Rate                : Yes
Alarm Control                   : Yes
Cluster Support                 : Yes
BBU                             : Yes
Spanning                        : Yes
Dedicated Hot Spare             : Yes
Revertible Hot Spares           : Yes
Foreign Config Import           : Yes
Self Diagnostic                 : Yes
Allow Mixed Redundancy on Array : No
Global Hot Spares               : Yes
Deny SCSI Passthrough           : No
Deny SMP Passthrough            : No
Deny STP Passthrough            : No
Support Security                : Yes
Snapshot Enabled                : No
Support the OCE without adding drives : Yes
Support PFK                     : Yes
Support PI                      : Yes
Support Boot Time PFK Change    : Yes
Disable Online PFK Change       : Yes
Support LDPI Type1                      : No
Support LDPI Type2                      : No
Support LDPI Type3                      : No
PFK TrailTime Remaining         : 0 days 0 hours
Support Shield State            : Yes
Block SSD Write Disk Cache Change: No
Support Online FW Update    : Yes

                Supported VD Operations
                ================
Read Policy          : Yes
Write Policy         : Yes
IO Policy            : Yes
Access Policy        : Yes
Disk Cache Policy    : Yes
Reconstruction       : Yes
Deny Locate          : No
Deny CC              : No
Allow Ctrl Encryption: No
Enable LDBBM         : No
Support Breakmirror  : No
Power Savings        : No

                Supported PD Operations
                ================
Force Online                            : Yes
Force Offline                           : Yes
Force Rebuild                           : Yes
Deny Force Failed                       : No
Deny Force Good/Bad                     : No
Deny Missing Replace                    : No
Deny Clear                              : No
Deny Locate                             : No
Support Temperature                     : Yes
NCQ                                     : Yes
Disable Copyback                        : No
Enable JBOD                             : No
Enable Copyback on SMART                : No
Enable Copyback to SSD on SMART Error   : Yes
Enable SSD Patrol Read                  : No
PR Correct Unconfigured Areas           : Yes
                Error Counters
                ================
Memory Correctable Errors   : 0 
Memory Uncorrectable Errors : 0 

                Cluster Information
                ================
Cluster Permitted     : No
Cluster Active        : No

                Default Settings
                ================
Phy Polarity                     : 0 
Phy PolaritySplit                : 0 
Background Rate                  : 30 
Strip Size                       : 128kB
Flush Time                       : 4 seconds
Write Policy                     : WB
Read Policy                      : Adaptive
Cache When BBU Bad               : Disabled
Cached IO                        : No
SMART Mode                       : Mode 6
Alarm Disable                    : No
Coercion Mode                    : 1GB
ZCR Config                       : Unknown
Dirty LED Shows Drive Activity   : No
BIOS Continue on Error           : 0 
Spin Down Mode                   : None
Allowed Device Type              : SAS/SATA Mix
Allow Mix in Enclosure           : Yes
Allow HDD SAS/SATA Mix in VD     : No
Allow SSD SAS/SATA Mix in VD     : No
Allow HDD/SSD Mix in VD          : No
Allow SATA in Cluster            : No
Max Chained Enclosures           : 16 
Disable Ctrl-R                   : Yes
Enable Web BIOS                  : Yes
Direct PD Mapping                : No
BIOS Enumerate VDs               : Yes
Restore Hot Spare on Insertion   : No
Expose Enclosure Devices         : Yes
Maintain PD Fail History         : Yes
Disable Puncturing               : Yes
Zero Based Enclosure Enumeration : No
PreBoot CLI Enabled              : Yes
LED Show Drive Activity          : Yes
Cluster Disable                  : No
SAS Disable                      : No
Auto Detect BackPlane Enable     : SGPIO/i2c SEP
Use FDE Only                     : Yes
Enable Led Header                : No
Delay during POST                : 0 
EnableCrashDump                  : No
Disable Online Controller Reset  : No
EnableLDBBM                      : No
Un-Certified Hard Disk Drives    : Allow
Treat Single span R1E as R10     : No
Max LD per array                 : 16
Power Saving option              : All power saving options are disabled
Default spin down time in minutes: 30 
Enable JBOD                      : No
TTY Log In Flash                 : No
Auto Enhanced Import             : No
BreakMirror RAID Support         : No
Disable Join Mirror              : No
Enable Shield State              : Yes
Time taken to detect CME         : 60s

Exit Code: 0x00

and

# ./MegaCli64 -PDList -a0

Adapter #0

Enclosure Device ID: 252
Slot Number: 0
Drive's position: DiskGroup: 0, Span: 0, Arm: 0
Enclosure position: N/A
Device Id: 0
WWN: 50025385700191ba
Sequence Number: 2
Media Error Count: 0
Other Error Count: 0
Predictive Failure Count: 0
Last Predictive Failure Event Seq Number: 0
PD Type: SATA

Raw Size: 223.570 GB [0x1bf244b0 Sectors]
Non Coerced Size: 223.070 GB [0x1be244b0 Sectors]
Coerced Size: 222.585 GB [0x1bd2c000 Sectors]
Sector Size:  512
Logical Sector Size:  512
Physical Sector Size:  512
Firmware state: Online, Spun Up
Commissioned Spare : No
Emergency Spare : No
Device Firmware Level: 203Q
Shield Counter: 0
Successful diagnostics completion on :  N/A
SAS Address(0): 0x4433221100000000
Connected Port Number: 0(path0) 
Inquiry Data: S1G0NWAG600282      SAMSUNG MZ7WD240HCFV-00003              DXM9203Q
FDE Capable: Not Capable
FDE Enable: Disable
Secured: Unsecured
Locked: Unlocked
Needs EKM Attention: No
Foreign State: None 
Device Speed: 6.0Gb/s 
Link Speed: 6.0Gb/s 
Media Type: Solid State Device
Drive:  Not Certified
Drive Temperature :34C (93.20 F)
PI Eligibility:  No 
Drive is formatted for PI information:  No
PI: No PI
Drive's NCQ setting : Enabled
Port-0 :
Port status: Active
Port's Linkspeed: 6.0Gb/s 
Drive has flagged a S.M.A.R.T alert : No



Enclosure Device ID: 252
Slot Number: 1
Drive's position: DiskGroup: 0, Span: 0, Arm: 1
Enclosure position: N/A
Device Id: 1
WWN: 50025385700191c6
Sequence Number: 2
Media Error Count: 0
Other Error Count: 0
Predictive Failure Count: 0
Last Predictive Failure Event Seq Number: 0
PD Type: SATA

Raw Size: 223.570 GB [0x1bf244b0 Sectors]
Non Coerced Size: 223.070 GB [0x1be244b0 Sectors]
Coerced Size: 222.585 GB [0x1bd2c000 Sectors]
Sector Size:  512
Logical Sector Size:  512
Physical Sector Size:  512
Firmware state: Online, Spun Up
Commissioned Spare : No
Emergency Spare : No
Device Firmware Level: 203Q
Shield Counter: 0
Successful diagnostics completion on :  N/A
SAS Address(0): 0x4433221101000000
Connected Port Number: 1(path0) 
Inquiry Data: S1G0NWAG600294      SAMSUNG MZ7WD240HCFV-00003              DXM9203Q
FDE Capable: Not Capable
FDE Enable: Disable
Secured: Unsecured
Locked: Unlocked
Needs EKM Attention: No
Foreign State: None 
Device Speed: 6.0Gb/s 
Link Speed: 6.0Gb/s 
Media Type: Solid State Device
Drive:  Not Certified
Drive Temperature :33C (91.40 F)
PI Eligibility:  No 
Drive is formatted for PI information:  No
PI: No PI
Drive's NCQ setting : Enabled
Port-0 :
Port status: Active
Port's Linkspeed: 6.0Gb/s 
Drive has flagged a S.M.A.R.T alert : No



Enclosure Device ID: 252
Slot Number: 2
Drive's position: DiskGroup: 0, Span: 1, Arm: 0
Enclosure position: N/A
Device Id: 2
WWN: 50025385700191c2
Sequence Number: 2
Media Error Count: 0
Other Error Count: 0
Predictive Failure Count: 0
Last Predictive Failure Event Seq Number: 0
PD Type: SATA

Raw Size: 223.570 GB [0x1bf244b0 Sectors]
Non Coerced Size: 223.070 GB [0x1be244b0 Sectors]
Coerced Size: 222.585 GB [0x1bd2c000 Sectors]
Sector Size:  512
Logical Sector Size:  512
Physical Sector Size:  512
Firmware state: Online, Spun Up
Commissioned Spare : No
Emergency Spare : No
Device Firmware Level: 203Q
Shield Counter: 0
Successful diagnostics completion on :  N/A
SAS Address(0): 0x4433221102000000
Connected Port Number: 2(path0) 
Inquiry Data: S1G0NWAG600290      SAMSUNG MZ7WD240HCFV-00003              DXM9203Q
FDE Capable: Not Capable
FDE Enable: Disable
Secured: Unsecured
Locked: Unlocked
Needs EKM Attention: No
Foreign State: None 
Device Speed: 6.0Gb/s 
Link Speed: 6.0Gb/s 
Media Type: Solid State Device
Drive:  Not Certified
Drive Temperature :33C (91.40 F)
PI Eligibility:  No 
Drive is formatted for PI information:  No
PI: No PI
Drive's NCQ setting : Enabled
Port-0 :
Port status: Active
Port's Linkspeed: 6.0Gb/s 
Drive has flagged a S.M.A.R.T alert : No



Enclosure Device ID: 252
Slot Number: 3
Drive's position: DiskGroup: 0, Span: 1, Arm: 1
Enclosure position: N/A
Device Id: 3
WWN: 50025385700191b7
Sequence Number: 2
Media Error Count: 0
Other Error Count: 0
Predictive Failure Count: 0
Last Predictive Failure Event Seq Number: 0
PD Type: SATA

Raw Size: 223.570 GB [0x1bf244b0 Sectors]
Non Coerced Size: 223.070 GB [0x1be244b0 Sectors]
Coerced Size: 222.585 GB [0x1bd2c000 Sectors]
Sector Size:  512
Logical Sector Size:  512
Physical Sector Size:  512
Firmware state: Online, Spun Up
Commissioned Spare : No
Emergency Spare : No
Device Firmware Level: 203Q
Shield Counter: 0
Successful diagnostics completion on :  N/A
SAS Address(0): 0x4433221103000000
Connected Port Number: 3(path0) 
Inquiry Data: S1G0NWAG600279      SAMSUNG MZ7WD240HCFV-00003              DXM9203Q
FDE Capable: Not Capable
FDE Enable: Disable
Secured: Unsecured
Locked: Unlocked
Needs EKM Attention: No
Foreign State: None 
Device Speed: 6.0Gb/s 
Link Speed: 6.0Gb/s 
Media Type: Solid State Device
Drive:  Not Certified
Drive Temperature :35C (95.00 F)
PI Eligibility:  No 
Drive is formatted for PI information:  No
PI: No PI
Drive's NCQ setting : Enabled
Port-0 :
Port status: Active
Port's Linkspeed: 6.0Gb/s 
Drive has flagged a S.M.A.R.T alert : No



Enclosure Device ID: 252
Slot Number: 4
Drive's position: DiskGroup: 1, Span: 0, Arm: 0
Enclosure position: N/A
Device Id: 4
WWN: 5000c5004f547de9
Sequence Number: 2
Media Error Count: 0
Other Error Count: 0
Predictive Failure Count: 0
Last Predictive Failure Event Seq Number: 0
PD Type: SATA

Raw Size: 931.512 GB [0x74706db0 Sectors]
Non Coerced Size: 931.012 GB [0x74606db0 Sectors]
Coerced Size: 930.390 GB [0x744c8000 Sectors]
Sector Size:  512
Logical Sector Size:  512
Physical Sector Size:  512
Firmware state: Online, Spun Up
Commissioned Spare : No
Emergency Spare : No
Device Firmware Level: HPGD
Shield Counter: 0
Successful diagnostics completion on :  N/A
SAS Address(0): 0x4433221104000000
Connected Port Number: 4(path0) 
Inquiry Data: Z1N3AYLE            MB1000GCEHH                             HPGD    
FDE Capable: Not Capable
FDE Enable: Disable
Secured: Unsecured
Locked: Unlocked
Needs EKM Attention: No
Foreign State: None 
Device Speed: 6.0Gb/s 
Link Speed: 6.0Gb/s 
Media Type: Hard Disk Device
Drive:  Not Certified
Drive Temperature :34C (93.20 F)
PI Eligibility:  No 
Drive is formatted for PI information:  No
PI: No PI
Drive's NCQ setting : Enabled
Port-0 :
Port status: Active
Port's Linkspeed: 6.0Gb/s 
Drive has flagged a S.M.A.R.T alert : No



Enclosure Device ID: 252
Slot Number: 5
Drive's position: DiskGroup: 1, Span: 0, Arm: 1
Enclosure position: N/A
Device Id: 5
WWN: 5000c5004f547bb5
Sequence Number: 2
Media Error Count: 0
Other Error Count: 0
Predictive Failure Count: 0
Last Predictive Failure Event Seq Number: 0
PD Type: SATA

Raw Size: 931.512 GB [0x74706db0 Sectors]
Non Coerced Size: 931.012 GB [0x74606db0 Sectors]
Coerced Size: 930.390 GB [0x744c8000 Sectors]
Sector Size:  512
Logical Sector Size:  512
Physical Sector Size:  512
Firmware state: Online, Spun Up
Commissioned Spare : No
Emergency Spare : No
Device Firmware Level: HPGD
Shield Counter: 0
Successful diagnostics completion on :  N/A
SAS Address(0): 0x4433221105000000
Connected Port Number: 5(path0) 
Inquiry Data: Z1N3AZR2            MB1000GCEHH                             HPGD    
FDE Capable: Not Capable
FDE Enable: Disable
Secured: Unsecured
Locked: Unlocked
Needs EKM Attention: No
Foreign State: None 
Device Speed: 6.0Gb/s 
Link Speed: 6.0Gb/s 
Media Type: Hard Disk Device
Drive:  Not Certified
Drive Temperature :34C (93.20 F)
PI Eligibility:  No 
Drive is formatted for PI information:  No
PI: No PI
Drive's NCQ setting : Enabled
Port-0 :
Port status: Active
Port's Linkspeed: 6.0Gb/s 
Drive has flagged a S.M.A.R.T alert : No




Exit Code: 0x00

Edit 1: We are using:

  • Percona Server 5.5.46-37.5
  • mydumper 0.6.2 (built against MySQL 5.5.45-37.4)
  • Kernel 3.13.0-68-generic #111-Ubuntu SMP Fri Nov 6 18:17:06 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

Edit 2: Problem is not related to:

  • myloader, mysql client has the same problem when importing with multiple processes
  • RAM of server (tested)
  • RAM of RAID controller (tested)
  • Multi-CPU, import also stalls when mysqld is bound to one CPU

Edit 3:

  • MariaDB (mysqld Ver 5.5.46-MariaDB-1ubuntu0.14.04.2 for debian-linux-gnu on x86_64 ((Ubuntu))) does not seem to have this issue

Edit 4:

The problem seem to be caused by one of the four settings below:

  • innodb_adaptive_flushing_method = keep_average
  • innodb_flush_neighbor_pages = none
  • log_warnings_suppress = 1592
  • query_response_time_stats = 1

Edit 5:

The problem seem to be caused by: innodb_adaptive_flushing_method = keep_average, innodb_adaptive_flushing_method = estimate runs stable

Edit 6:

The problem seem to be caused by: innodb_adaptive_flushing_method = keep_average, but only on Percona Server, MariaDB seems to run stable (with the same setting)

The strange thing is that we are able to import the dumps just fine on other (slower) machines, the main differences there are that they have a smaller buffer pool (<= 12G), less powerful CPU (Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHz or Intel(R) Core(TM) i7-3930K CPU @ 3.20GHz), only one SSD (or software raid 1 with 2).

We're really curious what could be the cause of this problem.

3

The root cause of this issue is Oracle MySQL regression in 5.5.46 / 5.6.27 caused by fix for http://bugs.mysql.com/bug.php?id=76135 and Percona Server will have a fix released ASAP (use https://bugs.launchpad.net/bugs/1519094 for tracking).

Edit: the Oracle bug for this is https://bugs.mysql.com/bug.php?id=79185

Adaptive flushing setting is not at fault, even though some values of this setting are more likely to invoke the bug than others. MariaDB is not affected, because they have fixed the underlying issue earlier (https://mariadb.atlassian.net/browse/MDEV-7026).

The whole story is fairly technical and has more to do with low-level systems programming rather than with databases as such. Bug 76135 is about InnoDB mutex primitive not being correct for ARM64. This has been corrected by using proper compiler barriers that ensure proper ordering between mutex word load/stores and the critical section being protected by that mutex. But InnoDB mutex is more than a lock word, and these barriers became too weak to order lock word accesses with other mutex fields. This was first analysed by Kristian Nielsen of MariaDB and can be read at https://lists.launchpad.net/maria-developers/msg07860.html.

3
+50

There is a known issue when certain checkpointing / flushing configuration results in stall. Here is a quote from Percona 5.5 documentation Improved InnoDB I/O Scalability:

Normally, the checkpoint is done passively at the current oldest page modification (this is called “fuzzy checkpointing”). When the checkpoint age nears the maximum checkpoint age (determined by the total length of all transaction log files), InnoDB tries to keep the checkpoint age away from the maximum by flushing many dirty blocks. But, if there are many updates per second and many blocks have almost the same modification age, the huge number of flushes can cause stalls.

[...]

keep_average [2]: This method attempts to keep the I/O rate constant by using a much shorter loop cycle (0.1 second) than that of the other methods (1.0 second). It is designed for use with SSD cards

keep_average is designed to be used with SSD cards which are faster than SSD disks. In your case InnoDB uses very short cycle to come back to flush dirty pages. Then, as per docs, too many pages with very close timestamps cause congestion and stall. Unless you are using SSD cards like Fusion IO you should not use this setting.

Hope this helps.

1

That is not a not normal behaviour, I use regularly mydumper under similar hardware (more memory, but worse disks) and it does not have that behaviour at all (even if my datasets are 30x larger). There are many things I do not like about your particular configuration (I would use at least 5.6 for a new installation, I would get rid of the QCache, I would set the RAID to writeback -not WT, your small strip size, disabling the binary logs for the import process, increasing the buffer pool and transaction log if it is dedicated hardware, getting read of the io threads as you are using async io, ...) but all of those would only improve the performance, not make it so bad that it is unbearable.

Sadly, there is not much to say without having a console in front of me except that you should monitor what is happening in the moment of the import (monitor insert rate, handlers, innodb stats, potential OS hardware/stalls). Maybe check that RAID is not in battery learning mode. There is definitely something wrong going on: DB contention, disk issues? Try doing a disk speed benchmark. Execute myloader with verbose output. Try using the regular commands: lsof, iotop, htop, vmstat, dmesg. If everything fails, use perf to profile your mysqld binary to see what you are wasting cycles on. Maybe you are overcommiting filesystem memory due to FlushLatTxC=2? Maybe you are swapping and you didn't realize that? Check your mydumper and PS version. Could you be experiencing problems with async IO present in older kernels?

Sorry, there is not much I can do from here.

0

Make sure you have: set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; I would de-activate binary logs for now. Turn off slow query logs. If you can, create the indexes after the load (the time it takes to load data will depends on how many indexes you have, the type of indexed and also the data type. Text field (blob) do take a long time to load. Lower the threads count.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.