postgresql 性能问题诊断总结

04/25 10:44
阅读数 0


--如果系统出现访问缓慢,首先可以通过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;

————————————————
版权声明:本文为CSDN博主「rudy_gao」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/rudygao/article/details/50352126

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部