困っていたこと

某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モジュールが提供する機能と重複
より透過的で標準に準拠した構文
多くの場合においてより良い性能
こんな図になる
インガオホー！ jsonb + postgres_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定義）

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側コード（外部テーブル定義）

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コード

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コード

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
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分程度。
原理は複雑だけど、実際にやっていること、変更点を見ると、こんなに簡単にできるのかと拍子抜けするほどシンプル。
運用上のコストを下げることも考えられていて良かった。