美色商城 网站建设,怎样做线上销售,有什么平台可以做网站,数字货币交易网站开发怎么做文章目录 1 为什么需要关注表膨胀#xff1f;2 如何确定是否发生了表膨胀#xff1f;2.1 通过查询表的死亡元组占比情况来判断膨胀率2.1.1 指定数据库和表名2.1.2 查询数据库里面所有表的膨胀情况 3 膨胀的原理3.1 什么是膨胀#xff1f;膨胀率#xff1f;3.2 哪些数据库元… 文章目录 1 为什么需要关注表膨胀2 如何确定是否发生了表膨胀2.1 通过查询表的死亡元组占比情况来判断膨胀率2.1.1 指定数据库和表名2.1.2 查询数据库里面所有表的膨胀情况 3 膨胀的原理3.1 什么是膨胀膨胀率3.2 哪些数据库元素会发生膨胀3.3 为什么会产生膨胀的现象3.4 必须要做清理的原因3.5 VACUUM进度报告 4 如何处理表膨胀问题4.1 简单粗暴但是有效彻底的空间回收“vacuum full 表名”-不推荐4.1.1 FULL参数使用注意4.1.2 FULL参数使用示例 4.2 相对优雅一些的回收方式“vacuum 表名”-推荐4.2.1 标准的vacuum不带full参数的方式4.2.2 标准VACUUM回收清理使用示例4.2.3 标准vacuum支持多个option命令方式vacuum (verbose,analyze) mytest; 4.3 模拟大表膨胀的简单方法 5 膨胀产生后的危害如何长远应对5.1 产生哪些危害5.2 如何应对 6 总结 1 为什么需要关注表膨胀
有一天发现数据库所在的服务器上的空间在报不够用了有一天发现某些和数据库有关的功能的莫名的变慢了有一天发现某些功能时快时慢。。。 这些时候我们可能要考虑一下是不是有表膨胀在发生?
2 如何确定是否发生了表膨胀
2.1 通过查询表的死亡元组占比情况来判断膨胀率
pg_stat_all_tables里面的relname是表名。 可以看到如果类似最后一列dead_tup_ratior如果值很大一般来说大于50了那么一定要介入处理了。这个值相当于是无效数据的占比。
2.1.1 指定数据库和表名
下面的示例是数据库名是test数据库表是mytest
test# select schemaname||.||relname as table_name,pg_size_pretty(pg_relation_size(schemaname||.||relname)) as table_size,n_dead_tup,n_live_tup,round(n_dead_tup*100/(n_live_tupn_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup n_dead_tup 0 and relnamemytest;table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
-------------------------------------------------------------------public.mytest | 1042 MB | 10000001 | 5999859 | 62.0
(1 row)
2.1.2 查询数据库里面所有表的膨胀情况
select schemaname||.||relname as table_name,pg_size_pretty(pg_relation_size(schemaname||.||relname)) as table_size,n_dead_tup,n_live_tup,round(n_dead_tup*100/(n_live_tupn_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup n_dead_tup 0
order by dead_tup_ratio desc;3 膨胀的原理
3.1 什么是膨胀膨胀率
顾名思义本来应该是一个大小但是因为某种原因它的大小变得更大了甚至超乎想象的那么大。这就是发生了膨胀而其中的膨胀的程度的体现就是膨胀率。因此膨胀率可以直观的体现膨胀到底有多厉害。 反观数据库的膨胀的理解一张A表或者A表的索引正常应该是占用一个大小的空间但是查出来实际占用超过了这个空间的大小往往随着时间的推移可能会占用的越来越来离谱。
3.2 哪些数据库元素会发生膨胀 数据库表、索引
*索引实际会和表强关联因此可以比较泛指到就是表膨胀。
3.3 为什么会产生膨胀的现象
之所以产生膨胀的原因跟PostgreSQL数据库的数据维护方式是息息相关的 PG使用MVCC(多版本并发控制) 实际上MVCC是oracle、sqlserver数据库也使用的核心原理但是新旧版本的数据存储的机制有所不同因此造就了各个数据库在膨胀现象的表现有所不同。 sqlserver数据库旧版本的数据专门写入到临时表中新数据写入日志然后再去更改数据。新旧分别放在不同的地方膨胀的情况相对低一点但是也会有这种情况发生发生后也需要按照SQLSERVER相关的处理方式进行。 oracle数据库旧版本的数据放到UNDO新数据放到REDO然后更改数据。oracle数据库本身对redo、undo文件有自动清理、扩展的机制因此总体来说对膨胀的控制相对可控但是也不排除处问题的情况。有的时候也需要有重建undo文件的事情发生。 PG数据库旧版本标示为无效新数据写入日志执行成功后把新版本的数据写入到新的位置。旧版本的数据在仍然是存储在表中只是标示为无效如果不及时做清理那么无效的数据会占用空间越来越大这样就发生了膨胀。
对于PG数据库它把旧版本、新版本的表数据都同时存储在业务实际访问的表中也称为数据页中只是对于就旧版本的数据元组做了一个标记。因此如果没有及时清理的话导致数据库表臃肿会很容易导致正常访问表的操作因此发生异常的现象。 delete、update操作会引发数据上的变化因此如果实际使用表会频繁出现删除和更新数据这种情况下会很容易出现死亡元组如果经常有大量数据的删除和更新那么这个表会比较容易出现大的膨胀现象。
3.4 必须要做清理的原因
PostgreSQL的VACUUM命令出于几个原因必须定期处理每一个表
恢复或重用被已更新或已删除行所占用的磁盘空间。 更新被PostgreSQL查询规划器使用的数据统计信息。 更新可见性映射它可以加速只用索引的扫描。 保护老旧数据不会由于事务ID回卷或多事务ID回卷而丢失。
VACUUM后会更新pg_class等其他的表会优化索引查询、优化执行计划等进而达到恢复数据库表相关操作的性能。
3.5 VACUUM进度报告
只要VACUUM正在运行每一个当前正在清理的后端包括autovacuum工作者进程在pg_stat_progress_vacuum视图中都会有一行。下面的表描述了将被报告的信息并且提供了如何解释它们的信息。VACUUM FULL命令的进度是通过pg_stat_progress_cluster报告的因为VACUUM FULL和CLUSTER都是重写表而普通的VACUUM只是原地修改表。
pg_stat_progress_vacuum视图说明
序号列类型描述1pidinteger后端的进程ID。2datidoid这个后端连接的数据库的OID。3datnamename这个后端连接的数据库的名称。4relidoid被vacuum的表的OID。5phasetextvacuum的当前处理阶段。6heap_blks_totalbigint该表中堆块的总数。这个数字在扫描开始时报告之后增加的块将不会并且不需要被这个VACUUM访问。7heap_blks_scannedbigint被扫描的堆块数量。由于可见性映射被用来优化扫描一些块将被跳过而不做检查被跳过的块会被包括在这个总数中因此当清理完成时这个数字最终将会等于heap_blks_total。仅当处于扫描堆阶段时这个计数器才会前进。8heap_blks_vacuumedbigint被清理的堆块数量。除非表没有索引这个计数器仅在处于清理堆阶段时才会前进。不包含死亡元组的块会被跳过因此这个计数器可能有时会向前跳跃一个比较大的增量。9index_vacuum_countbigint已完成的索引清理周期数。10max_dead_tuplesbigint在需要执行一个索引清理周期之前我们可以存储的死亡元组数取决于maintenance_work_mem。11num_dead_tuplesbigint从上一个索引清理周期以来收集的死亡元组数。 VACUUM的阶段:
序号阶段描述1初始化VACUUM正在准备开始扫描堆。这个阶段应该很简短。2扫描堆VACUUM正在扫描堆。如果需要它将会对每个页面进行修建以及碎片整理并且可能会执行冻结动作。heap_blks_scanned列可以用来监控扫描的进度。3清理索引VACUUM当前正在清理索引。如果一个表拥有索引那么每次清理时这个阶段会在堆扫描完成后至少发生一次。如果maintenance_work_mem不足以存放找到的死亡元组则每次清理时会多次清理索引。4清理堆VACUUM当前正在清理堆。清理堆与扫描堆不是同一个概念清理堆发生在每一次清理索引的实例之后。如果heap_blks_scanned小于heap_blks_total系统将在这个阶段完成之后回去扫描堆否则系统将在这个阶段完成后开始清理索引。5清除索引VACUUM当前正在清除索引。这个阶段发生在堆被完全扫描并且对堆和索引的所有清理都已经完成以后。6截断堆VACUUM正在截断堆以便把关系尾部的空页面返还给操作系统。这个阶段发生在清除完索引之后。7执行最后的清除VACUUM在执行最终的清除。在这个阶段中VACUUM将清理空闲空间映射、更新pg_class中的统计信息并且将统计信息报告给统计收集器。当这个阶段完成时VACUUM也就结束了。
4 如何处理表膨胀问题
*注意考虑到业务忙时影响
4.1 简单粗暴但是有效彻底的空间回收“vacuum full 表名”-不推荐 注意有失败的可能 vacuum full 需要注意看看是否操作的表多大磁盘空闲空间是否充足否则会命令执行失败。 如果遇到了该问题或者做之前提前在服务器上进行磁盘空间释放确保无用的文件再去删除。务必注意生产环境实施该操作要谨慎高危
4.1.1 FULL参数使用注意
序号vacuum full使用注意具体说明用途通常用于需要从表中回收庞大的空间时候而且最好是释放给操作系统使用一般不建议使用。1缺点需要给清理的表加上排它锁执行操作期间对该表的insert、update、select、delete等操作会被阻塞2缺点需要比较长的时间时间长短跟表本身的大小以及膨胀的程度相关3缺点需要临时额外占用操作系统上的磁盘空间用于创建该表的新的拷贝在操作执行完成前不会释放旧的拷贝。1优点可以回收更多空间2优点回收到的空间可以释放给操作系统真正的物理上清理释放
选择“FULL”清理它可以收回更多空间并且需要更长时间和表上的排他锁。这种方法还需要额外的磁盘空间因为它会创建该表的一个新拷贝并且在操作完成之前都不会释放旧的拷贝。通常这种方法只用于需要从表中收回数量庞大的空间时。
不引入其他插件的情况下如果能够挑选到业务不繁忙的时候或者说能够接受短时间内的锁表、功能卡顿的话可以执行下面的语句VACUUM FULL 具体表名; 使用前务必关注上述列出的缺点。
4.1.2 FULL参数使用示例
膨胀的时候的空间占用1个G左右
test# select schemaname||.||relname as table_name,pg_size_pretty(pg_relation_size(schemaname||.||relname)) as table_size,n_dead_tup,n_live_tup,round(n_dead_tup*100/(n_live_tupn_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup n_dead_tup 0 and relnamemytest;table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
-------------------------------------------------------------------public.mytest | 1042 MB | 10000001 | 5999859 | 62.0
(1 row)
完成回收后空间占用下降到了391MB
test# VACUUM FULL mytest;
VACUUM
test#
test# select schemaname||.||relname as table_name,pg_size_pretty(pg_relation_size(schemaname||.||relname)) as table_size,n_dead_tup,n_live_tup,round(n_dead_tup*100/(n_live_tupn_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup n_dead_tup 0 and relnamemytest;table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
-------------------------------------------------------------------public.mytest | 391 MB | 0 | 7481453 | 0.0
(1 row)4.2 相对优雅一些的回收方式“vacuum 表名”-推荐 注意可能有失败的可能 1、长事务如果当存在长时间运行的事务长事务对表进行操作如insert、delete、update等操作且未提交VACUUM操作会失效因为此时的死元组对长事务是可见的尽管对其他事务不可见但仍然无法删除死元组。 如果碰到了解决的办法就是杀死长事务或者以后合理设置idle_in_transaction_session_timeout 参数不要让连接不必要地“闲置在事务中”。配置参数idle_in_transaction_session_timeout可以被用来自动断开拖延会话的连接。 实际上执行的失败提示信息中会写明有更旧的xmin水平
SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;查询到后pg_terminate_backend(pid)来做终止事务 2、 废弃的复制槽 复制延迟或者备服务器关闭
SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;使用pg_drop_replication_slot()删除复制槽
test# select pg_drop_replication_slot(aaaa);3、孤立的准备事务 某种异常情况下导致事务处于准备好的状态但是未继续提交或者中止。
SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;ROLLBACK PREPARED transaction_id
例如ROLLBACK PREPARED 123
4.2.1 标准的vacuum不带full参数的方式
序号标准vacuum使用注意使用说明1用途推荐日常使用设置合理定时在非忙时执行但是VACUUM使用过程会带来I/O流量的的产生会导致数据库其他会话的操作性能变差因此使用过程中需要搭配一些策略参数来防护后台清理工作给数据库性能带来的冲击。例如vacuum_cost_delay、vacuum_cost_page_hit 、vacuum_cost_page_miss 、vacuum_cost_page_dirty 、vacuum_cost_limit 将导致清理进程休眠的累计代价。默认值为200。1缺点标准的vacuum完成回收后的空间不会实际释放给操作系统只是标记为继续给当前的表或者索引复用。当膨胀的程度很高的时候该方式无法释放占用空间给物理服务器操作系统使用。除非在特殊的情况中表尾部的一个或多个页面变成完全空闲并且能够很容易地得到一个排他表锁。2缺点执行过程中会可能产生大量I/O流量对数据库处理性能产生一定负面影响。1优点不阻塞基础数据库表增删改查标准形式的VACUUM可以和生产数据库操作并行运行SELECT、INSERT、UPDATE和DELETE等命令将继续正常工作但在清理期间你无法使用ALTER TABLE等命令来更新表的定义2优点执行耗时相对FULL参数方式较短
4.2.2 标准VACUUM回收清理使用示例
回收清理前表占用的大小空间table_size是781M膨胀比例为50% 执行标准的回收后表占用的大小空间table_size还是781M死亡元组为0。 pg_stat_all_tables字段 last_autovacuum 表示最近一次自动回收的时间。
test# select schemaname||.||relname as table_name,pg_size_pretty(pg_relation_size(schemaname||.||relname)) as table_size,n_dead_tup,n_live_tup,round(n_dead_tup*100/(n_live_tupn_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup n_dead_tup 0 and relnamemytest;table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
-------------------------------------------------------------------public.mytest | 781 MB | 6000001 | 5995257 | 50.0
(1 row)test# \timing on
Timing is on.
test# vacuum mytest;
VACUUM
Time: 29512.550 ms (00:29.513)
test# select schemaname||.||relname as table_name,pg_size_pretty(pg_relation_size(schemaname||.||relname)) as table_size,n_dead_tup,n_live_tup,round(n_dead_tup*100/(n_live_tupn_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup n_dead_tup 0 and relnamemytest;table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
-------------------------------------------------------------------public.mytest | 781 MB | 0 | 2997602 | 0.0
(1 row)Time: 124.583 ms
test#再次执行vacuum full mytest再查询表占用大小table_size为391M空间直接释放了一半这是把空间释放到了操作系统的效果。
test# vacuum full mytest;
VACUUM
Time: 54052.374 ms (00:54.052)
test# select schemaname||.||relname as table_name,pg_size_pretty(pg_relation_size(schemaname||.||relname)) as table_size,n_dead_tup,n_live_tup,round(n_dead_tup*100/(n_live_tupn_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup n_dead_tup 0 and relnamemytest;table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
-------------------------------------------------------------------public.mytest | 391 MB | 0 | 5972470 | 0.0
(1 row)Time: 22.492 ms
test#4.2.3 标准vacuum支持多个option命令方式vacuum (verbose,analyze) mytest;
VERBOSE 为每个表打印一份详细的清理活动报告。
ANALYZE 更新优化器用以决定最有效执行一个查询的方法的统计信息。
test# vacuum (verbose,analyze) mytest;
INFO: vacuuming public.mytest
INFO: index mytest_pkey now contains 6000001 row versions in 32905 pages
DETAIL: 0 index row versions were removed.
16450 index pages have been deleted, 16450 are currently reusable.
CPU: user: 0.10 s, system: 0.27 s, elapsed: 0.65 s.
INFO: mytest: found 0 removable, 2 nonremovable row versions in 1 out of 100001 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 853
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 50000 frozen pages.
0 pages are entirely empty.
CPU: user: 0.11 s, system: 0.27 s, elapsed: 0.66 s.
INFO: vacuuming pg_toast.pg_toast_16779
INFO: index pg_toast_16779_index now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: pg_toast_16779: found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 853
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing public.mytest
INFO: mytest: scanned 30000 of 100001 pages, containing 1793880 live rows and 0 dead rows; 30000 rows in sample, 5979660 estimated total rows
VACUUM
Time: 2096.122 ms (00:02.096)
test#4.3 模拟大表膨胀的简单方法
create table student(id int primary key, sname varchar(10), sex int, description text);
insert into student values(generate_series(1,20000000),test, 1, now());
update student set description123.123.123 where id10000000;
delete from student where id 10000;另外可以使用定时循环执行update语句持续增加死亡元组。
\watch 1 update student set description123.123.123 where id10000000;5 膨胀产生后的危害如何长远应对
5.1 产生哪些危害
如果对于膨胀的情况听之任之的化看得见的危害有哪些呢按照出现的时间序来看 第一、数据库表占用空间越来越大 第二、数据库对执行计划的选择不是最高效的 第三、普通的数据库表插入、删除、查询都变慢了 第四、突然有一天发现整个系统都慢起来了 第五、发现数据库磁盘配额报告警了 第六、扩展磁盘配额后过段时间又继续报数据库磁盘配额不足 第七、数据库所在服务器磁盘空间不足 第八、所有和数据库部署在同一个服务器上的应用无法正常创建文件 第九、整个服务器系统宕机了
看起来就是慢慢恶化的过程实际情况的确也会劣化的非常快。
5.2 如何应对
序号产生膨胀的可能原因应对举措1delete减少大量的删除动作优化删除语句效率减少耗时。常规建议开启autovacuum进行清理回收。2update减少大量更新动作优化更新语句效率减少耗时。常规建议开启autovacuum进行清理回收。3select长时间占用优化select查询语句降低执行查询耗时。减少针对大表的全表扫描。4索引创建常规建议开启autovacuum进行清理回收。5数据库事务回滚常规建议开启autovacuum进行清理回收。6系统异常导致的其他意外情况常规建议开启autovacuum进行清理回收。必要的时候执行vacuum full手动回收。7IO性能较差优化磁盘使用计划避免磁盘I/O浪费减少任务堆积访问数据库配置SSD或者调整raid模式到I/O更好的方式。8内存资源紧张建议根据业务特性情况考虑到业务峰值情况尽量分配充分的内存资源。且内存的分配要充分考虑PG数据库系统参数的各个值包括work_mem、shared_buffer等9CPU资源紧张建议根据业务特性情况考虑到业务峰值情况尽量分配充分的CPU资源。10磁盘资源不足max_wal_size和wal_segment_size特别是前面的max_wal_size在业务量存在突发海量更新数据的情况下这个参数需要扩大具体可以扩大到多少需要充分考虑一下磁盘的空闲空间。 如果这个值设置不当会引起主备PG同步复制流中断。10PG系统参数设置不合理适当针对部分PG参数进行调优shared_buffers 、temp_buffers 、max_prepared_transactions 、work_mem 、maintenance_work_mem 等PG参数调优11没有开启自动回收清理建议开启autovacuum但是需要根据各自的业务特性确定自动运行回收的策略。另外注意这个开关可以控制到具体的表级别。执行命令show autovacuum查看开启状态。它的目的是自动执行VACUUM和ANALYZE 命令。当它被启用时自动清理会检查被大量插入、更新或删除元组的表。这些检查会利用统计信息收集功能因此除非track_counts被设置为true自动清理不能被使用。在默认配置下自动清理是被启用的并且相关配置参数已被正确配置。12操作系统或者服务器或者数据库异常关闭检查是否有发生膨胀可以使用vacuum手动清理。
修改具体表的自动回收开关autovacuum例如
test# show autovacuum;autovacuum
------------on
(1 row)test#
test# alter table mytest set (autovacuum_enabledon);
ALTER TABLE
test# \d mytestTable public.mytestColumn | Type | Collation | Nullable | Default | Storage | Stats target | D
escription
-------------------------------------------------------------------------
-----------a | integer | | not null | | plain | |b | text | | | | extended | |
Indexes:mytest_pkey PRIMARY KEY, btree (a)
Options: autovacuum_enabledon
6 总结
减少或者降低膨胀的程度 总之轻微膨胀本身并无多大的危害且日常操作正常来说也的确是会产生死亡元组但是过度膨胀例如膨胀率超过50%以上那么需要重点关注。如何避免过度膨胀其中有效的方式核心来说包括3点 第一、合理设置自动回收autovacuum。 第二、尽可能的让各个数据库操作快速完成。手段包括但不限于数据库表设计调优、索引调优、SQL语句调优、PG数据库参数调优、服务器资源调优等。 第三、尽可能减少冗余的删除和修改数据减少一个事务长时间运行。进行日常检查 定期检查数据库健康状态包括但不限于 第一、服务器的磁盘、内存、cpu占用情况。 第二、膨胀的程度监控出现很大的膨胀率的时候先安排vacuum必要时尽快安排vacuum full。 第三、记录膨胀表的情况后续针对性安排可能的优化。
