Win10 x64 mysql 插入百万行数据耗时问题分析

performance - Inserting 1 Million records is taking too much time MYSQL - Stack Overflow
https://stackoverflow.com/questions/33820331/inserting-1-million-records-is-taking-too-much-time-mysql

场景：

mysql插入1百万行记录，表结构简单，16G，8核，耗时：6到 7小时。

 

Ask TOM "How to Update millions or records in a table"
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:6407993912330

Inserting about a billion rows in one table in about 4 hours
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b1476847-7897-465d-b392-bf5ca67f8ffb/inserting-about-a-billion-rows-in-one-table-in-about-4-hours

从s ... |中一次插入/选择一百万行 Oracle社区
https://community.oracle.com/thread/2460632

 

【MySQL】binlog缓存的问题和性能 - 倾斜的镜子 - 博客园
https://www.cnblogs.com/zhiqian-ali/p/5052440.html

mysqlbinlog - MySQL - max_binlog_cache_size vs binlog_cache_size - Stack Overflow
https://stackoverflow.com/questions/37813819/mysql-max-binlog-cache-size-vs-binlog-cache-size#

max_binlog_stmt_cache_size — MariaDB Enterprise Documentation
https://mariadb.com/docs/reference/mdb/system-variables/max_binlog_stmt_cache_size/

mysqlbinlog - MySQL - max_binlog_cache_size vs binlog_cache_size - Stack Overflow
https://stackoverflow.com/questions/37813819/mysql-max-binlog-cache-size-vs-binlog-cache-size#:~:text=According%20to%20it%2C%20max_binlog_cache_size%20means%2C%20If%20a%20transaction,sets%20the%20size%20for%20the%20transaction%20cache%20only

 

mysql开始binlog会影响性能吗_【MySQL】binlog缓存的问题和性能_weixin_39802055的博客-CSDN博客
https://blog.csdn.net/weixin_39802055/article/details/113221735

 

测试：

Windows 10

CPU:4 core 2.5GHz

Memory:16G

耗时超过4小时

 

Centos 6.9

耗时约20分钟

可能存在如下的坑：

max_binlog_stmt_cache_size

如果一个事务中的非事务性的语句需要大于这个数量的更多的内存，服务器会产生报错。最低值是4096 ，在32位平台最大和默认值是4GB。在64位平台最大和默认值是416EB（艾字节）。

 

Note ：Prior to MySQL 5.6.7, 64-bit Windows platforms truncated the stored value for this variable to 4G, even when it was set to a greater value (Bug #13961678).