在配置我们的新服务器（IBM X3650M4、2x Intel Xeon E5-2620、64G RAM、RAID 10（4x 240G SSD））时，我们遇到了一个奇怪的问题。我们正在尝试将转储 ( mydumper) 从我们的（旧）生产环境导入到我们的新环境中，但我们几乎无法做到。
转储为47G（压缩），+/- 4500 个表，最大表为 7.1G（54285914 行）、2.2G、1.7G 和 1.7G。
我们尝试myloader使用 12 个（或 8 个）线程运行，但大多数时候都失败了。几分钟后myloader遇到大桌子和摊位。一些工具显示没有更多活动（htop没有 cpu 负载，iostat没有写入或读取），mytop显示 4 个（长时间运行）查询永远不会完成，大多数innotop屏幕被冻结（因为SHOW INNODB STATUS不再返回任何结果）。当我尝试重新启动时mysql也失败了，因为mysql无法杀死多个线程。唯一的补救措施是使用killall -9 mysqld mysql.
我们正在使用以下 mysql 配置：
# 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/
我们正在使用以下 ext4 挂载选项：
defaults,noatime,nobarrier
我们正在使用以下 RAID（控制器）设置：
# ./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
和
# ./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
和
# ./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
编辑1：我们正在使用：
- Percona 服务器 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,mysqlclient 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_averageinnodb_flush_neighbor_pages = nonelog_warnings_suppress = 1592query_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.
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:
keep_averageis 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.
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.
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.
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.