PostgreSQL 数据库监控常用命令

最新推荐文章于 2024-07-10 15:16:06 发布

weixin_33725722

最新推荐文章于 2024-07-10 15:16:06 发布

阅读量297

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/innovation/blog/318749

版权

2019独角兽企业重金招聘Python工程师标准>>>

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;

转载于:https://my.oschina.net/innovation/blog/318749