第34回PostgreSQL勉強会備忘録
講演1「PostgreSQL9.6 パラレルクエリの本当のところ」
EDB Postgres
企業ユーザ向けの機能追加（Oracleとの互換性など）、エンタープライズ向け製品。
→PostgreSQL本体へのフィードバックを行っている。PostgreSQLのバージョンアップも反映される。
大規模データの分析→パラレルクエリ
テーブルサイズに合わせてワーカーが複数立ち上がり、それぞれのワーカーが範囲を処理しGATHERでまとめる。
Oracleは、レンジ - ハッシュパーティションで分割し1プロセス内で複数のパーティションを担当する。
→テーブル
日付の条件が効かなくても高速になる。（プルニング）
実行計画：EXPLAIN ANALYZE
window関数で集計結果をselectする。
課題
- パラレル・クエリを用いてもgatherの際に行が多いと速度は遅くなってしまう。
- マテリアライズド・ビューの更新ができない。
Q: 100秒を10秒にするというケースでイメージ。
使うのであれば、バッチ処理等になる。
テーブルスキャン→共有ロック、更新できない？パラレル・クエリを使うような全件抽出が間違っている？パーティションが次考えられるのでは？
A: 更新を邪魔するものではないのでフルスキャンは可能。”Alter table”などが邪魔される。また、パーティションとパラレルクエリを組み合わせて利用することは可能。
用語集
マテリアライズド・ビュー
実体を持つVIEW
http://www.ne.jp/asahi/hishidama/home/tech/oracle/mview.html
完全リフレッシュ→全データの再計算を行う。
高速リフレッシュ→更新のあったデータのみ再計算を行う。
パーティショニング
http://lets.postgresql.jp/documents/technical/partitioning/1
データを複数に分割して格納することです。データを分割することにより、性能や運用性が向上し、故障の影響を局所化することができます。巨大なデータを扱う場合にはパーティショニング機能を利用することも検討してみてください。
テーブル間の分割・ノード間の分割がある。
テーブル間の分割 = 巨大なテーブルを複数のテーブルに分割する、postgresqlでは複数のテーブルを一つのテーブルとして見せる機能がある。
ノード間の分割 = 複数台のノードを使うクラスタ構成になる。PGCluster ・ PL/Proxyを利用することになる。
分割は「レンジ」・「ハッシュ」「リスト」の3種類の分割方法がある。
「レンジ」・・・重ならないような範囲に分割する、日や月単位で分割することが多い。。
「リスト」・・・一定数の選択肢から選択する列がある場合に使用できる。例えば地域IDを元に都道府県で分割するなど。ただしパーティションサイズの偏りが発生する可能性がある。
「ハッシュ」・・・HASH値にもとづいて分割する。
パーティショニングの利点→性能面・管理面
・検索範囲の絞込、条件に合わないパーティションは検索対象から外れるため検索範囲の絞込が可能になる。
・キャッシュの有効活用、頻繁に使うレコードが存在するパーティションをキャッシュすることができる。PostgreSQLのキャッシュは8kBのページ単位で行われるためページ単位で頻度が低いレコードが存在する場合使用価値の低いキャッシュとなってしまう。
個別のパーティションについて意識せず親テーブルのみをアプリケーションが意識すればよい事になる。
パーティショニングの難点
・複数のパーティションに跨るINDEXが貼れない（グローバル・インデックス）
・INSERT性能の低下、親テーブルから子テーブルへの振り分けが必要になるため。
PGCluster
http://pgcluster.projects.pgfoundry.org/jp/feature.html
負荷分散のためのレプリケーション構成を取る。同期レプリケーションのため遅延が発生しない。マルチマスタ構成を取る
PL/Proxy
http://blog.goo.ne.jp/xmldtp/e/9da51f74757aa0441bb3bd3aa76ed4f9
ウェブからは一つのサーバにアクセスしているように見えて複数に分散させてアクセスすることができる。ロードバランサーのようなもの。
BRINインデックス
http://pgsqldeepdive.blogspot.jp/2015/07/brin-index-part1.html
BRIN = Brock-Range Index
「複数のデータブロックを束ねて、その最大値／最小値をサマリとして保持するタイプのインデックス」
一部の範囲のレコードをまとめて取り出す際に有効になる。
・・・PostgreSQLのテーブルファイルは、8kBごとにブロックの連続で構成されている。削除時は論理削除が行われVACUUMで物理削除が行われる。
シーケンシャルスキャン インデックススキャン
データを一つずつ順番に取り出して条件に一致するか確認していく検索のやり方
- ディスクソート メモリソート
メモリ上で展開してソート処理を行えば良いがサイズが大きい際にはディスクソートになり処理速度の低下につながる。
講演2 DBAサバイバルガイド～「pg_stats_reporterで性能トラブルを洗い出せ」
実績のある設定値の共有→※改善：社内共有体制
###用語
DAU
Daily Active User、日ごとにサービスを利用しているユーザ
負荷スパイク
Webサイトに対し急激にアクセスが増えること
tup_per_seq : 行数/スキャン回数
アドホッククエリ
使い捨てのクエリ、頻発するとプロシージャーキャッシュにコンパイル結果がたまりメモリを圧迫する。ストアド・プロシージャを利用してSQL実行計画を再利用する。
http://blogs.yahoo.co.jp/dk521123/30669342.html
単一列index・複数列index
http://itdoc.hitachi.co.jp/manuals/3000/30003F5500/EEXD0043.HTM
複数列INDEXの場合は、第一検索条件は=（イコール）で検索できるものにすることが望ましい。
講演3 PostgreSQL 10: What to look for?（Under development）
Amit Langote
メジャーバージョンアップを10.0, 11.0という採番で更新していく。
10.0 features
・並列処理（parallelism）
論理レプリケーション・パーティショニング・FDW（外部データラッパー）
・クエリ最適化のための統計（Statics for query oprimization）
・並列処理（Parallelism）
9.6 introduced limited pararell query support
10.0 Pararell Merge join, Hash, Bitmap Index Scan, subquery handling, Need Gather Merge
・パーティショニング
オプティマイザーの向上（Partition-wize Partirion-pruning partition-wise aggregation）
・FDW
・Write Amplification Mitigation
・Asynchronous and Vectorized execution
・Executor overhaul
more efficient processing of disk tuples, query operations and expressions and jit-compile
・PITR configration
merge recovery.conf into postgresql.conf
リリースは毎年の5月頃になりそう。
パーティションテーブル内でのINDEXは作成することはできない。
MySQLはSlaveにINDEXを貼ることができる。PostgreSQLでもマスタ - スレーブでスキーマが異なっても良い。
用語
FDW
Foreign Data Wrapping （外部データラッパ）
サーバ外部のデータを内部の仮想テーブルとしてマッピングする事が可能。
PostgreSQL9.3よりpostgres_fdwというモジュールによって別サーバのpostgresqlをローカルDBに存在している用に扱うことが可能。
その他モジュールを使えば、MySQL・MongoDB・Redis・csvなども扱うことが可能。ログを仮想的に扱うことで可読性を上げることも可能。
PITR （Point In Time Recovery)
http://powergres.sraoss.co.jp/s/ja/tech/exp/02_pitr.php
データの更新時に発生した差分（トランザクションログ）をアーカイブログとして残しておき、障害発生時にスナップショットにアーカイブをログを適用してリカバリする復旧機能。
logical or physical replication（論理レプリケーション・物理レプリケーション）
http://www.slideshare.net/masahikosawada98/mysqlpotgresql
・物理レプリケーション・・・PostgreSQLでは現在こちら、MySQLにはない。
物理レベルでのレプリケーション、バイナリの共有
・論理レプリケーション・・・PostgreSQL9.xでは無い、MySQLではこっち。
データベースのトランザクション・レベルで、トランザクションの内容をキャプチャーし、その内容を稼働した状態の複製先のデータベースに反映させます。
Quorum-baed synchronous replication
https://ja.wikipedia.org/wiki/Quorum
分散システムにおいて、分散トランザクションが処理を実行するための最低限の票、処理の整合性を取るために実装される。
Partition-wize join（パーティション・ワイズ結合）
https://docs.oracle.com/cd/E57425_01/121/VLDBG/GUID-7C28CD29-5189-4A53-9691-380E13515015.htm
パーティション・ワイズ結合では、結合がパラレルで実行されるときにパラレル実行サーバー間で交換されるデータ量が最小限に抑えられ、問合せのレスポンス時間が短縮されます。レスポンス時間は大幅に短縮され、CPUとメモリー・リソースの使用率が改善されます。
Partition-wize Partirion-pruning
https://docs.oracle.com/cd/B28359_01/server.111/b32024/part_avail.htm#CACGFIGJ
SQLのFROM句・WHERE句などから不要なパーティションをアクセスリストから外すことで処理データ量を減らし処理速度を向上させることができる。
Write Amplification
https://ja.wikipedia.org/wiki/%E3%83%A9%E3%82%A4%E3%83%88%E3%82%A2%E3%83%B3%E3%83%97%E3%83%AA%E3%83%95%E3%82%A3%E3%82%B1%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%B3
実際に書き込んだデータの量よりも、フラッシュチップに対する書込量が増加すると言う、好まざる現象の事を言う。
HOT
http://lets.postgresql.jp/documents/tutorial/hot_1/
Heap Only Tupple、INDEXを持たないテーブルデータのみのタプル
狙いは、不要なINDEXによる更新処理の削減・ガベージの自動回収（VACUUM)
UPDATE時にPostgreSQLの挙動は、「既存のレコードに削除フラグを立て、新規レコードをINSERTする。」その際、INDEXが貼られているIDが同一で会ってもINDEXへのキー追加が行われていた。HOTの考え方では、INDEXへのキー追加は行わない。（同じキーになるため。）INDEXの更新がない分、処理速度が向上した。