pg_top + idle进程分析

1. Install Howto

Download latest epel-release rpm from
http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/
Install epel-release rpm:
# rpm -Uvh epel-release*rpm
Install pg_top rpm package:
# yum install pg_top

参考：https://cloud.tencent.com/developer/article/1612270  https://centos.pkgs.org/7/epel-x86_64/pg_top-3.7.0-5.el7.x86_64.rpm.html

2. pg_top查看sql

按Q 键，然后输入你的线程号，就捕捉上图显示 4221 线程当前执行的命令

按L 键，然后输入你的线程号，则可以捕捉你当前执行命令所正在霸占得锁信息

 

Useful Interactive Features

There are a handful of interactive features in pg_top that can be accessed while it is running. A full list can be found by entering a ?, which will bring up a help screen with all the different options available.
Planner Information

E - Execution Plan
Entering E will provide a prompt for a process ID for which to show an explain plan. This is equivalent to running “EXPLAIN <QUERY>;” in the database manually.

A - EXPLAIN ANALYZE (UPDATE/DELETE safe)
Entering A will provide a prompt for a process ID for which to show an EXPLAIN ANALYZE plan. This is equivalent to running “EXPLAIN ANALYZE <QUERY>;” in the database manually.

Process Information

Q - Show current query of a process
Entering Q will provide a prompt for a process ID for which to show the full query.

I - Shows I/O statistics per process (Linux only)
Entering I switches the process list to an I/O display, showing each process reads, writes, etc to disk.

L - Shows locks held by a process
Entering L will provide a prompt for a process ID for which to show held locks. This will include the database, the table, the type of lock, and whether or not the lock has been granted. Useful for when exploring long running or waiting processes.

 

 

 

3. pg的idle进程分析

idle连接，最后查找问题，是代码的问题，NpgsqlDataReader没有关闭，形成了很多的空闲连接，连接npgsql后需要关闭，可以有效的释放连接；