我有一个 500G 的 mysqldump 要加载,它似乎需要很长时间。我的服务器配备 Intel i9-10920X @ 3.5GHz 和 128GB RAM。数据库设置在 HDD 硬盘中。我的 mysql (Ver 8.0.29-0ubuntu0.20.04.3) 设置如下:
innodb_buffer_pool_size = 32G
innodb_log_buffer_size = 512M
innodb_log_file_size = 2G
innodb_write_io_threads = 32
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0
我在源 .sql 文件之前进一步设置了以下内容:
SET global max_allowed_packet=1000000000;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
现在它每秒读取 20k 行。如何进一步优化?谢谢!
当你说永远时,你的意思是多久?小时?天?如果需要几天时间,我不会感到惊讶。
众所周知,导入 .sql 转储文件非常耗时。它必然是单线程的,所以无论你有什么类型的 CPU,你都只能使用一个 CPU 内核。
I/O 系统很重要。当您填充 2G InnoDB 日志文件时,缓冲池中的脏页必须刷新到磁盘。使用像 NVMe 这样的快速直连磁盘系统会有所帮助。使用条带化 RAID-0 或 RAID-10 会有所帮助。使用远程存储(例如 AWS EBS)不利于延迟。
在表中使用最少的索引会有所帮助。想想每行写入乘以表中的索引数。表本身存储为聚集索引,这是一次写入。然后每个二级索引都是一个额外的写入。唯一索引必须是同步写入(尽管如果您设置 unique_checks=off,这很容易)。非唯一索引可以通过更改缓冲延迟,但它们最终确实需要合并到表空间中。
与其加载 .sql 文件,不如使用 .sql 文件要快得多
LOAD DATA [LOCAL] INFILE
。请参阅我的演示文稿快速加载数据!. 但是您不能将其与 .sql 转储文件一起使用。它适用于 CSV 文件和类似文件。使用并行加载多个表
LOAD DATA [LOCAL] INFILE
可能是批量加载大型数据集的最佳方法。这就是MySQL Shell Parallel Table Import Utility背后的理念。在此处查看对并行表导入的评估:为了更快,您必须求助于物理备份而不是转储文件(即Percona XtraBackup)或文件系统快照。
这个文件对于mysql来说还是比较大的。建议拆分文件。
开启自动提交或分批多次提交。
3.innodb_buffer_pool设置得更大,日志缓存也设置得更大。