PostgreSQL9.3をカラム指向ストレージ(cstore_fdw)に対応させる

postgresql

分析向けデータベースを展開している CitusDB が PostgreSQL を列指向ストレージ対応させる foreign data wrapper(cstore_fdw) をオープンソース化したので、とりあえずインストールしてみた。

cstore_fdw の特徴

github の cstore_fdw に特徴がまとめられている。

http://citusdata.github.io/cstore_fdw/

箇条書きすると

Faster Analytics – Reduce analytics query disk and memory use by 10x
Lower Storage – Compress data by 3x
Easy Setup – Deploy as standard PostgreSQL extension
Flexibility – Mix row- and column-based tables in the same DB
Community – Benefit from PostgreSQL compatibility and open development

カラム指向ストレージには、 Hive でも利用されている Optimized Row Column(ORC) をベースにしたフォーマットが利用されている。

RCFile に対するメリットとして以下が挙げられている

Compression: Reduces in-memory and on-disk data size by 2-4x. Can be extended to support different codecs.
Column projections: Only reads column data relevant to the query. Improves performance for I/O bound queries.
Skip indexes: Stores min/max statistics for row groups, and uses them to skip over unrelated rows.

カラム指向ストレージのフォーマットで最近よく利用される ORC と RCFile と Parquet(hadoop) の違いは次の記事を参照

http://ozalog.blogspot.jp/2013/03/rcfileparquetorcfile.html

ベンチマーク

cstore_fdw オープンソース時のブログにベンチマーク結果が紹介されている。

http://www.citusdata.com/blog/76-postgresql-columnar-store-for-analytics

pglz 圧縮により圧縮率 3.5倍
クエリー速度が2倍
pglz 圧縮した cstore では disk I/O が 1/10 になった

といったことが書かれている。

インストール環境

Ubuntu 14.04 Server
PostgreSQL 9.3 (9.3 以降でないと動かない模様。 https://github.com/citusdata/cstore_fdw/issues/4)
cstore_fdw 1.0

という組み合わせで cstore_fdw をインストールする。

Ubuntu のインストール

http://www.ubuntu.com/download/server から Ubuntu 14.04 Server 版をダウンロード

PostgreSQL のインストール

Ubuntu 14.04 には PostgreSQL 9.3 がパッケージで提供されている。

まずは PostgreSQL 系パッケージのインストール

$ sudo apt-get install postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3

次に PostgreSQL のユーザー認証を設定

$ sudo vi /etc/postgresql/9.3/main/pg_hba.conf

Unix Domain Socket の認証方式を peer から md5 に変更する

$ sudo diff -u /etc/postgresql/9.3/main/pg_hba.conf*
--- /etc/postgresql/9.3/main/pg_hba.conf        2014-04-19 13:04:10.933407625 +0900
+++ /etc/postgresql/9.3/main/pg_hba.conf.orig   2014-04-19 01:18:28.773395127 +0900
@@ -87,7 +87,7 @@
 # TYPE  DATABASE        USER            ADDRESS                 METHOD

 # "local" is for Unix domain socket connections only
-local   all             all                                     md5
+local   all             all                                     peer
 # IPv4 local connections:
 host    all             all             127.0.0.1/32            md5
 # IPv6 local connections:

検証用のユーザ(ロール)とデータベースを作成

$ sudo su - postgres
$ createuser -P -s -e joe # -S : create super, -P : prompt for password, -e : Echo the commands that createuser generates and sends to the server
Enter password for new role:
Enter it again:
CREATE ROLE joe PASSWORD 'md5cbff98184193fb964111f2662c38fe0d' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

$ createdb myapp_fdw -O joe # -O : owner

cstore_fdw のインストール

ソースコードの取得

$ sudo apt-get install git
$ git clone https://github.com/citusdata/cstore_fdw.git
$ cd cstore_fdw/

cstore_fdw のコンパイル

$ sudo apt-get install make
$ sudo apt-get install protobuf-c-compiler libprotobuf-c0-dev
$ make
You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
protoc-c --c_out=. cstore.proto

エラーメッセージ通り postgresql-server-dev-X.Y もインストールして make やり直す

$ sudo apt-get install postgresql-server-dev-9.3
$ make
$ sudo make install
/bin/mkdir -p '/usr/lib/postgresql/9.3/lib'
/bin/mkdir -p '/usr/share/postgresql/9.3/extension'
/bin/mkdir -p '/usr/share/postgresql/9.3/extension'
/usr/bin/install -c -m 755  cstore_fdw.so '/usr/lib/postgresql/9.3/lib/cstore_fdw.so'
/usr/bin/install -c -m 644 cstore_fdw.control '/usr/share/postgresql/9.3/extension/'
/usr/bin/install -c -m 644 cstore_fdw--1.0.sql '/usr/share/postgresql/9.3/extension/'

PostgreSQL に cstore_fdw 用設定を追加する

$ sudo vi /etc/postgresql/9.3/main/postgresql.conf

shared_preload_libraries を変更

$ diff -u /etc/postgresql/9.3/main/postgresql.conf*
--- /etc/postgresql/9.3/main/postgresql.conf    2014-04-19 00:57:59.053394764 +0900
+++ /etc/postgresql/9.3/main/postgresql.conf.orig       2014-04-19 00:57:54.701394763 +0900
@@ -133,8 +133,7 @@

 #max_files_per_process = 1000          # min 25
                                        # (change requires restart)
-#shared_preload_libraries = ''         # (change requires restart)
-shared_preload_libraries = 'cstore_fdw'
+shared_preload_libraries = ''          # (change requires restart)

 # - Cost-Based Vacuum Delay -

cstore 向けのデータスストア先を postgresql ユーザ権限でディレクトリ作成する

$ sudo -u postgres mkdir /var/lib/postgresql/9.3/main/cstore

これを忘れると、あとのステップで cstore_fdw のデータ更新を行った時に

ERROR:  could not open file "/var/lib/postgresql/9.3/main/cstore/customer_reviews.cstore" for writing: No such file or directory

というようなエラーメッセージが表示される。

最後に PostgreSQL プロセスを再起動する。

$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server                            [ OK ]

postgres ユーザになり、psql 経由で cstore_fdw をコンパイルする

$ psql myapp_fdw
psql (9.3.4)
Type "help" for help.

myapp_fdw=# CREATE EXTENSION cstore_fdw;
CREATE EXTENSION

myapp_fdw=# \dx
                          List of installed extensions
    Name    | Version |   Schema   |                 Description
------------+---------+------------+---------------------------------------------
 cstore_fdw | 1.0     | public     | foreign-data wrapper for flat cstore access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

cstore_fdw を使ってみる:Part 1

次に cstore_fdw を使ってみる。

他の foreign data wrapper(fdw) と同じく、まずは fdw 向けの Server を定義する。

myapp_fdw=# CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
CREATE SERVER

foreign table(cstore_table) と通常のテーブル(plain_table) を作成。
foreign table の create table 文では、サーバとストレージ先も指定する。

CREATE FOREIGN TABLE cstore_table
  (num integer, name text)
SERVER cstore_server
OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/cstore_table.cstore',
        compression 'pglz');

CREATE TABLE plain_table
  (num integer, name text);

それぞれのテーブルに初期データを投入

myapp_fdw=# COPY cstore_table FROM STDIN (FORMAT csv);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1, foo
>> 2, bar
>> 3, baz
>> \.

myapp_fdw=# COPY plain_table FROM STDIN (FORMAT csv);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 4, foo
>> 5, bar
>> 6, baz
>> \.

cstore_fdw は通常の insert/update 操作が行えず、データ投入は copy コマンドから行う必要がある。

Note. We currently don’t support updating table using INSERT, DELETE, and UPDATE commands.

最後に、通常の table と foreign table を JOIN してみる

myapp_fdw=# SELECT * FROM cstore_table c, plain_table p WHERE c.name=p.name;
 num | name | num | name
-----+------+-----+------
   1 |  foo |   4 |  foo
   2 |  bar |   5 |  bar
   3 |  baz |   6 |  baz
(3 rows)

実行計画も覗いてみる

myapp_fdw=# explain SELECT * FROM cstore_table c, plain_table p WHERE c.name=p.name;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Hash Join  (cost=0.24..27.28 rows=12 width=72)
   Hash Cond: (p.name = c.name)
   ->  Seq Scan on plain_table p  (cost=0.00..22.30 rows=1230 width=36)
   ->  Hash  (cost=0.22..0.22 rows=2 width=36)
         ->  Foreign Scan on cstore_table c  (cost=0.00..0.22 rows=2 width=36)
               CStore File: /var/lib/postgresql/9.3/main/cstore/cstore_table.cstore
               CStore File Size: 112
(7 rows)

cstore_fdw を使ってみる:Part 2

AWS の “Getting Started with Amazon Redshift” で利用されているデータを cstore_fdw に突っ込んでみる。

http://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html

cstore_fdw と同じ列指向ストレージ on PostgreSQL ということで。

cstore_fdw 用テーブルの作成

http://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-connect-to-cluster.html#getting-started-create-sample-db

ここにある create 文を fdw 向けに書き換える

	CREATE EXTENSION cstore_fdw;

	CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

	create foreign table users(
	userid integer not null,
	username char(8),
	firstname varchar(30),
	lastname varchar(30),
	city varchar(30),
	state char(2),
	email varchar(100),
	phone char(14),
	likesports boolean,
	liketheatre boolean,
	likeconcerts boolean,
	likejazz boolean,
	likeclassical boolean,
	likeopera boolean,
	likerock boolean,
	likevegas boolean,
	likebroadway boolean,
	likemusicals boolean)
	SERVER cstore_server
	OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/users.cstore',
	compression 'pglz');

	create foreign table venue(
	venueid smallint not null,
	venuename varchar(100),
	venuecity varchar(30),
	venuestate char(2),
	venueseats integer)
	SERVER cstore_server
	OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/venue.cstore',
	compression 'pglz');

	create foreign table category(
	catid smallint not null,
	catgroup varchar(10),
	catname varchar(10),
	catdesc varchar(50))
	SERVER cstore_server
	OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/category.cstore',
	compression 'pglz');

	create foreign table date(
	dateid smallint not null,
	caldate date not null,
	day character(3) not null,
	week smallint not null,
	month character(5) not null,
	qtr character(5) not null,
	year smallint not null,
	holiday boolean default('N'))
	SERVER cstore_server
	OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/date.cstore',
	compression 'pglz');

	create foreign table event(
	eventid integer not null,
	venueid smallint not null,
	catid smallint not null,
	dateid smallint not null ,
	eventname varchar(200),
	starttime timestamp)
	SERVER cstore_server
	OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/event.cstore',
	compression 'pglz');

	create foreign table listing(
	listid integer not null,
	sellerid integer not null,
	eventid integer not null,
	dateid smallint not null ,
	numtickets smallint not null,
	priceperticket decimal(8,2),
	totalprice decimal(8,2),
	listtime timestamp)
	SERVER cstore_server
	OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/listing.cstore',
	compression 'pglz');

	create foreign table sales(
	salesid integer not null,
	listid integer not null,
	sellerid integer not null,
	buyerid integer not null,
	eventid integer not null,
	dateid smallint not null ,
	qtysold smallint not null,
	pricepaid decimal(8,2),
	commission decimal(8,2),
	saletime timestamp)
	SERVER cstore_server
	OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/sales.cstore',
	compression 'pglz');

view raw redshift_tutorial_table_cstore_fdw.sql hosted with ❤ by GitHub

$ psql -d myapp_fdw -a -f /tmp/data/create_fdw_table.sql

S3 からサンプルデータを取得

AWS redshift のチュートリアルで使われているサンプルデータを取得

http://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-connect-to-cluster.html

デリミターが統一されていなかったり、ファイル(sales_tab.txt)によっては行の途中で切れているように見受けられるなど、さり気なくトラップが仕込まれているので注意する。

データ投入

cstore_fdw では INSERT/DELETE/UPDATE は使えない。
COPY コマンド経由でデータ投入する。

You can use PostgreSQL’s COPY command to load or append data into the table. You can use PostgreSQL’s ANALYZE table_name command to collect statistics about the table. These statistics help the query planner to help determine the most efficient execution plan for each query.

Note. We currently don’t support updating table using INSERT, DELETE, and UPDATE commands.
https://github.com/citusdata/cstore_fdw/blob/master/README.md

	copy users from '/tmp/s3/allusers_pipe.txt' with null as '' delimiter '\|';
	copy venue from '/tmp/s3/venue_pipe.txt' delimiter '\|';
	copy category from '/tmp/s3/category_pipe.txt' with null as '' delimiter '\|';
	copy date from '/tmp/s3/date2008_pipe.txt' with null as '' delimiter '\|';
	copy event from '/tmp/s3/allevents_pipe.txt' with null as '' delimiter '\|';
	copy listing from '/tmp/s3/listings_pipe.txt' with null as '' delimiter '\|';
	copy sales from '/tmp/s3/sales_tab.txt' with null as '' delimiter '\t';

view raw redshift_tutorial_copy.sql hosted with ❤ by GitHub

$ psql -d myapp_fdw -a -f /tmp/data/copy.sql

SQL を実行

ロードしたデータに対して、集計系SQLを投げる。実行計画も確認。
\timing を有効にして、SQL の処理時間も表示させる。(3回実行)

myapp_fdw=# \timing
Timing is on.

Find total sales on a given calendar date.

SELECT sum(qtysold)
FROM   sales, date
WHERE  sales.dateid = date.dateid
AND    caldate = '2008-01-05';
 sum
-----
   4
(1 row)

Time:

185.435 ms
8.123 ms
6.681 ms

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Aggregate  (cost=50.63..50.64 rows=1 width=2)
   ->  Hash Join  (cost=1.45..50.59 rows=17 width=2)
         Hash Cond: (sales.dateid = date.dateid)
         ->  Foreign Scan on sales  (cost=0.00..36.04 rows=3448 width=4)
               CStore File: /var/lib/postgresql/9.3/main/cstore/sales.cstore
               CStore File Size: 110339
         ->  Hash  (cost=1.44..1.44 rows=1 width=2)
               ->  Foreign Scan on date  (cost=0.00..1.44 rows=1 width=2)
                     Filter: (caldate = '2008-01-05'::date)
                     CStore File: /var/lib/postgresql/9.3/main/cstore/date.cstore
                     CStore File Size: 3359
(11 rows)

Find top 10 buyers by quantity.

SELECT firstname, lastname, total_quantity
FROM   (SELECT buyerid, sum(qtysold) total_quantity
        FROM  sales
        GROUP BY buyerid
        ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;
 firstname | lastname  | total_quantity
-----------+-----------+----------------
 Slade     | Hatfield  |             20
 Blaine    | Anderson  |             17
 Brian     | Bowen     |             16
 Lars      | Nichols   |             16
 Oleg      | Spencer   |             16
 Violet    | Delaney   |             16
 Barrett   | Collins   |             16
 Quinlan   | Underwood |             16
 Evan      | Singleton |             14
 Carl      | Morrison  |             14
(10 rows)

Time:

73.764 ms
43.459 ms
59.307 ms

                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Sort  (cost=524.66..525.05 rows=154 width=74)
   Sort Key: q.total_quantity
   ->  Hash Join  (cost=53.32..519.07 rows=154 width=74)
         Hash Cond: (users.userid = q.buyerid)
         ->  Foreign Scan on users  (cost=0.00..348.96 rows=30731 width=70)
               CStore File: /var/lib/postgresql/9.3/main/cstore/users.cstore
               CStore File Size: 2950191
         ->  Hash  (cost=53.31..53.31 rows=1 width=12)
               ->  Subquery Scan on q  (cost=53.30..53.31 rows=1 width=12)
                     ->  Limit  (cost=53.30..53.30 rows=1 width=6)
                           ->  Sort  (cost=53.30..53.30 rows=1 width=6)
                                 Sort Key: (sum(sales.qtysold))
                                 ->  HashAggregate  (cost=53.28..53.29 rows=1 width=6)
                                       ->  Foreign Scan on sales  (cost=0.00..36.04 rows=3448 width=6)
                                             CStore File: /var/lib/postgresql/9.3/main/cstore/sales.cstore
(16 rows)

Find events in the 99.9 percentile in terms of all time gross sales.

AWS のマニュアルのままだと SQL のシンタックスエラーが起きたので(PostgreSQL 9.3)、少し修正した。

SELECT eventname, total_price
FROM  (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile
       FROM (SELECT eventid, sum(pricepaid) as total_price
             FROM   sales
             GROUP BY eventid) tmp) Q, event E
       WHERE Q.eventid = E.eventid
       AND percentile = 1
ORDER BY total_price desc;

   eventname    | total_price
----------------+-------------
 Les Miserables |     4161.00
 Jill Scott     |     3687.00
(2 rows)

Time:

39.838 ms
9.426 ms
33.706 ms

                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Sort  (cost=55.17..55.18 rows=6 width=150)
   Sort Key: q.total_price
   ->  Hash Join  (cost=36.12..55.09 rows=6 width=150)
         Hash Cond: (e.eventid = q.eventid)
         ->  Foreign Scan on event e  (cost=0.00..14.61 rows=1147 width=122)
               CStore File: /var/lib/postgresql/9.3/main/cstore/event.cstore
               CStore File Size: 174373
         ->  Hash  (cost=36.10..36.10 rows=1 width=36)
               ->  Subquery Scan on q  (cost=36.07..36.10 rows=1 width=36)
                     Filter: (q.percentile = 1)
                     ->  WindowAgg  (cost=36.07..36.09 rows=1 width=36)
                           ->  Sort  (cost=36.07..36.08 rows=1 width=36)
                                 Sort Key: tmp.total_price
                                 ->  Subquery Scan on tmp  (cost=36.04..36.06 rows=1 width=36)
                                       ->  HashAggregate  (cost=36.04..36.05 rows=1 width=18)
                                             ->  Foreign Scan on sales  (cost=0.00..24.55 rows=2299 width=18)
                                                   CStore File: /var/lib/postgresql/9.3/main/cstore/sales.cstore
                                                   CStore File Size: 110339
(18 rows)

References

	copy users from '/tmp/s3/allusers_pipe.txt' with null as '' delimiter '\|';
	copy venue from '/tmp/s3/venue_pipe.txt' delimiter '\|';
	copy category from '/tmp/s3/category_pipe.txt' with null as '' delimiter '\|';
	copy date from '/tmp/s3/date2008_pipe.txt' with null as '' delimiter '\|';
	copy event from '/tmp/s3/allevents_pipe.txt' with null as '' delimiter '\|';
	copy listing from '/tmp/s3/listings_pipe.txt' with null as '' delimiter '\|';
	copy sales from '/tmp/s3/sales_tab.txt' with null as '' delimiter '\t';