时间戳

首页 > 解决方案 > 提高 mysql LOAD DATA / mysqlimport 的性能？

mysql - 提高 mysql LOAD DATA / mysqlimport 的性能？

问题描述

我CSV将 15GB（30mio 行）批处理到mysql-8数据库中。

问题：任务大约需要 20 分钟，吞吐量约为 15-20 MB/s。虽然硬盘能够以 150 MB/s 的速度传输文件。

我有一个 20GB 的 RAM 磁盘，用于保存我的 csv。导入如下：

mysqlimport --user="root" --password="pass" --local --use-threads=8 mytable /tmp/mydata.csv

这LOAD DATA在引擎盖下使用。我的目标表没有任何索引，但大约有 100 列（我无法更改）。

奇怪的是：我尝试在 中调整几个配置参数/etc/mysql/my.cnf，但它们并没有带来任何显着的改进：

log_bin=OFF
skip-log-bin
innodb_buffer_pool_size=20G
tmp_table_size=20G
max_heap_table_size=20G
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
innodb_autoinc_lock_mode=2

问题：是否LOAD DATA/mysqlimport尊重这些配置更改？还是绕过？还是我使用了正确的配置文件？

至少对变量进行选择表明它们已由 mysql 服务器正确加载。例如show variables like 'innodb_doublewrite'节目OFF

无论如何，我怎样才能进一步提高导入速度？还是我的数据库是瓶颈，没有办法克服 15-20 MB/s 的阈值？

更新：有趣的是，如果我将我的 csv 从硬盘导入 ramdisk，性能几乎相同（只是稍微好一点，但从不超过 25 MB/s）。我还测试了相同数量的行，但只有几 (5) 列。在那里我达到了大约 80 MB/s。很明显，列数是瓶颈？但是为什么更多的列会减慢这个过程呢？

标签： mysqlmariadbload-data-infile

解决方案

这可能是正常的。让我们来看看正在做的事情：

  • 正在从 RAM 磁盘读取 csv 文件，因此没有使用 IOP。
  • 你在使用 InnoDB 吗？如果是这样，数据将进入 buffer_pool。在那里构建块时，它们被标记为“脏”以最终刷新到磁盘。
  • 由于 buffer_pool 很大，但可能没有表那么大，因此在读取完所有数据之前需要刷新一些块。
  • 读完所有的数据，表完成后，脏块会逐渐刷新到磁盘。
  • 如果您有非唯一索引，它们同样会以延迟方式写入磁盘（参见“更改缓冲”）。change_buffer，默认占用buffer_pool的25%。

结果表有多大？它可能比 csv 文件的 15GB 大得多，甚至更小。

将 csv 文件放入 ram 磁盘需要多少时间？我认为那是浪费时间，应该在执行LOAD DATA;时从磁盘读取它。I/O 可以重叠。

请SHOW GLOBAL VARIABLES LIKE 'innodb%';; 还有其他几个可能相关。

更多的

这些太可怕了：

tmp_table_size=20G
max_heap_table_size=20G

如果您有一个复杂的查询，可以在 RAM 中分配 20GB，可能是多次！将它们保持在 RAM 的 1% 以下。

如果将 csv 从硬盘复制到 ram 磁盘运行缓慢，我会怀疑 150 MB/s 的有效性。

如果您每 6 小时加载一次表，并且需要 1/3 小时来执行，我不认为加快它的紧迫性。OTOH，可能有一些值得研究的东西。如果由于表被锁定而导致 20 分钟停机，则可以轻松消除：

CREATE TABLE t LIKE real_table;
LOAD DATA INFILE INTO t ...;    -- not blocking anyone
RENAME TABLE real_table TO old, t TO real_table;  -- atomic; fast
DROP TABLE old;

推荐阅读