# Postgresql性能瓶颈排查
# 慢查询管理
-- 查看当前长时间执行，一直不结束的SQL
select datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10), ' ') as query from (select pgsa.datname as datname, pgsa.usename as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, pgsa.state as state, pgsa.backend_start as backend_start, pgsa.xact_start as xact_start, extract(epoch from (now() - pgsa.xact_start)) as xact_stay, pgsa.query_start as query_start, extract(epoch from (now() - pgsa.query_start)) as query_stay , pgsa.query as query from pg_stat_activity as pgsa where pgsa.state != 'idle' and pgsa.state != 'idle in transaction' and pgsa.state != 'idle in transaction (aborted)') idleconnections order by query_stay desc limit 5;
1
2
2
-- 查询扫描最多的表
select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;
1
2
2
-- 查询当前正在运行的访问到上述表的慢查询
select * from pg_stat_activity where query ilike '%pv_variable_107_value%' and query_start - now() > interval '10 seconds';
1
2
2
-- 上面的方法查出来的慢SQL，首先需要做的可能是Cancel或Kill掉他们，使业务先恢复
select pg_cancel_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();
select pg_terminate_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();
1
2
3
2
3
# 连接管理
-- 查看当前数据库连接信息
select pid,client_addr,application_name,backend_start,state,now() - xact_start as 事务时间,
now() - query_start as 查询时间,query from pg_stat_activity
where datname='数据库名称' order by client_addr,application_name;
1
2
3
4
2
3
4
-- 结束指定数据库连接（指定pid）
SELECT pg_terminate_backend(pid);
1
2
2
-- 杀死所有数据库连接
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
WHERE datname='数据库名称' AND pid<>pg_backend_pid();
1
2
3
2
3
-- 通过top cpu飙高的进程定位pg操作
SELECT procpid, START, now() - START AS lap, current_query
FROM ( SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid,
pg_stat_get_backend_activity_start (S.backendid) AS START,pg_stat_get_backend_activity (S.backendid) AS current_query FROM (SELECT
pg_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> '<IDLE>' and procpid=进程ID ORDER BY lap DESC;
1
2
3
4
5
2
3
4
5
# 磁盘管理
-- 数据库大小
select pg_size_pretty(pg_database_size('数据名'));
1
2
2
-- 查询当前database，每张表占用磁盘空间并降序排列
SELECT
table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;
1
2
3
4
5
6
7
2
3
4
5
6
7
-- 表磁盘位置
select* from pg_relation_filepath('oc0_chart_type');
1
2
2
# 数据库锁管理
-- 查询数据表是否被锁
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname
from pg_locks a join pg_class b on a.relation = b.oid where upper(b.relname) = '表名';
1
2
3
2
3
-- 再根据上面查出来的pid去表pg_stat_activity查询一下该锁对应的SQL语句：
select usename，current_query ，query_start，procpid，client_addr from pg_stat_activity where procpid=17509;
1
2
2
-- 结束该进程
select pg_cancel_backend(17509);
1
2
2
# 设置自增长从当前最大值开始
select setval('tablename_id_seq',(select max(id) from tablename))
1
# 重置自增序列
# 重置t_user_id_seq 从1开始
ALTER SEQUENCE t_user_id_seq restart with 1;
1
2
2