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