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