PostgreSQL bloat 检查与处理
1.工具软件
pg_bloat_check.py(pg_bloat_check-master.zip)
https://github.com/keithf4/pg_bloat_check
软件包需求：
1).pgstattuple,Pg源码crontrib目录.
2).python 2.6以上.
3).argparse-1.4.0.tar.gz
4).psycopg2-2.6.2.tar.gz
5).setuptools-23.1.0.tar.gz
2.安装步骤：
1).安装Pg扩展pgstattuple
make
make install
2).安装Python扩展
系统包python-devel
setuptools
argparse
psycopg2
3).下载pg_bloat_check压缩包pg_bloat_check-master.zip
解压并赋予执行权限
3.配置用例执行检查
1).配置设置用例
# su - postgres
$ createdb -p 5431 testdb
$ psql -p 5431 -c "create extension pgstattuple"
$ psql -p 5431 testdb
psql (9.5.2)
Type "help" for help.
testdb=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+------------------------------
pgstattuple | 1.3 | public | show tuple-level statistics
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
testdb=# create table t_test(id serial primary key, name text);
CREATE TABLE
testdb=# create index idx_t_test_name on t_test(name);
CREATE INDEX
testdb=# insert into t_test select generate_series(1,100000),md5(random()::text);
INSERT 0 100000
testdb=# insert into t_test select generate_series(100001,1000000),md5(random()::text);
INSERT 0 900000
testdb=# truncate t_test ;
TRUNCATE TABLE
testdb=# insert into t_test select generate_series(1,1000000),md5(random()::text);
INSERT 0 1000000
testdb=# truncate t_test ;
TRUNCATE TABLE
testdb=# insert into t_test select generate_series(1,100000),md5(random()::text);
INSERT 0 100000
testdb=# vacuum ANALYZE t_test ;
VACUUM
testdb=# \q
2).创建统计表
$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=testdb port=5431" --create_stats_table
bloat开头的bloat_stats, bloat_indexes, bloat_tables3个表
testdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | bloat_indexes | table | postgres
public | bloat_stats | table | postgres
public | bloat_tables | table | postgres
public | t_test | table | postgres
(4 rows)
testdb=# select objectname, pg_size_pretty(size_bytes) as object_size, pg_size_pretty(free_space_bytes) as reusable_space, pg_size_pretty(dead_tuple_size_bytes) dead_tuple_space, free_percent from bloat_stats ;
objectname | object_size | reusable_space | dead_tuple_space | free_percent
-----------------+-------------+----------------+------------------+--------------
idx_t_test_name | 7424 kB | 2164 kB | 0 bytes | 29.15
t_test_pkey | 2208 kB | 222 kB | 0 bytes | 10.04
(2 rows)
testdb=#
testdb=#
$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=testdb port=5431" -t t_test -z 10485760 -p 5 -s 5242880
1. public.idx_t_test_name.....................................................(19.15%) 1422 kB wasted
2. public.t_test_pkey........................................................(0.04%) 993 bytes wasted
[postgres@localhost ~]$
测试2
testdb=# insert into t_test select generate_series(1,1000000),md5(random()::text);
ERROR: duplicate key value violates unique constraint "t_test_pkey"
DETAIL: Key (id)=(1) already exists.
testdb=# truncate t_test ;
TRUNCATE TABLE
testdb=# insert into t_test select generate_series(1,1000000),md5(random()::text);
INSERT 0 1000000
testdb=# delete from t_test where id <= 900000;
DELETE 900000
testdb=# vacuum ANALYZE t_test ;
VACUUM
testdb=#
[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=testdb port=5431" -t t_test -z 10485760 -p 5 -s 5242880
1. public.idx_t_test_name........................................................(81.1%) 59 MB wasted
2. public.t_test_pkey...........................................................(80.88%) 17 MB wasted
[postgres@localhost ~]$
测试3
testdb=# truncate t_test ;
TRUNCATE TABLE
testdb=# insert into t_test select generate_series(1,2000000),md5(random()::text);
INSERT 0 2000000
testdb=# delete from t_test where id <= 1900000;
DELETE 1900000
testdb=# vacuum ANALYZE t_test ;
VACUUM
testdb=# \q
[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=testdb port=5431" -t t_test -z 10485760 -p 5 -s 5242880
1. public.idx_t_test_name......................................................(87.94%) 907 MB wasted
2. public.t_test_pkey..........................................................(89.24%) 230 MB wasted
[postgres@localhost ~]$
[postgres@localhost ~]$
3).vacuum full处理
提别提示：
a.步骤进行前要做好相关表备份,以便意外恢复.
b.业务不活动期间，维护窗口时间进行vacuu full tablename.
c.如果要处理的表和索引较多，为了减小维护窗口，不对相关业务进行干预或者调整，需分期分批次按照影响程度和范围依次进行。
d.做好前后数据校验工作，确保回缩成功。
[postgres@localhost ~]$
[postgres@localhost ~]$ psql -p 5431 testdb
psql (9.5.2)
Type "help" for help.
testdb=# vacuum FULL t_test ;
VACUUM
testdb=# \q
4).查询空间回缩情况
[postgres@localhost ~]$ /home/soft/pg_bloat_check-master/pg_bloat_check.py -c "dbname=testdb port=5431" -t t_test
1. public.t_test_pkey........................................................(0.04%) 993 bytes wasted
2. public.idx_t_test_name.......................................................(0.0%) 0 bytes wasted
[postgres@localhost ~]$
5).数据检查正常
[postgres@localhost ~]$ psql -p 5431 testdb
psql (9.5.2)
Type "help" for help.
testdb=# select count(*) from t_test;
count
--------
100000
(1 row)
testdb=# select objectname, pg_size_pretty(size_bytes) as object_size, pg_size_pretty(free_space_bytes) as reusable_space, pg_size_pretty(dead_tuple_size_bytes) dead_tuple_space, free_percent from bloat_stats ;
objectname | object_size | reusable_space | dead_tuple_space | free_percent
-----------------+-------------+----------------+------------------+--------------
idx_t_test_name | 5792 kB | 575 kB | 0 bytes | 9.93
t_test_pkey | 2208 kB | 222 kB | 0 bytes | 10.04
(2 rows)
testdb=#