这个问题是根据评论中的build议从Stack Overflow转载的,对于重复的道歉。
问题1:随着数据库表的大小变大,我如何调整MySQL来增加LOAD DATA INFILE调用的速度?
问题2:会用一组电脑来加载不同的csv文件,提高性能还是杀了它? (这是我明天使用加载数据和批量插入的基准testing任务)
我们正在尝试不同的特征检测器和图像search的聚类参数组合,因此我们需要能够及时构build大型数据库。
如果有一种方法可以通过分发数据库来提高创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
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';"
在脚本预先计算索引时,随着数据库大小的增长,似乎已经消除了性能问题。
大约快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分钟,这与将其直接插入到基于磁盘的表中大致相同。
好问题 – 很好的解释。
我如何调整MySQL以提高LOAD DATA INFILE调用的速度?
你已经有了关键缓冲区的高(ISH)设置 – 但是这足够了吗? 我假设这是一个64位安装(如果不是那么你需要做的第一件事是升级),而不是在MSNT上运行。 运行一些testing后,看看mysqltuner.pl的输出。
为了最大限度地利用caching,您可能会发现在对input数据进行批处理/预sorting方面的优势(最近版本的“sorting”命令对于sorting大型数据集有很多function)。 另外,如果你在MySQL之外生成ID号码,那么它可能更有效率。
会使用一组计算机来加载不同的csv文件
假设(再一次)你想让输出集像单个表一样工作,那么你将得到的唯一好处是通过分配sorting和生成id的工作 – 你不需要更多的数据库。 OTOH使用数据库集群时,会遇到争用问题(除了性能问题,您不应该看到这些问题)。
如果您可以分割数据并独立处理结果数据集,那么是的,您将获得性能优势 – 但这并不否定需要调整每个节点。
检查你的sort_buffer_size是否至less有4GB。
除此之外,性能的限制因素全是关于磁盘I / O。 有很多方法可以解决这个问题,但是您应该考虑在SSD上使用镜像条带化数据集来获得最佳性能。
load data...
比插入更快,所以使用它。 如果你想要真的很漂亮,你可以创build一个multithreading程序,将单个文件提供给一组命名pipe道,并pipe理插入实例。
总而言之,您不需要调整MySQL,就可以将您的工作负载调整为MySQL。
我不记得确切的syntacx,但如果它是inno分贝你可以closures外键检查。
你也可以在导入后创build索引,这可以是一个真正的性能增益。