如果快速做网站,天津网站设计哪家公司好,未来做那个网站致富,建筑图纸怎么看懂的最快 施工图最近遇到一个奇怪的问题#xff0c;一个百万级的PostgreSQL表#xff0c;只有3个索引。但是每次执行insert或update语句就要几百ms以上。经过查询发现是一个狠简单的问题#xff0c;数据库表死元组太多了#xff0c;需要手动清理。
在 PG 中#xff0c;update/delete 语句…最近遇到一个奇怪的问题一个百万级的PostgreSQL表只有3个索引。但是每次执行insert或update语句就要几百ms以上。经过查询发现是一个狠简单的问题数据库表死元组太多了需要手动清理。
在 PG 中update/delete 语句的实现通过 MVCC 机制的多版本链实现。如下图所示更新一条元组时会将原来的元组标记并新增一条元组。后续的事物通过快照来判断元组的可见性。
对于一条已经被更新/删除的元组来说当这条元组对所有事物都不可见后它的存在就没有意义了理应被删除对于这种元组我们称之为“死元组”。当一张表有大量更新/删除时如果不做清理的话表里面就会积攒很多这样的“死元组”占用大量的空间造成表空间膨胀。
一、清理前
查询死元组数量SQL
SELECTc.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) 死元组数
FROMpg_class c LEFT JOIN pg_stat_all_tables dON C.relname d.relname
WHEREc.relname 你要查询的表名 AND reltuples 0AND n_dead_tup (current_setting(autovacuum_analyze_threshold)::NUMERIC(12,4))(current_setting(autovacuum_analyze_scale_factor)::NUMERIC(12,4))*reltuples;此时的数据库插入耗时测试执行update语句 Affected rows: 1 时间: 1.371s 二、配置自动清理
AUTOVACUUM自动清理元组。开启自动清理后PostgreSQL会在合适的时候自动执行VACUUM操作。
-- 查看当前autovacuum的状态
SHOW autovacuum;-- 开启autovacuum
SET autovacuum on;三、使用VACUUM手动清理
自动清理有时候可能由于参数配置效果不佳。可以使用VACUUM命令手动清理注意清理过程中会锁表
VACUUM FULL VERBOSE 模式名.表名;
VACUUM FULL VERBOSE ANALYZE 模式名.表名;清理后update效果 Affected rows: 1 时间: 0.427s 四、查询历史清理信息
SELECTrelname 表名,seq_scan 全表扫描次数,seq_tup_read 全表扫描记录数,idx_scan 索引扫描次数,idx_tup_fetch 索引扫描记录数,n_tup_ins 插入的条数,n_tup_upd 更新的条数,n_tup_del 删除的条数,n_tup_hot_upd 热更新条数,n_live_tup 活动元组估计数,n_dead_tup 死亡元组估计数,last_vacuum 最后一次手动清理时间,last_autovacuum 最后一次自动清理时间,last_analyze 最后一次手动分析时间,last_autoanalyze 最后一次自动分析时间,vacuum_count 手动清理的次数,autovacuum_count 自动清理的次数,analyze_count 手动分析此表的次数,autoanalyze_count 自动分析此表的次数,( CASE WHEN n_live_tup 0 THEN n_dead_tup :: float8 / n_live_tup :: float8 ELSE 0 END ) :: NUMERIC ( 12, 2 ) AS 死/活元组的比例
FROMpg_stat_all_tables
WHEREschemaname public
ORDER BY n_dead_tup::float8 DESC;
