ユーザ用ツール

サイト用ツール


it技術:データベース:postgresql:運用

差分

このページの2つのバージョン間の差分を表示します。

この比較画面へのリンク

両方とも前のリビジョン前のリビジョン
次のリビジョン
前のリビジョン
it技術:データベース:postgresql:運用 [2019/07/28 11:34] – [pg_basebackup] yajuadminit技術:データベース:postgresql:運用 [2022/06/06 11:17] (現在) – [ANALYZE] yajuadmin
行 14: 行 14:
 代表的なチューニング項目を記す。\\ 代表的なチューニング項目を記す。\\
 pg_confファイルの設定内容を見直す。設定変更後はPostgreSQLを再起動する。\\ pg_confファイルの設定内容を見直す。設定変更後はPostgreSQLを再起動する。\\
 +チューニング設定生成ツール「[[https://pgtune.leopard.in.ua/#/|PGTune]]」を使用すると、postgresql.confの推奨値を出力してくれる。
  
   * [[https://dbstudychugoku.github.io/pdf/20140713_postgre_tuning.pdf|まずやっとく PostgreSQLのチューニング - pdf]]   * [[https://dbstudychugoku.github.io/pdf/20140713_postgre_tuning.pdf|まずやっとく PostgreSQLのチューニング - pdf]]
   * [[https://www.pgconf.asia/JA/2018/wp-content/uploads/sites/3/2018/12/D0P5.pdf|WindowsでもPostgreSQLが使いたい - pdf]]   * [[https://www.pgconf.asia/JA/2018/wp-content/uploads/sites/3/2018/12/D0P5.pdf|WindowsでもPostgreSQLが使いたい - pdf]]
   * [[https://pgtune.leopard.in.ua/#/|PGTune チューニング設定生成ツール]]   * [[https://pgtune.leopard.in.ua/#/|PGTune チューニング設定生成ツール]]
 +  * [[https://tech-note.gaz.co.jp/postgresql/2018083001/|postgresqlのチューニング]]
  
  
行 50: 行 52:
 [[https://dbstudychugoku.github.io/pdf/20140713_postgre_tuning.pdf|ディスクソートの発生を避ける]] [[https://dbstudychugoku.github.io/pdf/20140713_postgre_tuning.pdf|ディスクソートの発生を避ける]]
  
-  * メモリ上でソートが行えるようwork\_memパラメータを調整+  * メモリ上でソートが行えるようwork_memパラメータを調整
   * work_memはセッションごとに確保される領域であるため、SETコマンドで処理に応じて調整するのが望ましい   * work_memはセッションごとに確保される領域であるため、SETコマンドで処理に応じて調整するのが望ましい
  
行 74: 行 76:
  
 ^搭載メモリ^推奨値^ ^搭載メモリ^推奨値^
-|2GB|512MB| +|2GB|512MB→1GB
-|4GB|1GB| +|4GB|1GB→2GB
-|8GB|2GB| +|8GB|2GB→4GB
-|16GB|4GB|+|16GB|4GB→8GB|
  
 ==== random_page_cost ==== ==== random_page_cost ====
行 111: 行 113:
   * [[https://qiita.com/U_ikki/items/89b1eea657e47120e3ee|PostgreSQLのチェックポイント処理のチューニング]]   * [[https://qiita.com/U_ikki/items/89b1eea657e47120e3ee|PostgreSQLのチェックポイント処理のチューニング]]
  
 +==== wal_keep_segments ====
 +バックアップ後のpg_logに下記のエラーが発生していました。
 +<code>
 +2019-08-06 02:18:35 JST ERROR:  requested WAL segment 000000010000005A000000D1 has already been removed
 +要求された wal セグメント * はすでに削除されています。
 +</code>
 +
 +対処として、wal_keep_segmentsをゼロ以外の値に設定する。\\
 +これは、ストリームの危険性が追いつかなくなるのを防ぐためです。\\
 +ただしpg_basebackupを使用する場合は、 – checkpoint = fastも忘れないでください。\\
 +https://codeday.me/jp/qa/20190609/972945.html
 +
 +<code>
 +#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
 +
 +wal_keep_segments = 128
 +
 +backup.bat の見直し
 +pg_basebackup -U postgres -D "%bkPath%" -F t -x -z
 +
 +pg_basebackup -U postgres -D "%bkPath%" -F t -x -z --checkpoint=fast
 +</code>
 +
 +===== パフォーマンスチューニング =====
 +[[https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/tuningrule9-base/|パフォーマンスチューニング9つの技 ~「基盤」について~]]
 +
 +  - VACUUMで不要領域を再利用可能な状態にする
 +  - REINDEXでインデックスの不要領域を削除する
 +  - ANALYZEで統計情報を最新化する
 +  - VACUUM FREEZEでトランザクションIDを凍結状態にする
 ===== データベース初期化 ===== ===== データベース初期化 =====
 ==== 全てのデータベース初期化 ==== ==== 全てのデータベース初期化 ====
行 119: 行 151:
 </code> </code>
  
-  * [[https://blogs.yahoo.co.jp/yukigaku0313/21115869.html|PostgreSQL インストールのトラブルシューティング]]+  * [[https://web.archive.org/web/20180912063813/https://blogs.yahoo.co.jp/yukigaku0313/21115869.html|PostgreSQL インストールのトラブルシューティング]]
   * [[http://software.fujitsu.com/jp/manual/manualfiles/m130025/b1ws1136/01z200/b1136-00-04-03-02.html|4.3 インスタンスの作成 > 4.3.2 initdbコマンドを使用する場合]]   * [[http://software.fujitsu.com/jp/manual/manualfiles/m130025/b1ws1136/01z200/b1136-00-04-03-02.html|4.3 インスタンスの作成 > 4.3.2 initdbコマンドを使用する場合]]
  
行 125: 行 157:
 initdb: ディレクトリ "D:\Program Files\PostgreSQL\9.6\data"の権限を変更できませんでした:Permission denied initdb: ディレクトリ "D:\Program Files\PostgreSQL\9.6\data"の権限を変更できませんでした:Permission denied
  
-Userに変更チェックを追加 することで回避した。\\+Userに変更チェックを追加 することで回避した。\\
 他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\ 他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\
   * [[https://blog.tpc.jp/2018/05/postgresql-10-install-error.html|PostgreSQL 10 インストール時の不具合解消方法]]   * [[https://blog.tpc.jp/2018/05/postgresql-10-install-error.html|PostgreSQL 10 インストール時の不具合解消方法]]
行 221: 行 253:
  
 <code bash コマンドプロンプト> <code bash コマンドプロンプト>
-> pg_basebackup -U postgres -D "E:\backup" -F t -x -z+> pg_basebackup -U postgres -D "E:\backup" -F t -x -z --checkpoint=fast 
 +↓ PostgreSQL12では-xは使えない 
 +> pg_basebackup -U postgres -D "E:\backup" -F t -X f -z --checkpoint=fast
 </code> </code>
  
 ^Option^説明^ ^Option^説明^
 |-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。| |-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。|
-|-F t|出力する形式。tはtarで出力する。| +|-F t|出力する形式。tはtarで出力する。圧縮するためにはtarの指定が必要
-|-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ (そうすることで、出力ファイルが変な状態になることを防ぐ)|+|-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ (そうすることで、出力ファイルが変な状態になることを防ぐ) \\ -X f と同じ(PostgreSQL12では英小文字の-xは使用不可になった)|
 |-z|gzipで圧縮した状態にする| |-z|gzipで圧縮した状態にする|
 +|--checkpoint=fast|fast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。|
  
 -Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。 -Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。
行 238: 行 273:
 </code> </code>
  
-postgresql.conf の max_wal_sender 設定のデフォルトは 0 です。最低でも 1 以上でないと pg_basebackup コマンドは実行できないので修正します。+postgresql.conf の max_wal_sender 設定のデフォルトは 0 です。最低でも 1 以上でないと pg_basebackup コマンドは実行できないので修正します。※PostgreSQL12ではデフォルトは 10 となった
  
 <code bat postgresql.conf> <code bat postgresql.conf>
-max_wal_sender = 1+max_wal_sender = 10
 </code> </code>
  
行 258: 行 293:
   * [[https://qiita.com/bwtakacy/items/65260e29a25b5fbde835|PostgreSQLのWALファイル(トランザクションログ)について]]   * [[https://qiita.com/bwtakacy/items/65260e29a25b5fbde835|PostgreSQLのWALファイル(トランザクションログ)について]]
   * [[https://ja.wikipedia.org/wiki/%E3%83%AD%E3%82%B0%E5%85%88%E8%A1%8C%E6%9B%B8%E3%81%8D%E8%BE%BC%E3%81%BF|ログ先行書き込み - wikipedia]]   * [[https://ja.wikipedia.org/wiki/%E3%83%AD%E3%82%B0%E5%85%88%E8%A1%8C%E6%9B%B8%E3%81%8D%E8%BE%BC%E3%81%BF|ログ先行書き込み - wikipedia]]
 +  * [[https://qiita.com/kimullaa/items/e7bed14aaa680126b81a|PostgreSQL WALログの仕組みとタイミングを理解したい]]
 ==== 設定 ==== ==== 設定 ====
 postgresql.conf で設定する。 postgresql.conf で設定する。
行 280: 行 316:
 <code sql> <code sql>
 SELECT pg_switch_xlog(); SELECT pg_switch_xlog();
 +↓ PostgreSQL10以降ではコマンド名変更
 +SELECT pg_switch_wal();
 </code> </code>
  
行 294: 行 332:
 [[https://books.google.co.jp/books?id=D3JqBAAAQBAJ&printsec=frontcover&hl=ja&source=gbs_ge_summary_r&cad=0#v=onepage&q&f=false|内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 - 本]] [[https://books.google.co.jp/books?id=D3JqBAAAQBAJ&printsec=frontcover&hl=ja&source=gbs_ge_summary_r&cad=0#v=onepage&q&f=false|内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 - 本]]
  
 +=== archive_timeout ===
 +archive_timeoutはトランザクションがほとんど発生しない「なぎ」のとき、WALにたまった内容がいつまで経ってもアーカイブされないことを防ぐ。デフォルトでは0(機能無効)
 +
 +archive_timeout = 60 とした場合、16MB*60回/時*24時間=23040MB/日≒23GB/日 が発生しうる。
 +[[http://dbnote.web.fc2.com/note_param_pitr_walarchive.html|archive_timeoutの意味]]
 ==== WALアーカイブの削除 ==== ==== WALアーカイブの削除 ====
 pg_archivecleanupコマンドで削除する。 pg_archivecleanupコマンドで削除する。
  
 pg_basebackup を実行すると、WALアーカイブのフォルダに拡張子backupが作成される。\\ pg_basebackup を実行すると、WALアーカイブのフォルダに拡張子backupが作成される。\\
-これにより指定した拡張子backupより前の不要なWALアーカイブ削除される。+これにより指定した拡張子backupより前の不要なWALアーカイブ削除される。
  
 <code> <code>
行 318: 行 361:
 よって、不要なWALアーカイブの削除は手動で行うこととする。\\ よって、不要なWALアーカイブの削除は手動で行うこととする。\\
 ※postgreSQLサービスを再起動すればリカバリが実行され、recovery.confはrecovery.doneになります。 ※postgreSQLサービスを再起動すればリカバリが実行され、recovery.confはrecovery.doneになります。
-==== WALアーカイブからのリストア ==== +==== WALアーカイブからのリカバリ ==== 
-[[http://ossfan.net/setup/postgresql-37.html|PostgreSQL 9.6.3で時刻指定のPITR(Point In Time Recovery)を実行する]]+リストアとリカバリとは区別しないで使うときもあるが、今回は分けました。 
 + 
 +  * リストアはバックアップデータを、バックアップを取ったときと同じ状態に物理的に復元すること 
 +  * リカバリはリストアしたデータに何かの処理をして最新の状態や正常な状態に復旧させること 
 + 
 +=== PITRの仕組み === 
 +PITR(Point In Time Recovery)は、WALレコード適用によるリカバリが前提となっている。 
 +  [[http://ossfan.net/setup/postgresql-37.html|PostgreSQL 9.6.3で時刻指定のPITR(Point In Time Recovery)を実行する]] 
 +  * [[http://blog.poppypop.mydns.jp/2016/03/03/postgresql_onlinebackup_recovery/|PostgreSQLのオンラインバックアップとリカバリ]] 
 + 
 +**WALレコードの適用までの流れ**\\ 
 +リカバリを開始してWALレコードを適用するまでの流れは次のようになっている。 
 + 
 +  - pg_controlファイルを読み込む 
 +  - recovery.confを読み込む → PostgreSQL12ではrecovery.conf廃止、postgresql.confに統一 
 +  - backup_labelを読み込む 
 +  - pg_controlファイルを更新し、backup_labelを削除する 
 +  - 必要なWALを繰り返し適用する 
 + 
 +※backup_labelファイルから適用を開始すべきWALの位置を取得できなかった場合は、pg_controlファイルの情報を元にリカバリを開始します。 
 + 
 +pg_controlファイルは、バイナリファイルなので通常のエディタでは内容を確認できません。そのために pg_controldata コマンドが用意されています。\\ 
 +pg_controldata コマンドで最終チェックポイントのREDO WALファイルが分かる。 
 + 
 +<code> 
 +pg_controldata -D C:\PostgreSQL\9.6\data 
 + 
 +pg_controlバージョン番号:                         960 
 +カタログバージョン番号:                           201608131 
 +データベースシステム識別子:                       6599999130305146812 
 +データベースクラスタの状態:                       運用中 
 +pg_control最終更新:                               2019/07/28 20:58:08 
 +最終チェックポイント位置:                         0/B01B140 
 +前回のチェックポイント位置:                       0/B01B060 
 +最終チェックポイントのREDO位置:                   0/B01B108 
 +最終チェックポイントのREDO WALファイル:    00000004000000000000000B 
 +最終チェックポイントの時系列ID:                   4 
 +最終チェックポイントのPrevTimeLineID:   4 
 +最終チェックポイントのfull_page_writes オン 
 +最終チェックポイントのNextXID:                    0:610 
 +最終チェックポイントのNextOID:                    24655 
 +最終チェックポイントのNextMultiXactId: 
 +最終チェックポイントのNextMultiOffset: 
 +最終チェックポイントのoldestXID:      536 
 +最終チェックポイントのoldestXIDのDB: 
 +最終チェックポイントのoldestActiveXID: 610 
 +最終チェックポイントのoldestMultiXid:   1 
 +最終チェックポイントのoldestMulti'sのDB:
 +最終チェックポイントのoldestCommitTsXid:
 +最終チェックポイントのnewestCommitTsXid:
 +最終チェックポイント時刻:                         2019/07/28 20:58:08 
 +ログを取らないリレーション向けの偽のLSNカウンタ:   0/1 
 +最小リカバリ終了位置:                             0/0 
 +最小リカバリ終了位置のタイムライン:   0 
 +バックアップ開始位置:                 0/0 
 +バックアップ終了位置:                 0/0 
 +必要なバックアップ最終レコード:        no 
 +wal_level の設定:                    replica 
 +wal_log_hints の設定:                オフ 
 +max_connections の設定:              100 
 +max_worker_processes の設定:         8 
 +max_prepared_xacts の設定:           0 
 +max_locks_per_xact の設定:           64 
 +track_commit_timestamp の設定:       オフ 
 +最大データアラインメント              8 
 +データベースのブロックサイズ:                     8192 
 +ラージリレーションのセグメント当たりのブロック数: 131072 
 +WALブロックのサイズ:                              8192 
 +WALセグメント当たりのバイト数:                  16777216 
 +識別子の最大長:                                   64 
 +インデックス内の最大列数:             32 
 +TOASTチャンクの最大サイズ:                               1996 
 +ラージオブジェクトチャンクのサイズ:         2048 
 +日付/時刻型の格納方式:                            64ビット整数 
 +Float4 引数の渡し方:                 値渡し 
 +Float8  引数の渡し方:                値渡し 
 +データベージチェックサムのバージョン:           0 
 +</code> 
 ===== VACUUM処理 ===== ===== VACUUM処理 =====
 PostgreSQLは、追記型アーキテクチャ(MVCC:MultiVersion Concurrency Control)を採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。\\ PostgreSQLは、追記型アーキテクチャ(MVCC:MultiVersion Concurrency Control)を採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。\\
行 338: 行 459:
 === VACUUM FULL === === VACUUM FULL ===
 物理的にファイルを圧縮します。 物理的にファイルを圧縮します。
 +
 +VACUUM FULLは次のような処理を行っています。
 +  - テーブルのデータを1行ずつ取ってきて、別の新しいテーブルに詰め込む
 +  - 新しいテーブルのインデックスを作成する
 +  - テーブルを入れ替える
 +つまり、有効な行しか取ってこないので不要領域は一切存在しなくなる。VACUUM FULLは一時的に対象テーブルと新しいテーブルが同時に作成されるため容量不足で完遂できないことがあります。
  
 排他ロックが必要なため、VACUUM FULL中はテーブルへアクセス不可となります。\\ 排他ロックが必要なため、VACUUM FULL中はテーブルへアクセス不可となります。\\
行 355: 行 482:
 |約2400万件|6分| |約2400万件|6分|
  
-**VACUUM FULLを使うのではなく、pg_repackを使う。** +**VACUUM FULLを使うのではなく、pg_repack(Windows版は非対応)を使う。**
 ==== AUTOVACUUM(自動実行) ==== ==== AUTOVACUUM(自動実行) ====
 autovacuumは定期的にテーブル状態を監視し、必要があれば自動でVACUUMやANALYZE を実施する機能です。※VACUUM FULLは行わない。\\ autovacuumは定期的にテーブル状態を監視し、必要があれば自動でVACUUMやANALYZE を実施する機能です。※VACUUM FULLは行わない。\\
行 375: 行 501:
   * Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold)   * Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold)
  
-AUTOVACUUMデーモンが一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。+**統計情報の再計算**\\ 
 +以下の計算式以上のレコードが更新(UPDATE/DELETE)されたテーブルは自動的に実行 
 +autovacuum_vacuum_thresholdがデフォルト 50で、autovacuum_vacuum_scale_factorがデフォルト 0.1となっている。 
 + 
 +<code> 
 +autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * レコード数 
 +</code> 
 + 
 +AUTOVACUUMデーモン(Windowsサービス)が一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。 
 + 
 +パラメータはテーブル単位でも決定可能\\ 
 +大規模テーブルではテーブル単位に autovacuum_vacuum_scale_factor を 0 に\\ 
 +autovacuum_vacuum_threshold を適切な値に変更し、頻繁に VACUUM を実行することを推奨\\ 
 +[[https://web.archive.org/web/20190531052308/https://tihiro.hatenablog.com/entry/2018/07/17/223913|テーブルごとにAUTO_VACUUMおよびAUTO_ANALYZEをする閾値を設定する]] 
 + 
 +例えば、10000件廃止行数が超えたら、自動的にVACUUMさせたいって場合 
 + 
 +<code> 
 +ALTER TABLE {table_name} SET (autovacuum_vacuum_threshold = 10000); 
 +ALTER TABLE {table_name} SET (autovacuum_vacuum_scale_factor = 0); 
 +</code>
  
 +<wrap em>巨大なテーブルに対して実行すると、vacuum処理に時間がかかりすぎることによって、表面のアプリケーション側に影響を及ぼすことがあるので、パラメータ変更はせずに非稼働時間に定期的にVACUME ANALIZEを各テーブルに行うようにしたい。</wrap>
  
 ==== VACUUM実行確認 ==== ==== VACUUM実行確認 ====
行 402: 行 549:
 </code> </code>
  
 +=== 最後にVACUUM/ANALYZEした日時確認 ===
 +<code sql>
 +SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
 +FROM pg_stat_all_tables
 +WHERE schemaname = 'public' -- schemaname or relnameで絞りこむと見やすい
 +ORDER BY relname
 +</code>
 ==== VACUUM手動実行判断 ==== ==== VACUUM手動実行判断 ====
 デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。 デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。
行 432: 行 586:
   * [[https://qiita.com/seikoudoku2000/items/e49a321182b5f91b86fe|Postgresql サーバを移行したらAUTO VACUUM が実行されず、データベースが肥大化した時の話]]   * [[https://qiita.com/seikoudoku2000/items/e49a321182b5f91b86fe|Postgresql サーバを移行したらAUTO VACUUM が実行されず、データベースが肥大化した時の話]]
   * [[http://kashi.way-nifty.com/jalan/2014/02/postgresqlconf-.html|postgresql.confの罠]]   * [[http://kashi.way-nifty.com/jalan/2014/02/postgresqlconf-.html|postgresql.confの罠]]
 +
 +===== 統計情報 ======
 +[[https://www.ospn.jp/osc2014.enterprise/pdf/OSC2014_Enterprise_hp.pdf|PostgreSQL安定運用のための障害予防と検知 - pdf]]
 +
 +==== ANALYZE =====
 +ANYLYZEは、統計情報を行う。単独で動かせるし「VACUUM ANYLYZE」としてVACUUMと組み合わせることもできる。AUTOVACUUM時にもANYLYZEが行われている。\\
 +対象とするテーブルの読み取りロックのみ(読み込みはできるが書き込みができない状態)であり、そのテーブルに対する他の操作と並行して実行することができます。
 +==== 統計情報の取得 =====
 +  * SQL文の実行計画作成には統計情報を使用
 +  * 統計情報とはテーブルやインデックスのデータ情報
 +    * レコード数、ブロック数
 +    * ヒストグラム(データの最頻値、ばらつき)
 +  * 取得契機
 +    * 自動 VACUUM
 +    * VACUUM ANALYZE文の実行
 +    * ANALYZE 文の実行
 +  * 統計情報の確認
 +    * pg_statistic カタログ
 +    * pg_stats カタログ(pg_statistic カタログを見やすく整形している)
 +
 +==== サンプリング =====
 +=== 統計情報はサンプリングによって行われる ===
 +  * サンプリング量はデフォルト 30,000 レコード( MAX(列の STATISTICS 値)× 300 レコード)
 +  * 列の STATISTICS 値とは?
 +    * ヒストグラムを収集するバケット数(データの範囲を入れる箱の数)
 +    * デフォルト値はパラメータ default_statistics_target で決まる(デフォルト 100)
 +    * 「ALTER TABLE table_name ALTER column_name SET STATISTICS value」文で変更
 +
 +default_statistics_targetを単純に増やしてしまうとVACUUMの処理時間が長くなる。\\
 +よって、性能が低下しているSQLで参照しているテーブル、カラムを個別に拡張することが効果的。
 +<code>
 +ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET STATISTICS 200;
 +</code>
 +
 +上記設定が適用されるのはVACUUM実行時で、速くなるのは統計精度を拡張したカラムを条件としたSELECTのみで統計精度が関係ないカラムのSELECTやINSERTには何の影響もない。
 +
 +=== 大規模なテーブルではサンプリング・レコード数を拡大 ===
 +STATISTICS 値の拡大を推奨
 +
 +デフォルトの統計情報計算式はテーブルのレコード数を 1,000,000 レコードと想定。\\
 +10,000,000 レコードのテーブルでは STATISTICS = 114 程度にすると必要なサンプリングが行われる
 +
 +===== OID(Object Identifier:オブジェクト識別子) ======
 +==== 特徴 ====
 +特徴をまとめると次の通り
 +
 +  * データベース、テーブル、ロール、関数、操作、データ型などに割り振られる
 +  * 0は使わない(0は無効な値の扱い)。符号なし4バイト(最大値:4,294,967,295)の値をとる
 +  * OIDは一周して同じ値をとる場合があるため、一意であると仮定してはならない
 +  * 主キーを持たないテーブル、重複行があるテーブルなどに有効
 +
 +<wrap em>※テーブル作成時の「WITH OIDS」はPostgreSQL12で廃止</wrap>
 +
 +[[https://postgresweb.com/post-5484|【PostgreSQL】OIDとは?について調べたことのメモ]]
 +
 +==== レコードのデータ識別 ====
 +内部的にはレコードのデータにデータを識別するOID(オブジェクトID)とトランザクションを制御するXID(トランザクションID)をくっ付けて管理しています。\\
 +新しいトランザクションが発生すると元のデータはそのままにして、OIDはそのままでXIDを一個増やしたデータを追記しようとします。\\
 +[[https://thinkit.co.jp/cert/compare/1/3/3.htm|第3回:アーキテクチャ比較 ファイル構造の違い - PostgreSQLの特徴 MVCCの仕組み]]
  
 ===== トランザクション ===== ===== トランザクション =====
行 442: 行 655:
 SELECT datname, age(datfrozenxid) FROM pg_database SELECT datname, age(datfrozenxid) FROM pg_database
 </code> </code>
-ageが15億になる前にVACUUMをかける。かけるとまた10億になる。+ 
 +ageが15億になる前にVACUUMをかける。かけるとまた10億になる。\\
 [[http://blog.livedoor.jp/moonfishnet/archives/26220925.html|VACUUMとage関数]] [[http://blog.livedoor.jp/moonfishnet/archives/26220925.html|VACUUMとage関数]]
 +
 +===== インデックスの再構築 =====
 +PostgreSQL のインデックスサイズは一度大きくなると、その後小さくなるタイミングが限られています。
 +
 +  * DROP INDEX でテーブル自体を削除した場合
 +  * TRUNCATE TABLE でテーブル全体を空にした場合
 +  * REINDEX でインデックスを再構成した場合
 +
 +インデックスが肥大化した状況では実行計画のコスト計算に影響することがあります。これは適切な実行計画を選択する妨げとなるかもしれません。
 +
 +[[https://taityo-diary.hatenablog.jp/entry/2021/06/05/143617|PostgreSQL のインデックス肥大化と実行計画のコストへの影響]]
 +==== リインデックス ====
 +reindexコマンドを使用して、インデックスの再構築する。\\
 +<code bat>
 +rem PK_REP_RES_QUEをリインデックスする
 +SET PGPASSWORD=wh_kousei
 +psql -U wh_kousei -c "reindex INDEX pk_rep_res_que;"
 +</code>
 +
 +==== テーブルロックなしのリインデックス ====
 +reindexコマンドはテーブルロックがかかってしまうので、運用中のDBに対して使うのは難しい。\\
 +ただ、PostgreSQLでは別名で全く同じインデックスの作成を行うことができる。\\
 +<wrap em>※PostgreSQL12からは、REINDEX CONCURRENTLY が導入され、REINDEX による重いロックなしでインデックスを再構築できます。</wrap>
 +
 +  * [[http://cynipe.hateblo.jp/entry/2012/08/19/173230|PostgreSQLでテーブルロックせずにインデックスを再構築する方法]]
 +  * [[https://tak-w.hatenadiary.org/entry/20111125|index 再構築方法]]
 +  * [[https://tak-w.hatenadiary.org/entry/20111207/1323270854|index 再構築 Primary key]]
 +  * [[http://kashi.way-nifty.com/jalan/2014/04/postgresql-81a7.html|定期的なインデックス再作成を自動化]]
 +  * [[https://qiita.com/mkyz08/items/eb66419d43c56cce566e|PostgreSQLのインデックス再編成]]
 +
 +PostgreSQL 9.6上で疑似的にロック無しのインデックスの再構築
 +
 +<code bat>
 +rem PK_REP_RES_QUEをリインデックスする
 +SET PGPASSWORD=wh_test
 +psql -U wh_test -f reindex_rep_res_que.sql
 +</code>
 +
 +<code sql reindex_rep_res_que.sql>
 +create unique index concurrently pk_rep_res_que_new on rep_res_que(que_id);
 +
 +alter table rep_res_que drop constraint pk_rep_res_que;
 +alter index pk_rep_res_que_new rename to pk_rep_res_que;
 +alter table rep_res_que add primary key using index pk_rep_res_que;
 +</code>
 ===== 確認用SQL ===== ===== 確認用SQL =====
 === テーブルのディスク使用量を取得する === === テーブルのディスク使用量を取得する ===
行 470: 行 729:
 SELECT pg_database_size('db_name'); SELECT pg_database_size('db_name');
 </code> </code>
 +
 +=== テーブル単位のサイズを取得するSQL ===
 +<code sql>
 +SELECT pgn.nspname, relname, pg_size_pretty(relpages::bigint * 8 * 1024) AS size, CASE WHEN relkind = 
 +'t' THEN (SELECT pgd.relname FROM pg_class pgd WHERE pgd.reltoastrelid = pg.oid) WHEN nspname = 
 +'pg_toast' AND relkind = 'i' THEN (SELECT pgt.relname FROM pg_class pgt WHERE SUBSTRING(pgt.relname 
 +FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '')) ELSE (SELECT pgc.relname FROM 
 +pg_class pgc WHERE pg.reltoastrelid = pgc.oid) END::varchar AS refrelname, CASE WHEN nspname = 
 +'pg_toast' AND relkind = 'i' THEN (SELECT pgts.relname FROM pg_class pgts WHERE pgts.reltoastrelid = 
 +(SELECT pgt.oid FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname 
 +FROM 10), '_index', ''))) END AS relidxrefrelname, relfilenode, relkind, reltuples::bigint, relpages 
 +FROM pg_class pg, pg_namespace pgn WHERE pg.relnamespace = pgn.oid AND pgn.nspname NOT IN 
 +('information_schema', 'pg_catalog') ORDER BY relpages DESC
 +</code>
 +
 +[[https://qiita.com/awakia/items/99c3d114aa16099e825d|PostgreSQLで各テーブルの総サイズと平均サイズを知る]]
  
 === テーブル単位のダンプ === === テーブル単位のダンプ ===
行 496: 行 771:
 |3|Z|マイナーバージョン番号|セキュリティバグやデータ破損の可能性のあるバグ等が修正された場合。\\ その他の軽微な修正も同時に行われる。| |3|Z|マイナーバージョン番号|セキュリティバグやデータ破損の可能性のあるバグ等が修正された場合。\\ その他の軽微な修正も同時に行われる。|
  
-※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン新機能追加 – 最後がマイナーバージョンバグフィックスなど+※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン新機能追加最後がマイナーバージョンバグフィックスなど
  
 ==== マイナーアップデート ==== ==== マイナーアップデート ====
行 504: 行 779:
 ==== メジャーアップデート ==== ==== メジャーアップデート ====
 メジャーアップデートでは、データのバックアップとリストアの作業が必要である。\\ メジャーアップデートでは、データのバックアップとリストアの作業が必要である。\\
-メジャーアップデート用ツールとして、[[https://www.postgresql.jp/document/9.6/html/pgupgrade.html|pg_upgrade]]がある。\\ +メジャーアップデート用ツールとして、[[https://www.postgresql.jp/document/9.6/html/pgupgrade.html|pg_upgrade]]がある。 
-[[https://qiita.com/leon-joel/items/42eb018598052f8792af|pg_upgrade を使ったDBクラスタ移行手順 on Windows]]+ 
 +=== pg_upgrade === 
 +  * [[https://qiita.com/leon-joel/items/42eb018598052f8792af|pg_upgrade を使ったDBクラスタ移行手順 on Windows]] 
 +  * [[https://tech-blog.rakus.co.jp/entry/20190829/postgresql|【PostgreSQL 9.4 → 11】pg_upgradeでデータベースクラスタをバージョンアップ]] 
 +  * [[https://www.ashisuto.co.jp/db_blog/article/201712-postgresql10-upgrade.html|PostgreSQL10へのアップグレード前に押さえておきたい3つの注意点]] 
 +  * [[https://se.ykysd.com/2019/06/18/pg-upgrade-96-108/|PostgreSQL9.6からPostgreSQL10にバージョンアップ]] 
 + 
it技術/データベース/postgresql/運用.1564281247.txt.gz · 最終更新: 2019/07/28 11:34 by yajuadmin