PostgreSQLカラムストア拡張(cstore_fdw)の検証

postgresql

2016/7/22

はじめに

はじめまして、サービス開発部DBチームの本多と申します。

弊社のサービスポイントタウンではデータ分析用DBとしてPostgreSQLを使用しています。本番DBはOracleとMySQL併用で、そこから日々必要なデータを溜め込んでいます。

導入当初は特に不便も無く動いていたのですが、データが溜まりテーブルサイズが大きくなるにつれクエリ応答速度の低下に悩まされるようになってきたため、citusdata社がオープンソースで提供しているPostgreSQLのカラムストア拡張、cstore_fdwを導入してみましたので使用感を報告したいと思います。

cstore_fdwはカラムストア(列指向)テーブル、データ圧縮、SkipIndex(ストライプ毎に値の上限・下限値を持つ事で範囲検索時に不要なブロックの読み出しをSkipする(ファイルレイアウト)の3つの特徴を持つ外部テーブルをPostgreSQLに作成する事が出来ます。PostrgeSQLのforkではなくEXTENTIONの追加という形を取っているため、既存プロダクトへの導入も比較的容易です

インストール

OSはCentOS6.8、PostgreSQLは/usr/local/pgsqlに9.5.3をソースコンパイルでインストールしている前提で記載します(公式リポジトリに丁寧なドキュメントがありますが一応ご参考まで)。

◆事前にパッケージをインストール

 # yum install protobuf-c-devel (EPELリポジトリ登録が必要)

◆cstore_fdwソースコードの取得/インストール

 # cd /usr/local/src
 # git clone https://github.com/citusdata/cstore_fdw.git
 # cd /usr/local/src/cstore_fdw
 # PATH=/usr/local/pgsql/bin/:$PATH make
 # sudo PATH=/usr/local/pgsql/bin/:$PATH make install

◆postgres.confにライブラリ設定追加

 # su - postgres
 $ vi /data/pgsql/postgres.conf
 +shared_preload_libraries = 'cstore_fdw'
 設定保存後にDB再起動

◆EXTENSIONインストールと確認

 $ psql ※superuserで接続
 postgres=# \c sampledb ※対象のdatabaseに接続
 sampledb=# CREATE EXTENSION cstore_fdw;
 sampledb=# \dx
 List of installed extensions
 Name       | Version | Schema     | Description
 -----------+---------+------------+---------------------------------------------
 cstore_fdw | 1.4     | public     | foreign-data wrapper for flat cstore access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language

◆FDW向けサーバーオブジェクト作成

 postgres=# \c sampledb ※対象のdatabaseに接続
 sampledb=# CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

◆DBユーザーにサーバーオブジェクトの使用権限付与

 sampledb=# GRANT USAGE ON FOREIGN SERVER cstore_server TO sampledb;
 以降はDatabaseを所有しているユーザーで作業可能

◆外部テーブルの作成

作成した外部テーブルと通常のPostgreSQLテーブルとのJOINも可能なので、マスタデータは通常のテーブル、トランザクションデータはカラムストアテーブルと用途に応じて使い分けも出来ます。

 $ psql -U sampledb
 sampledb=> CREATE FOREIGN TABLE point_table (
 seq_id bigint NOT NULL,
 user_id bigint NOT NULL,
 strt_time date,
 id1 bigint,
 1d2 bigint NOT NULL,
 id3 bigint,
 id4 bigint,
 id5 bigint,
 strt_time2 date
 )
 SERVER cstore_server
 OPTIONS(compression 'pglz');
 CREATE FOREIGN TABLE

◆オプション一覧

オプションはDDLのOPTION(***)セクション内にカンマ区切りでまとめて記述します。

■compression
圧縮有り無しの選択
compression 'none' -> 圧縮無し
compression 'pglz' -> 圧縮有り(pglz)
■stripe_row_count
ストライプ毎のレコード数。デフォルトは150000。減らすと使用メモリ量が減少するが、性能も減少する。今回はデフォルトのまま使用。
■block_row_count
RowData内のカラムブロック毎のレコード数、デフォルトは10000。増やすと圧縮効率は上がるが、不必要なレコードも読む事になる。こちらもデフォルトのまま使用。
■外部ファイル名の指定
filename '/data/pgsql/cstore_fdw_manual/sample_tbl'
※ディレクトリはあらかじめ作成しておく必要がある。指定しない場合は、$PG_DATA下にcstore_fdwというディレクトリを作成して、ファイルが置かれる。

◆データ投入

データ投入はCOPYコマンドか、INSERT SELECTのみ。INSERT INTO,UPDATE,DELETE及びテーブルの定義変更は実施不可です。そのような操作を実施したい場合はテーブルを作り直す必要があります。(TRUNCATEはVer1.4から対応)

sampledb=> \COPY point_table FROM '/data/tmp/point_201510.csv' WITH CSV;
もしくは
sampledb=> insert point_table  select * from point_201510;

データ投入後にANALYZEを実施します。

sampledb=> ANALYZE point_table;

バックアップはpg_dumpで論理バックアップを取得してください。

ベンチマーク

簡単にベンチマークを取ってみました。

◆実施環境

Serrver: ProLiant DL360 G6 
CPU: Intel Xeon L5520 2.27GHz 4core x 2
Memory: 16GB
Storage: SATA HDD 7200rpm 1TB x 4 RAID5
OS: CentOS 6.8
Database:PostgreSQL 9.5.3 /cstore_fdw 1.4.1

◆PostgreSQL設定の主要変更箇所

work_mem = 1GB (DWHなのでOLTPより多めに割り当て)
shaerd_buffer : 3GB(物理メモリの20%)

◆テーブルデータ圧縮効率比較

下記テーブルにレコード数約27億行をインポートし通常のテーブル・cstore_fdw(圧縮なし)・cstore_fdw(pglz圧縮)の3パターンでテーブルを作成してパフォーマンスを比較します。

ポイント履歴テーブル
Table "point_table"
Column           | Type     | Modifiers
-----------------+----------+-----------
seq_id           | bigint   | not null
user_id          | bigint   | not null
strt_time        | date     |
id1              | bigint   |
id2              | bigint   | not null
id3              | bigint   |
id4              | bigint   |
id5              | bigint   |
strt_time2       | date     |

| DataStore                   | Datasize   | Rate   |
|-----------------------------|------------|--------|
|Normal Table                 |       220GB|  ------|
|cstore_fdw(compress='none')  |       120GB|     46%|
|cstore_fdw(compress='pglz')  |        26GB|     89%|

かなりの圧縮効率です（データの内容にもよりますが・・）。

◆クエリ実行速度、DiskI/O 計測

Index付き(strt_time)のテーブルも加えた4パターンのテーブルに対してクエリを実行します。DiskI/OはDB再起動、ページキャッシュクリア後にクエリ実行前後のiostatコマンドの累計値の差分で簡易的に取得しています。

select count(user_id) ct pt from point_table where strt_time >= '2012-01-01' AND strt_time < '2013-01-01' ;

| DataStore                  | Execution Time      | Data Transfer  |
|----------------------------|---------------------|----------------|
|Normal Table(Seq Scan)      |        985027.409 ms|  231,650,752 kb|
|Normal Table(index Scan)    |        396216.296 ms|   59,525,780 kb|
|cstore_fdw(compress='none') |        429137.730 ms|    7,679,868 kb|
|cstore_fdw(compress='pglz') |        350097.158 ms|    2,917,008 kb|

Indexを追加した通常のテーブルと同等の速度が出ており、DiskI/Oも抑えられている事がわかります。Index戦略の考慮しなくてもよくなれば、運用コストの低減に繋がります。

まとめ

オープンソースで実用的なカラムストア環境を構築できる点に大きな魅力を感じています。一方データのシャーディング、パラレルクエリなど分散処理的な機能は備えていませんので、より大規模な環境では別の手段を検討する必要があるでしょう。

同じcitusdata社が提供し先般オープンソース化されたcitusは、分散DWH環境を構築出来るようなので、後日試してみたいと考えています。

名無しのエンジニア

HOGE株式会社　エンジニアブログ