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 2.6or more.
3) .argparse-1.4.0.tar.gz
4). Psycopg2-2.6.2.tar.gz
5). Setuptools-23.1.0.tar.gz
two。 Installation steps:
1)。 Install the Pg extension pgstattuple
Make
Make install
2)。 Install the Python extension
System package python-devel
Setuptools
Argparse
Psycopg2
3)。 Download pg_bloat_check package pg_bloat_check-master.zip
Decompress and grant execution permission
3. Configure use case execution check
1)。 Configuration Settings use case
# 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 (1m 100000), md5 (random ():: text)
INSERT 0 100000
Testdb=# insert into t_test select generate_series (100001m 1000000), md5 (random ():: text)
INSERT 0 900000
Testdb=# truncate t_test
TRUNCATE TABLE
Testdb=# insert into t_test select generate_series (1,000,000), md5 (random ():: text)
INSERT 0 1000000
Testdb=# truncate t_test
TRUNCATE TABLE
Testdb=# insert into t_test select generate_series (1m 100000), md5 (random ():: text)
INSERT 0 100000
Testdb=# vacuum ANALYZE t_test
VACUUM
Testdb=#\ Q
2)。 Create a statistical table
$/ home/soft/pg_bloat_check-master/pg_bloat_check.py-c "dbname=testdb port=5431"-create_stats_table
Bloat_stats, bloat_indexes, bloat_tables3 tables at the beginning of bloat
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 | 2208 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 ~] $
Test 2
Testdb=# insert into t_test select generate_series (1,000,000), 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,000,000), md5 (random ():: text)
INSERT 0 1000000
Testdb=# delete from t_test where id
