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=#