PostgreSQL 接続数を確認

PostgreSQL 接続数を確認する

SELECT * FROM pg_stat_activity

SELECT count(*) FROM pg_stat_activity

同時接続数の設定値

show  max_connections;

---

アクセス頻度の高いテーブル

select

    relname,

    coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0)+

    coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)+coalesce(n_tup_del,0) as total,

    coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0) as select,

    coalesce(n_tup_ins,0) as insert,

    coalesce(n_tup_upd,0) as update,

    coalesce(n_tup_del,0) as delete

from pg_stat_user_tables

order by total desc;

---

I/O＆キャッシュヒット率

select

    *,

    (heap_blks_hit*100) / (heap_blks_read+heap_blks_hit) as ritu

  from pg_statio_all_tables

 where heap_blks_hit >= 1

   and schemaname = 'public' order by ritu desc;

---

インデックス利用回数

select * from pg_stat_user_indexes ;

---

統計情報をクリア

select pg_stat_reset();

---

select datname from pg_database;

select * from pg_database;

---

PostgreSQLが遅い原因を今すぐ監視して調査する方法

https://www.manageengine.jp/products/Applications_Manager/postgresql-monitoring.html

---

ログ

postgresql.conf  の　logging_collectorの値がoffの場合はonに変更

/var/lib/psql/12/data/log

---