Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.