天天看點
傳回

Postgresql快速寫入/讀取大量資料（.net）

環境及測試

使用.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)
)

1. 導入

使用資料備份，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<StringBuilder> listbuilder = new List<StringBuilder>();
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<StringBuilder> listbuilder = new List<StringBuilder>();
    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($"(${i*3 + 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<List<string>>();
var listt = new List<string>();
for (int i = 0; i < listbuilder.Count; i++)
{
    if (i % 1000 == 0)
    {
        listt = new List<string>();
        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|

2. 寫入更新

資料實時更新，數量可能繼續增長，使用簡單的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%。猜測多連接配接的情況下，更新互鎖導緻性能下降。

3. 讀取

  • 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<int>(NpgsqlDbType.Integer);
            var y = reader.Read<int>(NpgsqlDbType.Integer);
            var z = reader.Read<int>(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高出不少。

除非特殊說明，本作品由podolski創作，采用知識共享署名 4.0 國際許可協定進行許可。歡迎轉載，轉載請保留原文連結~喜歡的觀衆老爺們可以點下關注或者推薦~

postgresql copy 快速
上一篇: Git學習筆記（三）
下一篇: MacBook筆記本微信視訊聊天沒有聲音怎麼辦?

繼續閱讀