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 になった

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

インストール環境

という組み合わせで 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 向けに書き換える

$ 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

$ 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

Tagged with: , , , , , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
  • RT @__apf__: How to write a research paper: a guide for software engineers & practitioners. docs.google.com/presentation/d… /cc @inwyrd 6 days ago
  • RT @HayatoChiba: 昔、自然と対話しながら数学に打ち込んだら何かを悟れるのではと思いたち、専門書1つだけ持ってパワースポットで名高い奈良の山奥に1週間籠ったことがある。しかし泊まった民宿にドカベンが全巻揃っていたため、水島新司と対話しただけで1週間過ぎた。 それ… 3 weeks ago
  • RT @googlecloud: Ever wonder what underwater fiber optic internet cables look like? Look no further than this deep dive w/ @NatAndLo: https… 3 weeks ago
  • @ijin UTC+01:00 な時間帯で生活しています、、、 6 months ago
  • RT @mattcutts: Google's world-class Site Reliability Engineering team wrote a new book: amazon.com/Site-Reliabili… It's about managing produc… 9 months ago
%d bloggers like this: