前端之家收集整理的这篇文章主要介绍了
postgresql 性能问题诊断总结，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
--如果系统出现访问缓慢，首先可以通过zabbix查看系统中的数据库连接数，cpu使用率，内存使用率，swap使用率，以及系统io吞吐是不是有明显的抖动
--如果数据库连接数突增，可能是系统访问量突然增大，更有可能是数据库执行一个或多个sql，造成资源争用，数据库处理速度跟不上向数据库发送请求的速度
--如果cpu空增，内存变动不大，可能是数据库在进行大量的计算，比如sql的聚合操作
--如果内存使用率增大，swap使用率增大，并伴随系统中io突增，可能是大表的连接，大表的count，内存中放不少数据，系统不得不进行内存与磁盘的转换（io的read），如果io的write很高，可能是再大表创建索引
--使用dstat进行实时分析
dstat -cdmgnsyr --lock --ipc --top-cpu --top-io --top-mem --proc-count
--将文件从磁盘的src位置拷贝到磁盘的dst位置。文件会从src先读取进入到内核空间，然后再读取到用户空间，然后拷贝数据到用户空间的buf上，再通过用户空间，内核空间，数据才到磁盘的dst上。
--cpu消耗在kernel space的时候就是sy（系统态使用的cpu百分比），cpu消耗在user space的时候就是us（用户态使用的cpu百分比）。
--硬中断 hi，cpu在执行程序的时候，突然外设硬件（比如硬盘出现问题了）机器需要立刻通知cpu进行现场保存工作。这个时候会cpu会出现上下文切换。
--就是cpu会有一部分时间会被硬中断占用了，这个时间就是hi。相类似，si是软中断的cpu占用时间，软中断是由软件的指令方式触发的
--ni每个linux进程都有个优先级，优先级高的进程有优先执行的权利，这个叫做pri。进程除了优先级外，还有个优先级的修正值。
--即比如你原先的优先级是20，然后修正值为-2，那么你最后的进程优先级为18。这个修正值就叫做进程的nice值
--如下图所示，usr比率较高，说明系统在执行用户的SQL查询，write也比较高，说明有大量并发的写（可能是dml，也有可能是在写临时文件），
--read几乎为0，且free内丰较大，说明系统内存充足，数据已缓存至内存中，故不可能为临时文件写而为dml写
----total-cpu-usage---- -dsk/total- ------memory-usage----- ---paging-- -net/total- ----swap--- ---system-- --io/total- ---file-locks-- --sysv-ipc- -most-expensive- ----most-expensive---- --most-expensive- proc
usr sys idl wai hiq siq| read writ| used buff cach free| in out | recv send| used free| int csw | read writ|pos lck rea wri|msg sem shm| cpu process | i/o process | memory process |tota
95 3 2 0 0 0| 0 40k|3809M 184M 23.5G 43.4G| 0 0 |1519k 13k| 0 32G| 28k 37k| 0 6.00 |5.0 3.0 0 8.0| 0 78 3|postgres: pos0.8|postgres 1005M 344k|postgres: ch2065M| 509
96 2 2 0 0 0| 0 40k|3800M 184M 23.5G 43.4G| 0 0 |1834k 13k| 0 32G| 29k 33k| 0 8.00 |5.0 3.0 0 8.0| 0 78 3|postgres: pos0.7|postgres 456M 171k|postgres: ch2065M| 508
96 2 1 0 0 0| 0 64k|3777M 184M 23.5G 43.4G| 0 0 |2329k 13k| 0 32G| 27k 30k| 0 13.0 |5.0 3.0 0 8.0| 0 78 3|postgres: pos0.8|postgres 1964M 684k|postgres: ch2065M| 504
95 3 2 0 0 0| 0 48k|3746M 184M 23.5G 43.4G| 0 0 |1595k 14k| 0 32G| 28k 32k| 0 12.0 |5.0 3.0 0 8.0| 0 78 3|postgres: pos0.7|postgres 2235M 861k|postgres: ch2065M| 498
--如下图所示，sys比率较高，说明系统繁忙，write也比较高，说明有并发的写（可能是dml，也有可能是在写临时文件），
--read也很高，且memory中free内存较少，cache 也较少 说明内存不足,paging，swap 很大，说明由于内存不足，造成数据页的换入与换出,故此可能存在大量的磁盘文件写
----total-cpu-usage---- -dsk/total- ------memory-usage----- ---paging-- -net/total- ----swap--- ---system-- --io/total- ---file-locks-- --sysv-ipc- -most-expensive- ----most-expensive---- --most-expensive-
usr sys idl wai hiq siq| read writ| used buff cach free| in out | recv send| used free| int csw | read writ|pos lck rea wri|msg sem shm| cpu process | i/o process | memory process
20 79 0 0 0 1| 28M 876M|5793M 29.4M 1949M 101M| 21M 13M| 45k 71k|1105M 2927M| 135k 50k|1202 44.1k|3.0 0 0 3.0| 0 68 1|flush-8:32 13|postgres: p 0 5200k|postgres: po1016M missed 30 ticks
30 68 0 0 0 1| 11M 371M|5807M 29.8M 1934M 102M|8816k 0 | 25k 31k|1102M 2930M| 44k 19k| 527 13.3k|3.0 0 0 3.0| 0 68 1|postgres: pos5.8|postgres: p 0 5720k|postgres: po1017M missed 9 ticks
17 82 0 0 0 1| 17M 719M|5802M 30.4M 1940M 101M| 12M 31M| 38k 15k|1128M 2904M| 133k 43k| 747 46.3k|3.0 0 0 3.0| 0 68 1|postgres: pos 32|postgres: p 0 6480k|postgres: po 960M missed 30 ticks
--如果usr所占比率较高，说明系统中有大量的数据库进程在运行，且一直在运行，没有等待
--如果sys所占比率较高，说明系统现在繁忙，可能有大量的数据库进程在等待cpu进行内存与磁盘文件的读入与读出，可查看paging和swap应该比率较高
--如果read write比率较高，说明系统中有大量的数据库进程在运行，其可能在执行排序操作，由于内存不足，需要写到临时磁盘文件上，也有可能数据库并发量增大有大量的dml操作
--如果cache 和 free 之后很少，说明内存不足，不得不使用swap空间
--查看数据库临时表空间的大小，如果其在一直增大或者有上G的数据，说明数据库已经不能在内存中完成sql的排序或hash，不得不把中间结果写到硬盘中
du -sh %PGDATA/base/pgsql_tmp/
--查看数据库临时表空间位置
--temp_tablespaces是表空间名称的列表，当列表中有一个以上名称时， Postgresql 每次临时对象被创建时选择一个列表中的随机数；
--除了在一个事务中之外，先后创建临时对象放置在列表连续的表空间中。
--如果列表中选定的元素是一个空字符串， Postgresql 会自动使用当前数据库的缺省表空间
select * from pg_settings where name like '%tablespace%';
--查看数据库中缓存数据的大小，正常情况下每个表的缓存大小应该是不变的，如果某几个表突然增大，可能它们被突然访问引起的，如果它们的来回变换大小，说明数据库内存已不足，内存与磁盘文件之间不得不进行交换
SELECT c.relname,pg_size_pretty(count(*) * 8192) AS buffered,round(100.0 * count(*) /
(SELECT setting
FROM pg_settings
WHERE name='shared_buffers')::integer,1) AS buffers_percent,round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1) AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase = d.oid
AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY count(*) DESC LIMIT 30;
