PostgreSQLメンテナンスメモ

PostgreSQLのメンテナンス、その他についてのメモ。

# 基本
explain select * from test where insert_date > '2000-01-01 00:00:00';

# 問い合わせを実行し、実際の実行時間を表示する。UPDATE文は注意
explain analyze select * from test where insert_date > '2000-01-01 00:00:00';

# 詳細表示
explain verbose select * from test where insert_date > '2000-01-01 00:00:00';

prepare文

# prepare文登録
prepare query(int, int, date) as SELECT * FROM test WHERE id = $1 AND amount = $2 AND insert_date = $3;
# explain
explain analyze execute query('101', '11','2010-01-01');

統計情報コレクタ

統計情報を収集します。
統計情報コレクタを全て起動するには、postgresql.confのstats_start_collectorパラメータをtrueに設定する必要があります。これはデフォルトであり、推奨する設定です。

詳細:http://www.postgresql.jp/document/8.4/html/monitoring-stats.html

# インデックス調査
select * from pg_stat_user_indexes where relname like 'test%' order by relname, indexrelname;
# テーブル情報調査
select * from pg_stat_user_tables where relname like 'user_%' order by schemaname, relname;

遅延調査、SQLのキャンセル、lock調査

詳細:http://www.postgresql.jp/document/pg840doc/html/functions-admin.html

# 遅延SQLのプロセスID取得
SELECT
  procpid,
  start,
  now() - start AS lap,
  current_query
FROM
  (SELECT
    backendid,
    pg_stat_get_backend_pid(S.backendid) AS procpid,
    pg_stat_get_backend_activity_start(S.backendid) AS start,
    pg_stat_get_backend_activity(S.backendid) AS current_query
  FROM
    (SELECT pg_stat_get_backend_idset() AS backendid) AS S
  ) AS S
WHERE
  current_query <> ''
ORDER BY
  lap DESC;

# バックエンドの現在の問い合わせを取り消す
SELECT pg_cancel_backend(プロセスID);

# バックエンドを終結(>=8.4)
SELECT pg_terminate_backend(プロセスID);

# lock 検出
SELECT
  (SELECT relname FROM pg_class PGC WHERE PGC.oid = PL.relation) AS relation,
  (SELECT distinct datname FROM pg_database PGDB WHERE PGDB.oid = PL.database) AS database,
  PL.transactionid, PL.pid, PL.mode, PL.granted
FROM
  pg_locks PL; 

# lock待ち
SELECT
  l.locktype, c.relname, l.pid, l.mode,
  substring(a.current_query, 1, 6) AS query,
  age(l.transactionid) AS txn_age
FROM
  pg_locks l
  LEFT OUTER JOIN pg_stat_activity a ON l.pid = a. procpid
  LEFT OUTER JOIN pg_class c ON l.relation = c.oid
WHERE
  NOT l.granted ORDER BY l.pid;

リソース

一例ですが・・・。詳しくは↓を参考にしてください。
詳細:http://www.postgresql.jp/document/8.4/html/runtime-config-resource.html

メモリ
・shared_buffers
1ギガバイト以上のRAMを持つデータベース専用のサーバであれば、shared_buffersの値をシステムのメモリの25%から始めることが合理的。サーバ起動時のみ設定可能。

・temp_buffers
それぞれのデータベースセッションが使用する一時バッファの最大数を設定します。一時テーブルにアクセスする時にのみ使用されるセッション局所バッファです。

・work_mem
一時ディスクファイルに切替える前に、内部並べ替えとハッシュテーブル操作が使用するメモリ容量を指定します。

・maintenance_work_mem
VACUUM、CREATE INDEX、およびALTER TABLE ADD FOREIGN KEYの様な保守操作で使用されるメモリの最大容量を指定します。オートバキュームを稼動させると、最大autovacuum_max_workers倍ほどこのメモリーが配分されるので、デフォルトの値をあまり高く設定しないよう注意することを覚えておいてください。

・vacuum_cost_delay
バキュームにかかったコストがvacuum_cost_limitで指定した制限値に達したときにバキュームを休止する期間をミリ秒単位で指定。0の場合は、制限に達した場合でも休止を行わずバキューム負荷調整機能は無効。

postgresql.conf例

shared_buffers = 150000 # 150000 x 8KB
temp_buffers = 8000 # 8000x8KB
work_mem = 2048 # 2MB
maintenance_work_mem = 65536 # 64MB

vacuum_cost_delay = 0

pgbench

後で書く
詳細:http://www.postgresql.jp/document/9.0/html/pgbench.html

pgstattuple

リレーションの物理的な長さ、"不要"なタプルの割合、およびその他の情報を返します。
tuple_percentが70をきったらバキュームフルの目安。(テーブルのFILLFACTORにもよりますが・・・。)

詳細：http://www.postgresql.jp/document/8.4/html/pgstattuple.html

# testテーブルの情報取得
SELECT * FROM pgstattuple('test');

table_len	bigint	リレーションのバイト単位の物理長
tuple_count	bigint	有効なタプル数
tuple_len	bigint	有効なタプルの物理長（バイト単位）
tuple_percent	float8	有効タプルの割合
dead_tuple_count	bigint	無効なタプル数
dead_tuple_len	bigint	バイト単位の総不要タプル長
dead_tuple_percent	float8	不要タプルの割合
free_space	bigint	バイト単位の総空き領域
free_percent	float8	空き領域の割合

・インストール

# postgresqlのコンパイルディレクトリ
cd /usr/local/src/postgresql-[バージョン]
# pgstattupleソースディレクトリ
cd contrib/pgstattuple
make
su -
make install
exit

# postgresqlインストールディレクトリ
cd /usr/local/pgsql
# pgstattupleファンクションインストールスクリプト
cd share/contrib
psql [DB名] -f pgstattuple.sql

create database（文字コード指定）

次のようなときのcreate databaseの指定方法
・サーバのデフォルトコードとDBの文字コードが異なるとき
・initdbで指定した文字コード以外のDBを作成するとき

# 任意の文字コードのサーバでUTF-8のDBを作成
# TEMPLATE template0を指定するとうまくいく

# psql
CREATE DATABASE test2 ENCODING 'utf-8' LC_CTYPE 'C' LC_COLLATE 'C' TEMPLATE template0
# createdb
createdb --encoding='utf8' --lc-ctype='C' --lc-collate='C' test2 --template='template0'

インデックス

詳細:
CREATE INDEX
テキスト検索の方法とインデックス
 GiSTおよびGINインデックス種類
 GINインデックス

# 基本
CREATE INDEX test_idx_01 ON test (name);
# 部分
CREATE INDEX test_idx_02 ON test (name) where insert_date >= '2011-01-01';
# マルチカラム
CREATE INDEX test_idx_03 ON test (name, insert_date);
# 関数
CREATE INDEX test_idx_04 ON test (lower(name));
# ユニーク
CREATE UNIQUE INDEX test_idx_05 ON test (name);
# BTREE, RTREE, HASH
CREATE INDEX test_idx_06 ON test USING HASH (name);
# 配列型のカラムにGIN
CREATE INDEX test_idx_07 ON test USING gin (test_array);
# 検索でGist
CREATE INDEX test_idx_08 ON test USING gist (name);