PostgreSQL bloat 检查与处理

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了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相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问