Database Restore ERROR 2013 (HY000) at line 68497: Lost connection to MySQL server during query mariadb
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
Solution 1:
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 .