PostgreSQL bloat Inspection and processing

1. Tool Software

pg_bloat_check.py (Pg_bloat_check-master.zip)

Https://github.com/keithf4/pg_bloat_check

Package Requirements:

1). PGSTATTUPLE,PG source Crontrib directory.

2). Python above 2.6.

3). argparse-1.4.0.tar.gz

4). psycopg2-2.6.2.tar.gz

5). setuptools-23.1.0.tar.gz

2. Installation steps:

1). Installing the PG Extension Pgstattuple

Make

Make install

2). Install the Python extension

System Package Python-devel

Setuptools

Argparse

Psycopg2

3). Download Pg_bloat_check Compression Pack Pg_bloat_check-master.zip

Unzip and give execute permission

3. Configure the use case execution check

1). Configure Settings Use Cases

# 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). Create statistics

$/home/soft/pg_bloat_check-master/pg_bloat_check.py-c "Dbname=testdb port=5431"--create_stats_table

Bloat beginning of Bloat_stats, bloat_indexes, Bloat_tables3 table

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

[Email protected] ~]$

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

[Email protected] ~]$/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%) Wasted MB

2. Public.t_test_pkey ... ..... .... ... .... ..... .... ................................ (80.88%) MB wasted

[Email protected] ~]$

Test 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

[Email protected] ~]$/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 ... ..... ..... ..... ..... ..... ........, ... and .... ....., ... and .... ....., ...... (89.24%) MB wasted

[Email protected] ~]$

[Email protected] ~]$

3). Vacuum Full Processing

Tips for mentioning:

A. Make a backup of the related tables before the steps can be performed for accidental recovery.

B. During the business inactivity period, the Maintenance window time is vacuu full tablename.

C. If there are many tables and indexes to be processed, in order to reduce the Maintenance window, do not intervene or adjust the related business, the degree and scope of the impact should be carried out sequentially.

D. Do a good job before and after the data verification to ensure the retraction success.

[Email protected] ~]$

[Email protected] ~]$ psql-p 5431 TestDB

Psql (9.5.2)

Type ' help ' for help.

testdb=# vacuum Full t_test;

VACUUM

testdb=# \q

4). Query Space retraction condition

[Email protected] ~]$/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 bytes Wasted

[Email protected] ~]$

5). Data Check OK

[Email protected] ~]$ 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=#

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1875439

PostgreSQL bloat Inspection and processing