I have a Database of 150G – 300 Mio entries and i want to restore it (gz SQL Dump) on MariaDB 10.3 but the restore failed because the sql server gone away. I tried to raise max_allowed_packet to the maximum of 1G, it takes 20G more than before but got the same error every time.
error.log
–Thread 140190396552960 has waited at btr0cur.cc line 1357 for 241.00 seconds the semaphore:
SX-lock on RW-latch at 0x7f65a0265500 created in file dict0dict.cc line 2130
a writer (thread id 140075647571712) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time write locked in file dict0stats.cc line 1969
2020-11-09 23:12:33 0 [Note] InnoDB: A semaphore wait:
–Thread 140190396552960 has waited at btr0cur.cc line 1357 for 241.00 seconds the semaphore:
SX-lock on RW-latch at 0x7f65a0265500 created in file dict0dict.cc line 2130
a writer (thread id 140075647571712) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time write locked in file dict0stats.cc line 1969
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending reads 0, writes
Is there an other variable which i can adjust.
max_connections = 2000
connect_timeout = 15
wait_timeout = 1200
max_allowed_packet = 1G
thread_cache_size = 128
sort_buffer_size = 9M
bulk_insert_buffer_size = 32M
tmp_table_size = 2G
max_heap_table_size = 2G
myisam_recover_options = BACKUP
key_buffer_size = 128M
open-files-limit = 5000
table_open_cache = 4000
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
net_read_timeout = 15600
net_write_timeout = 15600
default_storage_engine = InnoDB
innodb_buffer_pool_size = 100G
innodb_log_buffer_size = 1G
innodb_file_per_table = 1
innodb_open_files = 4000
innodb_io_capacity = 200000
innodb_flush_method = O_DIRECT
Best Answer
Try lowering Innodb buffer pool variables until data is loaded, for example since you have 100G allocated, so bring it down to 5G the innodb-buffer-pool-size , complete the data load and then increase back to the same value which was 100G, see if that helps out .