2020年07月04日

PostgreSQLでシーケンスの一覧を表示


SELECT c.relname FROM pg_class c LEFT join pg_user u ON c.relowner = u.usesysid WHERE c.relkind = 'S';
posted by a23 at 14:35| Comment(0) | PostgreSQL

pgAdmin4 32bit Windows10


---------------------------
Error
---------------------------
Runtime Error (at 12:609):

Internal error: Cannot access 64-bit registry keys on this version of Windows.
---------------------------
OK   
---------------------------
err1.png



posted by a23 at 14:20| Comment(0) | PostgreSQL

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が遅い原因を今すぐ監視して調査する方法




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

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









posted by a23 at 09:31| Comment(0) | PostgreSQL