postgresql 查询缓慢原因分析

pg_stat_activity

最近发现系统运行缓慢，查询数据老是超时，于是排查下pg_stat_activity 系统表，看看有没有耗时的查询sql

SELECT pid, state, query, query_start, backend_type FROM pg_stat_activity 
WHERE state = 'active' AND query LIKE '%SELECT%'
--and now()-query_start > interval '1 s'
and query_start > now() - interval '5 minutes' order by query_start;

非空闲的：<> 表示不等于，idle表示空闲的

state <> 'idle'

非空闲的，也可以理解为活跃的状态

state = 'active'

如果有非常耗时的sql，就试着执行下Explain解释语句，分析下sql耗时原因

复杂 pg_stat_activity

SELECT
	pid,
	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.pid AS pid,
		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

看一下xact_stay这个值是不是很大，很大的话就看看或者Explain下query里面的sql语句

解释语句

EXPLAIN (ANALYZE) <your_query>;

pg_locks

去pg_locks和pg_stat_activity两张表里关联查询是不是有锁（关联条件是pid和transcationId），排除下是不是锁的原因，如果有锁，就把锁进程kill掉试试

shared_buffers

查询数据库参数有没有问题

show shared_buffers

vacuum

收缩表，清理无用数据

vacuum full verbose table_name
vacuum full verbose analyze table_name