postgresql 数据库 update更新慢的原因（已解决）

w1.pid as 等待进程, w1.mode as 等待锁模式, w2.usename as 等待用户, w2.query as 等待会话, b1.pid as 锁的进程, b1.mode 锁的锁模式, b2.usename as 锁的用户, b2.query as 锁的会话, b2.application_name 锁的应用, b2.client_addr 锁的IP地址, b2.query_start 锁的语句执行时间 from pg_locks w1 join pg_stat_activity w2 on w1.pid=w2.pid join pg_locks b1 on w1.transactionid=b1.transactionid and w1.pid!=b1.pid join pg_stat_activity b2 on b1.pid=b2.pid where not w1.granted;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid=‘62560‘

查询到有锁 把锁进程杀掉 重启服务 继续跟踪 发现5分钟后 又出现锁了 反复试了几次发现跟锁没有关系

3.查询参数

首先看的的 是shared_buffers 参数，发现也没有问题

4.收缩表 VACUUM

查询数据进程时，发现自动收缩 也执行10分钟还没好 就查询表收缩的情况

用于服务器监控，可查询进程，时间消耗与锁相关

SELECT 

C.relname 对象名称,
l.locktype 可锁对象的类型,
l.pid 进程id,
l.MODE 持有的锁模式,
l.GRANTED 是否已经对锁进行授权,
l.fastpath,
psa.datname 数据库名称,
psa.usesysid 用户id,
psa.usename 用户名称,
psa.application_name 应用程序名称,
psa.client_addr 连接的IP地址,
psa.client_port 连接使用的TCP端口号,
psa.backend_start 进程开始时间,
psa.xact_start 事务开始时间,
psa.query_start 事务执行此语句时间,
psa.state_change 事务状态改变时间,
psa.wait_event_type 等待事件类型,
psa.wait_event 等待事件,
psa.STATE 查询状态,

backend_xid 事务是否有写入操作,
backend_xmin 是否执事务快照,

psa.query 执行语句,
now( ) - query_start 持续时间

FROM

pg_locks l
INNER JOIN pg_stat_activity psa ON ( psa.pid = l.pid )
LEFT OUTER JOIN pg_class C ON ( l.relation = C.oid )
-- where l.relation = ‘tb_base_apparatus‘::regclass

where relkind =‘r‘
ORDER BY query_start asc

查询是否到达自动清理的表

SELECT
    c.relname 表名,
    (current_setting(‘autovacuum_analyze_threshold‘)::NUMERIC(12,4))+(current_setting(‘autovacuum_analyze_scale_factor‘)::NUMERIC(12,4))*reltuples AS 自动分析阈值,
    (current_setting(‘autovacuum_vacuum_threshold‘)::NUMERIC(12,4))+(current_setting(‘autovacuum_vacuum_scale_factor‘)::NUMERIC(12,4))*reltuples AS 自动清理阈值,
    reltuples::DECIMAL(19,0) 活元组数,
    n_dead_tup::DECIMAL(19,0) 死元组数
FROM
    pg_class c 

LEFT JOIN pg_stat_all_tables d

    ON C.relname = d.relname
WHERE
    c.relname LIKE‘tb%‘  AND reltuples > 0
    AND n_dead_tup > (current_setting(‘autovacuum_analyze_threshold‘)::NUMERIC(12,4))+(current_setting(‘autovacuum_analyze_scale_factor‘)::NUMERIC(12,4))*reltuples;

然后发现死元祖太多
然后我手动收缩了这个表 之后更新的就快了

VACUUM FULL VERBOSE 表名;
VACUUM FULL VERBOSE ANALYZE 表名;

5.总结

遇到这种情况 先需求确保你的sql语句没有问题，然后查看有没有锁 可以EXPLAIN 一下 ，看看数据库参数，是不是数据库的性能原因 最后再看看是不是需要收缩表

postgresql 数据库 update更新慢的原因（已解决）

标签：状态改变   style   遇到   ring   stp   code   view   eve   xpl