建構 1000M 行 MySQL 表

這個問題是根據評論中的建議從Stack Overflow重新發布的，為重複道歉。

問題

問題 1：隨著數據庫表的大小變大，如何調整 MySQL 以提高 LOAD DATA INFILE 呼叫的速度？

問題2：使用電腦集群載入不同的csv文件，提高性能還是殺死它？（這是我明天使用載入數據和批量插入的基準測試任務）

目標

我們正在嘗試圖像搜尋的特徵檢測器和分群參數的不同組合，因此我們需要能夠及時建構大型數據庫。

機器資訊

這台機器有 256 gig 的 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 TABLE test 
(
 match_index INT UNSIGNED NOT NULL,
 cluster_index INT UNSIGNED NOT NULL, 
 id INT NOT NULL AUTO_INCREMENT,
 tfidf FLOAT NOT NULL DEFAULT 0,
 UNIQUE KEY (id),
 PRIMARY KEY(cluster_index,match_index,id)
)engine=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 文件）載入。

所以我執行了一個腳本來創建表，並從二進製文件中插入不同數量的行，然後刪除表，見下圖。

從二進製文件中插入 1M 行大約需要 7 秒。接下來，我決定對一次插入 1M 行進行基準測試，以查看特定數據庫大小是否會出現瓶頸。一旦數據庫達到大約 59M 行，平均插入時間就會下降到大約 5,000/秒

設置全域 key_buffer_size = 4294967296 略微提高了插入較小二進製文件的速度。下圖顯示了不同行數的速度

但是對於插入 1M 行，它並沒有提高性能。

行：1,000,000 時間：0:04:13.761428 插入/秒：3,940

vs 對於空數據庫

行：1,000,000 時間：0:00:6.339295 插入/秒：315,492

更新

使用以下順序執行載入數據與僅使用載入數據命令

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;

因此，就正在生成的數據庫大小而言，這看起來很有希望，但其他設置似乎不會影響 load data infile 呼叫的性能。

然後我嘗試從不同的機器載入多個文件，但是 load data infile 命令鎖定了表，因為文件的大小導致其他機器超時

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

解決方案：在 MySQL 之外預先計算 id 而不是使用自動增量

用

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

通過將數據載入到基於記憶體的表中，然後將其以塊的形式複製到基於磁碟的表中，使用查詢複製 107,356,741 行的成本為 10 分 59.71 秒

insert into test Select * from test2;

這使得載入 100M 行大約需要 15 分鐘，這與直接將其插入基於磁碟的表中大致相同。

好問題 - 很好解釋。

如何調整 MySQL 以提高 LOAD DATA INFILE 呼叫的速度？

您已經為密鑰緩衝區設置了高（ish）設置 - 但這是否足夠？我假設這是一個 64 位安裝（如果不是，那麼您需要做的第一件事就是升級）並且不在 MSNT 上執行。執行一些測試後查看 mysqltuner.pl 的輸出。

為了以最佳效果使用記憶體，您可能會發現對輸入數據進行批處理/預排序的好處（“排序”命令的最新版本具有很多用於對大型數據集進行排序的功能）。此外，如果您在 MySQL 之外生成 ID 號，那麼它可能會更有效。

將使用一組電腦來載入不同的 csv 文件

假設（再次）您希望輸出集表現為單個表，那麼您將獲得的唯一好處是通過分配排序和生成 id 的工作 - 您不需要更多的數據庫。OTOH 使用數據庫集群，您將遇到爭用問題（除了性能問題，您不應該將其視為性能問題）。

如果您可以對數據進行分片並獨立處理生成的數據集，那麼是的，您將獲得性能優勢——但這並不能否定調整每個節點的需要。

檢查您的 sort_buffer_size 至少有 4 Gb。

除此之外，性能的限制因素都與磁碟 I/O 有關。有很多方法可以解決這個問題 - 但您可能應該考慮在 SSD 上鏡像一組條帶數據集以獲得最佳性能。

引用自：https://serverfault.com/questions/317643