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 .