この研究では、PostgreSQL ではなく ClickHouse データ ソースを使用することでどのようなパフォーマンスの向上が達成できるかを確認したいと思いました。 ClickHouse を使用することで生産性が向上することを私は知っています。外部データ ラッパー (FDW) を使用して PostgreSQL から ClickHouse にアクセスした場合でも、これらの利点は継続しますか?
調査したデータベース環境は、PostgreSQL v11、clickhousedb_fdw、および ClickHouse データベースです。最終的に、PostgreSQL v11 からは、clickhousedb_fdw を介して ClickHouse データベースにルーティングされたさまざまな SQL クエリを実行することになります。次に、ネイティブ PostgreSQL およびネイティブ ClickHouse で実行されている同じクエリと FDW のパフォーマンスがどのように比較されるかを見ていきます。
クリックハウスデータベース
ClickHouse は、従来のデータベース アプローチよりも 100 ～ 1000 倍高速なパフォーマンスを達成できるオープン ソースの列型データベース管理システムで、XNUMX 秒未満で XNUMX 億行以上を処理できます。
クリックハウスdb_fdw
clickhousedb_fdw - ClickHouse データベースの外部データ ラッパー (FDW) は、Percona のオープン ソース プロジェクトです。 .
.
ご覧のとおり、これにより、PostgreSQL v11 サーバーから ClickHouse データベースへの SELECT および INSERT INTO を可能にする ClickHouse 用の FDW が提供されます。
FDW は、集約や結合などの高度な機能をサポートしています。これにより、リソースを大量に消費する操作にリモート サーバーのリソースが使用されるため、パフォーマンスが大幅に向上します。
ベンチマーク環境
- Supermicro サーバー:
- インテル® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2ソケット/28コア/56スレッド
- メモリ：RAMの256GB
- ストレージ: Samsung SM863 1.9TB エンタープライズ SSD
- ファイルシステム: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: バージョン 11
ベンチマークテスト
このテストでは機械生成されたデータセットを使用する代わりに、1987 年から 2018 年までの「報告されたオペレーター時間による時間別生産性」データを使用しました。データにアクセスできます .
データベースのサイズは 85 GB で、109 列からなる XNUMX つのテーブルを提供します。
ベンチマーククエリ
ClickHouse、clickhousedb_fdw、PostgreSQL を比較するために使用したクエリを次に示します。
Q#
クエリに集計とグループ化が含まれる
Q1
SELECT DayOfWeek、count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
SELECT DayOfWeek、count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
SELECT キャリア、カウント() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() 説明;
Q5
SELECT a.キャリア、c、c2、c1000/c2 as c3 FROM ( SELECT キャリア、カウント() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN (SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b on a.Carrier=b.Carrier ORDER c3 DESC による;
Q6
SELECT a.キャリア、c、c2、c1000/c2 as c3 FROM ( SELECT キャリア、カウント() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;
Q8
ontime GROUP BY Year から年、avg(DepDelay) を選択します。
Q9
年ごとにオンタイム グループから年、カウント (*) を c1 として選択します。
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
select avg(c1) from (ontime グループから年、月、カウント (*) を年、月ごとに c1 として選択) a;
Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
クエリに結合が含まれています
Q14
SELECT a.年, c1/c2 FROM ( select 年, count()ontime の c1000 として 1 WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (年を選択し、c2 として count(*) from ontime GROUP BY Year ) b on a. Year=b. Year ORDER BY a. Year;
Q15
SELECT a.”年”, c1/c2 FROM ( select “年”, count()1000 as c1 FROM fontime WHERE “DepDelay”>10 GROUP BY “年”) a INNER JOIN (“年”を選択、c2 FROM fontime GROUP BY “年” として count(*) ) b on a.“年”=b。 "年";
表-1: ベンチマークで使用されるクエリ
クエリの実行
以下は、さまざまなデータベース設定で実行した場合の各クエリの結果です: インデックスありおよびインデックスなしの PostgreSQL、ネイティブ ClickHouse および clickhousedb_fdw。時間はミリ秒単位で表示されます。
Q#
PostgreSQL
PostgreSQL (インデックス付き)
クリックハウス
クリックハウスデータベース_fdw
Q1
27920
19634
23
57
Q2
35124
17301
50
80
Q3
34046
15618
67
115
Q4
31632
7667
25
37
Q5
47220
8976
27
60
Q6
58233
24368
55
153
Q7
30566
13256
52
91
Q8
38309
60511
112
179
Q9
20674
37979
31
81
Q10
34990
20102
56
148
Q11
30489
51658
37
155
Q12
39357
33742
186
1333
Q13
29912
30709
101
384
Q14
54126
39913
124
1364212
Q15
97258
30211
245
259
表-1: ベンチマークで使用されたクエリの実行にかかる時間
結果を見る
グラフはクエリの実行時間をミリ秒単位で示し、X 軸は上記の表のクエリ番号を示し、Y 軸は実行時間をミリ秒単位で示します。 ClickHouse の結果と、clickhousedb_fdw を使用して postgres から取得したデータが表示されます。表から、PostgreSQL と ClickHouse の間には大きな違いがありますが、ClickHouse と clickhousedb_fdw の間には最小限の違いがあることがわかります。
このグラフは、ClickhouseDB と clickhousedb_fdw の違いを示しています。ほとんどのクエリでは、FDW オーバーヘッドはそれほど高くなく、Q12 を除いてほとんど重大ではありません。このクエリには結合と ORDER BY 句が含まれています。 ORDER BY GROUP/BY 句があるため、ORDER BY は ClickHouse にドロップダウンされません。
表 2 では、クエリ Q12 と Q13 でのタイムジャンプがわかります。繰り返しますが、これは ORDER BY 句によって引き起こされます。これを確認するために、ORDER BY 句を使用した場合と使用しない場合でクエリ Q-14 と Q-15 を実行しました。 ORDER BY 句を使用しない場合、完了時間は 259 ミリ秒ですが、ORDER BY 句を使用すると、完了時間は 1364212 になります。このクエリをデバッグするために、両方のクエリについて説明しています。説明の結果は次のとおりです。
Q15: ORDER BY句がない場合
bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2
FROM (SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
INNER JOIN(SELECT "Year", count(*) AS c2 FROM fontime GROUP BY "Year") b ON a."Year"=b."Year";Q15: ORDER BY 句のないクエリ
QUERY PLAN
Hash Join (cost=2250.00..128516.06 rows=50000000 width=12)
Output: fontime."Year", (((count(*) * 1000)) / b.c2)
Inner Unique: true Hash Cond: (fontime."Year" = b."Year")
-> Foreign Scan (cost=1.00..-1.00 rows=100000 width=12)
Output: fontime."Year", ((count(*) * 1000))
Relations: Aggregate on (fontime)
Remote SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"
-> Hash (cost=999.00..999.00 rows=100000 width=12)
Output: b.c2, b."Year"
-> Subquery Scan on b (cost=1.00..999.00 rows=100000 width=12)
Output: b.c2, b."Year"
-> Foreign Scan (cost=1.00..-1.00 rows=100000 width=12)
Output: fontime_1."Year", (count(*))
Relations: Aggregate on (fontime)
Remote SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)Q14: ORDER BY 句を使用したクエリ
bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2 FROM(SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
INNER JOIN(SELECT "Year", count(*) as c2 FROM fontime GROUP BY "Year") b ON a."Year"= b."Year"
ORDER BY a."Year";Q14: ORDER BY 句を使用したクエリ プラン
QUERY PLAN
Merge Join (cost=2.00..628498.02 rows=50000000 width=12)
Output: fontime."Year", (((count(*) * 1000)) / (count(*)))
Inner Unique: true Merge Cond: (fontime."Year" = fontime_1."Year")
-> GroupAggregate (cost=1.00..499.01 rows=1 width=12)
Output: fontime."Year", (count(*) * 1000)
Group Key: fontime."Year"
-> Foreign Scan on public.fontime (cost=1.00..-1.00 rows=100000 width=4)
Remote SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10))
ORDER BY "Year" ASC
-> GroupAggregate (cost=1.00..499.01 rows=1 width=12)
Output: fontime_1."Year", count(*) Group Key: fontime_1."Year"
-> Foreign Scan on public.fontime fontime_1 (cost=1.00..-1.00 rows=100000 width=4)
Remote SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)出力
これらの実験の結果は、ClickHouse が非常に優れたパフォーマンスを提供し、clickhousedb_fdw が PostgreSQL から ClickHouse のパフォーマンス上の利点を提供することを示しています。 clickhousedb_fdw を使用すると多少のオーバーヘッドが発生しますが、それは無視できるものであり、ClickHouse データベースでネイティブに実行することによって達成されるパフォーマンスに匹敵します。これは、PostgreSQL の fdw が優れた結果を提供することも裏付けています。
クリックハウス経由の電報チャット
PostgreSQLを使用したテレグラムチャット
出所： habr.com