Symfoware

Symfowareについての考察blog

PostgreSQL + cstore_fdwによるログテーブルの容量圧縮

PostgreSQL 10にcstore_fdw拡張をインストールしました。
cstore_fdw(PostgreSQLカラムストア拡張)のインストールと設定

データサイズがどの程度圧縮されるか試してみます。


テーブルの作成とデータの登録



登録日や顧客ID。ログデータを格納するtextフィールドを3つ持つテーブルでテストしてみます。


log_1:通常
log_2:cstore_fdw,無圧縮
log_2:cstore_fdw,pglz圧縮




使用したCREATE文は以下のとおりです。


CREATE TABLE log_1 (
id bigint NOT NULL,
create_at timestamp NOT NULL,
customer_id bigint NOT NULL,
log_data_1 text NOT NULL,
log_data_2 text NOT NULL,
log_data_3 text NOT NULL
);

CREATE FOREIGN TABLE log_2 (
id bigint NOT NULL,
create_at timestamp NOT NULL,
customer_id bigint NOT NULL,
log_data_1 text NOT NULL,
log_data_2 text NOT NULL,
log_data_3 text NOT NULL
)
SERVER cstore_server
OPTIONS(compression 'none');


CREATE FOREIGN TABLE log_3 (
id bigint NOT NULL,
create_at timestamp NOT NULL,
customer_id bigint NOT NULL,
log_data_1 text NOT NULL,
log_data_2 text NOT NULL,
log_data_3 text NOT NULL
)
SERVER cstore_server
OPTIONS(compression 'pglz');




ダミーデータはこんなPythonプログラムを実行し、10,000,000行用意しました。
出力したCSVファイルの容量は8.2GBになりました。


  1. # -*- coding:utf-8 -*-
  2. import datetime
  3. def main():
  4.     
  5.     with open('sample.csv', 'w') as f:
  6.         for i in xrange(1, 10000001):
  7.             write_line(f, i)
  8.             
  9.             if i % 100000 == 0:
  10.                 print i
  11.             
  12.     
  13. def write_line(f, i):
  14.     
  15.     # 適当に日付を作成
  16.     create_at = datetime.datetime.fromtimestamp(1000000000 + i).strftime('%Y-%m-%d %H:%M:%S')
  17.     customer_id = i % 10000
  18.     log_text = 'abcdefghijklmnopqrstuvwxyx' * 10
  19.     
  20.     # id, create_at, customer_id, log_data_1, log_data_2, log_data_3
  21.     f.write('%d,%s,%d,%s,%s,%s\n' % (i, create_at, customer_id, log_text, log_text, log_text) )
  22. if __name__ == '__main__':
  23.     main()




COPYでデータを登録します。


sample=# COPY log_1 FROM '/path/to/sample.csv' with CSV;
COPY 10000000
sample=# COPY log_2 FROM '/path/to/sample.csv' with CSV;
COPY 10000000
sample=# COPY log_3 FROM '/path/to/sample.csv' with CSV;
COPY 10000000




cstore_fdwのテーブルはANALYZEを実行。


sample=# ANALYZE log_2;
ANALYZE
sample=# ANALYZE log_3;
ANALYZE



これで準備出来ました。




テーブルサイズ



PostgreSQLで各テーブルの総サイズと平均サイズを知る
PostgreSQL :: テーブルのサイズ(物理容量)とレコード数を調べる

上記を参考に、こんなクエリーで使用している容量をチェックしてみます。


SELECT
    relname,
    (reltuples / 1000) as trows,
    (relpages / 128) as mbytes
FROM
    pg_class
WHERE
    relname in ('log_1', 'log_2', 'log_3')
ORDER BY
    relname;


relname | trows | mbytes
---------+-----------+--------
log_1 | 10000.008 | 8680
log_2 |     10000 | 7790
log_3 |     10000 |    249
(3 rows)




text領域の内容がすべて同じとうこともあり、pglz圧縮を指定したlog_3テーブルは
通常テーブルの3%の容量にとどまっています。
これはすごい。




データの検索



idを指定して検索してみます。
全くインデックスは作成していません。
※cstore_fdwのテーブルはそもそもインデックスが作成できません。


  1. # -*- coding:utf-8 -*-
  2. import time
  3. import psycopg2
  4. def search_log(table):
  5.     con = psycopg2.connect(
  6.         host = "192.168.1.103",
  7.         port = 5432,
  8.         database="sample",
  9.         user="pgadmin",
  10.         password="P@ssw0rd")
  11.     cur = con.cursor()
  12.     cur.execute('select * from %s where id = 1' % table)
  13.     con.close()
  14.     print(u'ok')
  15. if __name__ == '__main__':
  16.     start = time.time()
  17.     search_log('log_1')
  18.     elapsed_time = time.time() - start
  19.     print('%f [sec]' % elapsed_time)




実行速度はこのようになりました。


log_1:12.048581 [sec]
log_2:0.087165 [sec]
log_3:0.069793 [sec]




cstore_fdwのテーブル、圧倒的な速度です。
log_1に対して2つのインデックスを作成し、create_atやcustomer_idで検索してみることにしました。


CREATE INDEX idx_log_1_create_at ON log_1 (create_at);
CREATE INDEX idx_log_1_customer_id ON log_1 (customer_id);




create_atの範囲指定で検索してみます。


  1. # -*- coding:utf-8 -*-
  2. import time
  3. import psycopg2
  4. def search_log(table):
  5.     con = psycopg2.connect(
  6.         host = "192.168.1.103",
  7.         port = 5432,
  8.         database="sample",
  9.         user="pgadmin",
  10.         password="P@ssw0rd")
  11.     cur = con.cursor()
  12.     query = """
  13. select
  14. min(id) as minid
  15. ,max(id) as maxid
  16. from
  17. %s
  18. where
  19. create_at >= '2001-10-01 00:00:00'
  20. and create_at < '2001-11-01 00:00:00'
  21. """ % table
  22.     cur.execute(query)
  23.     row = cur.fetchone()
  24.     print(row[0], row[1])
  25.     con.close()
  26.     print(u'ok')
  27. if __name__ == '__main__':
  28.     start = time.time()
  29.     search_log('log_3')
  30.     elapsed_time = time.time() - start
  31.     print('%f [sec]' % elapsed_time)




実行速度はこのようになりました。


log_1:5.932731 [sec]
log_2:0.520668 [sec]
log_3:0.473164 [sec]




customer_idを指定しての検索。


  1. # -*- coding:utf-8 -*-
  2. import time
  3. import psycopg2
  4. def search_log(table):
  5.     con = psycopg2.connect(
  6.         host = "192.168.1.103",
  7.         port = 5432,
  8.         database="sample",
  9.         user="pgadmin",
  10.         password="P@ssw0rd")
  11.     cur = con.cursor()
  12.     query = """
  13. select
  14. count(*) as cnt
  15. from
  16. %s
  17. where
  18. customer_id = 1234
  19. """ % table
  20.     cur.execute(query)
  21.     row = cur.fetchone()
  22.     print(row[0])
  23.     con.close()
  24.     print(u'ok')
  25. if __name__ == '__main__':
  26.     start = time.time()
  27.     search_log('log_3')
  28.     elapsed_time = time.time() - start
  29.     print('%f [sec]' % elapsed_time)




実行速度はこのようになりました。


log_1:0.690689 [sec]
log_2:0.632206 [sec]
log_3:0.715657 [sec]




インデックスを作成したテーブルと遜色ない速度で検索が実行されています。

・似たような文字列を保存している
・検索パターンが不定なのでインデックスが作成しづらい

こんなテーブルには有効なのではと思います。


テーマ:サーバ - ジャンル:コンピュータ

  1. 2018/01/17(水) 15:52:34|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

cstore_fdw(PostgreSQLカラムストア拡張)のインストールと設定

ログデータを登録しているPostgreSQLのテーブルがあるのですが、サイズが肥大化してきました。
なんとか圧縮する方法はないか調べていると、このブログを見つけました。
PostgreSQLカラムストア拡張(cstore_fdw)の検証

cstore_fdwというカラムストアを再現するための拡張が圧縮に有効なようです。
https://github.com/citusdata/cstore_fdw

インストールと設置を試してみます。


Ubuntu 16.04 + PostgreSQL 10



導入する環境は、Ubuntu 16.04 + PostgreSQL 10です。
こちらの手順でインストールしたものを使用します。

PostgreSQL 10.1をUbuntu 16.04へインストールし、外部からの接続を許可




cstore_fdwのインストール



こちらのBuildingを参考にインストールを進めました。
https://github.com/citusdata/cstore_fdw

ドキュメントの記載とは異なりますが、実際に必要だったものはこちら。


$ sudo apt install protobuf-c-compiler
$ sudo apt install make g++
$ sudo apt install postgresql-server-dev-10




githubからソースを取得してビルド&インストールします。


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




続いて、postgresql.confを編集しビルドしたcstore_fdwをロードするよう指定します。


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




146行目付近。

shared_preload_libraries = 'cstore_fdw'


を追記します。


# - Kernel Resource Usage -

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




ファイルを編集したら、サービスを再起動。


$ sudo service postgresql restart



これで設定は完了です。





cstore_fdw拡張の有効化とテーブルの作成



postgresqlに接続し、現在有効となっている拡張機能を表示してみます。
※pgadminは作成したユーザー


$ psql -h localhost -d postgres -U pgadmin
psql (10.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#




事前にsampleというデータベースを作っておきました。
使用するデータベースをsampleに変更。


postgres=# \c sample




「\dx」で拡張の一覧を表示。


sample=# \dx
                 List of installed extensions
Name | Version | Schema |         Description        
---------+---------+------------+------------------------------
plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)




まだcstore_fdwは存在しません。
「CREATE EXTENSION cstore_fdw」を実行し、有効にします。


sample=# CREATE EXTENSION cstore_fdw;




ちゃんと登録できました。


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





続いてテーブルを作成していきます。
まず、SERVERを定義。


CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;




上記で作成した「cstore_server」を指定してテーブルを作成します。


CREATE FOREIGN TABLE log_table (
id bigint NOT NULL,
log_data text NOT NULL
)
SERVER cstore_server
OPTIONS(compression 'pglz');



compressionは「none(無圧縮)」か「pglz(圧縮)」が選択できます。
これでlog_tableというテーブルが作成できました。


# \d
             List of relations
Schema | Name    |     Type     | Owner
--------+-----------+---------------+---------
public | log_table | foreign table | pgadmin
(1 row)




データをinsertしようとすると、エラーになります。


sample=# insert into log_table (id, log_data) values (1, 'test');
ERROR: operation is not supported




データの登録は、


COPY [テーブル名] FROM '[cvsファイルのパス]' WITH CSV;



によるファイルからの登録か、


insert into [テーブル名] select ...;



のようにvalueをselectで指定する形式でないと登録できないそうです。


とりあえず、こんなクエリーで登録しました。


sample=# insert into log_table select 1, 'test';




登録を実行したら、ANALYZEで最適化。
(実はここの意味がよくわかっていない)


sample=# ANALYZE log_table;




selectでデータが登録できていることが確認できます。


sample=# select * from log_table;
id | log_data
----+----------
1 | test
(1 row)




upadteやdeleteもサポートされていません。


sample=# update log_table set log_data = 'test2';
ERROR: operation is not supported
sample=# delete from log_table where id = 1;
ERROR: operation is not supported




truncateによる全件削除は可能です。


sample=# truncate table log_table;
TRUNCATE TABLE
sample=# select * from log_table;
id | log_data
----+----------
(0 rows)




引き続き、どの程度の圧縮効果があるのかや検索速度について調べてみようと思います。


テーマ:サーバ - ジャンル:コンピュータ

  1. 2018/01/16(火) 23:02:40|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

PostgreSQL 10.1にJava(JDBCドライバ)で接続

UbuntuにPostgreSQL 10.1をインストールしてみました。
PostgreSQL 10.1をUbuntu 16.04へインストールし、外部からの接続を許可

この環境にJavaで接続してみようと思います。
過去記事を参考にしました。
FreeBSD + PostgreSQL 9.3.2 にJavaで接続する



JDBCドライバの取得



こちらからJDBCドライバを入手します。
https://jdbc.postgresql.org/download.html

JDBCドライバのバージョン表記、変わりましたね。
プログラムはJava 8で実行する予定なので、「PostgreSQL JDBC 4.2 Driver, 42.1.4」のリンクをクリックしました。

808_01.png

「postgresql-42.1.4.jar」が取得できます。
このjarをビルドパスに含めておきます。




サンプル



データベースへの接続と検索のサンプルはこのようになりました。


  1. package com.fc2.blog68.symfoware;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.Statement;
  6. public class MainProcess {
  7.     
  8.     public static void main(String... args) {
  9.         
  10.         String url = "jdbc:postgresql://192.168.1.103:5432/sample";
  11.         String user = "pgadmin";
  12.         String pass = "P@ssw0rd";
  13.         
  14.         try (Connection con = DriverManager.getConnection(url, user, pass);
  15.                 Statement stmt = con.createStatement();
  16.                 ResultSet rs = stmt.executeQuery("select * from test")) {
  17.             
  18.             while (rs.next()) {
  19.                 System.out.println(rs.getInt("id"));
  20.                 System.out.println(rs.getString("val"));
  21.             }
  22.             
  23.             
  24.         } catch (Exception e) {
  25.             e.printStackTrace();
  26.         }
  27.     }
  28. }



無事データベースに接続し、クエリーを実行できました。

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2017/11/18(土) 17:18:58|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

PostgreSQL 10.1をUbuntu 16.04へインストールし、外部からの接続を許可

Ubuntu 16.04へPostgreSQL 10.1をインストールしてみます。
大筋は過去記事のこちらを参考にしました。
Debian 9(Stretch)にPostgreSQL 9.6をapt-getでインストールし、外部アクセスを許可する


apt



こちらを参考にしました。
Linux downloads (Ubuntu)


/etc/apt/sources.list.d/pgdg.listを新規作成。


$ sudo vi /etc/apt/sources.list.d/pgdg.list




ファイルの内容は以下の1行。


deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main




証明書ファイルを追加します。


$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -




aptの内容を更新。


$ sudo apt update



apt searchすると、postgres 10が見つかりました。


$ apt search postgresql-10
ソート中... 完了
全文検索... 完了
postgresql-10/xenial-pgdg 10.1-1.pgdg16.04+1 amd64
object-relational SQL database, version 10 server
...




aptでインストールを実行します。


$ sudo apt install postgresql-10
...
Creating new PostgreSQL cluster 10/main ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5
データベースシステム内のファイルの所有者は"postgres"ユーザでした。
このユーザがサーバプロセスを所有しなければなりません。

データベースクラスタはロケール"ja_JP.UTF-8"で初期化されます。
したがってデフォルトのデータベース符号化方式はUTF8に設定されました。
initdb: ロケール"ja_JP.UTF-8"用の適切なテキスト検索設定が見つかりません
デフォルトのテキスト検索設定はsimpleに設定されました。
...




デフォルトのユーザー「postgres」が作成されました。
psqlで接続してみます。


$ sudo -u postgres psql
psql (10.1)
Type "help" for help.

postgres=#




ちゃんと動いてくれているようです。
「\q」でコンソールを終了します。


postgres=# \q
$






データベースの接続ユーザーの追加(createuser)



createuserコマンドを使用して、外部から接続するためのユーザーを作成します。
今回は「pgadmin」という名前のユーザーを作成しました。


$ sudo -u postgres createuser --pwprompt --interactive pgadmin
新しいロールのためのパスワード: (設定するパスワード入力)
もう一度入力してください:(上記と同じパスワード入力)
新しいロールをスーパーユーザにしますか? (y/n)y




作成した「pgadmin」でログイン出来るかテストしてみます。


$ psql -U pgadmin -d postgres -h localhost
Password for user pgadmin: (設定したパスワードを入力)
psql (10.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#




いい感じです。
ついでにテスト用のデータベースを作成しておきます。


postgres=# create database sample;
CREATE DATABASE




「\c (データベース名)」で使用するデータベースを切り替え、
テスト用のテーブルも作成しておきました。


postgres=# \c sample
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "sample" as user "pgadmin".

sample=# create table test (id int not null, val varchar(100) not null);
CREATE TABLE




登録と検索も試しておきました。


sample=# insert into test (id, val) values (1, 'テスト');
INSERT 0 1

sample=# select * from test;
id | val
----+--------
1 | テスト
(1 row)




良いようです。
「\q」でコンソールを終了します。





外部接続許可



別の端末からも接続できるように構成します。
postgresql.confを編集。


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




59行目付近、「listen_addresses」の項目を探します。
コメントを解除し、設定値を「localhost」から「*」に変更。


#listen_addresses = 'localhost'
listen_addresses = '*'




続いてpg_hba.confを編集。


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




「IPv4 local connections」に、接続を許可するIPの範囲を指定。
今回は192.168.1.0/24の範囲を追加しました。


# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.1/24         md5




編集が終わったら、PostgreSQLを再起動。


$ sudo service postgresql restart



これで外部から接続できるようになっているはずです。
次回、Javaのプログラムを作成してテストしてみます。

テーマ:サーバ - ジャンル:コンピュータ

  1. 2017/11/18(土) 16:57:43|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

Debian 9(Stretch)にPostgreSQL 9.6をapt-getでインストールし、外部アクセスを許可する

Debian 9にPostgreSQL 9.6をインストールしてみます。

過去の記事を参考にしました。
Debian 8(Jessie)にPostgreSQL 9.4をapt-getでインストールする



PostgreSQLのインストール



apt-getでインストール


# apt-get install postgresql-9.6




インストール時のログの抜粋です。


(略)
postgresql-client-common (181) を設定しています ...
systemd (232-25) のトリガを処理しています ...
postgresql-common (181) を設定しています ...
ユーザ postgres をグループ ssl-cert に追加

Creating config file /etc/postgresql-common/createcluster.conf with new version
Creating config file /etc/logrotate.d/postgresql-common with new version
(略)
postgresql-9.6 (9.6.3-3) を設定しています ...
Creating new cluster 9.6/main ...
config /etc/postgresql/9.6/main
data /var/lib/postgresql/9.6/main
locale ja_JP.UTF-8
socket /var/run/postgresql
port 5432
update-alternatives: /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) を提供するために自動モードで /usr/share/postgresql/9.6/man/man1/postmaster.1.gz を使います
postgresql-contrib-9.6 (9.6.3-3) を設定しています ...
python3 (3.5.3-1) を設定しています ...
running python rtupdate hooks for python3.5...
(略)




ユーザー「postgres」が自動的に作成されています。
また、python3も一緒にインストールされるようです。

postgresユーザーに切り替えて、psqlを実行。


# su - postgres
postgres@debian9:~$ psql
psql (9.6.3)
"help" でヘルプを表示します.

postgres=#




バージョン9.6.3がインストールできました。

「\q」で終了します。


postgres=# \q
$







データベースユーザーの追加(createuser)



データベースに接続するためのユーザーを作成してみます。
今回は「pgadmin」というスーパーユーザーを作成してみます。


$ createuser --pwprompt --interactive pgadmin
新しいロールのためのパスワード: (設定するパスワード入力)
もう一度入力してください:(上記と同じパスワード入力)
新しいロールをスーパーユーザにしますか? (y/n)y




これでユーザーが作成できました。
ログイン出来るかテストしてみます。


$ psql -U pgadmin -d postgres -h localhost
ユーザ pgadmin のパスワード: (パスワードを入力)
psql (9.6.3)
SSL接続(プロトコル: TLSv1.2, 暗号化方式: ECDHE-RSA-AES256-GCM-SHA384, ビット長: 256, 圧縮: オフ)
"help" でヘルプを表示します.

postgres=#




接続できました。
データベースを作成してみます。


postgres=# create database sample;
CREATE DATABASE
postgres=#




「\l」でデータベースの一覧が表示できます。


postgres=# \l

                                         データベース一覧
名前    | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) |    
アクセス権    
-----------+----------+------------------+-------------+-------------------+-----------------------
postgres | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8     |
sample    | pgadmin | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8     |
template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8     | =c/postgres         +
         |         |                 |             |                 | postgres=CTc/postgres
template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8     | =c/postgres         +
         |         |                 |             |                 | postgres=CTc/postgres
(4 行)




ちゃんとsampleデータベースが作成できています。






外部接続許可



別の端末から接続できるようにしてみます。
まず、postgresql.confを編集。


# vi /etc/postgresql/9.6/main/postgresql.conf




60行目付近のlisten_addressesを'localhost'から'*'に変更します。


#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all

listen_addresses = '*'




続いてpg_hba.confを編集。


# vi /etc/postgresql/9.6/main/pg_hba.conf




「IPv4 local connections」に、接続を許可するIPの範囲を指定。
今回は192.168.1.0/24の範囲を追加しました。


# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.0/24         md5




編集が終わったら、PostgreSQLを再起動。


# service postgresql restart




Psycopg2を使用して、接続をテストしてみます。


  1. # -*- coding:utf-8 -*-
  2. import psycopg2
  3. con = psycopg2.connect(
  4.     host = "192.168.1.101",
  5.     port = 5432,
  6.     database="sample",
  7.     user="pgadmin",
  8.     password="P@ssw0rd")
  9. con.close()
  10. print(u'ok')




ちゃんと外部から接続できたようです。


$ python sample.py
ok




テーマ:サーバ - ジャンル:コンピュータ

  1. 2017/06/28(水) 22:09:02|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
次のページ