一写就写到了第五期，有点写连续剧的味道，可能会有第六期，我想是，今天的内容并不是某些工具，其实工具也是根据数据库的原理，通过各种方式获得数据。那怎么通过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; |
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 ratioFROM 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 今天就先说到这里
文章转载自AustinDatabases，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
