postgres_fdwによるDB垂直分割


## 困っていたこと
- 某Webサービス用DBServerの Disk usage が 79% まで膨張した
 - 近い将来、最大TABLEの水平分割工事をする余裕を残す必要あり
- 汎用ログの格納TABLE action_logs のカット率が75%
 - ユーザ端末のバッテリー状況とか、Wifi通信エラーレートとか 
 - 25%あれば全体統計をみるには十分だが、個々の状況の時間推移は追えない 

----

## 不可視バグでDBServerが危ない

- 端末log送信は直接目に見えないところなので、バグが仕込まれやすい
- バグがDailyAccessUser数の倍率で飛んでくる
- 例1:通信回復時の再送処理の考慮漏れで、毎正時(`*:00:00`)にPOST殺到
- 例2:(key,Value)のValueがやたらと長いlogが急増

----

## 対策概要

- action_logs 専用の DBServer を追加する
- postgres_fdw: Foreign Data Wrapper で連携させる
 - 既存DB に `CREATE FOREIGN TABLE ....` すれば**コード変更一切なし**
 - SQLだけの移行処理で対応可能

----
## postgres_fdw?

- 外部のPostgreSQLサーバに格納されたデータをアクセスする
- 以前のdblinkモジュールが提供する機能と重複
 - より透過的で標準に準拠した構文
 - 多くの場合においてより良い性能

- [こんな図になる](http://image.slidesharecdn.com/lt-ingaoho-jsonbpostgeresfdw-150131171142-conversion-gate01/95/lt-ingaohojsonbpostgeres-fdw-14-638.jpg?cb=1422724350)
- [インガオホー！ jsonb + postgres_fdw ](http://www.slideshare.net/toshiharada/lt-ingaohojsonbpostgeres-fdw)  by ぬこ＠横浜 サン

----

## 遅くならない？

内部TABLEと外部TABLE のFULL JOINを含むとか、そんなことをすれば、外部サーバとの通信量が跳ね上がって性能劣化するのは、postgres_fdw も dblink も同じ。
幸いなことに action_logs 系は、ひたすら INSERT するのみ。
通信オーバーヘッドは上乗せされるが、定数項だろう。

----

## 効果見積

直近で、合計 XXXGB を本体DBから削減可能。

サイズ・IO処理が本体から切り離されるので、POST api/logs を100%記録することも可能になる。

----

## 将来展開

action_logs の垂直分割に成功したら

- ほかにも運用でINSERTだけして、分析DBにコピーしてからSELECTしているTABLEがある
- そっちに適用拡大するのは容易
- エンドユーザにも必要なTABLEは、本体DBとの一体的Backup/Snapshotが必要になって面倒。今はそっちに拡大する予定はない。

----

## 移行リハーサル

- 移行前に 該当POSTを rewrite.conf で全て成功応答にする
 - Apache で遮断して、DB の TABLEの更新停止 
- 移行所要時間
 - 既存の行を新DBServerに移す処理が最大
 - 直近の合計で数千万行
 - 通常の **INSERT でトランザクション処理すると推定10時間**くらい
 - **COPY文のTSV出力（1分）、scp（10分）、COPY文のTSV入力（10分）**

----

## 本体DB側コード（fdw定義）

```sql
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER logdb FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'logdb', dbname 'product_x' , port '5432' );

GRANT ALL ON FOREIGN SERVER logdb TO product_x;

CREATE USER MAPPING FOR product_x SERVER logdb 
  OPTIONS ( user 'product_x' , password 'xxxxxx' );

```

----

## 本体DB側コード（外部テーブル定義）

```sql
CREATE FOREIGN TABLE schema_x.action_logs_new
(
  id bigint NOT NULL DEFAULT nextval('action_logs_new_id_seq'::regclass),
  child_id bigint NOT NULL,
-- ...
  created_at timestamp without time zone NOT NULL
) SERVER logdb
  OPTIONS (schema_name 'schema_x', table_name 'action_logs');
```

----

## データExportコード

```sql
COPY (SELECT * FROM schema_x.action_logs WHERE id >= 120000000 AND id < 130000000)
 TO '/tmp/action_logs_12.tsv' WITH DELIMITER E'\t' CSV;
 
COPY (SELECT * FROM schema_x.action_logs WHERE id >= 130000000 AND id < 140000000)
 TO '/tmp/action_logs_13.tsv' WITH DELIMITER E'\t' CSV;

-- $ scp -p host_db_1:/tmp/action_*.tsv .
-- $ scp -p ./action_*.tsv host_db_2:/tmp/
-- $ ssh host_db_1 sudo rm /tmp/action_*.tsv
```

----

## データImportコード

```sql
COPY action_logs_12 FROM '/tmp/action_logs_12.tsv' WITH DELIMITER E'\t' CSV;
COPY action_logs_13 FROM '/tmp/action_logs_13.tsv' WITH DELIMITER E'\t' CSV;
```

あとは、本体DBの対象TABLEを rename で 現行/new を入れ替える。

----

##  性能検証

- 過負荷ストレステストのJMeterがあったので再利用
 - 運用実績の180倍速
- 90%保証応答時間, 99%保証応答時間(msec)

| |分離前|分離後|⊿ |
|:---|---:|---:|--:|
|90%値|285|387|+36%|
|99%値|439|551|+26%|

----

## 検証結果

- POST 単体は 3-4割応答が遅くなる
- 本体DBServer の負荷は、一部外部Serverに移るので軽くなる

※社内用には Muninグラフがあった

----

## 運用コスト

幸い、某DBServer用AWS instanceのReserved期間の使い残しがある。
その間に、運用環境で必要な性能をみきわめて最適化する。

----

## 第2次性能検証

運用環境での実需に寄せた性能検証シナリオにして、必要十分なところを探す。

- リクエスト数
- 処理時間平均
- 平日ピーク 17:00-23:00 の6hに全集中するとして 全日POST数/6hのrequest/sec  
- この3倍を性能目標とした

----

## POST内容

運用action_log実績

- 90%応答時間 168msec
- 平均 4行INSERT/POST。最大100行 が約1000POST。
- 平均 label長 60文字、最大4440文字

JMeterシナリオを上記をもとに修正

----

## 検証結果

|Type|IOPS|90%|99%|503rate|rps|
|:--------|---:|----:|----:|----:|----:|
|m3.xlarge|4000|   74|  264|0.45||
|m3.large |4000|   77|  272|0.41||
|m3.large |1000|   75|  269|0.45||
|m3.medium|4000|   82|  283|0.50||
|m3.medium|1000|   87|  317|0.54||

2016-8時点の実需にそった計測条件で、レスポンスタイムにも、503発生率もほとんど影響のないところを選択して、 **m3.large, 1000IOPS** とする。
**$188/月で垂直分割**できた（前払い無し1年間リザーブド）。

----

## 上司講評

綿密な考証、入念な事前準備。
本体サービスは無停止で、ログ記録を止めた時間も20分程度。
原理は複雑だけど、実際にやっていること、変更点を見ると、こんなに簡単にできるのかと拍子抜けするほどシンプル。
運用上のコストを下げることも考えられていて良かった。