0

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?

Your Answer

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

Browse other questions tagged or ask your own question.