优化现有MySQL数据大容量更新的性能

Question

我正在尝试对大约200,000个现有MySQL行执行批量更新。更具体地说，我需要更新这些行中的8个空LONG BLOB字段，每个字段都有一个~0.5Mb的文件(使用LONG BLOB是因为在某些特殊情况下存储了非常大的文件；但是，在此批量更新中不考虑这些情况)。需要插入的文件存储在本地磁盘上。

我使用编写的MATLAB脚本遍历存储这些文件的每个文件夹，读取其中的文件并将其转换为十六进制表示形式，然后执行更新查询以使用每行的八个文件更新这八列。

起初，事情运行得相当快；然而，我注意到在几千个完成的查询之后，事情真的开始变慢了。我做了一些关于优化MySQL和InnoDB系统变量的研究，并将innodb_buffer_pool_size增加到25G，将innodb_buffer_pool_instances增加到25。

在这个修改之后，事情再次加速，但在另一个几千次查询后速度变慢了。我做了更多的研究，并试图摆弄一些其他变量，如innodb_log_buffer_size和innodb_log_file_size，将两者都增加到1亿，看看会发生什么。我还将innodb_write_io_threads和innodb_read_io_threads设置为16，因为我在一台内存为32 GB的高端服务器上运行这一切。不幸的是，这些修改没有起到多大作用，现在我遇到了每次查询都需要几分钟才能完成的问题。

有没有人对我如何优化这个过程并让它尽可能快地运行有任何建议或想法？

谢谢,

乔

Answer 1

innodb_buffer_pool_instances=8可能会以更少的开销满足您的需求。

在内存中累积了10M之后，innodb_log_buffer_size=10M将在写入innodb_log_file之前进行“缓冲”。buffer*10=log文件大小的比例是合理的。

当innodb_log_buffer_size与innodb_log_file_size相同时，您实际上没有缓冲。使buffer_size远小于日志文件大小。