昨日の記事は @kabaome さんによる 拡張統計情報とテーブル結合 でした。
本エントリでは、PostgreSQLのカラムナーDB拡張である cstore_fdw について、その基本的な使い方から、 DBT-3 のスキーマとクエリを使ってベンチマークをしてみた結果を解説してみます。
とは言え、私自身、cstore_fdw をそれなりに使ったのはこれが初めてですので、あまり深く踏み込めていないところもあるかと思いますが、そういったところがありましたら、コメント欄や Twitter などで補足いただけると助かります。
■cstore_fdw とは
cstore_fdw は Citus Data によって開発されているオープンソースの PostgreSQL 拡張で、PostgreSQL 本体に手を加えなくてもカラムストア型（カラムナー型）の備えたテーブルを利用することができるようになるものです。
cstore_fdw では、テーブルを外部テーブルとして定義することによって、 PostgreSQL のオリジナルのストレージ構造をバイパスして、独自のストレージフォーマットを持つテーブルを保持することができるようになっています。
■cstore_fdw のセットアップ
cstore_fdw のインストールの手順は、他のエクステンションや FDW と変わりません。Github から clone してきて、 make install します。
# git clone https://github.com/citusdata/cstore_fdw.git # env USE_PGXS=1 make install
実際にデータベースに組み込む前に postgresql.conf の shared_prealod_libraries に cstore_fdw を設定しておく必要があります。
shared_preload_libraries = 'cstore_fdw' # (change requires restart)
■cstore_fdw の基本的な使い方
それでは、 cstore_fdw を実際に使ってみましょう。ここでは cstore_fdw のドキュメントにあるサンプルをベースに進めます。
まず、Citus で提供しているサンプルの CSV ファイルをダウンロードして準備します。
$ wget http://examples.citusdata.com/customer_reviews_1998.csv.gz $ gzip -d customer_reviews_1998.csv.gz
次に、データベースに cstore_fdw のエクステンションをインストールし、FDW 用のサーバの設定を行います。
CREATE EXTENSION cstore_fdw; CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
そして、通常の PostgreSQL のテーブルとカラムストアのテーブルをそれぞれ作成します。
CREATE TABLE customer_reviews
(
customer_id TEXT,
review_date DATE,
review_rating INTEGER,
review_votes INTEGER,
review_helpful_votes INTEGER,
product_id CHAR(10),
product_title TEXT,
product_sales_rank BIGINT,
product_group TEXT,
product_category TEXT,
product_subcategory TEXT,
similar_product_ids CHAR(10)[]
);
CREATE FOREIGN TABLE customer_reviews_cstore
(
customer_id TEXT,
review_date DATE,
review_rating INTEGER,
review_votes INTEGER,
review_helpful_votes INTEGER,
product_id CHAR(10),
product_title TEXT,
product_sales_rank BIGINT,
product_group TEXT,
product_category TEXT,
product_subcategory TEXT,
similar_product_ids CHAR(10)[]
)
SERVER cstore_server
OPTIONS(filename '/tmp/customer_reviews.cstore',
compression 'pglz');
先に言及したように、 cstore_fdw では FDW の仕組みを使うことによって PostgreSQL のストレージをバイパスしてカラムストアのテーブルを実現していますので、外部テーブル（FOREIGN TABLE）として作成します。
そして、そのカラムストアのテーブルの実体は、ここでは /tmp/customer_review.cstore となります。「compression 'pglz'」はカラムストアのテーブルを圧縮する、というオプションです。
テーブルの作成が終わったら、通常のテーブルと同じように COPY コマンドを使って先ほどの CSV ファイルをそれぞれのテーブルにロードします。
COPY customer_reviews FROM '/tmp/customer_reviews_1998.csv' WITH CSV; COPY customer_reviews_cstore FROM '/tmp/customer_reviews_1998.csv' WITH CSV;
データのロードが終わったら、それぞれのテーブルに ANALYZE を実行します。
ANALYZE customer_reviews; ANALYZE customer_reviews_cstore;
さて、おもむろに count(*) を実行して、実行コストと実行時間を見てみましょう。
通常のテーブルに対して SELECT COUNT(*) を EXPLAIN ANALYZE で実行したのが以下の結果です。
postgres=# explain analyze select count(*) from customer_reviews;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=21832.40..21832.41 rows=1 width=8) (actual time=289.804..289.804 rows=1 loops=1)
-> Gather (cost=21832.18..21832.39 rows=2 width=8) (actual time=284.375..290.013 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=20832.18..20832.19 rows=1 width=8) (actual time=275.572..275.573 rows=1 loops=3)
-> Parallel Seq Scan on customer_reviews (cost=0.00..20217.75 rows=245775 width=0) (actual time=0.040..194.432 rows=196620 loops=3)
Planning time: 0.060 ms
Execution time: 290.072 ms
(8 rows)
同様に、カラムストアのテーブルに対して同じクエリを実行したものが以下の結果になります。
postgres=# explain analyze select count(*) from customer_reviews_cstore;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7373.24..7373.25 rows=1 width=8) (actual time=185.477..185.477 rows=1 loops=1)
-> Foreign Scan on customer_reviews_cstore (cost=0.00..5898.59 rows=589859 width=0) (actual time=0.058..97.149 rows=589859 loops=1)
CStore File: /tmp/customer_reviews.cstore
CStore File Size: 35505490
Planning time: 0.074 ms
Execution time: 185.650 ms
(6 rows)
見ると分かる通り、カラムストアのテーブルでは実行コストが 1/3 程度になり、実行時間も半分強になっています。
このように、カラムストアのテーブルでは（クエリの内容によっては）実行時間を短縮することができます。
■DBT-3 を使った性能評価
さて、最後に DBT-3 のスキーマとクエリを使って性能を比較してみます。（DBT-3 はデータ分析系のワークロードの性能評価するためのオープンソース実装のツールです。）
cstore_fdw のベンチマーク結果はネット上でもいくつか見つかるのですが、そういった情報は総じて「速くなったクエリだけを抜き出して性能をアピール」しているものが多く、そういった評価はユーザの立場から見て必ずしも十分な情報とは言えないように思います。
DBT-3 ベンチマークには 1 ～ 22 までの 22 種類のクエリがありますので、それらをすべて実行して比較することで、さまざまなクエリにおける性能を確認するとともに、全体の傾向などを把握したいと思います。
評価に使った環境は以下の通りです。
- ThinkPad X Carbon
- VirtualBox (VM 2GB RAM, 1CPU)
- CentOS 7
- PostgreSQL 10.6
- DBT-3 (SF=1, DBサイズ 約1GB)
- DBT-3 のクエリ 22 種 + データロード
実行するクエリは、以下のレポジトリのコードを使いました。
また、測定のルールは以下の通りです。
- データローディングについては、通常/カラムストア、それぞれ 1 回だけ計測。
- その他のクエリについては、 6 回計測し、最初の 1 回を除いた 5 回分を平均。
- （時間の都合上）クエリの実行時間の上限を 10 分に設定し、それを越えたクエリは無効とする。
通常のテーブルに対するカラムストアのテーブルの処理性能の倍率ですので、グラフは高くなっている方が、よりカラムストアの方が高速であることを示しています。
これを見ると、50 倍近く速くなっているクエリもある一方で、10 倍以下だったりするクエリもあります。
なお、グレーアウトされているクエリの「17」と「20」は、通常テーブルおよびカラムストアの両方が 10 分のタイムアウトを越えたため、無効となっています。
上記のグラフをもう少し細かく見てみたのが以下のグラフです。（縦軸の最大値を10、つまり 10 倍に設定）
これを見ると、通常のテーブルと比べてカラムストアの方が遅くなっている（1倍を割っている）のは、
- ローディング
- クエリ5、クエリ7、クエリ16、クエリ19
（データロードを除いた）クエリ全体を平均すると、カラムストアのテーブルの方が通常のテーブルよりも約 5.17 倍高速、という結果になりました。
■まとめ
以上、駆け足になりましたが、 cstore_fdw の使い方、および DBT-3 を使ったカラムナー型テーブルの性能を見てきました。
カラムナーテーブルは、あらゆるクエリが高速化されるものではありませんが、今回見てきたように、アナリティクス系のクエリでは高速化される可能性が高くなります。
ぜひ、PostgreSQL でアナリティクスの世界に挑戦してみていただければと思います。
では、また。
PostgreSQL Advent Calendar 2018、明日の担当は kitayama_t さんです。
0 件のコメント:
コメントを投稿