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,md5(random()::text);

INSERT 0 1000000

testdb=# truncate t_test ;

TRUNCATE TABLE

testdb=# insert into t_test select generate_series(1,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=#

$ /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,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,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 ~]$

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

PostgreSQL bloat 检查与处理

猜你在找的Postgre SQL相关文章