PostgreSql Postgresql 监控你说了不算，谁说了算 ? (5 整理的一些脚本)

一写就写到了第五期，有点写连续剧的味道，可能会有第六期，我想是，今天的内容并不是某些工具，其实工具也是根据数据库的原理，通过各种方式获得数据。那怎么通过PG中的系统表来获得数据就是这期的重点。

当然只给脚本，那就有点LOW ，首先要告诉读者，这个到底要做什么能给你什么信息，解决什么问题。

以下的脚本均在PG11中使用，或验证。

1 内存命中 cache hit

关注这个点是因为，你系统中正在运行的表，在查询中内存的命中率，主要考虑这个点要考虑 1 内存是否存在短缺的可能， 2 你的查询的方式是否合理，（说白了就是你读取这个表的SQL 是否有垃圾的可能），一软一硬。

SELECT
	'index hit rate' AS name,
	(sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio
	FROM pg_statio_user_indexes
	UNION ALL
	SELECT
	'table hit rate' AS name,
	sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio
	FROM pg_statio_user_tables;

2 关于表中的INDEX 的命中率

SELECT relname,
	CASE idx_scan
	WHEN 0 THEN NULL
	ELSE round(100.0 * idx_scan / (seq_scan + idx_scan), 5)
	END percent_of_times_index_used,
	n_live_tup rows_in_table
	FROM
	pg_stat_user_tables
	ORDER BY
	n_live_tup DESC;

在查询中基本上都愿意使用INDEX 来进行相关的查询，那表中的查询使用INDEX 索引和不使用之间的时间比是多少，通过这样的脚本可以进一步分析哪些表可能存在缺少搜索的情况。

3  检查数据库中那些索引没有被使用过，这是一个经常需要问的问题，当然通过脚本获取的数据后，到底这个索引需要不需要，也是要在分析的，不能由于这个索引被使用的次数过小，就直接将他删除。

SELECT
	schemaname || '.' || relname AS table,
	indexrelname AS index,
	pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
	idx_scan as index_scans
	FROM pg_stat_user_indexes ui
	JOIN pg_index i ON ui.indexrelid = i.indexrelid
	WHERE NOT indisunique
	AND idx_scan < 50
	AND pg_relation_size(relid) > 5 * 8192
	ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
	pg_relation_size(i.indexrelid) DESC;

4 一个表的大小，在PG中对于字符的字段是有一个toast 的概念的，要关注toast在每个表中占有多大的空间，可以通过下面的脚本来进行查看

SELECT c.relname AS name,
	pg_size_pretty(pg_total_relation_size(c.oid)) AS size
	FROM pg_class c
	LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
	WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
	AND n.nspname !~ '^pg_toast'
	AND c.relkind='r'
	ORDER BY pg_total_relation_size(c.oid) DESC;

5 查询当前系统中语句的状态，包含锁的状态，这个语句可能是会经常被使用的，如果当前系统例如出现性能，或应用系统的问题，首先就要查看当前语句运行的情况。

SELECT count(pg_stat_activity.pid) AS number_of_queries,
       substring(trim(LEADING
                      FROM regexp_replace(pg_stat_activity.query, '[\n\r]+'::text,
                       ' '::text, 'g'::text))
                 FROM 0
                 FOR 200) AS query_name,
       max(age(CURRENT_TIMESTAMP, query_start)) AS max_wait_time,
       wait_event,
       usename,
       locktype,
       mode,
       granted
  FROM pg_stat_activity
  LEFT JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid
  WHERE query != '<IDLE>'
    AND query NOT ILIKE '%pg_%' AND query NOT ILIKE '%application_name%' AND query NOT ILIKE '%inet%'
    AND age(CURRENT_TIMESTAMP, query_start) > '5 milliseconds'::interval
  GROUP BY query_name,
           wait_event,
           usename,
           locktype,
           mode,
           granted
  ORDER BY max_wait_time DESC;

6 在查询中表读取在内存中的命中的数据块是一个需要被关注的参数，下面的脚本中可以看到每个表被读取时，在磁盘中读取和在内存中直接读取之间的数字和比率。

SELECT relname AS "relation",
       heap_blks_read AS heap_read,
       heap_blks_hit AS heap_hit,
       ( (heap_blks_hit*100) / NULLIF((heap_blks_hit + heap_blks_read), 0)) AS ratio
FROM pg_statio_user_tables;

7 表膨胀的问题是PG中需要关注和注意的，所以经常监控膨胀率是一个很重要的问题，通过下面的脚本

WITH constants AS (
	SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma
	), bloat_info AS (
	SELECT
	ma,bs,schemaname,tablename,
	(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
	(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
	FROM (
	SELECT
	schemaname, tablename, hdr, ma, bs,
	SUM((1-null_frac)*avg_width) AS datawidth,
	MAX(null_frac) AS maxfracsum,
	hdr+(
	SELECT 1+count(*)/8
	FROM pg_stats s2
	WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
	) AS nullhdr
	FROM pg_stats s, constants
	GROUP BY 1,2,3,4,5
	) AS foo
	), table_bloat AS (
	SELECT
	schemaname, tablename, cc.relpages, bs,
	CEIL((cc.reltuples*((datahdr+ma-
	(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
	FROM bloat_info
	JOIN pg_class cc ON cc.relname = bloat_info.tablename
	JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
	), index_bloat AS (
	SELECT
	schemaname, tablename, bs,
	COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
	COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
	FROM bloat_info
	JOIN pg_class cc ON cc.relname = bloat_info.tablename
	JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
	JOIN pg_index i ON indrelid = cc.oid
	JOIN pg_class c2 ON c2.oid = i.indexrelid
	)
	SELECT
	type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste
	FROM
	(SELECT
	'table' as type,
	schemaname,
	tablename as object_name,
	ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat,
	CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste
	FROM
	table_bloat
	UNION
	SELECT
	'index' as type,
	schemaname,
	tablename || '::' || iname as object_name,
	ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat,
	CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste
	FROM
	index_bloat) bloat_summary
	ORDER BY raw_waste DESC, bloat DESC;

8 在PG 中一个数据块系统中有没有进行autovacuum 什么时候做的，最后一次分析是什么时间，等等都是重要的信息，一个系统的管理或者DBA是需要知晓这些事情，并根据这些信息来进行后续的操作等等。

WITH table_opts AS (
	SELECT
	pg_class.oid, relname, nspname, array_to_string(reloptions, '') AS relopts
	FROM
	pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid
	), vacuum_settings AS (
	SELECT
	oid, relname, nspname,
	CASE
	WHEN relopts LIKE '%autovacuum_analyze_threshold%'
	THEN substring(relopts, '.*autovacuum_analyze_threshold=([0-9.]+).*')::integer
	ELSE current_setting('autovacuum_analyze_threshold')::integer
	END AS autovacuum_analyze_threshold,
	CASE
	WHEN relopts LIKE '%autovacuum_analyze_scale_factor%'
	THEN substring(relopts, '.*autovacuum_analyze_scale_factor=([0-9.]+).*')::real
	ELSE current_setting('autovacuum_analyze_scale_factor')::real
	END AS autovacuum_analyze_scale_factor
	FROM
	table_opts
	)
	SELECT
	vacuum_settings.relname AS table,
	to_char(psut.last_analyze, 'YYYY-MM-DD HH24:MI') AS last_analyze,
	to_char(psut.last_autoanalyze, 'YYYY-MM-DD HH24:MI') AS last_autoanalyze,
	to_char(pg_class.reltuples, '9G999G999G999') AS rowcount,
	to_char(pg_class.reltuples / NULLIF(pg_class.relpages, 0), '999G999.99') AS rows_per_page,
	to_char(autovacuum_analyze_threshold
	+ (autovacuum_analyze_scale_factor::numeric * pg_class.reltuples), '9G999G999G999') AS autovacuum_analyze_threshold,
	CASE
	WHEN autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup
	THEN 'yes'
	END AS will_analyze
	FROM
	pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid
	INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid
	ORDER BY 1

OK 今天就先说到这里