我有一个 150G - 3 亿个条目的数据库,我想在 MariaDB 10.3 上恢复它(gz SQL Dump),但由于 SQL 服务器消失,恢复失败。我尝试将 max_allowed_packet 提高到最大值 1G,这比以前多占用了 20G,但每次都出现相同的错误。
错误日志
--线程 140190396552960 已在 btr0cur.cc 第 1357 行等待了 241.00 秒信号量:位于 0x7f65a0265500 的 RW-latch 上的 SX-lock 在文件 dict0dict.cc 第 2130 行创建,写入器(线程 ID 140075647571712)已在 SX 模式下保留它,读取器数量为 0,等待者标志为 1,lock_word:10000000 上次写入锁定在文件 dict0stats.cc 第 1969 行 2020-11-09 23:12:33 0 [注意] InnoDB:信号量等待:--线程 140190396552960 已在 btr0cur.cc 第 1357 行等待了 241.00 秒信号量:在文件 dict0dict.cc 第 2130 行中创建的 0x7f65a0265500 处的 RW 闩锁上的 SX 锁,一个写入器(线程 ID 140075647571712)已在 SX 模式下保留它,读者数量为 0,等待者标志为 1,lock_word:10000000 上次写入锁定在文件 dict0stats.cc 第 1969 行 InnoDB:######启动 InnoDB 监视器 30 秒以打印诊断信息:InnoDB:待处理的读取 0,写入
还有其他我可以调整的变量吗?
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
答案1
尝试降低 Innodb 缓冲池变量直到数据加载完成,例如,由于您已分配 100G,因此将 innodb-buffer-pool-size 降至 5G,完成数据加载,然后增加回相同的值 100G,看看是否有帮助。
答案2
您是否尝试设置这些变量:
innodb_stats_presistent='OFF'
innodb_lock_mode=2
确保表上没有自动递增值,然后查看问题是否消失,上面的错误日志似乎是 SX 锁的问题,基本上,InnoDB 在内部使用 rw-lock 实现来保持内部资源的一致性,并且 rw-lock 有两种类型:S 锁(共享)和 X 锁(排除)。因此,根据您的错误日志,我们在信号量中看到 SX 锁,因此假设这可能是写入 Innodb 表时的争用问题。尝试一下,看看这是否有帮助。