標籤:duration pool cpu佔用 讀取 簡單的 nec stat dump lin
Postgresql快速寫入/讀取大量資料http://www.cnblogs.com/podolski/p/7152144.html
環境及測試
使用.net驅動npgsql串連post資料庫。配置:win10 x64, i5-4590, 16G DDR3, SSD 850EVO.
postgresql 9.6.3,資料庫與資料都安裝在SSD上,預設配置,無擴充。
CREATE TABLE public.mesh
(
x integer NOT NULL,
y integer NOT NULL,
z integer,
CONSTRAINT prim PRIMARY KEY (x, y)
)
- 匯入
使用資料備份,csv格式匯入,檔案位於機械硬碟上,480MB,資料量2500w+。
使用COPY
copy mesh from ‘d:/user.csv‘ csv
已耗用時間107s
使用insert
單串連,c# release any cpu 非偵錯模式。
class Program
{
static void Main(string[] args)
{
var list = GetData("D:\user.csv");
TimeCalc.LogStartTime();
using (var sm = new SqlManipulation(@"Strings", SqlType.PostgresQL))
{
sm.Init();
foreach (var n in list)
{
sm.ExcuteNonQuery($"insert into mesh(x,y,z) values({n.x},{n.y},{n.z})");
}
}
TimeCalc.ShowTotalDuration();
Console.ReadKey();}static List<(int x, int y, int z)> GetData(string filepath){ List<ValueTuple<int, int, int>> list = new List<(int, int, int)>(); foreach (var n in File.ReadLines(filepath)) { string[] x = n.Split(','); list.Add((Convert.ToInt32(x[0]), Convert.ToInt32(x[1]), Convert.ToInt32(x[2]))); } return list;}
}
Postgresql CPU佔用率很低,但是跑了一年,程式依然不能結束,沒有耐性了...,這麼插入不行。
multiline insert
使用multiline插入,一條語句插入約100條資料。
var bag = GetData("D:\user.csv");
//使用時,直接執行stringbuilder的tostring方法。
List listbuilder = new List();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < bag.Count; i++)
{
if (i % 100 == 0)
{
sb = new StringBuilder();
listbuilder.Add(sb);
sb.Append("insert into mesh(x,y,z) values");
sb.Append($"({bag[i].x}, {bag[i].y}, {bag[i].z})");
}
else
sb.Append($",({bag[i].x}, {bag[i].y}, {bag[i].z})");
}
Postgresql CPU佔用率差不多27%,磁碟寫入大約45MB/S,感覺就是在幹活,最後時間217.36s。
改為1000一行的話,CPU佔用率提高,但是磁碟寫入平均來看有所降低,最後時間160.58s.
prepare文法
prepare文法可以讓postgresql提前規劃sql,最佳化效能。
使用單行插入 CPU佔用率不到25%,磁碟寫入63MB/S左右,但是,使用單行插入的方式,效率沒有改觀,時間太長還是等不來結果。
使用多行插入 CPU佔用率30%,磁碟寫入50MB/S,最後結果163.02,最後的時候出了個異常,就是最後一組資料長度不滿足條件,無傷大雅。
static void Main(string[] args)
{
var bag = GetData("D:\user.csv");
List listbuilder = new List();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < bag.Count; i++)
{
if (i % 1000 == 0)
{
sb = new StringBuilder();
listbuilder.Add(sb);
//sb.Append("insert into mesh(x,y,z) values");
sb.Append($"{bag[i].x}, {bag[i].y}, {bag[i].z}");
}
else
sb.Append($",{bag[i].x}, {bag[i].y}, {bag[i].z}");
}
StringBuilder sbp = new StringBuilder();
sbp.Append("PREPARE insertplan (");
for (int i = 0; i < 1000; i++)
{
sbp.Append("int,int,int,");
}
sbp.Remove(sbp.Length - 1, 1);
sbp.Append(") AS INSERT INTO mesh(x, y, z) values");
for (int i = 0; i < 1000; i++)
{
sbp.Append($"(${i3 + 1},${i 3 + 2},${i*3+ 3}),");
}
sbp.Remove(sbp.Length - 1, 1);
TimeCalc.LogStartTime();
using (var sm = new SqlManipulation(@"string", SqlType.PostgresQL)){ sm.Init(); sm.ExcuteNonQuery(sbp.ToString()); foreach (var n in listbuilder) { sm.ExcuteNonQuery($"EXECUTE insertplan({n.ToString()})"); }}TimeCalc.ShowTotalDuration();Console.ReadKey();
}
使用Transaction
在前面的基礎上,使用事務改造。每條語句插入1000條資料,每1000條作為一個事務,CPU 30%,磁碟34MB/S,耗時170.16s。
改成100條一個事務,耗時167.78s。
使用多線程
還在前面的基礎上,使用多線程,每個線程建立一個串連,一個串連處理100條sql語句,每條sql語句插入1000條資料,以此種方式進行匯入。注意,連接字串可以將maxpoolsize設定大一些,我機器上實測,不設定會報連線逾時錯誤。
CPU佔用率上到80%, 磁碟這裡需要注意,由於產生了非常多個Postgresql server進程,不好統計,累積算上應該有小100MB/S,最終時間,98.18s。
使用TPL,由於Parallel.ForEach返回的結果沒有檢查,可能導致時間不是很準確(偏小)。
var lists = new List>();
var listt = new List();
for (int i = 0; i < listbuilder.Count; i++)
{
if (i % 1000 == 0)
{
listt = new List();
lists.Add(listt);
}
listt.Add(listbuilder[i].ToString());
}
TimeCalc.LogStartTime();
Parallel.ForEach(lists, (x) =>
{
using (var sm = new SqlManipulation(@";string;MaxPoolSize=1000;", SqlType.PostgresQL))
{
sm.Init();
foreach (var n in x)
{
sm.ExcuteNonQuery(n);
}
}
});
TimeCalc.ShowTotalDuration();
寫入方式 耗時(1000條/行)
COPY 107s
insert N/A
多行insert 160.58s
prepare多行insert 163.02s
事務多行insert 170.16s
多串連多行insert 98.18s
- 寫入更新
資料即時更新,數量可能繼續增長,使用簡單的insert或者update是不行的,操作使用postgresql 9.5以後支援的新文法。
insert into mesh on conflict (x,y) do update set z = excluded.z
吐槽postgresql這麼晚才支援on conflict,mysql早有了...
在表中既有資料2500w+的前提下,重複往資料庫裡面寫這些資料。這裡只做多行插入更新測試,其他的結果應該差不多。
普通多行插入,耗時272.15s。
多線程插入的情況,耗時362.26s,CPU佔用率一度到了100%。猜測多串連的情況下,更新互鎖導致效能下降。
- 讀取
Select方法
標準讀取還是用select方法,ADO.NET直接讀取。
使用adapter方式,耗時135.39s;使用dbreader方式,耗時71.62s。
Copy方法
postgresql的copy方法提供stdout binary方式,可以指定一條查詢進行輸出,耗時53.20s。
public List<(int x, int y, int z)> BulkIQueryNpg()
{
List<(int, int, int)> dict = new List<(int, int, int)>();
using (var reader = ((NpgsqlConnection)_conn).BeginBinaryExport("COPY (select x,y,z from mesh) TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() != -1)
{
var x = reader.Read(NpgsqlDbType.Integer);
var y = reader.Read(NpgsqlDbType.Integer);
var z = reader.Read(NpgsqlDbType.Integer);
dict.Add((x, y, z));
}
}
return dict;
}
結論
總結測試結果,對於較多資料的情況下,可以得出以下結論:
向空資料表匯入或者沒有重複資料表的匯入,優先使用COPY語句(為什麼有這個前提詳見P.S.);
使用一條語句插入多條資料的方式能夠大幅度改善插入效能,可以實驗確定最優條數;
使用transaction或者prepare插入,在本情境中最佳化效果不明顯;
使用多串連/多線程操作,速度上有優勢,但是把握不好容易造成資源佔用率過高,串連數太大也容易影響其他應用;
寫入更新是postgresql新特性,使用會造成一定的效能消耗(相對直接插入);
讀取資料時,使用COPY語句能夠獲得較好的效能;
ado.net dbreader對象由於不需要fill的過程,讀取速度也較快(雖然趕不上COPY),也可優先考慮。
P.S.
為什麼不用mysql
沒有最好的,只有最合適的,講道理我也是挺喜歡用mysql的。使用postgresql的原因主要在於:
postgresql匯入匯出的sql指令“copy”直接支援Binary模式到stdin和stdout,如果程式想直接整合,那麼用這個是比較方便的;相比較,mysql的sql文法(load data infile)並不支援到stdin或者stdout,匯出可以通過mysqldump.exe實現,匯入暫時沒什麼特別好的辦法(mysqlimport或許可以)。
相較於mysql缺點
postgresql使用copy匯入的時候,如果目標表已經有資料,那麼在有主鍵約束的表遇到錯誤時,COPY自動終止,而且可能導致不完全插入的情況,換言之,是不支援匯入的過程進行update操作;mysql的load文法可以顯式指定出錯之後的動作(IGNORE/REPLACE),不會打斷匯入處理程序。
其他
如果需要使用mysql從程式匯入資料,可以考慮先通過程式匯出到檔案,然後藉助檔案進行匯入,據說效率也要比insert高出不少。
net快速寫入/讀取大量資料Postgresql