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