弊社では、RDBMSにPostgreSQLを利用して数年間サービスを運営しています。
PostgreSQLはMySQLと違って、Webサービスでの運用事例をあまり見かけないので、今回は弊社サービスの「夜行バス比較なび 」でどのように運用しているかを紹介いたします。
システムの特徴
- ユーザからのアクセスは、9割が参照処理。
- データはバッチ処理で、随時 ( 毎分 ) 更新されている。
- 参照SQLの結果はmemcachedを利用してキャッシュをしているが、データの更新頻度が高いため長時間のキャッシュはしていない。
- 参照SQLは、集計処理が多いため比較的重いSQL。
- 参照対象となるテーブルのデータ量は、最大で数100万レコードと比較的少ない。
- 24/7で稼働。
構成
AWSのEC2上に、PostgreSQL 9.3を導入しています。c4系のインスタンスを使いたいので、RDSは使っていません。インスタンスは複数台で構成しており、ストリーミングレプリケーションを利用してマスタ・スレーブ形式を取っており、アクセスの増減に応じて、Web,DBサーバを増減させています。
パラメータ設定(チューニング)
多くのパラメータはデフォルトのまま使用おり、一般的に変更が推奨されているパラメータを適切な値にチューニングしています。
主要なパラメータは以下のように設定しています。
- max_connections
- pgpool-IIの コネクション数 + α (バッチ処理・管理接続)
- shared_buffers
- サーバメモリの25%
- effective_cache_size
- サーバメモリの50%
- work_mem
- 一般的にはあまり大きくする必要はないが、集計処理が多いので大きめの値に設定
- checkpoint_segments
- timeoutトリガー (checkpoint_timeoutの値)で、チェックポイントを発生させるため、300など大きい値に設定。PostgreSQL9.5から廃止されるらしい。
- checkpoint_timeout
- チェックポイントの多発をさけるため、15分ほどに設定。
参考ページ
ストリーミングレプリケーション
ストリーミングレプリケーションは、PostgreSQL9.0で登場した新しい機能ですが、トラブルなく安定して運用できています。 以前までレプリケーションには、サードパーティー制のツールを利用する必要がありましたが、現時点ではストリーミングレプリケーションの選定がベストだと考えています。
レプリケーションの同期方式は4種類ありますが、スレーブで障害が発生しても全体としては障害にならない「local」を利用しています。 同期方式については、以下の記事が詳しいです。
http://blog.nedia.ne.jp/2014/11/06/3421.htmlblog.nedia.ne.jp
pgpool-II
pgpool-IIは、アプリケーションとPostgreSQLの間を中継する形で動作するミドルウェアです。 アプリケーションのコードの変更が不要のため、既に運用中のサービスに対しても比較的容易に導入することができます。
pgpool-IIは多機能ですが、弊社サービスではコネクションプール、クエリ分散機能のみを利用しています。
(レプリケーションやクエリキャッシュなどの他の機能は、PostgreSQL標準機能や他ミドルウェアで賄っているため利用していません)
pgpool-IIについては、詳しくは下記の記事に記載しました。
pgpool-II 入門（インストールと簡易設定） - LCL Engineers' Blog
pgpool-IIによるクエリ分散
Webアプリケーションのクエリは、負荷分散のためpgpool-IIでマスタとスレーブへ分散させています。 以下の点を考慮してクエリ分散してくれるため、アプリケーション側での考慮が不要で済みます。
- 登録・更新系のクエリは、マスタのみに送る
- 参照系のクエリは、 設定ファイルに定義された割合に応じてマスタとスレーブへ分散
- 各ノードに障害が発生した場合は、自動的に切り離してクエリを振り分けない
バッチ処理は、クエリ分散させる必要が無いので、pgpool-IIを利用していません。
pgpool-IIの配置(インストール)場所
pgpool-IIは専用サーバに配置することもできますが、以下の理由からWebサーバ上に配置しています。
- pgpool-IIのために、サーバを追加する必要がない。
- Webサーバが複数台構成のため、pgpool-IIも自動的に複数台に導入されことになるため、単一障害点となることを避けられる。
- 専用サーバで単一障害点を避けるには、watchdogという機能を利用してHA構成を組む必要がある。
pgpool-IIの構成ファイルはchefで管理しており、Webサーバが増えても管理が煩雑にならないようにしています。
バックアップ
現時時点では、バックアップ対象のDBのサイズがそれほど大きくないことや、Point-In-Time-リカバリ(PITR)が必須ではないので、pg_dumpを利用してバックアップを行っています。
今後、PITRが必要になった場合は、pg_rmanの利用を検討しています。(PostgreSQL標準機能だけでは、バックアップ・リカバリ手順がとても煩雑なので・・)
監視
各インスタンスにZabbixを導入し、プロセスの死活監視やリソース監視のみを行っています。 pg_monzというZabbixのテンプレートがありますが、現時点では利用していません。Newrelicなどを併用することで、大きな問題なく運用出来ています。
※ 2018年現在は、Zabbixからマカレルへ移行しています。
レプリケーションの遅延が発生していないかどうかは、以下のSQLを定期的に発行してチェックしています。
SELECT pg_last_xact_replay_timestamp()
テーブル定義の変更
サービス稼働中に安全にテーブル定義を変更する方法は、下記にまとめました。
vacuum
vacuumについては、特別なことは全くしていません。デフォルトのまま autovacuumを利用して安定して運用出来ています。
pg_repackを利用したテーブル・インデックスの再編成
弊社のサービスでは、頻繁にレコードを削除・更新を繰り返しているため、テーブル・インデックスはかなり肥大化してしまいます。
そのため、pg_repackというツールを利用して定期的に再編成を行っています。
pg_repackは、排他ロックが一瞬しかかからないため、アクセスが少ない時間帯であれば全くサービスへ影響することなく再編成ができます。
まとめ
PostgreSQLを運用する上での弊社事例を、項目別に紹介致しました。 各項目について、今回の記事では簡単な紹介にとどめていますので、今後より詳細な情報も公開していきたいと考えています。
