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

⽬录

1.查看语句是否有问题2.查找影响updata的因素查询锁3.查询参数4.收缩表VACUUM5.总结;⼤约140000条数据） 竟然运⾏了⼀个⼩时还没有完成下⾯是我的⼏点解决⽅案

我的update 语句 是从⼀个临时表更新值到另⼀个正式表

因为具体数据需要保密，我就不截图了 只说说⼤体思路，与⽅法

1.查看语句是否有问题

复制俩个⼀模⼀样的表 和数据 ⼿动执⾏语句 发现不到⼀分钟就运⾏成功了 这样就可以确认语句没有问题

2.查找影响updata的因素

我的第⼀反应是不是有锁 有锁的情况会导致等待或者死锁

查询锁

select 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.pidjoin pg_stat_activity b2 on b1.pid=b2.pidwhere 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'::regclasswhere 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.relnameWHERE

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更新慢的原因的⽂章就介绍到这了,更多相关postgresql 数据库 update更新慢内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！