KKIDA-GALAXY

2018年2月12日月曜日

pgAdmin4をバージョンアップする（Windows版）

# pgAdmin4とは
PostgreSQLの簡単な運用管理やクエリ作成に使える標準のGUIツールです。
以前に英語のみ提供されていたpgAdmin4を日本語化する方法を紹介しました。

[pgAdmin4を日本語化する](http://kkida-galaxy.blogspot.jp/2017/12/pgadmin4ja.html)

# pgAdmin4をバージョンアップする
2018年1月11日にバージョン2.1がリリースされました。
以前のバージョンではGUIツールとして~~残念な~~表示上の不具合などがあったのですが、そのようなバグ修正や、クエリツールの利便性を改善するような修正が行われています。

pgAdmin4はクライアントツールなので、「メンテナンス」というと嫌がられるDBサーバーの中身をバージョンアップするのとは違います。各担当者の作業用PCの中で、好き勝手にバージョンアップしてしまいましょう！

> *ちなみに「4」は製品名で「2.1」がバージョンです。中の人の中心は同じだったりしますが、「pgAdmin3」とは別プロジェクト、別物として開発されています。と言うワケで、pgAdmin3をお使いの方は4に乗り換えるかどうかと言う話で、本記事とはまた別と考えましょう。*

# バージョンアップ手順
実のところ、私も探り探りです。やりながら書いていきます。笑

## 現在の環境
32bitのWindows10にpgAdmin4 2.0をインストールして、日本語にしてあります。
DBサーバーへの接続を2つ（2つのDBに対する接続）を登録してあります。
![バージョンアップ前](https://lh3.googleusercontent.com/cKgMxWRbnHbnhyJ7S4rr9pt3RrJw5o_ymtp89eroR9m07kh7W1M5Du8yg1ViugE9djRfVovH92qr "バージョンアップ前")

今回の目標はこの`接続定義を保持したままバージョン2.1に上げる`こととします。

※ pgAgentを導入し、DBに対するタスクスケジューリングをしている場合、本手順で完全に移行できるかどうかは***未確認***です。ご注意ください！

<br> 
## 最新版のダウンロード
pgAdmin4プロジェクトの[ダウンロードページ](https://www.pgadmin.org/download/pgadmin-4-windows/)から最新版のexeファイルを取得します。

* CURRENT_MAINTAINER
* pgadmin4-2.1-x86.exe　★必要なのはコレのみ
* pgadmin4-2.1-x86.exe.sig

<br>
## インストーラーを実行
取得したexeファイルを右クリックして「管理者として実行」します。
インストーラーの中身は英語ですが、次々と進めてOK。
今回はバージョンアップなので、インストール先だけ既存フォルダを指定します。

### インストールを開始
![enter image description here](https://lh3.googleusercontent.com/2W_bQXAvfezHT1__lp31z94nR6mhkMK_nO23iFfSEtaFUlqW4AbrEAooNpIC6YUVWEGvd-oe3QDS "インストール開始")

### 規約に同意
![enter image description here](https://lh3.googleusercontent.com/6laOjeUR6qslPz2KO2Pj8yxF4nO8Jm7I_A8yYH57oyq_hHlZd3GnIYdm6XQrGPUQATM-WKx9pCHD "規約")

### インストールフォルダを指定
ここで今回は既存のフォルダを指定しました。
![フォルダ指定](https://lh3.googleusercontent.com/XAvWuu6KrXsyGQ-lhob_z1ZdT0Fbb2ammgN9vrMyXVciKof4Zo6Xli-4_xUsYpOJRax92Z_wBKgH "フォルダ指定")

既に同ファイルが存在する旨の警告がでますが、そのまま進めて問題ありませんでした。
![enter image description here](https://lh3.googleusercontent.com/bt3sJvTqjQsLumd4BEGUwTVDOedq6s3-Op-4ev8ajAc3Y03n_XYXM7ZgafM02szeUYcGNkM7mnwS "警告")

### メニューの作成
Windowsのスタートメニューに追加するかどうかの確認です。
![enter image description here](https://lh3.googleusercontent.com/WzG5QbLNJwtiuodeJgACWrbkY96F_BIygNq2s4fnR-mKXvy3yTW_zkpFkLle6p_tLTtUk2boEjWk "メニュー作成")

### インストール内容の確認
![enter image description here](https://lh3.googleusercontent.com/1hFdcILn2IaGxO2zpFWoPcBFTzjD2HnutFQClYwwNPMrz2BVv-Dkak3Vomr2pZHYTjdYLQZmK44k "確認")

### インストール完了
「Launch pgAdmin4」にチェックを入れると、インストーラー終了後にpgAdmin4が自動起動されます・・・が、一回目は上手くいかないのがお約束（大きな声では言えない。。。）

![enter image description here](https://lh3.googleusercontent.com/a4InolTiq4jtIVx5fFj-D7aSZj9Lu05FBojk3_9e14f3pvCt39YLtaWntr94euIcK2e0oaH2_IXP "完了")

## インストール後の状態

以下の2点を確認します。
* 日本語になっている
* 設定済みの接続情報（サーバー定義）がきちんと残っている事

![enter image description here](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgY8PRduZQMAHW8x-ljyBpL_HutKoQXb-TZULSzzlOWxNp8st0waU1IMjLVBUiLDrBVWFIonfTRrLuWxXO5TyywINgpG4sOaRnc13e-3YpAhyphenhyphenaWB5e-6i_HvohBSgEie_-fwp8AVv99_sY/ "バージョンアップ後")

問題なさそうです！**よかった！**

2017年12月13日水曜日

pgAdmin4を日本語化する

この記事はPostgreSQL Advent Calendar 2017の13日目です。昨日はkmoriさんの「.psqlrcの話」でした。
本記事では、私もメンバーの一員として取り組んでいるpgAdmin4の日本語化について書きます。

pgAdmin4とは？

クライアントPCからリモートのPostgreSQLサーバーに接続し、メンテナンス、状態監視、簡単な開発に利用できるGUIクライアントです。

pgAdmin4、こんな人におススメ

　・データベースをGUIで手軽に操作したい
　・PostgreSQLを使い始めたので、まずはアクセスみたい
　・監視やチューニングに関しても、手元でちょろっとできて欲しいんよ

データベースを利用する上で、例えばマルチDB対応しているA5や、補完機能などが抜群で圧倒的支持とウワサのオブジェクトブラウザ－など、便利なクライアントツールは複数知られていますが、PostgreSQL向けにとにかく気軽に使いたいならpgAdminでしょう。
特にGUIクライアントの需要が高いであろうWindows版であれば、PostgreSQLのインストーラーに同梱されていますので、使い始めが簡単ですし、簡単な監視、SQLチューニングまでいろいろできます。

日本語対応しました！

知る人ぞ知る、pgAdminの開発メーリングリスト。
ついに日本語対応しました。日本PostgreSQLユーザ会の有志で数ヶ月にわたる翻訳を行い、この度日本語対応することができました。

日本語化する方法

PostgreSQL10に同梱されているpgAmind4 v2以降、簡単に日本語化することができます。既に稼働しているpgAdmin4を日本語化する手順を説明します。

・翻訳ファイルの配置
まずは以下のファイルを手元にダウンロードしておいてください。
poファイルのダウンロード
こちらのリンク先で、「messages.po」「messages.mo」をそれぞれ入手しておいてください。（注：右クリックで「リンク先を保存」では正しく保存できませんでした。私の場合、pgadmin4のトップでソースコード全体をDOWNLOADしました）

Windowsの場合、デフォルトで「C:\Program Files\PostgreSQL\10\pgAdmin 4\web\pgadmin\translations」に各国語の翻訳ファイルが配置されていますので、「ja\LC_MESSAGES」フォルダを作成し、両ファイルを配置します。

・設定変更
「C:\Program Files\PostgreSQL\10\pgAdmin 4\web」にpgAdmin起動時に読み込まれる「config.py」がありますので、これをテキストエディタで開き、一か所編集します。

'ja','Japanese'の行を追加します。'Polish'の後のカンマも忘れずに。
－－－
# Languages we support in the UI
LANGUAGES = {
    'en': 'English',
    'zh': 'Chinese (Simplified)',
    'de': 'German',
    'pl': 'Polish',
    'ja': 'Japanese'
}
－－－

・pgAdminの起動と設定変更
追加したフォルダのパスや設定が間違っていたりするとpgAdminが上手く起動しません。私は↑の設定ファイルでカンマを付け忘れて数時間悩みましたｗ

pgAdmin4を起動し、上部メニューの「File」-「Preferences」より設定変更を行います。
「Miscellaneous」-「User Language」で使用する言語を選択します。ここでは config.pyファイルに記述した言語が候補になります。

変更はすぐには反映されませんので、「File」-「Reset Layout」します。

なにやら警告が。pgAdminから実行中の処理がある場合は気をつけましょう。

日本語になりました！

ところで、最初から日本語に対応した版が出るのはもう少し先になりそうです。そして今は日本語化しても英語交じりで「ん？！」って感じです。パッと見は確かに日本語なのですが、実用的なメニューではどこが変わったのよ？！という方に日本語版おススメポイントを1つ挙げておきます。

ちょっとマニアックな部分ですが、SQL実行する「Query Tool」では（そもそも「Query Tool」を日本語にしてよ、という意見が・・・(∩ﾟдﾟ)ｱｰｱｰきこえなーい）
雷アイコンでユーザーが書いたSQLを実行します。が、ん？実行どこ？！って思ってました。昔のpgAdmin3は雷アイコンが黄色かったのでなんとなく目についたんですよね。

「どれだよ実行～」と思って探していくと、雷アイコンのマウスオーバーでちゃんと日本語ヘルプが出ますし、複数の実行オプションがあるのですが、こういうマニアックなメニューが日本語になっているのは嬉しい気がします。
（「EXPLAIN ANALYZE」はSQLコマンドなのでこのまま英語表記かな・・・）

以上です。

明日はやっさん（yassan168）さんがデータ分析でPostgreSQLな話をしてくれるそうです！

2017年12月1日金曜日

コピペで試す PG10 の 3ステップレプリケーション！

本記事は PostgreSQL Advent Calendar 2017 の1日目です。

のっけから23時台の更新とひやひやさせてすみません。

■何を書くか

11/3に開催されたPostgreSQL Conforence 2017 JapanでPostgreSQLのレプリケーションに関するチュートリアルを受け持ったのですが、その準備中、PG10でレプリケーション作成の簡単さにびっくりしたのです。当然チュートリアル内でもその簡単さを熱く語ったわけですが、それを書こうと思います。

PostgreSQLレプリケーション（pgcon17j_t4） from Kosuke Kida

■PG10のレプリケーションはこうなった！
・パラメータのデフォルト値が変わって、簡単にレプリケーションできるようになった
・ロジカルレプリケーションができるようになった

その他にも細かい話題はあるにせよ、このうち、ロジレプの陰で感動するほど簡単になったレプリケーション作成を取り上げたいと思います。

■ステップ0：インストール
AWSでEC2インスタンス（CentOS）にPostgreSQLをインストールします。

$ sudo yum install wget

$ wget https://download.postgresql.org/pub/repos/yum/testing/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

$ sudo rpm -ivh pgdg-centos10-10-2.noarch.rpm

$ sudo yum install postgresql10 postgresql10-server postgresql10-contrib postgresql10-devel

$ sudo vi /usr/lib/systemd/system/postgresql-10.service

---起動スクリプトのパスを変更---

# Location of database directory

# Environment=PGDATA=/var/lib/pgsql/10/data/

Environment=PGDATA=/home/postgres/data/

$ su - postgres

$ vi .bash_profile

---ユーザーの環境変数を変更---

### edit for PostgreSQL10

export PGDATA=/home/postgres/data

export PATH=/usr/pgsql-10/bin:.:$PATH

---

プライマリのデータベースを作成しておきます。

$ sudo systemctl start postgresql-10.service

$ sudo systemctl status postgresql-10.service

$ su – postgres

$ createuser -d -r -l -P demo

$ createdb -O demo demodb

サンプルデータも入れておきましょう。

$ psql -U demo demodb

demodb=> create table sample (a int,b text);

demodb=> insert into sample values (1,'test1');

■ステップ1：プライマリ側の設定

レプリケーション用のユーザーを作成し、ユーザーのアクセス制御をpg_hba.confに書きます。

$ createuser --replication rep_user

$ vi $PGDATA/pg_hba.conf

----pg_hba.confを編集---

TYPE	DB	USER	CIDR-ADDRESS	METHOD
host	replication	rep_user	192.168.10.0/24	trust
host	all	rep_user	0.0.0.0/0	reject

---

このように、アクセス許可設定はもちろのこと、拒否設定もすると良いと思ってます。

レプリケーション用のパラメータを修正します。ここがポイント。デフォルト値が変更になり、（最小ではパラメータ変更なしでも）レプリケーションできます。

$ vi $PGDATA/postgresql.conf

パラメータ	設定	説明
listen_addresses	*	（通常はDB作成後にほぼ必須で実施）
wal_level	replica	レプリケーションに必要なWAL情報を生成
max_wal_senders	10	起動可能なwal senderプロセスの上限
max_replication_slots	10	作成可能なレプリケーションスロットの上限
synchronous_standby_names	任意	同期スタンバイの名前を指定
synchronous_commit	on	同期レベルを指定
hot_standby	on	自身がスタンバイの時に参照可能とする
hot_standby_feedback	on	自身の情報をプライマリに送信

自分がスタンバイになったときのために、サンプルのrecovery.confを作成しておきます。

$ vi $PGDATA/recovery.conf.node1

パラメータ	設定	説明
standby_mode	on	起動時にスタンバイモードになる
primary_conninfo	プライマリへの接続情報
primary_slot_name	slot2	プライマリのレプリケーションスロット名
recovery_target_timeline	latest	最新のマスターに追従する設定

■ステップ2：スタンバイの作成

スタンバイ側でpg_basebackupを使ってプライマリのバックアップを取得します。取得したデータはそのまま展開され、スタンバイのデータディレクトリになります。

$ pg_basebackup -U rep_user -h <node1_ip> -p 5432 -D /home/postgres/data

$ ls -ltr $PGDATA

drwx------. 3 postgres postgres 60 Oct 28 15:44 pg_wal

drwx------. 6 postgres postgres 54 Oct 28 15:44 base

drwx------. 2 postgres postgres 4096 Oct 28 15:44 global

drwx------. 2 postgres postgres 32 Oct 28 15:44 log

-rw-------. 1 postgres postgres 22844 Oct 28 15:44 postgresql.conf

-rw-------. 1 postgres postgres 88 Oct 28 15:44 postgresql.auto.conf

-rw-rw-r--. 1 postgres postgres 169 Oct 28 15:44 recovery.conf.node1

-rw-------. 1 postgres postgres 4760 Oct 28 15:44 pg_hba.conf

　：

スタンバイサーバーの設定もプライマリから取得したバックアップに含まれているのでほぼそのまま流用できます。変更すべき点は、recovery.confです。これはレプリケーション時にスタンバイがプライマリに接続しにいくための設定ですので、常にレプリケーションの相手ノードの情報が書いておくものだからです。

$ cp $PGDATA/recovery.conf.node1 $PGDATA/recovery.conf.node2

$ vi $PGDATA/recovery.conf.node2

primary_conninfo	プライマリへの接続情報
primary_slot_name	slot1	プライマリのレプリケーションスロット名

この2行をプライマリへの接続情報に変更します。

■ステップ3：スタンバイを起動

レプリケーションスロットを使う場合、スタンバイ起動時に、必ずレプリケーションスロットを作成、または状態を確認するようにします。（レプリケーションスロットで運用する場合。）

recovery.confに書いた primary_slot_name がまさにそのための設定で、ここで指定したスロットがマスター側に存在しないとレプリケーションできません。

プライマリでレプリケーションスロットを作成します。

$ su - postgres

$ psql -h <node1_ip> -U postgres postgres

postgres=# select pg_create_physical_replication_slot('slot1');

スタンバイでデータベースを起動します。

$ sudo systemctl start postgresql-10.service

$ su - postgres

$ ps x

11089 ? Ss 0:00 /usr/pgsql-10/bin/postmaster -D /home/postgres/data/

11093 ? Ss 0:00 postgres: startup process recovering 000000010000000000000005

11097 ? Ss 0:00 postgres: wal receiver process streaming 0/5000140

ほら、レプリケーションできました！

今回は、AWSでたぶん一番簡単にPostgreSQLをインストールするところから、レプリケーションができるまでをコピペして使えるそのままのコマンドで紹介しました。

（書式がぐっちゃぐちゃで申し訳なさすぎます。見栄えはあとでコソッと変更します。）

明日は、Morihayaさんが担当してくださいます。

2017年10月24日火曜日

PostgreSQLでサンプルデータを生成する

PostgreSQLカンファレンスのチュートリアルトラックに向けて準備して（後輩のお世話、というか半強制的に「やります！」と言わせたセッションなので手を差し伸べて）るんだけど、インデックスチューニングの話をするのに美しくランダムなデータが欲しいわけじゃん。

というわけで、Let's PostgreSQLの記事を参考にいろんな型のサンプルデータを作るSQLを作成した。

SELECT * FROM
 (SELECT i
        ,(random()*1000)::int%80                                               num_sample
        ,(now()-(random()*1000)::int%365*interval'1day')::date                 date_sample
        ,(now()-(random()*1000000000)::int*interval'1microseconds')::timestamp ts_sample
        ,lpad(string_agg(str1,''),8)                                           text_sample1
        ,lpad(string_agg(str2,''),4)                                           text_sample2
        ,string_agg(str3, '')                                                  mbyte_sample
  FROM ( SELECT
                 i,
                 chr(97+(random() * 1000)::int % 26 )    as str1,
                 chr(97+(random() * 10)::int % 3 )       as str2,
                 chr(12354+(random() * 1000)::int % 85 ) as str3
         FROM generate_series(1,20) length
             ,generate_series(1,1000) num(i)
         )t
  GROUP BY i) datagen
;

2016年12月20日火曜日

PostgreSQL 9.6 パラレルクエリに関する小ネタ

よく考えたら当たり前なんだけど、いざ気付くと「えーっ！」となった衝撃的な話。

「パラレルクエリは、マテリアライズド・ビューのリフレッシュに使えない」疑惑

パラレルクエリの制限事項って結構あるんですね。そのうちの1つを9.6.1時点のマニュアルから抜粋すると

クエリがデータを書き込むか、データベースの行をロックする場合。クエリがデータ更新操作をトップレベルあるいはCTE内で含むと、そのクエリに対するパラレルプランは生成されません。これは現在の実装の制限で、将来のリリースでは解除される可能性があります。

要はDMLはダメと言っているように見える。じゃあマテビュー作るのもダメじゃね？という当然の話ではあるのですが。夜間に巨大なテーブルの加工や集計（そんな処理を数十分とか、数時間かけて）した結果をマテビューにして、なんとか日中はサクサク動くように頑張るわけじゃないですか。
で、パラレルクエリが入ったときに、「くそ重いロックをとるマテビューのリフレッシュが、2時間かかってたやつが1分になったら（キラキラ）」などと夢をいだいたものです。ところがそれが現状のパラレルクエリでは何も変わらないのです。がびーん。

そんなわけで、実際に試してみることにします。わざわざ実行計画を見るのは~~めんどい~~マテビューでは内部で実行されたクエリの実行計画は覆い隠されてしまうので、単に所要時間で（psqlの\timingで）パラったか判断します。

今回使う表（今回、表の中身は重要ではない。件数と所要時間だけ注目）

demo=# \timing
demo=# SELECT count(*) FROM tab1;
  count
----------
 28864000
(1 行)
時間: 669.988 ms   --- パラレルの時は1秒以下、非パラレルだと4秒強かかることを確認済み。

/* 実行計画はこんな感じ */
demo=# EXPLAIN ANALYZE
demo-# SELECT count(*) FROM tab1;
                                                                 QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=333435.96..333435.97 rows=1 width=8) (actual time=1015.604..1015.604 rows=1 loops=1)
   ->  Gather  (cost=333435.33..333435.94 rows=6 width=8) (actual time=1015.510..1015.594 rows=7 loops=1)
         Workers Planned: 6
         Workers Launched: 6
         ->  Partial Aggregate  (cost=332435.33..332435.34 rows=1 width=8) (actual time=1008.983..1008.983 rows=1 loops=7)
               ->  Parallel Seq Scan on tab1  (cost=0.00..320408.67 rows=4810667 width=0) (actual time=0.043..622.811 rows=4123429 loops=7)
 Planning time: 0.126 ms
 Execution time: 1017.730 ms

/* テーブルサイズはこれぐらい */
demo=# SELECT pg_size_pretty(pg_relation_size('tab1'));
 pg_size_pretty
----------------
 2127 MB

マテリアライズド･ビューを作成してリフレッシュ

demo=# CREATE MATERIALIZED VIEW mv1 AS
demo-# SELECT count(*) FROM tab1;
SELECT 1
時間: 4510.077 ms   --- む、この時間は・・・。

demo=# REFRESH MATERIALIZED VIEW mv1;
REFRESH MATERIALIZED VIEW
時間: 4451.020 ms   --- む、この時間は・・・。

うーん、パラられない。残念でならない！
というわけで、何かそれに代わる策がないか試してみます。

VIEWの場合

demo=# CREATE VIEW v2 AS
SELECT count(*) FROM tab1;
CREATE VIEW
時間: 2.151 ms
demo=# SELECT * FROM v2;
  count
----------
 28864000
(1 行)

時間: 682.303 ms --- viewならOKだよねうん。

まあ、これは大丈夫か。SELECTだけだし。でも結果を保存して再利用できない。

普通の表をつくってINSERT SEELCT

demo=# CREATE TABLE t3 (count numeric);
CREATE TABLE

demo=# INSERT INTO t3
demo-# SELECT count(*) FROM tab1;
INSERT 0 1
時間: 4506.088 ms --- だめ

だめ。デスヨネー。

外部テーブルを介してMビュー。

外部テーブルで問合せた先は、リモートのサーバー側でよしなに実行計画を作ってくれるはずなので淡い期待を込めてやってみるが・・・

demo=# CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
demo-# OPTIONS (dbname 'demo',host 'localhost',port '5432'); -- 自分自身を見に行くFDW
CREATE SERVER
時間: 1.331 ms
demo=# CREATE USER MAPPING FOR public SERVER loopback
demo-# OPTIONS (user 'postgres',password 'postgres');
CREATE USER MAPPING
時間: 0.914 ms
demo=# IMPORT FOREIGN SCHEMA public LIMIT TO (tab1)
demo-# FROM SERVER loopback
demo-# INTO loopback;
IMPORT FOREIGN SCHEMA
時間: 7.039 ms
demo=# EXPLAIN ANALYZE
demo-# SELECT * FROM loopback.tab1 WHERE sales_id = 0;　-- 一応ループバックしてFOREIGN SCANしてることを確認
                                                   QUERY PLAN

----------------------------------------------------------------------------------------------------------------
 Foreign Scan on tab1  (cost=100.00..117.59 rows=3 width=112) (actual time=396.211..4527.848 rows=1152 loops=1)
 Planning time: 0.286 ms
 Execution time: 4528.583 ms
(3 行)

時間: 4529.705 ms

demo=# CREATE MATERIALIZED VIEW mv2 AS
demo-# SELECT count(*) FROM loopback.tab1;
SELECT 1
時間: 71687.426 ms  ---パラレルかどうかよりaggrigate push downが効いてないせいかな。

demo=# REFRESH MATERIALIZED VIEW mv2;
REFRESH MATERIALIZED VIEW
時間: 68418.958 ms

ダメ。むしろダメ。Aggrigateのpush downはまだ実装されてないんだった。全件もってきて集計してるんだとしたら、仮にリモートでパラレルで動いてくれても、ネットワークが重すぎる。

\copy と COPY

demo=# \copy (SELECT count(*) FROM tab1) TO demo.copy;
COPY 1
時間: 4518.860 ms --ダメ

demo=# COPY (SELECT count(*) FROM tab1) TO '/home/enterprisedb/demo.copy';
COPY 1
時間: 4524.612 ms --ダメ

だめ。うーん、これぐらいはイケると思ったんだが。

\oでファイルに出力

demo=# \o demo2.copy
demo=# SELECT count(*) FROM tab1; -- OK
時間: 699.871 ms

OK！うれしい！

psqlの引数としてクエリを投げ、結果をファイルに出力

[postgres@demo ~]$ time psql -U postgres demo -At -c 'SELECT count(*) FROM tab1' -o demo3.copy

real    0m0.725s -- OK
user    0m0.003s
sys     0m0.009s

[postgres@demo ~]$ psql -U postgres demo -At -c "COPY t3 FROM '/home/enterprisedb/demo3.copy'"
　-- これで格納もできる。（t3は上部検証で作成したやつ）
  -- もちろん、集計ずみの1行だけ格納するので一瞬で終わる。

OK！うれしい！これだ感！これができればシェルとかプログラムで簡単にマテビューチックなことができる、はず。

2016年12月16日金曜日

PostgreSQL 9.6のパラレルが間違う件！

本投稿は、PostgreSQL Advent Calendar 2016 の 12/16 担当です。
昨日は「.NET Core で PostgreSQL を使ってみたら結構いけていたという話」を @creativewebjp さんが書いてくださいました。

■パラレルクエリと戯れて

さて、Ver9.6の登場までポスグレ界の話題を総ざらいしていたパラレルクエリですが、みなさん使ってますか？
大量データの領域で真価を発揮するものあって、なかなか現実的なサイズで試せていないのが現状で、採用に二の足を踏んでしまう、なんて話もちらほら聞いています。私は仕事でEnterpriseDB社が開発するPostgreSQLの商用版、EDB Postgresを担当していますが、そのβテストを兼ねてOSS版PostgreSQLのパラレル動作をみていたのですが、やはり試してみてわかることが結構ありました。
採用するかどうか迷っている方がいたら、とっかかりは非常に簡単なので、是非トライして、自身のアプリケーションでどの程度使えるかを評価してみるべきだと思います。バッチリとハマったときの効果は抜群ですよ。

とは言え、良いときの性能評価はそこそこ出回っていると思うので、今回は、案外うまくいかないパラレルクエリの話を紹介します。

■いろいろ検証して得られた「間違うケース」

間違うケース1　でかい表同士をJOINするとき

前提知識として、PostgreSQLのパラレル・クエリは、表のサイズで並列度が決定されます。
適当な例で言うなら、10GBの表は8並列、500MBの表は4並列ぐらいで動きます。
んまあ、これはあくまでも単一の表をFROM句に書いてSELECTした場合に、これぐらいの並列度が良さそうと判断されたにすぎないです。
10GBを

1プロセスで読むコスト　>　8プロセスで分担して、後でGather（集約）するコスト

と判断したので、そういう実行計画が選択されるということですね。

これが、JOINの場合はどうやら変わってくるようです。
2つの表をパラレルで読んで、Gatherするとなると、そのオーバーヘッドの方が大きくなってしまって、せっかくパラレルにしたのに所要時間はむしろ長くなってしまいます。

どちらもパラレルが効くぐらい大きい表なので、適切にパラレル処理して欲しいところではありますが、単純に表のサイズだけでは決められず、どちらか一方だけパラレルに動いてくれた方が良い結果になるのです。もちろんそういう目論見で、プランナはどちらか一方の表をパラレルで読もうとします。
問題は、PostgreSQLだと「どちらの表をパラレルで処理するか？」の調整が非常に難しいこと。約10GBのlineoder表と、約500MBのpart表をごく単純に結合してみると、このような実行計画になりました。

　/* 実行したクエリ（ごく単純なJOIN） */
EXPLAIN ANALYZE
SELECT lo_partkey,sum(lo_quantity)
FROM   lineorder
JOIN   part
       ON    lo_partkey = p_partkey 
WHERE  p_name LIKE 'k%'
GROUP BY lo_partkey;

 /* いかにもパラレルが効きそうなlineorderが普通にSeqScanされてる */
->  Hash Join  (cost=28661.34..3682122.56 rows=1111820 width=9) 
               (actual time=67.061..57496.965 rows=1286337 loops=1)
->  Seq Scan on lineorder  (cost=0.00..3214594.56 rows=114066256 width=9)
                         ★(actual time=0.010..27750.208 rows=114066226 loops=1)
      ->  Hash  (cost=28490.76..28490.76 rows=13646 width=4) 
                (actual time=66.904..66.904 rows=15731 loops=1)
            Buckets: 16384  Batches: 1  Memory Usage: 682kB
            ->  Gather  (cost=1000.00..28490.76 rows=13646 width=4) 
                        (actual time=0.342..61.640 rows=15731 loops=1)
                  Workers Planned: 3
                  Workers Launched: 3
                  ->  Parallel Seq Scan on part  (cost=0.00..26126.16 rows=4402 width=4) 
                                               ★(actual time=0.029..59.519 rows=3933 loops=4)
                        Filter: ((p_name)::text ~~ 'k%'::text)
                        Rows Removed by Filter: 346067

今回のケースでは、どうしても小さい方の表をパラレルにしたがる（先に読むからだろうか？）で、大きい方の表をシーケンシャルに読んでしまいました。

効果は未確認ですが、解決策としては、ALTER TABLEでそのテーブルを読むときに使うワーカーの数を指定できるので、それでpartテーブルの方は0に固定してしまうことでしょうか。
ただし、「こっちクエリではpart表をパラレルに読んでほしいんだよね～」という要望に応えるのが難しくなります。

そこでEDB Postgresです。EDB Postgresでは、オプティマイザ・ヒントを指定でき、2017年1月にリリース予定のEDB 9.6ではパラレル・ヒントに対応します。そこで実行計画をいじくるとこんな感じです。

　/* EDB版の実行したクエリ（パラレル・ヒントで各表の並列度を指定） */
EXPLAIN ANALYZE
SELECT /*+ PARALLEL(part 0) PARALLEL(lineorder 4) */ lo_partkey,sum(lo_quantity)
FROM   lineorder
JOIN   part
       ON    lo_partkey = p_partkey 
WHERE  p_name LIKE 'k%'
GROUP BY lo_partkey;

 /* スキャンの並列度が変わり、Hash Joinまでパラレル実行している */
HashAggregate  (cost=2624706.98..2636997.29 rows=983225 width=36) 
               (actual time=17414.085..17421.061 rows=11272 loops=1)
   Group Key: lineorder.lo_partkey
   ->  Gather  (cost=39151.57..2619147.88 rows=1111820 width=9) 
               (actual time=181.809..16260.314 rows=1286337 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Hash Join  (cost=38151.57..2506965.88 rows=1111820 width=9) 
                      ☆(actual time=233.851..16886.113 rows=257267 loops=5)
               Hash Cond: (lineorder.lo_partkey = part.p_partkey)
               ->  Parallel Seq Scan on lineorder  (cost=0.00..2359097.64 rows=28516564 width=9) 
                                                 ★(actual time=0.034..8187.008 rows=22813245 loops=5)
               ->  Hash  (cost=37981.00..37981.00 rows=13646 width=4) 
                         (actual time=233.525..233.525 rows=15731 loops=5)
                     Buckets: 16384  Batches: 1  Memory Usage: 682kB
                     ->  Seq Scan on part  (cost=0.00..37981.00 rows=13646 width=4) 
                                         ★(actual time=0.026..225.321 rows=15731 loops=5)

狙った表をパラレルスキャンさせることで、PostgreSQLで生じていた問題をバッチリ解決することができました。

間違うケース2　Window関数なのか、ソートなのか。原因不明（ヲイ！）

PostgreSQLでは、Windows関数つかって、それでないと1クエリで得ることがなかなか難しい複雑な集計を任せることができます。
とは言えその複雑さはなかなかなもので、さぞかしプランナは頭を悩ませることでしょう。

以前に本ブログで書いた「PostgreSQLらしい集計クエリ」で紹介しているテーブルで、データを何重にもいれまくって、2GBぐらいの表を用意しました。それを集計してみます。

　/* window関数 */
EXPLAIN ANALYZE
SELECT *,
    rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
    SELECT
        sales_date::date as "売上日"
       ,shop             as "ブランド"
       ,sum(price)       as "売上"
    FROM  tab1
    GROUP BY "売上日","ブランド") AS "集計";

 /* 結果イメージ。ブランド毎に売上の多かった日ランキング */
   売上日   |  ブランド  |   売上    | 順位
------------+------------+-----------+------
 2016-05-11 | DIESEL     | 632141504 |    1
 2016-07-31 | DIESEL     | 622096896 |    2
 2016-11-29 | DIESEL     | 620818688 |    3
　　：
 2016-04-06 | Dior       | 962435003 |    1
 2016-03-23 | Dior       | 889015872 |    2
    ：

 /* この時の実行計画がこちら。パラレルになってる。約9秒。 */
 WindowAgg  (cost=3430456.39..3454473.19 rows=1200840 width=52) (actual time=9776.175..9777.199 rows=1095 loops=1)
   ->  Sort  (cost=3430456.39..3433458.49 rows=1200840 width=44) (actual time=9776.165..9776.300 rows=1095 loops=1)
         Sort Key: "集計"."ブランド", "集計"."売上" DESC
         Sort Method: quicksort  Memory: 134kB
         ->  Subquery Scan on "集計"  (cost=3115228.89..3235312.89 rows=1200840 width=44)
                                      (actual time=9764.055..9773.283 rows=1095 loops=1)
               ->  Finalize GroupAggregate  (cost=3115228.89..3223304.49 rows=1200840 width=44)
                                            (actual time=9764.054..9773.062 rows=1095 loops=1)
                     Group Key: ((tab1.sales_date)::date), tab1.shop
                     ->  Sort  (cost=3115228.89..3133241.49 rows=7205040 width=44)
                               (actual time=9764.012..9765.509 rows=7665 loops=1)
                           Sort Key: ((tab1.sales_date)::date), tab1.shop
                           Sort Method: quicksort  Memory: 1270kB
                           ->  Gather  (cost=1064650.75..1851272.12 rows=7205040 width=44)
                                       (actual time=6412.121..9757.054 rows=7665 loops=1)
                                 Workers Planned: 6
                                 Workers Launched: 6
                                 ->  Partial GroupAggregate  (cost=1063650.75..1129768.12 rows=1200840 width=44)
                                                             (actual time=6518.572..9655.286 rows=1095 loops=7)
                                       Group Key: ((tab1.sales_date)::date), tab1.shop
                                       ->  Sort  (cost=1063650.75..1075676.95 rows=4810477 width=19)
                                                 (actual time=6516.858..8298.955 rows=4123429 loops=7)
                                             Sort Key: ((tab1.sales_date)::date), tab1.shop
                                           ☆Sort Method: external merge  Disk: 125680kB
                                             ->  Parallel Seq Scan on tab1  (cost=0.00..332432.96 rows=4810477 width=19)
                                                                          ★(actual time=0.071..1195.131 rows=4123429loops=7)

お、パラレルになった。
しかし、これ、Disk Sortになっていますね。メモリソートになればもっと早くなるんじゃ。SET work_mem to '2GB' などとして再実行

あれ？

 /* 実行計画がこちら。メモリソートになったらなぜか非パラレルに。約20秒かかっている。 */
 WindowAgg  (cost=1000838.70..1024855.50 rows=1200840 width=52) (actual time=21083.366..21084.609 rows=1095 loops=1)
   ->  Sort  (cost=1000838.70..1003840.80 rows=1200840 width=44) (actual time=21083.347..21083.521 rows=1095 loops=1)
         Sort Key: "集計"."ブランド", "集計"."売上" DESC
         Sort Method: quicksort  Memory: 134kB
         ->  Subquery Scan on "集計"  (cost=849559.20..879580.20 rows=1200840 width=44)
                                      (actual time=21075.482..21080.224 rows=1095 loops=1)
               ->  HashAggregate  (cost=849559.20..867571.80 rows=1200840 width=44)
                                  (actual time=21075.480..21080.024 rows=1095 loops=1)
                     Group Key: (tab1.sales_date)::date, tab1.shop
                     ->  Seq Scan on tab1  (cost=0.00..633087.75 rows=28862860 width=19)
                                         ★(actual time=0.064..7888.289 rows=28864000 loops=1)
 Planning time: 0.234 ms
 Execution time: 21097.712 ms

なんと、ソートが軽量に済むやいなや、パラレル動くことをやめちゃいました。なんてこった。ディスクソートしていたときのほうが圧倒的に早いので、このケースこそプランナが間違えてるとしか思えません。

しかもこれ、上記のJOINのケースのようにパラレルさせないという選択ではなく、パラレルして欲しい方向なので、これ以上PostgreSQLでは調整のやりようがないです。困った。

そんなときはEDB Postgres
ヒントを使って・・・・

EXPLAIN ANALYZE
SELECT /*+ PARALLEL(tab1 6) */ *,
    rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
    SELECT
        sales_date::date as "売上日"
        ,shop            as "ブランド"
        ,sum(price)      as "売上"
    FROM  tab1
    GROUP BY "売上日","ブランド") AS "集計";

あれ。

悩むこと数十分。サブクエリの側にヒントを入れて、無事解決しました。

 /* EDBでヒントをいれてみる。正解はこの位置 */
EXPLAIN ANALYZE
SELECT *,
    rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
    SELECT  /*+ PARALLEL(tab1 6) */
        sales_date::date as "売上日"
        ,shop            as "ブランド"
        ,sum(price)      as "売上"
    FROM  tab1
    GROUP BY "売上日","ブランド") AS "集計":

 /* EDBでヒントを使うと、パラレルスキャンし、メモリソートもできた */
 WindowAgg  (cost=1327297.76..1351662.32 rows=1218228 width=56) (actual time=9667.291..10095.048 rows=441334 loops=1)
   ->  Sort  (cost=1327297.76..1330343.33 rows=1218228 width=48) (actual time=9667.270..9746.242 rows=441334 loops=1)
         Sort Key: "集計"."ブランド", "集計"."売上" DESC
         Sort Method: quicksort  Memory: 46768kB
         ->  Subquery Scan on "集計"  (cost=1176747.00..1204157.13 rows=1218228 width=48) (actual time=8986.449..9216.731 rows=441334 loops=1)
               ->  Finalize HashAggregate  (cost=1176747.00..1191974.85 rows=1218228 width=48) (actual time=8986.447..9129.055 rows=441334 loops=1)
                     Group Key: tab1.sales_date, tab1.shop
                     ->  Gather  (cost=357488.67..1103653.32 rows=7309368 width=48) (actual time=3751.793..5775.296 rows=3081355 loops=1)
                           Workers Planned: 6
                           Workers Launched: 6
                           ->  Partial HashAggregate  (cost=356488.67..371716.52 rows=1218228 width=48) (actual time=3736.607..4506.717 rows=440194 loops=7)
                                 Group Key: tab1.sales_date, tab1.shop
                                 ->  Parallel Seq Scan on tab1  (cost=0.00..320408.67 rows=4810667 width=23) (actual time=0.041..870.314 rows=4123429 loops=7)

ぶっちゃけactual timeを見ると元々9秒だったのが10秒と、むしろ若干悪いのだが・・・ディスクソートを排除したいという欲望を満たすべく試したらこうなった、と言う話。たぶんデータ量とかでこの辺はセンシティブに最適解は変わってしまうのだと思います。

間違・・・わないケース3　グループ集計（GROUPING SETS）

結論から言うと、いろいろ頑張ったけど思ったより早くならないのでプランナが正しかった！説があります。以下のクエリを実行してみると、PostgreSQLで一向にパラレルになってくれません。

 /* グループ集計のクエリ */
EXPLAIN ANALYZE
SELECT
    shop            AS "ブランド"
    ,kind           AS "種類"
    ,count(*)       AS "数量"
FROM tab1
GROUP BY GROUPING SETS (("ブランド"),("種類"),());

 /* 結果のイメージ */
  ブランド  |    種類    |   数量
------------+------------+----------
 DIESEL     |            |  8448000
 Paul Smith |            | 15488000
 dior       |            |  4928000
            |            | 28864000
            | シャツ     | 15488000
            | ジャケット |  4224000
            | パンツ     |  9152000

 /* PostgreSQLでの実行計画 パラレルにならない */
 GroupAggregate  (cost=4137591.06..8147200.27 rows=14 width=26)
                 (actual time=27350.631..48641.465 rows=7 loops=1)
   Group Key: shop
   Group Key: ()
   Sort Key: kind
     Group Key: kind
   ->  Sort  (cost=4137591.06..4209751.06 rows=28864000 width=18)
             (actual time=22549.349..26069.469 rows=28864000 loops=1)
         Sort Key: shop
         Sort Method: quicksort  Memory: 2953433kB
         ->  Seq Scan on tab1  (cost=0.00..560942.00 rows=28864000 width=18)
                               (actual time=0.025..5461.728 rows=28864000 loops=1)
 Planning time: 0.160 ms
 Execution time: 49108.929 ms

EDBでヒントをいれてみます。

 GroupAggregate  (cost=6784457.73..10794066.94 rows=14 width=26)
                 (actual time=45959.909..70152.520 rows=7 loops=1)
   Group Key: shop
   Group Key: ()
   Sort Key: kind
     Group Key: kind
   ->  Sort  (cost=6784457.73..6856617.73 rows=28864000 width=18)
             (actual time=39595.620..44537.665 rows=28864000 loops=1)
         Sort Key: shop
         Sort Method: quicksort  Memory: 2953433kB
         ->  Gather  (cost=1000.00..3207808.67 rows=28864000 width=18)
                     (actual time=0.442..18630.270 rows=28864000 loops=1)
               Workers Planned: 6
               Workers Launched: 6
               ->  Parallel Seq Scan on tab1  (cost=0.00..320408.67 rows=4810667 width=18)
                                              (actual time=0.052..1111.439 rows=4123429 loops=7)
 Planning time: 0.130 ms
 Execution time: 70680.047 ms

パラレル度が上がったが早くならないケースもある。ということで。

■総評

パラレルクエリで効果を得づらいケースを3パターン紹介しました。

これはたまたま私が検証する中で気付いたもので、他にもあるかもしれないし、賢い解決策があるのかもしれません。
本投稿では触れていませんが、同じ検証の中で、1クエリ実行するのに50分かかっていた処理が、パラレルクエリとテーブル・パーティショニングの組合せで8秒まで短縮していますから、ハマれば効果は抜群です。
しかしながら、間違うケースもこうやってポンポン出てきたということは、これから採用を考えている人は、自身のアプリケーションでどの程度性能がでるか、どんな書き方をすれば効果がでるか、（そしてEDBにすればどう変わるか。ぐへへ！）是非試してみて欲しいと思います。
策をいろいろ考えるのはパズルみたいで楽しかったです。

明日はぬこ@横浜さんが書いてくれます。楽しみです！

登録: 投稿 (Atom)

KKIDA-GALAXY

2018年2月12日月曜日

pgAdmin4をバージョンアップする（Windows版）

2017年12月13日水曜日

pgAdmin4を日本語化する

pgAdmin4とは？

pgAdmin4、こんな人におススメ

日本語対応しました！

日本語化する方法

2017年12月1日金曜日

コピペで試す PG10 の 3ステップレプリケーション！

2017年10月24日火曜日

PostgreSQLでサンプルデータを生成する

2016年12月20日火曜日

PostgreSQL 9.6 パラレルクエリに関する小ネタ

「パラレルクエリは、マテリアライズド・ビューのリフレッシュに使えない」疑惑

マテリアライズド･ビューを作成してリフレッシュ

VIEWの場合

普通の表をつくってINSERT SEELCT

外部テーブルを介してMビュー。

\copy と COPY

\oでファイルに出力

psqlの引数としてクエリを投げ、結果をファイルに出力

2016年12月16日金曜日

PostgreSQL 9.6のパラレルが間違う件！

■パラレルクエリと戯れて

■いろいろ検証して得られた「間違うケース」

間違うケース1　でかい表同士をJOINするとき

間違うケース2　Window関数なのか、ソートなのか。原因不明（ヲイ！）

間違・・・わないケース3　グループ集計（GROUPING SETS）

■総評

PostgreSQL11のJITコンパイリングを試す

このブログを検索

2018年2月12日月曜日

2017年12月13日水曜日

pgAdmin4とは？

pgAdmin4、こんな人におススメ

日本語対応しました！

日本語化する方法

2017年12月1日金曜日

2017年10月24日火曜日

2016年12月20日火曜日

「パラレルクエリは、マテリアライズド・ビューのリフレッシュに使えない」疑惑

マテリアライズド･ビューを作成してリフレッシュ

VIEWの場合

普通の表をつくってINSERT SEELCT

外部テーブルを介してMビュー。

\copy と COPY

\oでファイルに出力

psqlの引数としてクエリを投げ、結果をファイルに出力

2016年12月16日金曜日

■パラレルクエリと戯れて

■いろいろ検証して得られた「間違うケース」

間違うケース1 でかい表同士をJOINするとき

間違うケース2 Window関数なのか、ソートなのか。原因不明（ヲイ！）

間違・・・わないケース3 グループ集計（GROUPING SETS）

■総評

間違うケース1　でかい表同士をJOINするとき

間違うケース2　Window関数なのか、ソートなのか。原因不明（ヲイ！）

間違・・・わないケース3　グループ集計（GROUPING SETS）