Slony-IでレプリケーションしているDBに対して、停止せずにテーブルを追加する方法
Slony-IはPostgreSQL用の非同期のシングルマスタレプリケーションソフト。
意外と簡単にPostgreSQLのレプリケーションをすることが可能です。
今回は、一旦運用を開始したレプリケーションしているDBに対して、機能追加等でテーブルの追加が必要になった場合に
Slonyを止めずに新しくレプリケーション対象のテーブルを追加するやり方を紹介します。
（他にもっと簡単な方法があるかもしれませんが。あれば教えてください）
１．新しいテーブルを追加する
execute_script経由で、セット全体にテーブルを追加。
full_path_to_sql_script_fileにCREATE TABLE文を書いて、事前に準備しておきます。
execute_script [options] (セット番号) full_path_to_sql_script_file
２．新セットを追加する
slon_tools.confに新しいセットの設定を追加
set_id は稼働中のset_idと被らないように。
table_id は、select max(tab_id) from _<<レプリケーション名>>.sl_table;の結果+1
sequence_id は、select max(seq_id) from _<<レプリケーション名>>.sl_sequence ;の結果+1
slonik_create_set (セット番号) | slonik
３．追加したセットの購読
slonik_subscribe_set (追加したセット番号) (ノード番号) | slonik
４．セットのマージ
追加したセットを元のセットにマージする
slonik_merge_sets (ノード番号) (元のセット) (追加したセット番号) | slonik
これで完了です。簡単です。
PostgreSQL CE 8 Goldを受けてきました。
久しぶりのベンダー試験です。
前回の「PostgreSQL CE Silver」以来なので、緊張するかと思ったのですが、もう慣れたものですね・・。
今月末ぐらいをターゲットに勉強してたのですが、段々飽きてきたので1週間程集中して勉強し、
2日前に試験予約して強行受験してきました。
セクション毎の正解率
全体の成績は90%で、無事合格。
| セクション | 構成 | 正解率 |
|---|---|---|
| 運用管理 | 20%(6問) | 100% |
| パフォーマンスチューニング | 60%(18問) | 94% |
| 障害対応 | 20%(6問) | 66% |
正解率は90%なので、3問不正解だった模様。
パフォーマンスチューニングの1問は、どれが間違っているのか良く解らないが、
障害対応は、pg_resetxlog絡みの問題だと思われる。
この辺りは、幸いというかなんというか実務で発生したことがないので理解が不足してました。
勉強方法
この試験は、試験対策の教科書や問題集がないので勉強しづらいですが、JPUGの皆さんの努力のおかげで
日本語のマニュアルや資料は豊富にあります。
つまりは、これらをいかに活用するかということが大事かと。
まずは、試験の範囲として公開されている部分とマニュアルの該当部分を紐付ける作業を行いました。
マニュアル全部に目を通すとなると、さすがに途中で飽きそうだったので、該当部分を見て、
理解して、実行して、バージョンの差異をまとめるという方法をとりました。
正直、試験に受かるためだけならそこまでやる必要はないのでしょうが、私の目的は
PostgreSQL8.3系をきちんと使いこなせるようになることでしたので、このような方法となりました。
PostgreSQL関連のメモとして使ってたサイトで、公開していますので受ける方は参考にしてください。
マニュアルをまとめただけなので、微妙ですが・・・。
試験感想
「思ったより簡単だった」というのが、正直な感想です。pg_classやpg_statsなんかのデータを見せられて、
○○なのはどれか、○○するためにはどうしたらよいか等の問題が出たら厳しいかな思ってましたが
そこまでは聞かれることはありませんでした。
計算が必要な問題が何個かあったりしたのですが、電卓機能があったのが驚きました。ディスク容量の計算も
自力でやる覚悟はしてたのですがｗ
これから受ける方にアドバイス
- Goldの練習問題は、8と7.4両方解いておくこと。普通に出題されます。（5問ぐらい？）
- 重箱の隅を突付くような問題はなく、きちんと理解してれば解けます。
- 実行計画を変更するには、どんな方法があるのかをよく理解する。
その他
これでようやく実務に見合う資格が取得できたと思いますが、資格自体がマイナーなため某転職サイトでは、
資格欄に表示すらされません。もっと、PostgreSQLを使いこなせるエンジニアが増えてメジャーになれることを願います。
pg_statsinfoを使用する際の注意点
pg_statsinfoは、PostgreSQLやOSのリソース情報、統計情報をスナップショットとして取得するためのツールで、
複数のスナップショット間の差分をレポートとして出力できます。
これによって、DBで行われた活動状況の確認や性能ボトルネックの発見、また問題発生時の原因調査などが簡単になります。
数十台のPostgreSQLサーバを管理しようとすると、さすがに個別で見るのは厳しいので、半年程前に
pgstatspackで取得したスナップショット情報を集中管理し、Web上で各レポートやグラフの表示ができるものを
こっそり作成してたりしたのですが、情報取得部分だけ作って絶賛放置中でした・・・。
（取得してた情報はそれなりに役に立ったのですが。）
ですが、良さそうなツールが公開されたとのことで、早速使ってみることに。
日本で開発されているので、ドキュメントが全て日本語で書かれていて、しかも豊富です。
pg_statinfoのページはこちらへ
インストールは、マニュアルを見てセットアップすると問題なくできたのですが、いざスナップショットを取得しようとすると
以下のようなエラーが発生しました。
stats=# SELECT get_stats();
ERROR: sql error
DETAIL: ERROR: relation "_replication_xxxxx.partind_replication_xxxx_sl_log_1-node-21" does not exist
SQLがエラーになる箇所があるようで、うーむ、困った。
インデックス名の"-"がダメな気がするのですが、該当のインデックスはSlony-Iが自動で作成するものなので、さらに困った。
Slony-Iのsrc/backend/slony1_funcs.sqlの5747行目付近（addPartialLogIndicesの中）に書かれています。
既に、Slony-Iは稼動中なのでそこに手を入れるわけにもいかず、pg_statsinfoの方に手をいれることにしました。
修正する箇所は、stats_info/stats_info_sql.hのSQL_INSERT_T_INDEXINFO_FORMATで定義されているSQL。（386行目付近）
pg_relation_size(stat_idx.schemaname ||''.''||stat_idx.indexrelname), \
↓
pg_relation_size(stat_idx.schemaname ||''.''||''\"''||stat_idx.indexrelname||''\"''), \
上記の変更することで、うまくスナップショットが取得できるようになりました。
もう少し使ってみて、問題無い様でしたら本格的に運用に乗せていこうかなと考えています。
（レポートの自動取得やメール送信なんかもできたら、結構楽できそうな気がします。）
ちなみに環境は、こんな感じです。
管理DB：PostgreSQL8.3.0
対象DB：PostgreSQL8.3.3+Slony-I 1.2.14
追記1
スナップショットを削除するdelete_statsがうまく動かずに、以下のエラーが発生します。
stats=# SELECT delete_stats(1);
ERROR: relation "t_colcorr" does not exist
CONTEXT: SQL statement "DELETE FROM t_colcorr WHERE snapshot_id = $1 "
PL/pgSQL function "delete_stats" line 27 at SQL statement
「t_colcorrテーブルがない」とのことなので、テーブルを見てみると確かにない・・・。
これは、きっとt_columncorrテーブルのことだと信じて、delete_stats関数の該当箇所を修正。
すると消せるようになりました。
5/18追記
pg_statsinfo1.0.1でこのバグは修正されました。
追記2
マニュアルに間違いっぽ箇所を発見。
ソースによるインストールのPATHを通すところです。
$ cp $PGSRC/contrib/pg_statsinfo-*/target_db/pg_get_logfilterinfo $LOGFILTER
$ cp $PGSRC/contrib/pg_statsinfo-*/target_db/* $REPORT
$ export PATH=$LOGFILTER:$REPOTER:$PATH
↓
$ cp $PGSRC/contrib/pg_statsinfo-*/target_db/pg_get_logfilterinfo $LOGFILTER
$ cp $PGSRC/contrib/pg_statsinfo-*/reporter/* $REPORT
$ export PATH=$LOGFILTER:$REPORT:$PATH
レポート出力時のサンプルで$REPORTを指定しているので、きっと$REPORTが正しいのでしょう。
要望1
情報取得対象となるschemaを選択できるようにして欲しい。
基本的にpublic schemaだけで十分なケースが多いので、システムカタログ等の情報はサイズも大きくなるので
取らないようにできるようになったらいいですね。
（私は幾つかのSQLの条件にschema名を指定しています。）
もっとも、情報を取得しないschemaでボトルネックが発生しないことが前提ですが。
要望2
Webの管理画面とか欲しいですねー。（言うだけなら無料なので）
Open Flash Chart等を使ってグラフ表示できたら、尚いいですねー。（無茶も言うだけなら無料！！）
気が向いたら、自分で作りますか。。。
PostgreSQL8.3.7にLudia1.5.2をインストールする
2009/04/07時点の最新版であるPostgreSQL8.3.7+Ludia1.5.2をRHEL5.2にインストールしていたところ、
Ludiaのmake時に以下のようなエラーが発生した。
pgsenna2.c: In function 'pgs2build0':
pgsenna2.c:613: warning: passing argument 4 of 'IndexBuildHeapScan' makes integer from pointer without a cast
pgsenna2.c:613: error: too few arguments to function 'IndexBuildHeapScan'
make[1]: *** [pgsenna2.lo] エラー 1
PostgreSQL8.3.3+Ludia1.5.1+RHEL5.2の環境では問題なくインストールできていたので、
PostgreSQLかLudiaのマイナーバージョンアップの影響の可能性が高い。
試しに、Ludiaを1.5.1に戻してみたが状況は変わらず。原因はほぼPostgreSQL側にあるようだ。
エラーの内容を見ると、IndexBuildHeapScanの4番目の引数がまずいよと言っているっぽいので、
src/backend/catalog/index.cの該当箇所を見てみると・・・・
- PostgreSQL8.3.5
double IndexBuildHeapScan(Relation heapRelation, Relation indexRelation, IndexInfo *indexInfo, IndexBuildCallback callback, void *callback_state)
- PostgreSQL8.3.6
double IndexBuildHeapScan(Relation heapRelation, Relation indexRelation, IndexInfo *indexInfo, bool allow_sync, IndexBuildCallback callback, void *callback_state)
確かに、4番目の引数にallow_syncが追加されています・・・。
ということは、PostgreSQLのバージョンアップにLudiaがまだ対応できていない状況。
とりあえず、Ludiaを使用する予定がある方は、PostgreSQL8.3.5以降のバージョンを使うと危険ということです。
Ludia1.5.3以降で対応して頂けることに期待しましょう。
それでも、俺はPostgreSQL8.3.7+Ludia1.5.2を使いたいぜ！！って言う人はLudiaのソースちょっとだけ弄ると
うまくコンパイルが通るようになります。
- pgsenna2.c
reltuples = IndexBuildHeapScan(heap, index, indexInfo,
buildCallback, (void *) &bs);
↓
reltuples = IndexBuildHeapScan(heap, index, indexInfo,true buildCallback, (void *) &bs);
新しく追加された4番目のallow_sync引数にbooleanの値を与えて上げればよいだけですが、問題はこの引数を変更すると
どんな影響があるのか？ということで、ちょっと調べてみると8.3.6の変更点の下記に該当するっぽいです。
GIN インデックスを構築する間は、同期スキャンを行なわないようになりました。(Tom)
これは、GIN は TID の昇順での行を挿入するように最適化されているため、同期スキャンによって
インデックス構築が３倍以上遅くなる可能性があるためです。
allow_syncにtrueを指定すると前バージョンと同じ動作になり、falseを指定するとGINインデックス構築中に同期スキャンを
行わないような設定になるようです。
今回は、あくまでも暫定対応ということでallow_syncにfalseを指定することにしました。
これ以上、変更点に躓いても仕方が無いので・・・。
重要なので2度言いますが、Ludiaを使用する予定がある方は、PostgreSQL8.3.5以降のバージョンを使うと危険です。
大人しく8.3.5を使用しましょう。
CREATE INDEXを使いこなす
検索時には大変お世話になっているインデックス。
使われなくても文句も言わずに更新され続けるインデックス。
ずっと放置して運用しているといつの間にか肥大化しているインデックス。
今回は、そんなインデックスを再構築する際に気をつけることをまとめてみました。
まずは、インデックスを作成、または再作成するやり方について簡単に。
CREATE INDEX
普通にインデックスを作成。
他のトランザクションはテーブルを読み取ることはできるが、対象テーブル内の行を挿入、更新、削除しようとすると、
インデックス作成が完了するまでブロックされる。
CREATE INDEX CONCURRENTLY
挿入、更新、削除をブロックしないインデックスの作成方法。
テーブルを2回スキャンする必要があり、潜在的にそのインデックスを使用する可能性がある、実行中のすべての
トランザクションが終わるまで待機する必要がある。
通常よりも総作業時間がかかり、また、完了するまでの時間が非常に長くなる。
REINDEX INDEX
インデックスの再構築。
インデックスの元となるテーブルの書き込みをロックしますが、読み込みはロックしません。
また、処理中のインデックスに対する排他ロックを取得するので、そのインデックスを使用する読み込みはブロックされる。
まとめ
上記の内容を簡単にまとめると下表のようになります。
| 方式 | 読み込みブロック | 更新ブロック | 備考 |
|---|---|---|---|
| CREATE INDEX | 無し | 有り | 普通 |
| CREATE INDEX CONCURRENTLY | 無し | 無し | 作業時間が長い |
| REINDEX INDEX | 一部有り | 有り | reindex中のindexを使用する読み込みは待機 |
実際、運用中にインデックスの再構築を行う必要がある場合（読み込み、書き込みともに停止できない）は、
- CREATE INDEX CONCURRENTLYで新しくインデックスを作成する。
- 古いインデックスを削除
- 先程作成したインデックスを元の名前にリネーム
- Analyzeを実施
こんな感じでやるのが、良いのではないでしょうか。
夜間等、使用されない時間帯が決まっている場合、定期的にメンテナンスの時間が確保できる場合は、
reindexを行う方が簡単なのは間違いないです。
遅いSQLの見つけ方
OracleでSQLのチューニングをそこそこしていた人がPostgreSQLで同じことをやろうとした場合に
問題になるのはチューニング対象のSQLを探す作業だと思う。
Oracleであれば、SQL traceを仕掛けたり、$sql_textなどから引っ張ってきたり、Statspackのレポート読んだりと
色んな方法で遅いSQLを特定することが可能だ。
しかし、PostgreSQLではOracle程簡単に対象のSQLを特定するのは難しい。（この辺りの機能がもう少し欲しいところ）
ということで、今回は遅いSQLを見つける特定する方法です。
log_min_duration_statementの設定を変更する
postgresql.confにlog_min_duration_statementというパラメータがある。
指定したミリ秒数以上、時間がかかったSQLをログに出力してくれるものだ。
MySQLのスロークエリーログと同じような感じらしい（MySQLは全然詳しくないので伝聞ですが）
普段、遅いクエリが走っていないか、夜間バッチ等、会社に居ない間に実行されるクエリのパフォーマンスを
確認したい場合なんかに向いている機能ではないでしょうか。
こんな感じで出力されます。
2009-02-18 11:43:23 JST LOG: duration: 2750.721 ms statement: select count(*) from master_tbl;
ちなみにPDOを使っていた場合も、2行目にパラメータを出力してくれるので再実行する際も楽です。
2009-02-23 04:08:10 JST LOG: duration: 8511.299 ms execute pdo_pgsql_stmt_0098b4b0: SELECT COUNT(*) FROM tableA WHERE id = $1 AND type = 0 2009-02-23 04:08:10 JST DETAIL: parameters: $1 = '2782034'
気をつける点としては、このパラメータを変更すると閾値を上回ったSQLをすべてログに出力するので、下手するとログ出力がボトルネックになりかねないということ。
設定する際は、最初は大きめにとって徐々に閾値を下げていくという方向でやるのがいいでしょう。
実行中のSQLを取得
もうひとつの方法は、現在実行中のSQLを取得する方法。
下記のSQLを実行すると、現在実行中のSQLと実行時間を取得できます。
testdb=# SELECT testdb-# pid, testdb-# start, testdb-# now() - start AS lap, testdb-# current_query testdb-# FROM testdb-# (SELECT testdb(# backendid, testdb(# pg_stat_get_backend_pid(S.backendid) AS pid, testdb(# pg_stat_get_backend_activity_start(S.backendid) AS start, testdb(# pg_stat_get_backend_activity(S.backendid) AS current_query testdb(# FROM testdb(# (SELECT pg_stat_get_backend_idset() AS backendid) AS S testdb(# ) AS S testdb-# WHERE testdb-# current_query <> '' testdb-# ORDER BY testdb-# lap DESC; pid | start | lap | current_query