PostgreSQL チューニング

チューニング

・キャッシュサイズ増加

インデックスがこのサイズに収まってる場合、効果が最大限発揮される。

[root]# vim /usr/local/pgsql/data/postgresql.conf

    effective_cache_size = 512MB
・インデックススキャン高速化

値を大きくするとシーケンススキャンが採用されやすくなり、

値を小さくするとインデックススキャンが採用され安くなる。

[root]# vim /usr/local/pgsql/data/postgresql.conf

    random_page_cost = 3.0
・データベースエンジンが使用する、共有メモリ内のバッファ領域

[root]# sysctl -a | grep kernel.shm

    kernel.shmmax = 33554432(32MB) # 共有メモリのバイト単位の大きさの上限
    kernel.shmall = 2097152          # システム全体の共有メモリ・ページの最大数
    kernel.shmmni = 4096             # 共有メモリのバイト単位の大きさの下限

[root]# cat /proc/sys/kernel/shmmax

    33554432(32MB)

[root]# vim /etc/sysctl.conf

    kernel.shmmax = 1073741824(1GB)
    kernel.shmall = 67108864

[root]# sysctl -p

[root]# vim /usr/local/pgsql/data/postgresql.conf

    shared_buffers = 256MB

[root]# systemctl reload-or-restart postgresql

[root]# service postgresql reload

VACUUM

    update 時、新しいレコードに内容を移動。delete 時、削除マークを付けるだけ。
   で共に古いレコードは削除されない。
    VACCUM はデータベースの不要領域の回収、およびデータベースの解析を行う。
・不要領域を回収し、最利用可能な状態に変更

[user]$ vacuumdb -v -a(全データベース)-U postgres

[user]$ vacuumdb -v データベース名 [-t デーブル名] -U 所有者

DB=> vacuum verbose [テーブル名];

・統計情報を更新し、問い合わせを最適化

[user]$ vacuumdb -v -z -a(全データベース)-U postgres

[user]$ vacuumdb -v -z データベース名 [-t テーブル名] -U 所有者

DB=> vacuum verbose analyze [テーブル名];

・テーブルの排他的ロックを行う、完全なバキューム

テーブル内の全ての行を削除した場合のみ実行する

[user]$ vacuumdb -v -f データベース名 [-t テーブル名] -U 所有者

DB=> vacuum verbose full [テーブル名];

・自動VACUUM

最後のVACUUMのあとに発生した不要領域の数が閾値を超えるとVACUUMが実行される

閾値 = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * テーブルの行数

    [root]# vim /usr/local/pgsql/data/postgresql.conf

    autovacuum = on
    autovacuum_vacuum_threshold = 50
    autovacuum_vacuum_scale_factor = 0.2