1

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
Norbert Sehm
  • 11
  • 1
  • 4

2 Answers2

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 .

user616825
  • 11
  • 2
  • I was having trouble restoring a 1.8GB database, always more or less at the same dump file line. This (comenting innodb_buffer_pool_size at mysql.cnf) worked like a charm! – Facundo Fasciolo Oct 27 '21 at 14:23
0

Did you trying setting these variables:

innodb_stats_presistent='OFF'
innodb_lock_mode=2

ensuring there are no auto-increment values on your tables and see if the issue is gone, the above error log seems to be an issue on the SX-lock, Basically the InnoDB internally uses rw-lock implementation to keep consistency of internal resources and the rw-lock has 2 types S-lock (shared) and X-lock (excluded). So as per your error log we see SX-lock seen in the semaphore so assuming this may be an contention issues while writing to Innodb table. Try out and see if this helps out.

user616825
  • 11
  • 2