数据库性能

以下所有命令，请在用psql登录数据库后执行。

查看会话状态

SELECT datname,
usename,
pid,
client_hostname,
backend_start,
query_start,
wait_event_type,
STATE,
backend_type
FROM pg_stat_activity;

查看锁的信息

select * from pg_locks;

查看 blocker

SELECT datname,
pid,
usename,
client_hostname,
wait_event,
STATE,
query
FROM pg_stat_activity
WHERE pid IN
(SELECT pid
FROM pg_locks
WHERE GRANTED<>'f'
AND transactionid =
(SELECT transactionid
FROM pg_locks
WHERE GRANTED= 'f'));

查看膨胀高的 top 10 表

SELECT relname AS TABLE_NAME,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,
n_dead_tup,
n_live_tup,
(n_dead_tup * 100 (n_live_tup + n_dead_tup))AS
dead_tup_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup<>0 LIMIT 10;

查看当前数据库表的年龄

SELECT Current_database(),
nspname,
CASE relkind
WHEN $$r$$ THEN$$ordinary table$$
WHEN $$t$$ THEN $$toast table$$
END AS relkind,
relname,
age(relfrozenxid),
CASE
WHEN (
substring(reloptions::text,
$$autovacuum_freeze_max_age=(\d+)$$
)::int8) IS NOT NULL THEN
(substring(reloptions::text,
$$autovacuum_freeze_max_age=(\d+)$
$)::int8)-age(relfrozenxid)
ELSE (
SELECT setting

FROM pg_settings

WHERE

name=$$autovacuum_freeze_max_age$$)::int8 - age(relfrozenxid)

END AS age_remain

FROM pg_class t2

join pg_namespace t3

ON t2.relnamespace=t3.oid

WHERE t2.relkind IN ($$t$$,$$r$$)

and nspname not in

('pg_catalog','pg_toast','information_schema')

ORDER BY age(relfrozenxid) DESC;

freeze 表的年龄

vacuum freeze test;

查看数据库长事务

SELECT extract(epoch
FROM (clock_timestamp() - xact_start)) AS
longtrans,
extract(epoch
FROM (clock_timestamp() - query_start)) AS
longquery
FROM pg_stat_activity
WHERE 1=1
AND STATE <> 'idle';