OSS-DB Gold Ver.3.0 取得とoldest xminについて
この記事はPostgreSQL Advent Calendar 2023の24日目の記事です。
PostgreSQLのカレンダー | Advent Calendar 2023 - Qiitaqiita.com
OSS-DB Gold Ver.3.0 を取得しました
とりあえず
12/21(木) にOSS-DB Gold Ver.3.0 を取りました。OSS-Silver と Gold の有意性の期限が2028年12月21日まで延びました。
経緯
5年前(2018年)の PostgreSQL Advent Calendar 2018 の最終日(25日目)に、OSS-DB Silver と Gold を取った記事を書きました。明日(2023/12/25)でちょうど丸5年になります。
OSS-DB Silver / Gold の有意性の期限は「認定日から5年以内」となっており、取得日から5年以内に再度OSS-DB Goldに合格しないと認定ステイタスが「Active」から「Inactive」になってしまいます。
資格を取得したこと自体は消えないのですが、今の仕事はPostgreSQLサポートで所属するグループでは有意性を保持したOSS-DB Goldの資格が必須となっていて、所属会社として出来る限りランキングに出るモチベーションもあります。あまり早く受けると次の5年が短くなるので出来るだけ5年間の有意性の期限を長くするために、期限が切れる5日前の12/21に受けました。5日間の余裕は落ちた場合に再受験をする猶予期間でした(このブログを書くネタにする意図もありました)。
準備
OSS-DB Ver.3.0認定教材
学習教材のご紹介（OSS-DB認定教材）
■OSS-DB Gold（2023年2月現在）
https://oss-db.jp/learning#gold
現時点で存在するGold Ver.3.0の学習教材は下記の二つで、出来れば両方使った感想を書きたかったのですが、準備不足のため使ったのは後者のみとなりました(理由は後述)。
- OSS-DB Gold 攻略会 Ver.3.0対応
- PostgreSQL運用管理トレーニング PostgreSQL 14対応
一つ目の「OSS-DB Gold 攻略会 Ver.3.0対応」はnoteのメンバーシップ、すなわち月単位のサブスクリプション形式で解説テキストや問題集を閲覧できるもので、OSS-DB Goldの解説テキストを閲覧できるスタンダードプランは月1000円、前者に加えてOSS-DB Goldの問題集とOSS-Silverの解説テキストを閲覧できるプレミアムプランは月3000円となります。今回はこの存在を知ったのが試験直前の一週間前で(単なる準備不足&一応仕事も忙しかった)、二つ目のトレーニングテキストも一通り読んでない状態でサブスクリプションを申し込んでも私の性格的にろくに見ずに試験日が来ると思ったので、残念ながら今回の受験では見送りました。本当は問題集をやりこんで自分の弱点を残らず埋めていくのがいいのですが、一か月前に認定教材ぐらいはチェックしておくべきでした。
二つ目の「PostgreSQL運用管理トレーニング PostgreSQL14対応」は仕事をしているSRA OSS LLCのトレーニングテキストで、ちょうどOSS-DB Ver.3.0の範囲であるPostgreSQL 14に対応したテキストです。前回は試験範囲よりも古いテキストで、テキストをやっても新しいバージョン分の差分が必要という面倒臭さがありましたが、今回はこれをやっておけば大丈夫と安心して学べました。トレーニングを受講すると入手できますが、自習者向けにトレーニングテキストだけの購入も可能です。
OSS-DB 教材 | SRA OSS Online Shop
行なった試験勉強
上にも書きましたが実際に着手したのが一週間前で、実行した内容は下記の4点になります。
- 「OSS-DB Gold Ver.3.0」の出題範囲を印刷して眺める。→大丈夫そうか大丈夫そうではないかを判断する。
- 「PostgreSQL運用管理トレーニング PostgreSQL14対応」を一通り読む。→出題範囲を意識して一通り理解する。
- OSS-DB Gold Ver.1.0対応の「LPI-Japan OSS-DB Gold 認定教材 PostgreSQL 高度技術者育成テキスト」の模擬問題集のうち、Ver.3.0から外れた容量見積以外の問題を解いて解説を読む。→問題を解くと実際には理解できていない・覚えていない箇所が分かるので、2.のテキストの該当箇所を読み直す。
- 公式サイトの例題解説を解く。→3.と同様、弱い部分を2.のテキストの該当箇所を読み直す。
上記の結果、時間も無くなって合格点はたぶん大丈夫だろうと判断して受験しました。5年前の前回が80点、今回が90点でした。30問中、27問正解、3問間違いということになります。運用管理とパフォーマンスチューニングが上がって、障害対応は変わらず、性能管理が下がりました。100点を目指すには出題範囲の用語や内容を完璧に理解して反応(回答)できるようにしておく必要がありますが、そこまで網羅的には出来ていませんでした。90点は上出来だと思いますが、PostgreSQLサポートの仕事を4年半続けているので「受かって当たり前」「合格した上で何点取れるか」という思いはあるので、「受かって嬉しい」という気持ちはあまりなく、嬉しいのは嬉しいですが「受かってホッとした」というのが正直なところです。
試験時間は30問で90分あるので1問あたり3分と余裕があることと、計算問題はまったくなかったので時間がかかるような問題は無く、知っているか知っていないか、選択肢を間違わないかどうか、だけですので後で見直すチェックを付けながら一通り回答して30分、その後見直しを10分ほどしたので、実際に使った時間は40分程度でした。
試験結果
今回(OSS-DB Ver.3.0)の結果
受験日：2023/12/21
取得点：90
合格ライン：70
結果：合格
運用管理：100%
性能管理：77%
パフォーマンスチューニング：100%
障害対応：83%
前回(OSS-DB Ver.1.0)の結果
受験日：2018/12/25
取得点：80
合格ライン：70
結果：合格
運用管理：66%
性能管理：88%
パフォーマンスチューニング：83%
障害対応：83%
資格を持っているからと言って仕事ができるとは限らない？
よく「資格を持っていても仕事が出来るとは限らない」「資格取得は無駄」という意見を見ますが、前者は真で、後者は偽と私は思っています。
「資格を持っているだけで仕事が出来る証明にはならない」のはその通りで、5年前の私はPostgreSQLの実務経験はほとんどない状態で80点を取ってOSS-DB Goldを取りましたが、PostgreSQLでバリバリ仕事ができるという状態ではなかったです。その後、PostgreSQLのサポートで日常的にPostgreSQLを触ってドキュメントとソースコードを読んでいるので、5年前とは格段に違ったPostgreSQLの実務経験がついていますが、今回のOSS-DB Goldの点数と前回の点数との差は10点しかありません。5年前は仕事が忙しくなかったので全力で2週間試験準備をしたのと、Ver.1.0とVer.3.0との難易度の差もあるかもしれませんが、実務経験があっても、試験の点数がたったの10点しか差が無いので、実務経験のありなしを資格試験を持っているかどうか、そして点数では計れるものではないと思います。
ですが、資格試験にも良いところはあって「網羅的に一通りその分野のことについて学ぶ」というのは大事なことで、「どんなものがあるかを知っておく」「何ががあったら気付くようにするよう脳内にインデックスを作っておく」のが重要です。今回もPostgreSQL 14対応のトレーニングテキストを読んで馴染みが無い項目というか新たな発見がいくつかありました。また、資格試験に受かった瞬間だけでもその問題は解けているので「何もしていない(資格を持っていない)人よりは技術的にできる可能性が高い」ことは事実だと思いますので、「技術的な知識を獲得する」ことと「実務的な経験を得る」ことの両方を進めるべきだと考えます。
お勧めの教材/教科書
上記に書いた以外で触れていない教材としてまっさきに紹介するのは下記の「[改訂3版] 内部構造から学ぶPostgreSQL－設計・運用計画の鉄則」になります。中上級者向けの内容が多いと思いますが、PostgreSQL 14対応で、技術的な解説と実務経験に役立つノウハウが詰まっています。PostgreSQLに携わる人はこの本を常に傍らに置いて読む・参照することをお勧めします。
また、PostgreSQL本体とそれ以外(contribとサードバーティソフトウェア)の紹介として有用なのが、企業のコミュニティで各種検証を行なったPGECons(PostgreSQLコンソーシアム)の成果物の「PostgreSQL自習書」です。「Oracleデータベース経験者がPostgreSQLの概要を理解することを目的とした技術文書です。」との説明なので、差異を知りたいOracle DBを使っている人にはもちろん読みやすい内容ですが、Oracle DB技術者以外でもPostgreSQL本体とcontrib、プラスサードバーティソフトウェアを組み合わせてどのようなことができるかの紹介としてまとまった内容となっています。
リンクが二つありますが「本文(PDF):PostgreSQL12以降」の方です。こちらはPostgreSQL 14がベースとなっています。
PostgreSQL エンタープライズ・コンソーシアム : 活動報告トップページ
oldest xminについて
VACUUMを何度実行しても、VACUUMの実行ログで「DETAIL: 5182 dead row versions cannot be removed yet, oldest xmin: 134852157」といったメッセージが出て dead row versions の数字が減るどころか増えていく一方でVACUUMが進まないという困った事象が発生することがあります。
発生する原因
oldest xminでVACUUMが進まない事象が発生する原因は、ロングトランザクション、使われていないレプリケーションスロット、残存した(孤児の)プリペアドステートメントの三つですが、よくある原因は一つ目と二つ目で、三つ目の原因には今まで出会ったことが無いです。
レプリケーション構成での落とし穴
レプリケーション構成の複数台のPostgreSQLサーバで発生する時の注意点は、hot_standby_feedback=on となっている場合のスタンバイ側での確認です。ロングトランザクションの存在確認は割とするのですが、スタンバイ側でのレプリケーションスロットが原因となっていることに気付かず、全然oldest xminが変わらない時がありました。原因は元プライマリであった時に存在していたレプリケーションスロットが残っていて、かつ hot_standby_feedback=onとなっていたために、ずっとoldest xminが変わらない状態が続いてしまいました。これがあってから、スタンバイ側ではpg_stat_activiityだけでなくpg_replication_slotsも確認するようになりました。
Xmin Horizon
今年のPostgreSQLカンファレンス2023のチュートリアルトラック【T2】でpganalyzeの織田 敬子さんが【T2】VACUUM Simulatorを使ってVACUUMをもっと理解しようというタイトルで講演をされてて、絶対時間内では終わらないけど後で読んでね！という伝えたいてんこ盛りの有用なVACUUMに関する講演資料が公表されています。この講演で日頃馴染んでいる「oldest xmin」を指す言葉として Xmin horizon という表現をしり、ソースコードを見るとPostgreSQLログへの出力などにはありませんでしたが、コメントのあちらこちらでこの単語を見かけました。
講演は、VACUUM Simulatorにたどり着く前にVACUUMの説明でほとんど時間を使ってしまいましたが、別途時間を取って後半部分のVACUUM Simulatorやpganalyzeについても話を伺いたいと思いました。
https://www.postgresql.jp/jpug-pgcon2023#T2
終わりに
OSS-DB Gold取得について、ある程度のDB経験がある人が現時点でイチから勉強して受けるとすれば、「［改訂3版］内部構造から学ぶPostgreSQL ―設計・運用計画の鉄則」を読んでPostgreSQLを触る、OSS-DB Gold認定教材の二つを使って学ぶ、後者は可能ならばトレーニングを受講して学ぶ、になるかと思います。
VACUUMに関しては、4年前のPostgreSQL Advent Calendarのエントリでも書きましたがlog_autovacuum_min_duration=0にして全てのVACUUMの実行状況を把握しましょう、が現時点でもお伝えしたいことになります(もちろんlog_checkpoints=onも)。
織田さんのVACUUM講演資料はまだじっくり読み込めてないので時間を取って読んでみます。VACUUMは奥が深くて面白いです(大変ですが)。
PostgreSQL の拡張モジュールなどの周辺ツールをご紹介
本エントリは、PostgreSQL Advent Calendar 2022 の14日目の記事です。
昨日は、@yohsita_ さんによるこちらの記事でした。
はじめに
PostgreSQLは CREATE EXTENSION コマンドでモジュールを追加することより、色々な機能拡張が可能です。モジュールの大まかな分類として、PostgreSQL 本体のソースコードに含まれるか含まれないか、があります。PostgreSQL本体に含まるモジュールは contrib ディレクトリの下にあるため contrib と呼ばれており、contrib のrpmファイルをインストールすることで付録F 追加で提供されるモジュールを追加して拡張可能となります。
PostgreSQL本体のソースコードに含まれるcontrib モジュール以外にも、多種多様なモジュールがあちこちのサイトからダウンロードして利用可能ですが、常にメンテナンスされているモジュールもあれば、長い期間メンテナンスされていないモジュールもあり玉石混交です。また、個人的な好みや判断に依存しますが、contribモジュールでもよく利用されると考えられるものはそんなに多くありません。
そこで、本エントリでは、最低限 PostgreSQL 14 に対応しているモジュールの中から主に性能問題が発生した時に役に立ちそうと考えているモジュールをピックアップしてご紹介します。その後、モジュール以外もご紹介します。
contribのお勧め拡張モジュール
auto_explain
実行時間がかかるSQL文を PostgreSQL ログに出力するパラメータは PostgreSQL 本体でも log_min_duration_statement パラメータがあり、一定時間以上かかったSQL文が出力されますが、その時の実行計画もあわせて PostgreSQLログに出力してくれるモジュールが auto_explain モジュールです。
最低限設定が必要なパラメータは auto_explain.log_min_duration パラメータで、これは log_min_duration_statement の auto_explain 版です。
ストアドプロシージャを使っている場合のお勧めはauto_explain.log_nested_statements パラメータで、有効にすればストアドプロシージャので内部でネストした文の実行計画もあわせて出力されます。
実行計画そのものが悪い場合は上記の情報でよいと思いますが、実行計画と実際の実行時間がかけ離れている場合は、性能への影響を考慮しつつ auto_explain.log_analyze パラメータを有効にすることをご検討ください。
pg_stat_statements
pg_stat_statements モジュールは、PostgreSQLのSQL文のプラン作成時と実行時の統計情報を記録します。単体で導入される場合ももちろんありますが、後述の pg_statsinfo モジュールを使う時に(これまた後述の pg_store_plans モジュール)と合わせて導入される場合が多く、本モジュールで収集したデータを pg_stasinfo で集めて蓄積して、蓄積したデータを pg_stats_reporter で見るという流れで使用されます。
postgresql_fdw
postgresql_fdw モジュールは、外部(他)のPostgreSQLデータベースに接続する機能を持ちます。末尾の FDW は Foreign Data Wrapper の略で他にも種々の FDW があり、有名どころでは Oracle DB に接続する oracle_fdw モジュールがあります(会社の技術ブログ)。
am_check
am_checkモジュールは、B-Treeインデックスやヒープ(テーブル)の構造の一貫性を検査する、別の言い方をすると内容(データ)が壊れていないかどうかをチェックする機能を持ちます。PostgreSQL14からpg_amcheckコマンドが利用可能となったのでコマンドラインから簡単に実行できるようになりました。データベースが壊れていないかどうかのチェックに今後使われていくと思います。
contrib以外のお勧め拡張モジュール
pg_statsinfo
pg_stasinfo は、性能情報を収集する pg_statsinfo モジュールと、収集した情報をWebブラウザでGUIで表示する pg_stats_reporter から構成されています。Oracle DB で雑に例えると statspack / AWR と OEM みたいなものです(例えが古い?)。NTT OSS センタのDBMS開発サポートチームが開発しているソフトウェアで、本カテゴリ(contrib以外のお勧めモジュール)のほとんどがこの組織が開発しているソフトウェアです。
最近の流れとして、本ソフトウェアもPostgreSQL 13 までSourceforge で配布していましたが、PostgreSQL 14 からGitHub に移行しています。マニュアルは13のpg_statsinfoとpg_stats_reporterが見やすいです。
pg_statsinfo を導入した環境では継続的に性能情報を収集してくれているので、性能が悪化した時など後から調査できる情報が残っているのはサポートとして特にありがたいと思っています。そうそう、少し古いバージョンが対象(PostgreSQL13)ですが、会社の技術ブログでも紹介しています。
pg_hint_plan
pg_hint_plan モジュールは、ヒント句を指定することでSQL文の実行計画を固定して、実行計画が変化することによる性能悪化を防ぐ機能を有します。これも NTT OSSセンタの開発ソフトウェアです。こちらも PostgreSQL 13までOSDNで配布していましたが、現在はGitHubに移行しています。バージョンは古いですがマニュアルは pg_hint_plan 1.1.0 が見やすいです。
Oracle DBを使ってから PostgreSQL を使っているので、ヒント句で実行計画を望みのものに固定することは全く違和感がないのですが、突然性能が悪化して困った時にこの機能が使えるようになっているかどうかは大きいですので、是非初期導入時にこのモジュールも導入しておいてください。性能問題の調査対応として、auto_explain と pg_hint_plan がまず思いつきますのでお勧めです。そうそう、こちらもかなり古いバージョンが対象(PostgreSQL9.6)ですが、会社の技術ブログで紹介しています。
pg_store_plans
pg_store_plansモジュールは実行計画の統計情報を取得するモジュールで、前述のpg_stasinfo モジュールの導入とともに導入される場合が多いです。これもNTT OSSセンタの開発ソフトウェアです。pg_hint_plan のOSDNのサイトはPostgreSQL 14も配布されており、GitHubのサイトに移行したわけでもなさそうです。英語のドキュメントが見つかります。
pgaudit/pgauditlogtofile
pgAudit モジュールは、PostgreSQLで監査ログを PostgreSQlログに出力する機能、pgAudit Log to File モジュールは pgAudit で出力される監査ログをPostgreSQLログとは別の独立した監査ログファイルに出力する機能を提供します。監査ログを取る必要がある時にはこの二つを思い出していただければと思います。そうそう、会社の技術ブログで pgAudit と pgAudit Log to File を紹介しています。
pg_repack
pg_repack は、必要最小限のロックで、テーブルやインデックスの再編成をする機能を提供します。PostgreSQL はアーキテクチャとして VACUUM という定期的なメンテナンスが必要ですが、VACUUMを止めて定期的に pg_repack でテーブルやインデックスを再編成する運用をしているシステムもあります。CREATE EXTENSION でモジュールを導入して、pg_repack コマンドを使ってテーブルやインデックスを再編成します。そうそう、こちらも対象バージョンは古いですが、会社の技術ブログで紹介しています。
その他ソフトウェア(拡張モジュール以外)
pg_rman
pg_rmanは物理バックアップの容易な管理と差分バックアップが出来るソフトウェアで、超ざっくり言うと、Oracle RMAN のPostgreSQL版です。見やすいマニュアルは pg_rman 1.3 になります。
pg_basebackup を使ってバックアップは取得できますが、バックアップしたファイルを複数世代保管して古いバックアップファイルを削除するとか、データベースが壊れてリストア・リカバリが必要となり PITR (ポイントインタイムリカバリ)の実行が必要となった時に、PITRをきちんとそれなりに(かなり)面倒なので、お手軽にバックアップファイルの複数世代管理が出来てPITRが出来るツールは、バックアップ・リカバリの敷居が低くなって良いと思います。そうそう、こちらも対象バージョンは古いですが、会社の技術ブログで紹介しています。
pgAdmin4
pgAdmin4は、PostgreSQLのGUI管理ツールです。とても使いやすいツールだと思います。会社でPostgreSQLソフトウェアのリリース情報に携わっていますが、このソフトウェアの更新頻度は半端なく一番で、月に1回更新しているのでは？というレベルです。そうそう、これは珍しく対象バージョンが新しく、会社の技術ブログで紹介しています。
終わりに
PostgreSQL本体に拡張機能として色々追加できる拡張モジュールと周辺ソフトウェアの主なものを一通り書いてみると、NTT OSSセンタの開発ソフトウェアの紹介とそれらのサイトがPostgreSQL14から徐々にGitHubに移行しているという状況、会社の技術ブログの紹介とそれらの対象バージョンが古いことをあらためて認識して更新せねばと思った記事となりました。今回紹介したソフトウェアのほとんどがよく使われていますし、今回紹介できなかった周辺ソフトウェア(pg_bigm, PostGIS, psqlODBC, TimescaleDBなど)は会社の技術ブログの下の方に「サードパーティーソフトウェア」として記事がありますので、とりあえず使ってみようと思った時にみていただければありがたいです。
PostgreSQL12 関連情報とログ関係のpostgresql.confパラメータについて
本記事はPostgreSQL Advent Calendar 2019の5日目の記事です。
はじめに
昨年のPostgreSQL Advent Calendarの25日目の記事で、日頃PostgreSQLを触ってなく特にネタも無かったので記事のネタにするために OSS-DB Silver / Gold を受験して無事合格した体験記を書きました。
OSS-DB Silver / Gold 合格体験記 - kitayama_t's blog
これを書いた半年後の2019年6月から新しい会社に移ってなんとPostgreSQLのお仕事がメインとなりました！(パチパチパチ)。記事を書いた1年前はまったく想像していませんでしたが、新しい会社に入るきっかけともなり現在のお仕事は OSS-DB Silver / Gold 資格保持が必須だったので入社前に済ませておいてよかったです(^o^)。
ちなみに今回の記事は(内容は会社と大いに関係していますが(^^;))、個人的な立場でのエントリとなっていますのであらかじめご承知おきください。
今回書くこと
つい数か月前に出た新バージョンのPostgreSQL12に関する情報と、PostgreSQLを使う時にpostgresql.confでログに残すようにするパラメータ変更を色々しているのでそれをご紹介します。
PostgreSQL 12
PostgreSQL 12が2019年10月3日にリリースされました。ここ数年毎年安定して秋頃にリリースされています。その後最初のマイナーバージョンの12.1が2019年11月14日にリリースされて今日時点(2019年12月5日)の最新バージョンは12.1です。
PostgreSQLはマイナーバージョンアップでは原則バグフィックスのみで機能追加変更など無いので、安心して出来る限り新しいマイナーバージョンを使うようにしましょう。
PostgreSQL 12の情報
PostgreSQL 12ってどんなもの？という紹介を私がここで書くより、世の中には既によい資料がインターネット上にあるのでそれをご紹介します。
- PostgreSQL 12の話
ご存じ澤田さんの発表スライド。32ページのプレゼン資料で分かりやすく一通りの特徴(新機能)が紹介されているので、まず最初にこれを見て概要を把握するのがよいです。 - PostgreSQL 12 検証報告
ご存じSRA OSS, Inc. 日本支社が公開している新機能検証レポートです。こちらは主要な新機能を選択して、それを実際に動かしてどうなるという新機能ハンズオンを兼ねたチュートリアル的な機能紹介となっています。上記ページの中にPostgreSQL 12 検証レポート(PDF)へのリンクがあります。 - PostgreSQL 12 新機能検証結果 (GA) [日本語版]
ご存じ篠田さんの虎の巻。かなり網羅的に調査していてリファレンスとして使っています。
PostgreSQLの新しいバージョン(しかもベータ版から！)がリリースされるとすぐに新機能を検証した結果を公開されていて(たぶん一番で内容も濃い)、現在公開されているのはGA版ときちんと新しいリリースにも対応されています。ちなみに日本語版だけではなく英語版も公開されているので日本以外にも広く知られていると思います。 - @nuko_yokohama さんのQiita
ご存じnukoさんのQiitaでPostgreSQL12がやってくる！(11と10もあり)という連番のついたタイトルで新しいPostgreSQLの機能やパラメータ差分等について書いてます。PostgreSQLのレアなパラメータをググると日本語では篠田さんかnukoさんだけの時がよくありますw - SIOS Tech.Lab - PostgreSQL
SIOSさんの技術ブログのPostgreSQLタグです。PostgreSQL12に関しても色々機能検証されています。 - SRA OSS, Inc. 日本支社 - セミナー資料
SRA OSSがセミナーで発表した資料のPDFがずらっと並んでいます。ソフトウェア別の分類ではなく時系列でPostgreSQL以外にZabbixやRedis、Kubernetesなども混ざってます。一番下の2005年はPostgreSQL 8.1が最新版として紹介されてました(^^) - Discovering Lesser-Known PostgreSQL 12 Features
たまたま今日PostgreSQL Weeklyで見かけて、ちょうどよいレベル感で新機能を選定されたのでざっと眺めるにはよいです。
PostgreSQL 12の感想
まだがっつり触れてなくて一通り新機能や変更点を知っての感想です。
パラレルスキャンや宣言型パーティションテーブルなど大きな目玉があるリリースではなく、以前のバージョンで実装された機能が色々と改善されてより実用的になったバージョンだと思います。JITがパラメータ設定でデフォルトがONになったり、数千単位のパーティションでの性能改善やCTEでの性能改善など、普通に使っていたらレスポンスが改善されていたりするでしょう。
運用の観点ではREINDEXでCONCURRENTLYがサポートされたのが一番おおっと思いました。CREATE INDEXでは使えてましたが違う名前で別途作ってなどの手間が必要だったのが、REINDEXの内部で勝手にやってくれるようになりました。INDEXに対するVACUUM処理をOFFに出来るとかVACUUM対象テーブルがロックされていたらスキップするとか進捗状況がレポートされるなど、種々の細かな機能のよいところを把握したいと思っています。
postgresql.confでよくやるログ関係のパラメータ設定
PostgreSQL 12以外の話題としてpostgresql.confファイルでPostgreSQLを使う時にパラメータ設定としてログ関係でこんなん設定してますというメモです。
logging_collecter
メッセージをログに記録するために、まず最初にこれをonにしましょう。新規にDBを作った時に絶対これだけはonに設定しています。log_destinationはstderrのままです。
ちなみにソースコードからインストールしたらoffですが、rpmパッケージからインストールしたらonとなっていて、ソースコードとrpmとでpostgresql.confファイルで設定されている値が違うことを初めて知りました。
log_line_prefix
ログファイルの行の先頭に共通的に付加する時刻や接続ユーザやプロセスIDなどを％記法で定義します。シングルクオーテーションで囲みますが、閉じる右側のシングルクオーテーションの前には必ずスペースを入れましょう(一度体験したら忘れない地味な落とし穴)。
バージョン10以前のデフォルト値は空白('')で時刻すら記録されてませんでしたが、バージョン10から'%m [ %p ] 'となりました(日時時刻(ミリ秒) [ プロセスID] )。
これ以外にもバージョン10で色々改善(変更)された点が多く(例：xlogがwalになったり、パラメータ設定で変更する箇所が減ったり)、バージョン10以前の9.xの人はそろそろ2桁バージョンにバージョンアップしましょう。
アシストさんのDatabase Support Blog の障害発生に備えて設定すべき3つのログ関連パラメーターでは、パラメータ設定のお勧めとして'[%t]%u %d %p[%l]'が記載されています。
log_line_prefixで指定できるフォーマットはマニュアルの該当箇所を参照しましょう。PostgreSQL日本語マニュアルは右上でバージョンの切り替えが出来るのでとても便利です。
log_autovacuum_min_duration
自動VACUUMの実行をここで指定した数字のミリ秒以上かかったものをログに記録します。デフォルト値は-1で記録しませんが、私は自動VACUUMがいつ実行されたかを把握したいので0(すべて記録)を設定しています。
log_checkpoints
checkpointがいつ実行されたのかをログに残す習慣はOracleでやってたので普通にonを設定しています。checkpointが30分ぐらいの間隔になるようにcheckpoint関連パラメータを変更もしたりしますが、今回はログに残す設定関連だけ触れることにしています。
log_temp_files
work_memをどの程度まで増やせば一時ファイルが書かれなくなるかを調べる時に0に設定して一時ファイルの書き出しをチェックします。
log_min_duration_statement
まだ本気で設定したことがないのですが、本番稼働の環境ではロングトランザクションの存在が色々困ることの起因となるので、普通はこれ以上時間がかかるクエリはないというレベルの分単位(3分とか5分とか30分とか)を設定すると思います。とりあえず全部みたい時は一時的に0を設定します(ログがSQL文だらけになるので必要がなくなったらすぐ元に戻しますが)。
[TIPS] マイナーバージョンリリースでの変更点を確認するには？
基本はというか原則英語版のリリースノートやニュースを読め、なのですが、マイナーバージョンでどんな変更があったかをこちらのサイトで日本語で知ることが出来ます。
何か障害が発生した時に、自分が使っているバージョン以降のマイナーリリースで不具合修正されているかどうかをざっと確認するのにとても便利です。
https://www.sraoss.co.jp/technology/postgresql/
おわりに
PostgreSQLは基本機能も色々あってそれだけでもお腹いっぱいなのですが、それに加えてcontribやEXTENSIONで追加してあれこれ出来てしまうので範囲が広くて大変ですが視点を変えるとあれこれ出来て楽しいです。オープンソースの世界はまだまだ学ぶことだらけですが、楽しんでやっていきたいと思っています。
EXPLAIN関連資料URLメモ
PostgreSQLの実行計画を読むことが多くなってきたので、世の中にころがっている資料のURLをメモ。追記・更新すると思います(Wiki的な使い方)。
- Explaining Explain ～PostgreSQLの実行計画を読む～ (47p)
(2009-03-17更新 / バージョン8.3対応)
by Robert Treat (Inspired by Greg Sabino Mullane)
翻訳：日本PostgreSQLユーザ会
https://lets.postgresql.jp/sites/default/files/2016-11/Explaining_Explain_ja.pdf - Explaining EXPLAIN 第２回 (44p)
第20回しくみ+アプリケーション勉強会(2011年6月4日)
PostgreSQLしくみ分科会 中西剛紀
https://www.postgresql.jp/sites/default/files/2016-12/Explain%E8%AA%AC%E6%98%8E%E8%B3%87%E6%96%99%EF%BC%88%E7%AC%AC20%E5%9B%9E%E3%81%97%E3%81%8F%E3%81%BF%E5%8B%89%E5%BC%B7%E4%BC%9A%EF%BC%89.pdf
⇒上の資料を別の視点で解説した資料、こちらをまず読むのがいいかもしれない。 - PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～ (88p)
第30回(仮称)PostgreSQL勉強会(2014年10月11日)
TIS株式会社 下雅意美紀
OSS-DB Silver / Gold 合格体験記
この記事はPostgreSQL Advent Calendar 2018の25日目の記事です。
とりあえず
OSS-DB Gold受かりました！酔った勢いでAdvent Calendar申し込んでネタにするために期限を切って受けてよかったです！とりあえずめでたい！OSS-DB Silverも2週間前に受かりました！Gold難しかった！
申し込んだきっかけとこの題材を選んだ理由
おそらく11/27(火)の夜にいつものようにクラフトビールを飲んでTwitterを見ていたら、PostgreSQL Advent Calendarに気付いて最終日(12/25)だけが空いていたので、「アウトプットしないと何も変わらない!」と申し込みました。翌日、あれは幻だったと思いたかったのですが、どうも本当みたいなのであきらめてw何を書こうかと考えました。
で、OSS-DB Silver/Goldは前々から受けようと思って、3年前のするめごはんさんの合格体験記も参考にして参考書やトレーニングなどを受講してきましたが、受験しないままになっていました。実はSilverは一度受験しようとしたことがあって、踏ん切りがつかず何回かリスケしていたらとある受験日をすっかり忘れて受験料を無駄にしたアホな思い出もありますw。アホな思い出をクリアしたいこともあり、一か月ほどの間にGoldまで受けてみることにしました。
OSS-DB Silver
準備
学習教材のご紹介（OSS-DB認定教材）
■OSS-DB Silver（2018年6月現在）
https://oss-db.jp/measures/learning.shtml#text_silver
上記の学習教材のうち、使ったのはいわゆる緑本(OSS教科書 OSS-DB Silver)と、黒本(徹底攻略 OSS-DB Silver問題集［OSDBS-01］対応)の二つです。今見るとたくさんありますが、ずっと前からあるこの二つで済ませました。
緑本を読み込んで、黒本と公式サイトのサンプル問題／例題解説を解いてを繰り返しました。
サンプル問題／例題解説｜受験対策｜DBスペシャリストを認定する資格 OSS-DB技術者認定試験
結果
受験日：2018/12/11
取得点：84
合格ライン：64
結果：合格
一般知識：100%
運用管理：80%
開発/SQL：81%
感想
PostgreSQLを使うために必要な知識を幅広く一通り網羅的していて勉強になりました。Goldの資格の前提ですが、このぐらいは知っとけレベルの読み書きそろばんという感じです。
一言
はよ受けに行っとけ(未受験で終わった数年前の自分に向かって)
OSS-DB Gold
準備
学習教材のご紹介（OSS-DB認定教材）
■OSS-DB Gold（2017年4月現在）
https://oss-db.jp/measures/learning.shtml#text_gold
Goldの学習教材はSilverに比べて少なく、下記の三つだけです。
- LPI-Japan OSS-DB Gold 認定教材 PostgreSQL 高度技術者育成テキスト
- PostgreSQL運用管理トレーニング
- iStudy for OSS-DB技術者 OSS-DB Gold
私は今回このうちの二つ、「PostgreSQL高度技術者育成テキスト」と「PostgreSQL運用管理トレーニング」を使用しました。
もう一つのiStudyはGold受験の準備期間が短かったこともあり(2週間)今回は見送りました。昔々のORACLE MASTER受験ではとてもお世話になりました(Oracle 7/8/9iあたりですw)。昔のCD-ROM提供のアプリのようにずっと使えたら他の二つと同様に数年前に一緒に買って放置していたと思いますが、今は90日間しか使えないというのも、本気で受けに行くタイミングで買う必要があるため、踏ん切りがつかない性格には向いてない点もあります。
まずAmazonデマンド印刷で入手可能な「PostgreSQL高度技術者テキスト」は公式テキストというだけのことはあり、試験の準備には必要でした。いつ買ったか表示してくれるAmazonによると「お客様は、2015/6/12にこの商品を注文しました。 」とのことでしたw また最近のカスタマーレビューの正誤表は助かりました。
次にSRA OSSの「PostgreSQL運用管理トレーニング」で使っているテキストですが、これが一押しです。OSS-DB Goldを受けないけどPostgreSQLをちゃんと理解する最短経路だと思います(他の教材を全部見たわけではない個人的な意見ですが)。出来るならば会社負担でトレーニングを受講させてもらうのが一番良いですが、個人レベルでOSS-DB Goldを受験する人向けにテキスト販売もしています。私が持っているテキストはトレーニングを数年前に受けたため、少し古くて「PostgreSQL 9.2対応」(テキストバージョンPG90G-8)なのですが、現在はPG96-1の正誤表があるのでたぶん「PostgreSQL9.6対応」だろうと思っています。
Silverの時と同様、公式サイトの「サンプル問題／例題解説」と2冊の練習問題を繰り返しました。
サンプル問題／例題解説｜受験対策｜DBスペシャリストを認定する資格 OSS-DB技術者認定試験
後、教材ではないですが、これも前に買っていた「内部構造から学ぶPostgreSQL設計・運用計画の鉄則」電子版を読みました。今はPostgreSQL10対応の［改訂新版］が出てますが、今回のGold受験の試験範囲にはちょうど良かったです。
内部構造から学ぶPostgreSQL 設計・運用計画の鉄則：書籍案内｜技術評論社
Goldでは、仮想マシン上にPostgreSQL9.4環境を作ってパラメータの値を変更したりログ出力させたり、postgresql.confを眺めたり種々のシステムビューを確認しました。
もう一つ、Gold試験範囲(（※）2016年現在、9.4まで対応しております。)を印刷して、上記教材とのバージョン差異を意識して、足りない部分は自分で補足しました。
OSS-DB Exam Gold出題範囲詳細｜DBスペシャリストを認定する資格 OSS-DB技術者認定試験
結果
受験日：2018/12/25
取得点：80
合格ライン：70
結果：合格
運用管理：66%
性能管理：88%
パフォーマンスチューニング：83%
障害対応：83%
感想
PostgreSQLを実環境で環境構築して運用管理するために必要な内容を一通り網羅しているのを実感しました。
バックアップ・リカバリやPITR、ホットスタンバイ構築や、実際に環境を壊してどんなメッセージが出るとか、今は仮想マシン等でとても敷居が低くなっているので、
「実際に触って試してみる。経験を積んで整理して慣れる。」これに尽きると思います。
一言
受かって終わりではなく、これがスタートだと思います。
クリスマスなのでもう一言
「運用管理トレーニング」の最新版が欲しいです(^o^)
JPOUG> SET EVENTS 20140907 発表資料の補足説明
この記事は JPOUG Advent Calendar 2015 の23日目のエントリです。
- はじめに
今回のエントリはJPOUG> SET EVENTS 20140907 で発表した資料 DB思い出話いろいろ（仮） についての補足説明です。
タイトルは超ゆるいのですが、前半はORACLE MASTERや書籍などの紹介、後半にはDB移行やトラブル対応やパフォーマンスチューニングなどOracle Databaseでのお仕事経験から選んだ事例紹介とわりと役に立つ内容もあるかと思いますので、伝えたかったことや更新情報を交えて紹介します。
- きっかけ
Twitterで @wrcsus4 さんが
とのつぶやきに @yoshikaw さんがクロスエントリで2回目書きましょか？と返事していたので、それなら私が書いてみようかとほんと気軽に申し込んでしまいました。が、22日目までのエントリを見てみなさんガチに実用的なことが多くて少々ビビッてます(^^;)が、私らしくむかしばなしを交えて他の方とは違った雰囲気を味わっていただければと思います。
- about me
このブログの一つ前のエントリ JPOUG> SET EVENTS 20140907 でお話した「DB思い出話いろいろ（仮）」の資料をslideshareで公開しました。 に書いているように、Twitterスクリーンネーム（と言うんですんね。Twitter IDかと思ってました）の@kitayama_tで、昨年の9月のJPOUGイベントの最初のセッションで発表しました。（ちなみに同じ時間帯には篠田さんの12c新機能解説があって大人気で、私のセッションに来ていただいた方はとても貴重で嬉しかったです。ありがとうございました。）
イベントの様子はDB Onlineで加山恵美さんが 「JPOUG> SET EVENTS 20140907」フォト・レポート で記事と写真が掲載されています。発表は個人の立場でおこなったためTwitterスクリーンネームを使いましたが、特に名前を隠しているつもりもないので記事では本名が記載されています(^o^)。
超まじめな仕事モードの写真は所属と共にこちらに載ってます（こちらも加山恵美さんの記事）。ユーザ登録要のページなので気が向けばご覧ください。
http://itpro.nikkeibp.co.jp/atcl/column/15/052800134/052900001/?ST=system&P=2
- 目次
こんな内容を書きました。
-
- 自己紹介
- Oracle Databaseの思い出
- ORACLE MASTER
- 書籍などの技術情報
- 事例紹介：DB移行
- 事例紹介：トラブル対応
- 事例紹介：パフォーマンスチューニング
以下は各項目で解説というか思うところをば。
- 自己紹介
最初は自己紹介で、お仕事の経歴と社外活動について。お仕事の経歴は変わらずデータベースがメインで、それに加えて中学生の頃から触っているコンピュータそのものが大好きで現在に至っています。自分では明示的に意識してませんでしたが、修士論文の題が「データベースシステムの並列アクセス方式に関する研究」だったり、今の会社に転職する時に受ける予定だった他の2社がOracleとSunだったりで、最近はデータベースが専門と自覚するようになりました(^o^)
社外活動とかコミュニティなどでかかわっているデータベースはOracle DatabaseとPostgreSQLとMySQLでして、データベース自体もそれに関わっている人たちもそれぞれ強い個性と違いがあって楽しいです。そうそう、仕事では自社DBのVerticaも関わってます。NonStop SQL MX 1.5とかUnify 2000 とか思い出しましたが、それは横によけときます。
- Oracle Databaseの思い出
次のOracle Databaseの思い出は、タイトルの「DB思い出話いろいろ（仮）」に沿ってOracle Databaseの歴史を自分の経験とともにレアグッズや未だに覚えているエピソードを交えて紹介しました。一番のエピソードはSONY VAIO 505XでOracle Database8 Enterprise Edition for Linuxを動かしたことですね。メモリ64MBの貧弱なノートパソコンで起動した時は感動しました。今はメモリが2TBとか12TBとかでデータがオンメモリな状態がメインなのでまったく違う世界です。
次にあらためてOracle Databaseに対する「よくできたデータベース」で色んな機能を実装して他のデータベースの目標となって追いかけるという想いは変わりません。12c新機能に触れて「こんな機能まで実装してるの？」と思うこともしばしば。
統計情報はあいかわらず重要なポジションを占めていて、安全に運用するには「固定」したくなりますよね〜、人間古いからかな。10gでコストベース必須となってその後の11g、12cの機能改善はいかに暴れ回る実行計画をおとなしく制御して使いやすくするかだと感じます。
Index Only Scanもあいかわらずインデックスを使ってる限りは強力なチューニング手法だと思います。今から考えると、Index Only Scanは列志向DBそのものという気がしています。
今年(2015年)、久しぶりにORACLE MASTERを受験しました。どのくらい久しぶりかと言えば、資料に書いている日付を見ると一番最後が2010/10/15なので5年ぶりですね。
会社で1Z0-060のバウチャーチケットを配っていたので、つい手を上げてしまいました。バウチャーチケットの期限が近づいてやばくなって、やっとこさ準備を始めてシステム・テクノロジー・アイの代田佳子さんの黒本(Gold Oracle Database 12c Upgrade「新機能」編(オラクルマスター教科書))を買ってiStudy Onlineやって準備してチャレンジしました。
残念ながら結果は不合格。原因は一言で言えば勉強が足りてなくて準備不足で、本当に理解しているレベルまで達していなかったのが原因ですが、落ちるのは悔しいですね。落ちた直後は再受験して受かるつもりでしたが、再受験まで2週間という間が空くのがまた微妙な設定で、落ちた直後の悔しさと間違えた問題を覚えているのが2週間でほぼ薄れてしまうので、きちんと身につける必要があるのを実感しています。（結局まだ受けてません）
ちなみにOracle DBエンジニアの経験を積めば積むほど実環境への新機能の適用には慎重になることもあり、デリバーするエンジニアとして新機能を学ぶモチベーションは正直言ってほぼないです。今回強制的に試験を受けて感じたのは、ともかく試験範囲を一通り勉強することで、これから先もまったく触ることがないかもしれない機能なども触れて理解する機会としては有用です。資格を持っていても実際出来るかどうかはありますが、持ってないよりも持っていれば少なくとも知っている（いた）証明になります。
後、ちょっと横道にそれますが、今年はRed Hatの認定試験もいくつか受けました。4日間の講習の後に実技試験が1日用意されてます。実際にPCを触って環境設定をこなす実技試験はORACLE MASTER Platinum以来で、画面から選択肢を選ぶ知識試験とは違った技能が求められて苦しいながらもかなり楽しかったです。試験を準備して採点するほうも受けるほうも大変ですが、単なる知識の確認だけでなく、実際に操作して設定などが出来る実務技術の一定レベルの確認というか証明には有用だと感じました。
- 書籍などの技術情報
最近のOracle Databaseの書籍では「Oracleの現場を支える100の技」は良い書籍だと思います。
また製品固有ではないデータベースエンジニアの基礎知識として、奥野さんの「理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL (WEB+DB PRESS plus) 」はお勧めです。あまり得意でないSQLはミックさんのを選べば間違いないです。
- 事例紹介：DB移行
日経SYSTEMSに記事が載った移行事例で、実はこの項目がものすごく内容が充実してまして、タイトルに「DB移行事例解説」と入れたら集客違ってたかもと思います。
内容は9i on Windowsから11g on HP-UXへの一括データ移行で、エンディアンの異なるDBをDataPumpと従来型Import/Exportの併用でいかに工夫したかの項目が詰まっています。
ちなみにData Pumpって本当に速いですね。今年は初めてNETWORK_LINKオプションを使ったData Pumpでのデータ移行しましたが速くて便利でした。
こういう事例がMySQLやPostgreSQLのようにSlideshareなどで広まったらいいなと思っています。
- 事例紹介：トラブル対応
これまでの経験から紹介したいものを3つを選んで説明しています。どのトラブル対応も飲み会のネタにできる印象深い思い出です。ここには書きにくいトラブルも色々ありますが、それはまたご飯でも食べながら
- 事例紹介：パフォーマンスチューニング
経験はたくさんありますが、事例としては1つだけになってしまいました(たぶん息切れですね、資料のこのあたりは明け方でしたw)。とはいえ「性能検証/DBパフォチューで注意すべき点」でこれだけは気をつけて欲しいことをメモしています。
ということで、昨年のイベント資料をネタに書いてみました。資料と同じく最後のほうは息切れ感ありありですが、ここまで読んでいただいてたらとても嬉しいです！
JPOUG> SET EVENTS 20140907 でお話した「DB思い出話いろいろ（仮）」の資料をslideshareで公開しました。
2014年9月7日に飯田橋駅近くの新しいIIJさんの会場で、JPOUG(Japan Oracle User Group)のイベントがあり、そこで「DB思い出話（仮）」というタイトルで発表した資料をslideshareにアップロードしました。
JPOUG> SET EVENTS 20140907
http://www.jpoug.org/2014/08/05/jpoug-set-events-20140907
Slidesahre
http://www.slideshare.net/kitayamat/20140907-jpoug-myoldmemoriesofdatabase
よいこともあまりよくないことも(Platinum落ちたとか某アプライアンスとか)含めてセキララなお話をしたのであまり公開する気持ちはなかったのですが、Slideshareに一つもアップロードしてないこともあり、まーいいかと思ってえいやと公開しました。
内容は前半がOracle Databaseとの長年のお付き合いでの出来事（仕事で接したものとか趣味で接したものとか）で自分の歴史帳みたいなものになりまいた。
後半は技術的に何か役立つことをお持ち帰りしてもらおうと思って、「自分だったら事例が聞きたいな。」と思って、Oracle 9i on Windows から Oracle 11i on HP-UXへのDB移行のお話をメインに、これはというトラブルを三つほどピックアップして紹介しました。
ORACLE MASTERを取得し始めたのは35歳からってなかなかスロースタートだなとあらためて気づきました(^o^)
会場を提供していただいたIIJ様、発表する機会をいただけたJPOUGの中の人のみなさま、参加していただいたみなさま、ありがとうございました。
