pg_repack介绍
PostgreSQL数据库,因为multiversion concurrency control (MVCC)的原因,在update和delete的事务处理中需要将老数据另行保存一份,会导致占用的磁盘空间会比实际有效空间要大很多。
PostgreSQL自带了autovacuum和vacuum命令, 可以检测这部分旧数据,标记这部分空间为可用,供后续使用,一定程度上可以缓解存储空间肿胀(bloat)的问题,但问题在于autovacuum和vacuum只是在PG范围内标记空间为可用,并不会将空间释放给操作系统进行重新利用。
如果需要将空间释放和操作系统使用,需要使用vacuum full
命令,但是该命令会使用排它锁,堵塞对应表的读写操作。在生产环境中是不推荐使用的。
pg_repack这个插件,可以在不阻塞表读写的情况下,进行表数据的清理。
作用机理是:创建一个新表,将数据从旧表移动到新表。为了避免表被独占锁定,创建了一个额外的日志表来记录原始表的改动,还添加了一个把INSERT / UPDATE / DELETE操作记录到日志表的触发器。当原始表中的数据全部导入到新表中,索引重建完毕,日志表的改动全部完成,pg_repack会连同新索引,用新表替换旧表,并将原旧表Drop掉。整个过程非常简单,非常可靠,但是需要注意的是——需要额外剩余足够的磁盘空间(原表大小 + 索引 + 额外的日志表空间)
实操
下面记录一次使用pg_repack来回收AWS RDS for PostgreSQL中磁盘空间的过程。
准备工作
查看RDS的数据库版本和支持的pg_repack版本。
查看数据库版本为PG 10.18
1 2 3 4 5 6 7
| ebdb=> select version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 10.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit (1 row) ebdb=>
|
查看支持的pg_repack版本为pg_repack 1.4.3
1 2 3 4 5 6 7
| ebdb=> SELECT * FROM pg_available_extension_versions where name='pg_repack'; name | version | installed | superuser | relocatable | schema | requires | comment -----------+---------+-----------+-----------+-------------+--------+----------+-------------------------------------------------------------- pg_repack | 1.4.3 | t | t | f | | | Reorganize tables in PostgreSQL databases with minimal locks (1 row) ebdb=>
|
启动一个EC2来安装pg_repack命令
pg_repack的客户端版本,需要和数据库支持的pg_repack扩展版本相同,所以pg_repack的客户端版本也需要是1.4.3。
如下是在一台系统为Amazon Linxu 2018.03
的EC2上安装pg_repack客户端的步骤:
1 2 3 4 5 6
| sudo yum install readline-devel postgresql96-static postgresql96-devel wget --no-check-certificate 'https://api.pgxn.org/dist/pg_repack/1.4.3/pg_repack-1.4.3.zip' unzip pg_repack-1.4.3.zip cd pg_repack-1.4.3 make
|
在数据库中开启pg_repack扩展
登录数据库,开启pg_repack扩展
1 2 3
| ebdb=> create extension pg_repack; CREATE EXTENSION ebdb=>
|
pg_repack命令基础用法
在EC2上执行pg_repack命令进行清理操作。
清理命令:
pg_repack -h host -U username -t table_name -e -k database
参数说明:
- -h host: 数据库地址
- -U username: 连接数据库的用户名
- -t table_name: 需要清理的表名
- -e: 打印查询请求
- -k: –no-superuser-check, 客户端运行时,不进行superuser的检查。
清理数据
清理目标是一台用于测试的staging服务器,数据运行跑了好几年了,测试时候频繁更新删除比较多的数据,会有很多的膨胀tuples。
实例类型是: db.t3.medium
, vCPU: 2, RAM: 4GB.
测试结果如下(隐藏了真实的表名):
表名 |
更新前表大小 |
更新前索引大小 |
更新前总计大小 |
更新后表大小 |
更新后索引大小 |
更新后表大小 |
pg_repack耗时 |
A |
44 GB |
13 GB |
57 GB |
37 GB |
4347 MB |
41 GB |
112m41.611s |
B |
14 GB |
19 GB |
33 GB |
31 MB |
28 MB |
59 MB |
1m26.535s |
C |
4974 MB |
9037 MB |
14 GB |
4896 MB |
6671 MB |
11 GB |
23m11.457s |
D |
5580 MB |
4912 MB |
10 GB |
5578 MB |
4909 MB |
10 GB |
8m34.591s |
E |
4930 MB |
2225 MB |
7155 MB |
4917 MB |
2226 MB |
7143 MB |
11m28.664s |
F |
3753 MB |
3250 MB |
7002 MB |
2960 MB |
1932 MB |
4892 MB |
6m42.480s |
G |
3987 MB |
358 MB |
4345 MB |
3217 MB |
123 MB |
3339 MB |
3m56.969s |
H |
2392 MB |
1084 MB |
3476 MB |
18 MB |
59 MB |
77 MB |
0m22.968s |
I |
1277 MB |
1689 MB |
2965 MB |
1203 MB |
1187 MB |
2390 MB |
2m20.795s |
J |
685 MB |
682 MB |
1367 MB |
624 MB |
310 MB |
934 MB |
1m33.532s |
K |
1100 MB |
17 MB |
1116 MB |
1100 MB |
14 MB |
1114 MB |
1m0.908s |
L |
545 MB |
509 MB |
1054 MB |
542 MB |
387 MB |
929 MB |
0m39.896s |
M |
839 MB |
24 MB |
863 MB |
8200 kB |
6192 kB |
14 MB |
0m12.013s |
N |
275 MB |
476 MB |
750 MB |
273 MB |
388 MB |
661 MB |
0m27.279s |
O |
602 MB |
72 MB |
675 MB |
602 MB |
72 MB |
674 MB |
0m33.905s |
P |
74 MB |
434 MB |
508 MB |
63 MB |
40 MB |
104 MB |
0m25.461s |
拿典型的例子分析一下:
- A表,是基础表。更新比较频繁,但没有删除过任何数据。repack后表数据本身减少不多,但索引大小有极大的压缩, repack耗时很长。
- B表,是图片表,更新比较少,但有巨多的删除操作。repack前正好清空了表中大部分的数据,repack过后,原先的所有空间基本都被回收了,repack耗时很短。
- C表,是信息记录表,更新居多,但不删除,和A表类似,回收的空间有限。
- D表,是关系表,基本无更新和删除操作。repack基本无效果。
- H和M表,和B表类似,repack之前原有数据基本都已经delete掉了,所以repack过后,极大的回收了空间,耗时也很短。
总结:
- 操作耗时主要由表中当前的数据量所决定,而不是表当前的大小所决定。
- 有频繁update和delete操作的表,会节省出更多的磁盘空间。
几个有用的sql
获取bloat率的sql,来自https://gist.github.com/jeechu/e32ee9d6c79dccbf931a4925638c94c6
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| SELECT current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/ round(avg((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric),1) AS tbloat, round(avg(CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END), 0) AS wastedbytes FROM ( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta, COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols FROM ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM ( SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+( SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename ) AS nullhdr FROM pg_stats s, ( SELECT (SELECT current_setting('block_size')::numeric) AS bs, CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo ) AS constants GROUP BY 1,2,3,4,5 ) AS foo ) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid ) AS sml group by 1, 2, 3 ORDER BY 4 DESC limit 5;
|
获取每个表磁盘使用情况的sql:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS indexes_size, pg_total_relation_size(table_name) AS total_size FROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name FROM information_schema.tables ) AS all_tables ORDER BY total_size DESC ) AS pretty_sizes;
|
Reference