构build一个1,000M行的MySQL表

这个问题是根据评论中的build议从Stack Overflow转载的，对于重复的道歉。

问题

问题1：随着数据库表的大小变大，我如何调整MySQL来增加LOAD DATA INFILE调用的速度？

问题2：会用一组电脑来加载不同的csv文件，提高性能还是杀了它？ （这是我明天使用加载数据和批量插入的基准testing任务）

目标

我们正在尝试不同的特征检测器和图像search的聚类参数组合，因此我们需要能够及时构build大型数据库。

• 最大化rsync性能和吞吐量 – 直接连接的千兆服务器
• 如何确定dd命令的最佳字节大小
• 针对仅为静态内容提供服务的站点的IIS 7.5优化
• ServerLimit，MaxClients，MaxRequestsPerChild指令的最佳值
• 有效同步100万个文件与远程服务器的选项？

机器信息

如果有一种方法可以通过分发数据库来提高创build时间，那么机器上有256个ram，还有另外2台机器可以使用相同数量的ram？

表格模式

表模式看起来像

+---------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+----------------+ | match_index | int(10) unsigned | NO | PRI | NULL | | | cluster_index | int(10) unsigned | NO | PRI | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | tfidf | float | NO | | 0 | | +---------------+------------------+------+-----+---------+----------------+ 

用…创造

  CREATE TABLEtesting 
 （
   match_index INT UNSIGNED NOT NULL，
   cluster_index INT UNSIGNED NOT NULL， 
   id INT NOT NULL AUTO_INCREMENT，
   tfidf FLOAT NOT NULL DEFAULT 0，
  唯一键（id），
   PRIMARY KEY（cluster_index，match_index，id）
 ）发动机= innodb的; 

标杆到目前为止

第一步是比较批量插入与从二进制文件加载到空表中。

 It took: 0:09:12.394571 to do 4,000 inserts with 5,000 rows per insert 
 It took: 0:03:11.368320 seconds to load 20,000,000 rows from a csv file 

鉴于性能的差异，我已经加载数据从二进制CSV文件，首先我加载二进制文件包含100K，1M，20M，200M行使用下面的调用。

 LOAD DATA INFILE '/mnt/tests/data.csv' INTO TABLE test; 

我在2小时后杀死了200M行二进制文件（〜3GB csv文件）。

所以我运行了一个脚本来创build表格，然后从二进制文件中插入不同数量的行，然后删除表格，参见下面的图表。

从二进制文件中插入1M行耗时约7秒。 接下来，我决定每次插入1M行进行基准testing，看看在特定的数据库大小是否会出现瓶颈。 一旦数据库命中大约59M行，平均插入时间降到5000 /秒左右

设置全局key_buffer_size = 4294967296稍微提高了插入较小二进制文件的速度。 下图显示了不同行数的速度

但是，插入1M行不会改善性能。

行数：1,000,000时间：0：04：13.761428插入/秒：3,940

vs空数据库

行数：1,000,000时间：0：00：6.339295插入/秒：315,492

更新

使用以下顺序执行加载数据，而只使用load data命令

 SET autocommit=0; SET foreign_key_checks=0; SET unique_checks=0; LOAD DATA INFILE '/mnt/imagesearch/tests/eggs.csv' INTO TABLE test_ClusterMatches; SET foreign_key_checks=1; SET unique_checks=1; COMMIT; 

因此，就正在生成的数据库大小而言，这看起来很有希望，但其他设置似乎不会影响加载数据infile调用的性能。

然后，我尝试从不同的机器加载多个文件，但负载数据infile命令locking表，由于文件的大尺寸导致其他机器超时

 ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction 

增加二进制文件中的行数

 rows: 10,000,000 seconds rows: 0:01:36.545094 inserts/sec: 103578.541236 rows: 20,000,000 seconds rows: 0:03:14.230782 inserts/sec: 102970.29026 rows: 30,000,000 seconds rows: 0:05:07.792266 inserts/sec: 97468.3359978 rows: 40,000,000 seconds rows: 0:06:53.465898 inserts/sec: 96743.1659866 rows: 50,000,000 seconds rows: 0:08:48.721011 inserts/sec: 94567.8324859 rows: 60,000,000 seconds rows: 0:10:32.888930 inserts/sec: 94803.3646283 

解决scheme：在MySQL之外预先计算id，而不是使用自动增量

build立与表

 CREATE TABLE test ( match_index INT UNSIGNED NOT NULL, cluster_index INT UNSIGNED NOT NULL, id INT NOT NULL , tfidf FLOAT NOT NULL DEFAULT 0, PRIMARY KEY(cluster_index,match_index,id) )engine=innodb; 

与SQL

 LOAD DATA INFILE '/mnt/tests/data.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';" 

在脚本预先计算索引时，随着数据库大小的增长，似乎已经消除了性能问题。

更新2 – 使用内存表

大约快3倍，而不考虑将内存表移动到基于磁盘的表的成本。

 rows: 0 seconds rows: 0:00:26.661321 inserts/sec: 375075.18851 rows: 10000000 time: 0:00:32.765095 inserts/sec: 305202.83857 rows: 20000000 time: 0:00:38.937946 inserts/sec: 256818.888187 rows: 30000000 time: 0:00:35.170084 inserts/sec: 284332.559456 rows: 40000000 time: 0:00:33.371274 inserts/sec: 299658.922222 rows: 50000000 time: 0:00:39.396904 inserts/sec: 253827.051994 rows: 60000000 time: 0:00:37.719409 inserts/sec: 265115.500617 rows: 70000000 time: 0:00:32.993904 inserts/sec: 303086.291334 rows: 80000000 time: 0:00:33.818471 inserts/sec: 295696.396209 rows: 90000000 time: 0:00:33.534934 inserts/sec: 298196.501594 

通过将数据加载到基于内存的表格中，然后将其复制到基于磁盘的表格中，具有10分钟59.71秒的开销以复制具有查询的107,356,741行

 insert into test Select * from test2; 

这使得加载100M行大约需要15分钟，这与将其直接插入到基于磁盘的表中大致相同。