釣りキチ翔平の備忘録 
はじめに
この記事では、
特にパフォーマンスチューニング時
アプリケーションエンジニアも理解しておくと役に立つ部分に絞って、
PostgreSQLの内部構造について図を多めに使ってまとめました。
PostgreSQLを普段を使ってて内部構造に興味がある方、
リリース間際に性能問題が発覚してパフォーマンスチューニングすることになった方、
データベースが好きな方に参考にして頂けると幸いです。
僕は今までデータベース関連だと以下のようなことをしてきましたが、
- データベーススペシャリスト、Oracle Master Blonze 12cの資格取得
- 数億規模のテーブルを持つアプリケーションのクエリチューニング(SQL Server)
- 5TPS→100TPSほどのパフォーマンスチューニング(クエリチューニングやパラメータ設定変更など)
- SQL Serverのデータベース本番運用
- 現場で10年ほどテーブル設計
その辺りの経験も踏まえつつ、
今までの経験からして重要そうなところだけに絞ってまとめてみました。
PostgreSQLのディスク〜メモリ〜プロセスとパフォーマンスチューニングのポイント
まずはパフォーマンスチューニング時にしていると役に立つ部分に絞って
PostgreSQLの内部構造(ディスク、メモリ、プロセス)についてみていきます。
ざっくりこの辺りが抑えておきたい部分です。
ここからプロセス動作について解説しつつ、
よくあるパフォーマンスチューニングの例もセットで解説していきます。
まずはWALライタプロセスからみていきます。
WALライタはメモリ上のWALバッファをディスク上のWALファイルに書き出しにいきます。
これによってPostgreSQLがクラッシュした際、
後で解説するチェックポイントとWALファイルを使って
クラッシュ直前の状態に戻すことができます。
(メモリにあるだけだとクラッシュ時に復元できない)
WALバッファにはテーブルにレコードが登録・更新、
テーブル自体が更新されたなどの情報を記録します。
実はレコードが登録・更新されると共有バッファにも同じように記録しますが、
システムがクラッシュした時にはWALを利用して復旧するために、
WALバッファ(→WALファイル)→共有バッファ→データファイル
のようにまずはWALが記録されます。
また、パフォーマンスチューニングの観点で言うと、
短時間に多くの更新クエリが発行されたりすると、
すぐにWALバッファがいっぱいになってしまい、
ディスク書き込みに頻繁に行くことになります。
ディスクI/Oはメモリ上での読み書きと違って速度が遅いため、
PostgreSQLの性能が思うように出なくなることがあります。
そのような場合はwal_bufferのサイズを調整してやることで
ディスクI/Oを減らしてパフォーマンスチューニングをしたりします。
(実はWALバッファが短時間でいっぱいになると、
この後解説するチェックポイントが頻繁に発生するのでそのチューニングも必要です)
次にチェックポインタをみていきます。
チェックポインタは設定に従って一定時間が経過するとチェックポイントを発生させます。
チェックポイントは、現在実行中のトランザクションも含めてディスクに記録します。
このチェックポイントで記録した情報と、
次のチェックポイントまでにWALファイルに記録した情報を利用して
PostgreSQLがクラッシュした際に、
整合性の取れたクラッシュ直前のコミット断面を復元できます。
(ここは文字だと分かりずらいので図を作ります・・・)
パフォーマンスチューニングの観点で言うと、
チェックポイントが頻発すると図の通りディスクI/Oが発生して、
本来の性能が出ないケースがあります。
そのためチェックポイントの頻度は本番運用では監視し、
頻度が高いようであれば、
恐らくWALバッファが頻繁にいっぱいになっている可能性が高いので、
min_wal_sizeとmax_wal_sizeの設定を行います。
(WALバッファは使い回すのでmin_wal_sizeはmax_wal_sizeに揃えるのが推奨)
次はバキュームワーカーについてみていきます。
PostgreSQLは追記型の仕組みで、
レコードをdeleteしてもupdateしてもデータをinsertするように新規で追加し、
更新前や削除前のデータを無効扱いにする特徴があります。
(後でイメージ図を入れます・・・)
そのため更新前や削除前の無効になったデータを削除(厳密に言うとその領域を再利用可能にする)
するのがバキュームワーカーの役目です。
図の通り空き領域を探して再利用可能な領域があれば空き領域マップを更新しています。
(空き領域が全くないテーブルは可視性マップを見ると判断できるので、
無駄なディスクI/Oを減らすために可視性マップを利用しています)
次はバックエンドプロセスについてみていきます。
バックエンドプロセスはWebアプリからデータベース接続したり、
ローカルPCでクライアントツールを利用してデータベースを接続したりすると、
そのクライアントからの接続単位で起動するプロセスで、
接続単位でメモリも確保します。
パフォーマンスの観点での注意事項としては
特にsortやgroup byしてavgで集計などするクエリが発行されると、
作業メモリ上で計算を行います。
そのため、作業メモリサイズを設定する際は、
同時接続クライアント数も考慮しつつ設定する必要があります。
メモリを大きくしても同時接続数が多く結局メモリが足りないために、
一時ファイルに書き出してディスクI/Oが頻発する。
みたいなことにならないように注意しましょう！！
最後にライタープロセスをみていきます。
ライタープロセスは共有バッファのメモリ上のデータを定期的にデータファイルに書き出します。
selectやupdateといったクエリを発行すると、
ディスクから共有バッファにキャッシュしてそこから参照したり、
共有バッファ上のキャッシュを更新します。
(ディスクの読み書きよりもメモリの読み書きのほうが高速に行えるため)
共有バッファ上で更新した内容を定期的にディスクに書き出すのがライタープロセスの役割です。
パフォーマンスチューニングの観点で言うと、
共有バッファのサイズは128MBなので本番運用する際は恐らく設定変更をする必要があります。
(共有バッファのサイズが小さいとディスク書き出しを頻繁に行うことになり本来の性能が出ないため)
ただ、メモリサイズを大きくするだけだと、
メモリのテーブルページ(仮想メモリと物理メモリをマッピングするもの)のサイズが大きくなり、
CPUの負荷もかかるためOSのHuge Page機能を有効にすることで対応します。
Huge Pageを有効にすることで、
1テーブルページでマッピングできるメモリサイズを増やすことができ、
テーブルページサイズとCPU負荷を減らすことができます。
まとめ
今回はパフォーマンスチューニングに必要なポイントに絞って
PostgreSQLの内部構造について見てきましたが、
性能問題が発生した際、もっと言うと発生する前から
この辺り内部構造とパフォーマンスチューニングの関係を
ふわっとでも誰かが見ておいてなんとなく意識しているチームと、
リリース直前まで誰も全く知らないし意識していないチームとでは、
全然安定感が違うと思います。
僕自身DBAもいなくてリリース直前で誰もデータベースの内部構造や
パフォーマンスチューニングしたことないから全部一から見たりさせられて
辛い経験をしてきましたが。。。
この記事がそういう状況に陥らないお役に立てたら幸いです。
他にもデータベース関連について現場で役立つネタをまとめ記事にしているので
興味ある方はこちらも参照して見てください！！
現場で役立つデータベースの基礎知識まとめ
参考文献
