• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    解决postgresql 数据库 update更新慢的原因

    ;大约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.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更新慢的原因的文章就介绍到这了,更多相关postgresql 数据库 update更新慢内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

    您可能感兴趣的文章:
    • PostgreSQL实现批量插入、更新与合并操作的方法
    • 基于PostgreSQL和mysql数据类型对比兼容
    • 将MySQL数据库移植为PostgreSQL
    • PostgreSQL使用MySQL外表的步骤详解(mysql_fdw)
    • PostgreSQL使用MySQL外表的步骤详解(mysql_fdw)
    • 实现MySQL + PostgreSQL批量插入更新insertOrUpdate
    上一篇:postgresql查询自动将大写的名称转换为小写的案例
    下一篇:springboot 没法扫描到repository的解决
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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