PostgreSQL怎么用系统表来分析postgresql的问题

这篇文章将为大家详细讲解有关PostgreSQL怎么用系统表来分析postgresql的问题，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

数据库中本身的系统表提供了对外展示当前数据库状态的作用，其中这些系统表可以监控系统的状态，查询执行计划的状态，以及作为服务器管理状态显示的一部分。

对于任何的数据库理解和巧妙的使用这些系统表都很重要。

一般来说如果客户开始抱怨你的应用使用的postgresql 反映缓慢，或者你自己发现部分查询反馈的时间已经很慢，已经肉眼可查的时候，该怎么做。

1  查看cache hit ratio  这个东西其实放到其他数据库也是一样，如果你的内存对于系统的缓冲支持不足，需要的数据无法驻留在内存，经常会产生 fault page (有些数据库对于读取的数据不在内存中的一种叫法)， 那就必须要要查看你的一个系统参数 cache hit ratio ，大部分建议最低不要低于95%，如果达到99% 才是一个令人满意的数字。

不同的在于每种数据库对于查询的方便些和便捷性，从我掌握的数据库来说，PG获取 cache hit ratio的方法比较简单。

select  sum(heap_blks_read) as heap_read,

        sum(heap_blks_hit) as heap_hit,

sum(heap_blks_hit) /(sum(heap_blks_hit) + sum(heap_blks_read)) as ratio 

from pg_statio_user_tables;

其实研究一下 pg_statio_uer_tables 这张表，可以很容易发现通过pg_statio_user_tables 这张表可以变化出多种系统的指标参数。

而实际上这个pg_statio_user_tables 是一个view 从 pg_statio_all_tables 中变化而成的

 SELECT pg_statio_all_tables.relid,

    pg_statio_all_tables.schemaname,

    pg_statio_all_tables.relname,

    pg_statio_all_tables.heap_blks_read,

    pg_statio_all_tables.heap_blks_hit,

    pg_statio_all_tables.idx_blks_read,

    pg_statio_all_tables.idx_blks_hit,

    pg_statio_all_tables.toast_blks_read,

    pg_statio_all_tables.toast_blks_hit,

    pg_statio_all_tables.tidx_blks_read,

    pg_statio_all_tables.tidx_blks_hit

   FROM pg_statio_all_tables

  WHERE (pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND pg_statio_all_tables.schemaname !~ '^pg_toast'::text;

而什么会引起 cache hit ratio 比较低的问题

1 设计的表中存储了比较大的字段或者存储其他方式的不适合存储在传统数据库的数据，例如大型的图片，或者大量的文字，并且经常调用

2 由于vacuum 的问题，dead tuple 没有及时被清理，

3 查询并未被优化，大量的走了 sequential scans 的方式

4 你缺乏足够的内存来进行目前面对的查询活动

那么接下来的问题如果从找寻到底哪个表可能会存在问题的角度入手，可以马上先看一下

2  pg_stat_database 这个系统表，这样表可以很清楚的给出如下信息

1  单独每个数据库产生的事务多少

2  回滚事务有多少，（从这点就可以看出某些问题）

3  整体数据库的读写比 , tup_fetched 与 tup_inserted, tup_updated, tup_deleted 和的比率

4   查询数据回馈与实际数据的搜索的比率，也就是查找多少数据返回的行数与对应到底数据库检索了多少行 tup_fetched / tup_returned

5  是否数据库有死锁

等等以上信息。应该可以确认至少那个数据库是 热的，或者对比历史同期数据指标，指标不大对，那就可以继续针对这个数据库进行问题的查找.

在确认了数据库后，下一步就可以开始针对这个数据库的表进行问题的确认了。

3 pg_stat_all_tables

select * from pg_stat_all_tables where relname not like 'pg%' and relname not like 'sql%';

通过pg_stat_all_tables 可以将当前数据库中的表进行一个梳理，例如某个表的数据的 insert ,update del ,以及查询中使用的到的,以及查询的比率，还有了解到一个表最后一次 autovacuum的时间，等等有用的信息，尤其可以通过n_dead_tup     这个参数的跟踪，得到某个表是否有事务没有commit 制造了大量的 dead_tup 或者长事务，造成某个时间段的 dead_tup急剧上升等等，问题。

然后我们在得到这些证据后，就可以将其report 给相关的开发人员，并且通过 POSTGRESQL 的慢查询来进一步确认某些设计的问题，或者语句缺少索引的问题。

关于“PostgreSQL怎么用系统表来分析postgresql的问题”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。