提高mysql加载数据/ mysqlimport的性能？

Question

我将15‘m (30mio行)的CSV批处理到一个mysql-8数据库中。

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

我有一个20 my的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是否尊重这些配置更改？或者它绕过了？或者我根本没有使用正确的配置文件？

至少在变量上的select表明它们被mysql服务器正确加载。例如，show variables like 'innodb_doublewrite'显示OFF

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

更新:有趣的是，如果我将csv从硬盘导入到内存中，性能几乎相同(只是稍微好一点，但永远不会超过25MB/s)。我也测试了相同数量的行，但只有几(5)列。这样我就可以达到大约80MB/s。那么很明显，列数是瓶颈？但是为什么更多的列会减慢这个过程呢？

Answer 1

MySQL/MariaDB引擎在进行批量插入时几乎没有并行化。每条LOAD DATA语句只能使用一个CPU核心。您可能会在负载期间监视CPU利用率，以查看一个核心是否被充分利用，并且它只能提供如此多的输出数据-从而使磁盘吞吐量未得到充分利用。

最新版本的MySQL具有新的并行加载特性：https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-parallel-table.html。它看起来很有希望，但可能还没有收到太多反馈。我不确定这对你的情况会有帮助。

我在互联网上看到了各种清单，建议在以下配置参数中设置更高的值：log_buffer_size、log_file_size、write_io_threads、bulk_insert_buffer_size。但当我执行比较测试时，好处并不是非常明显(可能比仅仅是innodb_buffer_pool_size足够大快10-20% )。

Answer 2

这可能是正常的。让我们来看看正在做些什么：

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

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

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

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

More

这些太可怕了

tmp_table_size=20G
max_heap_table_size=20G

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

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

如果您每6个小时加载一次表，并且需要1/3个小时才能执行，我看不出提高速度的紧迫性。哦，也许有什么值得一查的。如果这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;