Postgres SQL めもめも

テーブル件数・容量

select relname, to_char(reltuples, '999999999') as rows, to_char(pg_relation_size(relname::regclass), '999999999999') as bytes from pg_class where relkind='r' and relnamespace = (select oid from pg_namespace where nspname='public') order by relname;

インデックス件数・容量

select relname, to_char(reltuples, '999999999') as rows, to_char(pg_relation_size(relname::regclass), '999999999999') as bytes from pg_class where relkind='i' and relnamespace = (select oid from pg_namespace where nspname='public') order by relname;

キャッシュヒット率（データベース）

select datname,round(blks_hit*100/(blks_hit+blks_read), 2) as cache_hit_ratio from pg_stat_database where blks_read > 0;

キャッシュヒット率（テーブル）

select relname,round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2) as cache_hit_ratio from pg_statio_user_tables where heap_blks_read > 0;

インデックスキャッシュヒット率

select relname, indexrelname, round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2) as cache_hit_ratio from pg_statio_user_indexes where idx_blks_read > 0;

表スキャンあたりの読み取り行数の確認

select relname, seq_scan, seq_tup_read, seq_tup_read/seq_scan as tup_per_read  from pg_stat_user_tables where seq_scan > 0;

ガベージの量の確認

select relname, n_live_tup, n_dead_tup, round(n_dead_tup*100/(n_dead_tup+n_live_tup), 2)  as dead_ratio, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where n_live_tup > 0;

HOT更新の比率の確認

select relname, n_tup_upd, n_tup_hot_upd, round(n_tup_hot_upd*100/n_tup_upd, 2) as hot_upd_ratio from pg_stat_user_tables where n_tup_upd > 0;

トングトランザクションの処理と経過時間の確認

select procpid, waiting, (current_timestamp - xact_start)::interval(3) as duration, current_query from pg_stat_activity where procpid <> pg_backend_pid();

ロック待ちとなっている処理内容と対象のテーブルを確認

select l.locktype, c.relname, l.pid, l.mode, substring(a.current_query, 1, 6) as query, (current_timestamp - xact_start)::interval(3) as duration 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;

参照

Let's Postgres 稼動統計情報を活用しよう(2)

Postgres ディスク 負荷分散 テーブルスペース tablespace

ディスクの負荷分散！？

Postgresインストール先・データは/usr/local/pgsql/（/dev/sda）としてＨＤＤ２個追加してＤＢを作ってみる

テーブルスペース用のディスクをフォーマット＆マウント

# mke2fs -j /dev/sdb1

# e2label /dev/sdb1 /pgsql1

# mkdir /pgsql1

# mount /dev/sdb1 /pgsql1

# mke2fs -j /dev/sdc1

# e2label /dev/sdc1 /pgsql2

# mkdir /pgsql2

# mount /dev/sdc1 /pgsql2

# vi /etc/fstab

LABEL=/pgsql1 /pgsql1 ext3 defaults,noatime 1 2

LABEL=/pgsql2 /pgsql2 ext3 defaults,noatime 1 2

テーブルスペース作成

# mkdir /pgsql1/pgdata1

# chown postgres:postgres /pgsql1

# chown postgres:postgres /pgsql1/pgdata1

# mkdir /pgsql2/pgdata2

# chown postgres:postgres /pgsql2

# chown postgres:postgres /pgsql2/pgdata2

# su - postgres

$ psql -c "\db+" template1 又は↓

$ psql -c "SELECT spcname FROM pg_tablespace;" template1

                        List of tablespaces

    Name    |  Owner   | Location | Access privileges | Description 

------------+----------+----------+-------------------+-------------

 pg_default | postgres |          |                   | 

 pg_global  | postgres |          |                   | 

(2 rows)

$ psql -c "create tablespace ts_pgdata1 location '/pgsql1/pgdata1';" template1

CREATE TABLESPACE

$ psql -c "create tablespace ts_pgdata2 location '/pgsql2/pgdata2';" template1

CREATE TABLESPACE

$ psql -c "\db+" template1

                            List of tablespaces

    Name    |  Owner   |    Location     | Access privileges | Description 

------------+----------+-----------------+-------------------+-------------

 pg_default | postgres |                 |                   | 

 pg_global  | postgres |                 |                   | 

 ts_pgdata1 | postgres | /pgsql1/pgdata1 |                   | 

 ts_pgdata2 | postgres | /pgsql2/pgdata2 |                   | 

(4 rows)

出来上がった環境

Postgresインストール先 /dev/sda上の/usr/local/pgsql

Postgresデータ /dev/sda上の/usr/local/pgsql/data

テーブルスペース１ /dev/sdb上の/pgsql1/pgdata1

テーブルスペース２ /dev/sdc上の/pgsql2/pgdata2

ＤＢ作成

日々更新・参照されるdb(db1+db2+db3...)はts_pgdata1へ

日々更新されるdb1,db2,db3.....とdbのINDEXはts_pgdata2へ

ということをしてみようかと。

# su - postgres

$ createdb db -D ts_pgdata1

$ psql -c "create table table1 ....." db

$ psql -c "create index index1 ..... tablespace ts_pgdata2" db

$ createdb db1 -D ts_pgdata2

$ psql -c "create table table1 ....." db1

$ psql -c "create index index1 ....." db1

$ createdb db2 -D ts_pgdata2

$ psql -c "create table table1 ....." db2

$ psql -c "create index index1 ....." db2

$ createdb db3 -D ts_pgdata2

$ psql -c "create table table1 ....." db3

$ psql -c "create index index1 ....." db3

確認。。。。。

？。。。テーブルとインデックス作成時にテーブルスペースを指定しなかったので現状を確認したいけれど、方法が分からなかったのでpg_adminでプロパティを見てみると意図したとおりに作成されていた。プロパティを持ってるってことはSQLでも確認できそうなので今度Postgresのシステムテーブルを漁ってみることに。。。

PostgresドキュメントのCREATE INDEX欄も見てみると載っていた。。。

＞CREATE [ UNIQUE ] INDEX name ON table [ USING method ]

＞ ( { column | ( expression ) } [ opclass ] [, ...] )

＞ [ TABLESPACE tablespace ]

＞ [ WHERE predicate ]

＞tablespace

＞インデックスを生成するテーブル空間です。 指定されなかった場合、default_tablespaceが使用されます。 もし、default_tablespaceが空文字列であった場合はデータベースのデフォルトのテーブル空間が使用されます。

おまけ

ＤＢダンプを取って見てみると違う環境でＤＢダンプから再構築したとしてもCREATE文ではエラーが出ないようになっていた。

ただ同じ名前のテーブルスペースがあったとしたら意図しないテーブルスペースを使用する可能性があるので余計な事を考えたくなければ、テーブルスペース名にサーバの名称など一意なものを加えると管理しやすいかも。

SET default_tablespace = '';

...

CREATE TABLE table1 ...;

...

...

SET default_tablespace = ts_pgdata2;

...

CREATE INDEX index1 ...;