PostgreSQL bloat 检查与处理

前端之家收集整理的这篇文章主要介绍了PostgreSQL bloat 检查与处理前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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@

psql (9.5.2)@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@

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@

提别提示:@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@

psql (9.5.2)@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@

4).查询空间回缩情况@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@

psql (9.5.2)@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@

猜你在找的Postgre SQL相关文章