I am using a Raspberry Pi 3 B+ with the official charger running Raspbian Stretch Lite (Debian Version 9.4, Kernal 4.14.34-v7+ #1110) with MariaDB (Version 10.1.23) and I am trying to import a 52GB SQL file.
To help with storage I have connected a 500GB Samsung SSD 750 EVO SATA disk via a Sabrent USB 3.0 to SSD cable and added safe_mode_gpio=4 to the end of the /boot/config.txt file and mounted the drive and copied the 52GB SQL onto it.
I then moved the MariaDB data folder onto the mounted drive and updated the 50-server.cnf file with the new location of the data folder datadir=/mnt/Data/mysql then restarted MariaDB:
MariaDB [(none)]> select @@datadir;
+------------------+
| @@datadir |
+------------------+
| /mnt/Data/mysql/ |
+------------------+
1 row in set (0.00 sec)
Despite all of that it the import consistently fails after importing around 1.2 GB of data:
pi@raspberrypidb:/mnt/Data/export $ sudo mysql -u root -p springbootdb < import.sql
Enter password:
ERROR 2013 (HY000) at line 18939: Lost connection to MySQL server during query
pi@raspberrypidb:/mnt/Data/export $
Checking the mysql.log file shows a lot of waiting errors:
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2280
--Thread 1463808832 has waited at buf0flu.cc line 1886 for 241.00 seconds the semaphore:
Mutex at 0x6ae589a0 '&buf_pool->flush_state_mutex', lock var 1
Last time reserved by thread 1535112000 in file not yet reserved line 0, waiters flag 1
--Thread 1455420224 has waited at buf0flu.cc line 1886 for 241.00 seconds the semaphore:
Mutex at 0x6ae589a0 '&buf_pool->flush_state_mutex', lock var 1
Last time reserved by thread 1535112000 in file not yet reserved line 0, waiters flag 1
--Thread 1501557568 has waited at log0log.cc line 1817 for 241.00 seconds the semaphore:
Mutex at 0x76107588 '&log_sys->mutex', lock var 1
Last time reserved by thread 1639392064 in file not yet reserved line 0, waiters flag 1
OS WAIT ARRAY INFO: signal count 2237
Mutex spin waits 2347, rounds 54859, OS waits 1664
RW-shared spins 161, rounds 4581, OS waits 149
RW-excl spins 4, rounds 13329, OS waits 439
Spin rounds per wait: 23.37 mutex, 28.45 RW-shared, 3332.25 RW-excl
...
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: complete io for buf page (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 1
3377 OS file reads, 94822 OS file writes, 6201 OS fsyncs
0 pending reads, 1 pending writes
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
Checking the kern.log file shows another timeout relating to my mounted disk:
INFO: task mount.ntfs:255 blocked for more than 120 seconds.
Tainted: G C 4.14.34-v7+ #1110
"echo 0 > /proc/sys/kernel/hung_task_timeout_secs" disables this message.
mount.ntfs D 0 255 1 0x00000000
[<8079a0b8>] (__schedule) from [<8079a730>] (schedule+0x50/0xa8)
[<8079a730>] (schedule) from [<8079e314>] (schedule_timeout+0x1d0/0x3f4)
[<8079e314>] (schedule_timeout) from [<8079ac7c>] (io_schedule_timeout+0x28/0x48)
[<8079ac7c>] (io_schedule_timeout) from [<8079b62c>] (wait_for_common_io.constprop.2+0xc0/0x144)
[<8079b62c>] (wait_for_common_io.constprop.2) from [<8079b6e8>] (wait_for_completion_io+0x1c/0x20)
[<8079b6e8>] (wait_for_completion_io) from [<80465b48>] (submit_bio_wait+0x6c/0x78)
[<80465b48>] (submit_bio_wait) from [<804743d8>] (blkdev_issue_flush+0x94/0xbc)
[<804743d8>] (blkdev_issue_flush) from [<802c8930>] (blkdev_fsync+0x48/0x58)
[<802c8930>] (blkdev_fsync) from [<802c0418>] (vfs_fsync_range+0x68/0xc8)
[<802c0418>] (vfs_fsync_range) from [<802c0500>] (do_fsync+0x4c/0x74)
[<802c0500>] (do_fsync) from [<802c07d8>] (SyS_fsync+0x1c/0x20)
[<802c07d8>] (SyS_fsync) from [<80108060>] (ret_fast_syscall+0x0/0x28)
sd 0:0:0:0: [sda] tag#0 UNKNOWN(0x2003) Result: hostbyte=0x00 driverbyte=0x08
sd 0:0:0:0: [sda] tag#0 Sense Key : 0x2 [current]
sd 0:0:0:0: [sda] tag#0 ASC=0x4 ASCQ=0x7
sd 0:0:0:0: [sda] tag#0 CDB: opcode=0x35 35 00 00 00 00 00 00 00 00 00
print_req_error: I/O error, dev sda, sector 0
In the kern.log file I also found this line rather suspicious:
EXT4-fs (mmcblk0p2): re-mounted. Opts: (null)
scsi 0:0:0:0: Direct-Access JMicron 4102 PQ: 0 ANSI: 6
sd 0:0:0:0: Attached scsi generic sg0 type 0
snd_bcm2835: module is from the staging directory, the quality is unknown, you have been warned.
In an attempt to fix this lock I have tried setting innodb_adaptive_hash_index=0 in 50-server.cnf as suggested here and tried changing the dirty ratio as suggested here:
sudo sysctl -w vm.dirty_ratio=10
sudo sysctl -w vm.dirty_background_ratio=5
sudo sysctl -p
The import runs fine if I move the MariaDB data folder back onto my 128GB microSD card but I am wanting to store more data which is why I am wanting to use a spare SSD instead of forking out a costly 400GB microSD card.
Is it possible my USB to SATA cable is not fully supported?
