PostgreSQL 数据库监控常用命令

SELECT
((select setting from pg_settings where name = 'block_size')::decimal * 
    (select setting from pg_settings where name = 'shared_buffers')::decimal 
         / (1024 * 1024))::decimal(10,4) AS shared_buffers_mb,((select setting from pg_settings where name = 'block_size')::decimal * 
    (select setting from pg_settings where name = 'temp_buffers')::decimal 
        / (1024 * 1024))::decimal(10,4) AS temp_buffers_mb,((select setting from pg_settings where name = 'block_size')::decimal * 
    (select setting from pg_settings where name = 'effective_cache_size')::decimal 
        / (1024 * 1024))::decimal(10,4) AS effective_cache_size_mb,((select setting from pg_settings where name = 'block_size')::decimal * 
    (select setting from pg_settings where name = 'segment_size')::decimal 
        / (1024 * 1024))::decimal(10,4) AS segment_size_mb,((select setting from pg_settings where name = 'block_size')::decimal * 
    (select setting from pg_settings where name = 'wal_segment_size')::decimal 
        / (1024 * 1024))::decimal(10,4) AS wal_segment_size_mb,((select setting from pg_settings where name = 'block_size')::decimal * 
    (select setting from pg_settings where name = 'wal_buffers')::decimal 
        / (1024 * 1024))::decimal(10,4) AS wal_buffers_mb,((SELECT setting FROM pg_settings WHERE name = 'work_mem')::decimal 
        / 1024)::decimal(10,4) AS work_mem_mb

以下SQL postgresql高版本中 current_query为query

SELECT
	SUM(d1.numbackends) AS num_backends,SUM((SELECT COALESCE(count(current_query)::bigint,0::bigint) 
	      FROM pg_catalog.pg_stat_activity WHERE datname = d1.datname 
                                                 AND current_query = '<IDLE>')) AS idle_backends,SUM(d1.xact_commit) AS xact_commit,SUM(d1.xact_rollback) AS xact_rollback,SUM(d1.blks_hit) AS blks_hit,SUM(d1.blks_read) AS blks_read,SUM(d1.tup_returned) AS tup_returned,SUM(d1.tup_fetched) AS tup_fetched,SUM(d1.tup_inserted) AS tup_inserted,SUM(d1.tup_updated) AS tup_updated,SUM(d1.tup_deleted) AS tup_deleted,(CASE WHEN SUM(d1.blks_hit) + SUM(d1.blks_read) = 0 THEN 0 
	      ELSE SUM(blks_hit) * 100 / (SUM(blks_hit) + 
                   SUM(blks_read)) END)::numeric(30,2) AS hit_ratio
FROM
	pg_catalog.pg_stat_database d1

统计数据库集簇 的大小信息

SELECT datname AS dbname,pg_database_size(a.oid) / 1048576 
     AS dbsize_mb FROM 
        pg_catalog.pg_database a,pg_catalog.pg_tablespace b 
     WHERE a.dattablespace = b.oid

统计数据库表空间的大小信息：

SELECT spcname AS tblspcname,pg_catalog.pg_tablespace_size(oid) / 1048576 AS tblspcsize_mb 
FROM pg_catalog.pg_tablespace

统计数据库磁盘写信息

SELECT checkpoints_timed,checkpoints_req,buffers_clean,buffers_checkpoint,maxwritten_clean,buffers_backend,buffers_alloc 
FROM pg_catalog.pg_stat_bgwriter

SELECT
	COUNT(CASE WHEN mode = 'AccessShareLock' AND granted THEN 1 ELSE NULL END) AS access_share_lock_granted,COUNT(CASE WHEN mode = 'RowShareLock' AND granted THEN 1 ELSE NULL END) AS row_share_lock_granted,COUNT(CASE WHEN mode = 'RowExclusiveLock' AND granted THEN 1 ELSE NULL END) AS row_exclusive_lock_granted,COUNT(CASE WHEN mode = 'ShareUpdateExclusiveLock' AND granted THEN 1 ELSE NULL END) AS share_update_exclusive_lock_granted,COUNT(CASE WHEN mode = 'ShareLock' AND granted THEN 1 ELSE NULL END) AS share_lock_granted,COUNT(CASE WHEN mode = 'ShareRowExclusiveLock' AND granted THEN 1 ELSE NULL END) AS share_row_exclusive_lock_granted,COUNT(CASE WHEN mode = 'ExclusiveLock' AND granted THEN 1 ELSE NULL END) AS exclusive_lock_granted,COUNT(CASE WHEN mode = 'AccessExclusiveLock' AND granted THEN 1 ELSE NULL END) AS access_exclusive_lock_granted,COUNT(CASE WHEN mode = 'AccessShareLock' AND NOT granted THEN 1 ELSE NULL END) AS access_share_lock_waiting,COUNT(CASE WHEN mode = 'RowShareLock' AND NOT granted THEN 1 ELSE NULL END) AS row_share_lock_waiting,COUNT(CASE WHEN mode = 'RowExclusiveLock' AND NOT granted THEN 1 ELSE NULL END) AS row_exclusive_lock_waiting,COUNT(CASE WHEN mode = 'ShareUpdateExclusiveLock' AND NOT granted THEN 1 ELSE NULL END) AS share_update_exclusive_lock_waiting,COUNT(CASE WHEN mode = 'ShareLock' AND NOT granted THEN 1 ELSE NULL END) AS share_lock_waiting,COUNT(CASE WHEN mode = 'ShareRowExclusiveLock' AND NOT granted THEN 1 ELSE NULL END) AS share_row_exclusive_lock_waiting,COUNT(CASE WHEN mode = 'ExclusiveLock' AND NOT granted THEN 1 ELSE NULL END) AS exclusive_lock_waiting,COUNT(CASE WHEN mode = 'AccessExclusiveLock' AND NOT granted THEN 1 ELSE NULL END) AS access_exclusive_lock_waiting,COUNT(CASE WHEN locktype = 'relation' AND granted THEN 1 ELSE NULL END) AS relation_type_granted,COUNT(CASE WHEN locktype = 'extend' AND granted THEN 1 ELSE NULL END) AS extend_type_granted,COUNT(CASE WHEN locktype = 'page' AND granted THEN 1 ELSE NULL END) AS page_type_granted,COUNT(CASE WHEN locktype = 'tuple' AND granted THEN 1 ELSE NULL END) AS tuple_type_granted,COUNT(CASE WHEN locktype = 'transactionid' AND granted THEN 1 ELSE NULL END) AS transactionid_type_granted,COUNT(CASE WHEN locktype = 'virtualxid' AND granted THEN 1 ELSE NULL END) AS virtualxid_type_granted,COUNT(CASE WHEN locktype = 'object' AND granted THEN 1 ELSE NULL END) AS object_type_granted,COUNT(CASE WHEN locktype = 'advisory' AND granted THEN 1 ELSE NULL END) AS advisory_type_granted,COUNT(CASE WHEN locktype = 'relation' AND NOT granted THEN 1 ELSE NULL END) AS relation_type_waiting,COUNT(CASE WHEN locktype = 'extend' AND NOT granted THEN 1 ELSE NULL END) AS extend_type_waiting,COUNT(CASE WHEN locktype = 'page' AND NOT granted THEN 1 ELSE NULL END) AS page_type_waiting,COUNT(CASE WHEN locktype = 'tuple' AND NOT granted THEN 1 ELSE NULL END) AS tuple_type_waiting,COUNT(CASE WHEN locktype = 'transactionid' AND NOT granted THEN 1 ELSE NULL END) AS transactionid_type_waiting,COUNT(CASE WHEN locktype = 'virtualxid' AND NOT granted THEN 1 ELSE NULL END) AS virtualxid_type_waiting,COUNT(CASE WHEN locktype = 'object' AND NOT granted THEN 1 ELSE NULL END) AS object_type_waiting,COUNT(CASE WHEN locktype = 'advisory' AND NOT granted THEN 1 ELSE NULL END) AS advisory_type_waiting,COUNT(*) AS total_locks
FROM
	pg_locks;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!