狠狠撸

位置情報を使ったサービス「スマ
ポ」をPostgreSQLで作ってみた
株式会社スポットライト

浅羽義之

はじめに
? この資料はスマポではこうやって運用していますとい

う事例を紹介します。

? 他の環境でもそのまま使えるかについてはみなさんの

ご判断の下、参考にしていただけると幸いですが、何
かあっても無保証でお願いします。

会社绍介

http://www.smapo.jp/

位置情報を使ったサービス「スマポ」をPostgreSQLで作ってみた db tech showcase 2013 Tokyo

なぜ笔辞蝉迟驳谤别厂蚕尝か

PostgreSQL選定理由

? 位置情報を扱いたい

? （特に初期段階では）サービス運営のランニングコス

トを抑えたい

? マニュアルが充実している、等

shop B

200m

100m

300m

shop A
現在地から

近い順にソート

shop C

http://postgis.net/docs/manual-2.0/ST_Distance.html

サーバ構成
? AWSを利用

? Instance

? m1.large

* 2 (master/slave, availability zoneを分けている)

? ELB→App

Servers→DB Servers

? EBS

? provisioned

IOPS volume (IOPS=1000)

? 分析環境は別のデータセンターに配置

NEWRELIC
? EnterpriseDB

が公開してい
るnewrelic
pluginを利用

? webappのパ

フォーマンス
監視も可能

VACUUM/ANALYZE
? VACUUM/ANALYZEは今のところautovacuum任せ

? UPDATEの多いテーブルはFILLFACTORを設定

? HOT

UPDATEと呼ばれる最適化を効かせるため

? http://lets.postgresql.jp/documents/tutorial/hot_1/

例：FILLFACTOR=90
? すごくざっくりした説明です

8KB
8KB
…

PageHeader lineptr1(lp) lp2 lp3 lp4

…

8KB

lpN

FreeSpace

(90%超えたら次のページ)

…

8KB
tuple3

…

tuple2

tupleN
tuple4

tuple1 SpecialSpace

REPLICATION != BACKUP
? replicationは冗長化?負荷分散が目的

? backupはオペミスした時にも復旧できないといけない

? PITR(Point

In Time Recovery)

STREAMING REPLICATION
? PostgreSQL

9.0から導入された機能

? SLAVEはRead-onlyなDBとして動かすことも可能

? hot

standby

hot_standby = on

max_standby_streaming_delay = 90s
slaveのpostgresql.conf

wal_level = hot_standby

max_wal_senders = 5

wal_keep_segments = 16
masterの
postgresql.conf

standby_mode = 'on'

primary_conninfo = 'host=x.x.x.x port=5432 user=repl_user
password=XXXXXXX'

restore_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e
wal-fetch "%f" "%p"'
slaveのrecovery.conf

WAL-E
? https://github.com/wal-e/wal-e
Amazon S3

backup-push
wal-push

PostgreSQL

(master)

archive_mode = on

archive_command = 'envdir /etc/wal-e.d/
env /usr/local/bin/wal-e wal-push %p'
postgresql.conf

backup-fetch
wal-fetch
PostgreSQL

(slave)

PostgreSQL

(analytics)

standby_mode = 'on'

restore_command = 'envdir /etc/wal-e.d/
env /usr/local/bin/wal-e wal-fetch "%f" "%p"'
recovery.conf

REPLICATION監視

? select

* from pg_stat_replication;

? replicationが正常に動いていればslaveの数だけ結果が

帰ってくる

? masterで実行

ANALYTICS DB
? ?uentdでログデータを回収し、分析専用のPostgreSQL

へ格納

? 各種KPIをPostgreSQLで集計

? マシンパワーが必要なので、物理サーバで構築

データをキャッシュに載せる
? cache?

? PostgreSQLのshared_buffer

? OSのページキャッシュ(free

-mで確認)

? 他にもあるが省略

? サーバ再起動時などはキャッシュがクリアされている?

載せ方
? shared_buffer

? SELECT

count(*) FROM sample_table

? インデックスは載らないので注意

? page

cache

? (ionice

-c 3) cat 物理ファイル > /dev/null

ファイルの場所
test=# SELECT relname, current_setting('data_directory') || '/' ||
pg_relation_filepath(oid) as filepath, pg_relation_size(oid) as filesize FROM
pg_class WHERE relname = 'sample_table';

!
relname | filepath | filesize

--------------+-----------------------------------------------+----------

sample_table | /var/lib/postgresql/9.2/test/base/16385/18572 | 1351680

(1 row)
test=# SELECT relname, current_setting('data_directory') || '/' ||
pg_relation_filepath(oid) as filepath, pg_relation_size(oid) as filesize FROM
pg_class WHERE oid IN (SELECT indexrelid FROM pg_index WHERE indrelid =
(SELECT oid FROM pg_class WHERE relname = ‘sample_table'));

!
relname | filepath | filesize

--------------------+----------------------------------------------+----------

sample_table_pkey | /var/lib/postgresql/9.2/test/base/16385/22203 | 16384

idx_sample_table_b | /var/lib/postgresql/9.2/test/base/16385/22205 | 16384

idx_sample_table_c | /var/lib/postgresql/9.2/test/base/16385/22206 | 16384

何を見ようか？
? newrelicのレスポンスタイム

? slow

query log

? log_min_duration_statement

= 1s

? newrelic/cloudwatchのサーバステータス

参照のチューニング
? EXPLAIN ANALYZEでどの実行プランが遅いか確認

? table

scanが遅い

? indexが無い
? table

or indexが不適切？

joinが遅い

? ANALYZEが足りないか確認

? limit句がある場合はsubqueryにしてJOINの回数を減らせるか試す

EXPLAIN ANALYZE
? 実行計画がそれぞれどれくらい時間かかったか見るこ

とが可能
test=# EXPLAIN ANALYZE SELECT t1.a, t2.a FROM t1, t2 WHERE t1.a = t2.a and t1.a < 100;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------

Nested Loop (cost=0.85..805.31 rows=95 width=8) (actual time=0.038..3.319 rows=99 loops=1)

-> Index Only Scan using t1_pkey on t1 (cost=0.42..10.09 rows=95 width=4) (actual
time=0.012..0.449 rows=99 loops=1)

Index Cond: (a < 100)

Heap Fetches: 99

-> Index Only Scan using idx_t2_a on t2 (cost=0.42..8.36 rows=1 width=4) (actual time=0.006..0.011
rows=1 loops=99)

Index Cond: (a = t1.a)

Heap Fetches: 99

プランノード
コスト
実行時間
Total runtime: 3.782 ms

(8 rows)

やったこと
? 使ってほしいインデックスが使われていないのでクエ

リを書き換え

? インデックスないものは追加

? JOINしたあとにLIMITしていた箇所は、先にLIMITしてか

らJOINするように変更
SELECT *FROM a, b WHERE a.id = b.id and a.hoge >= 1000 LIMIT10;

↓

SELECT* FROM (SELECT * FROM a WHERE a.hoge >= 1000 LIMIT10) as aa,
b WHERE aa.id = b.id

PostgreSQL 9.1→9.2
PostGIS 1.5→2.0
tool

down
time

comment
postgisのupgradeに

pg_upgrade

△

pg_dump/
pg_restore

?

停止時間が長い

slony-I

?

面倒

対応していない？

MASTER切り替え
AppServer

PostgreSQL

(旧master)

slonyのreplication

PostgreSQL

(新master)

streaming replication
PostgreSQL

(旧slave)

PostgreSQL

(新slave)

MASTER切り替え
AppServer

PostgreSQL

(旧master)

slonyのreplication

停止

PostgreSQL

(新master)

streaming replication
PostgreSQL

(旧slave)

PostgreSQL

(新slave)

結局pg_dump/pg_restoreやりま
した。すみません。
9.2→9.3でリベンジ予定。

SELECT WAITING
? DB

migration時などにロック待ちが発生

? psコマンド

? ps

aux | grep postgres | grep waiting

? SELECT

* FROM pg_locks;

? もう少し細かくロックの獲得状況を確認できる

クエリキャンセル
? クエリをキャンセル

? SELECT
? or

pg_cancel_backend(pid);

kill -INT pid

? クエリをキャンセルしてバックエンドプロセスを落とす

? SELECT
? or

pg_terminate_backend(pid)

kill -TERM pid

デッドロックの原因
? ロックを取る順番が異なるため

? テーブルロック、行ロックなど

? ロック獲得

? 明示的なロック

? 暗黙的なロック

実際に起きたケース（簡略版）
2013-10-30 00:11:22 JST DETAIL: Process 3225 waits for ShareLock on transaction
11759339; blocked by process 3040.

Process 3040 waits for ShareLock on transaction 11759337; blocked by process 3225.

Process 3225:

insert into foo (x,y,z)

select x, 12345, z

from bar

where id in (( values (1), (2), (3) ) except ( select x from foo where date = '...'))

!

Process 3040:


select x, 12345, z

from bar

where id in (( values (2), (3), (1) ) except( select x from foo where date = '...'))

!

調査
? 前提：開発環境でやること

? postgresql.conf

? log_statement

= all

? log_line_pre?xに%pをつける

? COMMIT直前のpg_locksを確認

一次調査でわかったこと
? テーブルロックの順番は同じ

? そもそも強いロックレベルでテーブルロック取ってない

? INSERTなのでfooに対する行ロックはあるのか？

? 要はよくわからなかった

? ただ、確かにinsertで片方が待たされる

止まっている箇所を調べる
? PostgreSQLをデバッグビルド

? CFLAGS=-O0

debug

? 片方でSELECT
? gdb

./con?gure —pre?x=$HOME —enable-

pg_backend_pid()

-p backendのpid

BACK TRACE
(gdb) bt

…

#5 0x00000000006f0ada in LockAcquire (locktag=0x7fff7ce28d20, lockmode=5,
sessionLock=0 '000', dontWait=0 '000') at lock.c:662

#6 0x00000000006effec in XactLockTableWait (xid=768) at lmgr.c:495

#7 0x00000000004890e4 in _bt_doinsert (rel=0x7fa0c1634838, itup=0x2122468,
checkUnique=UNIQUE_CHECK_YES, heapRel=0x7fa0c162f820) at nbtinsert.c:168

#8 0x000000000048f3b4 in btinsert (fcinfo=0x7fff7ce28e40) at nbtree.c:257

#9 0x0000000000819bb5 in FunctionCall6Coll (?info=0x2115650, collation=0,
arg1=140328416004152, arg2=140735288611488, arg3=140735288611840,
arg4=34743148, arg5=140328415983648, arg6=1) at fmgr.c:1439

#10 0x0000000000487c64 in index_insert (indexRelation=0x7fa0c1634838,
values=0x7fff7ce292a0, isnull=0x7fff7ce29400 "", heap_t_ctid=0x212236c,
heapRelation=0x7fa0c162f820, checkUnique=UNIQUE_CHECK_YES) at indexam.c:216

#11 0x00000000005f29aa in ExecInsertIndexTuples (slot=0x21167c0, tupleid=0x212236c,
estate=0x2115e60) at execUtils.c:1087

#12 0x0000000000605273 in ExecInsert (slot=0x21167c0, planSlot=0x21167c0,
estate=0x2115e60, canSetTag=1 '001') at nodeModifyTable.c:248

create table tt(a text unique);

select pg_backend_pid();

begin;

insert into tt values (‘aa’), (‘bb’)

select pg_backend_pid();

begin;

insert into tt values (‘bb’), (‘aa’)

psql

psql

(gdb) b _bt_doinsert

Breakpoint 1 at 0x488fc7: ?le nbtinsert.c, line 106.

(gdb) c

Continuing.

!
Breakpoint 1, _bt_doinsert (….) at nbtinsert.c:106

(gdb) c

Continuing.

!

‘aa’をinsertしたところで止める

(gdb) b _bt_doinsert

Breakpoint 1 at 0x488fc7: ?le nbtinsert.c, line 106.

(gdb) c

Continuing.

!

(gdb) c

Continuing.

!

‘bb’をinsertしたところで止める

!
(gdb) c

!
(gdb) c

gdb

gdb

回避方法
2013-10-30 00:11:22 JST DETAIL: Process 3225 waits for ShareLock on transaction
11759339; blocked by process 3040.

Process 3040 waits for ShareLock on transaction 11759337; blocked by process 3225.

Process 3225:


select x, 12345, z

from bar

where x in (( values (1), (2), (3) ) except ( select x from foo where date = ‘…’))

ORDER BY x

!

Process 3040:


select x, 12345, z

from bar

where x in (( values (2), (3), (1) ) except( select x from foo where date = ‘...'))

ORDER BY x

!

9/9 19:00-21:00
NTV
有吉ゼミ

AWS
? instanceをscale
? m1.large
? app

up

→ m3.2xlarge

server増強

? Elastic

LoadBalancerのpre-warming

PERFORMANCE TEST
? pgbenchを使用

? tps(transaction

per second)を知ることができる

? -f

実行したいSQLファイル

? -c

並列数

? -t

トランザクション数

? New

Relic/CloudWatchでパフォーマンスを監視

当日

? warmupを直前で実施

? master/slave

? あとは祈るのみ

振り返り
? 3000over
? app

req/sec

serverが持ちこたえられない時間帯(数分)が発生

? ゴールデン放送のアクセスはすげー

? DBについては、checkpointのチューニングが甘く、

disk書き込みが結構発生した

10/614:00-16:00
NTV
有吉ゼミ(再)

再放送をたまたま気がついた
のが2日前

（教えてよ。。。）

postgresql.conf
?

memory

?

?

shared_buffer=搭載メモリの1/4程度

planner

?

effective_cache_size=page cacheのサイ
ズ

?

wal_buffer=16MB

?

?

random_page_cost=2.0

checkpoint

?
?

lock

checkpoint_segments = 64

?

?

checkpoint_timeout = 1h

?

checkpoint_completion_target = 0.9

!

deadlock_timeout = 10s

振り返り
? 問題なく捌けた

? 再放送は連絡こないので要注意

4000%?

PostgreSQL使ってみて
? スタートアップのサービスで使うRDBMSとしてよい

? 機能面、コスト面

? もちろんMySQLもすごいと思います

? 運用も工夫次第で色々できる

? 一台で結構なリクエスト数を耐えられる！

エンジニア絶賛募集中！

(特にインフラやりたい人)

http://www.smapo.jp/recruit/index.html

狠狠撸

位置情報を使ったサービス「スマポ」をPostgreSQLで作ってみた db tech showcase 2013 Tokyo

Recommended

More Related Content

What's hot (18)

Similar to 位置情報を使ったサービス「スマポ」をPostgreSQLで作ってみた db tech showcase 2013 Tokyo (20)

Recently uploaded (11)

位置情報を使ったサービス「スマポ」をPostgreSQLで作ってみた db tech showcase 2013 Tokyo