PostgreSQL数据库管理第八章日常运维
目录
8.4.2 安装pg_bloat_check 和pg_repack
概述
保持PostgreSQL 数据库平稳运行,必须做日常性的维护工作务来达到最优的性能。主要三种日常维护，1日常清理（VACUUM;），2 更新数据库索引，3日志文件维护。check_postgres可用于检测数据库的健康并报告异常情况。
8.1日常清理（VACUUM）
在PostgreSQL中，使用delete和update语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行VACUUM操作。
VACUUM的变体：标准VACUUM和VACUUM FULL。
VACUUM FULL可以收回更多磁盘空间但是运行起来更慢。VACUUM FULL要求在其工作的表上得到一个排他锁，因此无法和对此表的其他使用并行。
标准形式的VACUUM可以和生产数据库操作并行运行（SELECT、INSERT、UPDATE和DELETE等命令将继续正常工作，但在清理期间你无法使ALTER TABLE。
VACUUM[( { FULL | FREEZE | VERBOSE | ANALYZE| DISABLE_ PAGE_ SKIPPING }[,..] )][ table_ name [ (column_ name [,..] ) ]]
VACUUM[ FULL ][ FREEZE ] [ VERBOSE ] [ table_ name ]
VACUUM [ FULL][ FREEZE ][ VERBOSE ] ANALYZE [ table name [ (column name[...])]]
FULL ------选择"完全"清理，这样可以恢复更多的空间， 但是花的时间更多并且在表上施加了排它锁。
FREEZE ---------选择激进的元组"冻结"。
VERBOSE --------- 为每个表打印一份详细的清理工作报告。
ANALYZE --------- 更新用于优化器的统计信息，以决定执行查询的最有效方法。
table ------- 要清理的表的名称（可以有模式修饰）。缺省时是当前数据库中的所有表。
column ---------要分析的具体的列/字段名称。缺省是所有列/字段。
8.1.1 事例
1 使用客户端vacuumdb命令
完全清理与统计更新postgres数据库。
[postgres@Redhat7 ~]$ vacuumdb --full --verbose --analyze postgres;
标准清理与统计更新postgres数据库。
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze postgres;
统计更新postgres数据库。
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze-only postgres;
完全清理与统计更新postgres数据库中的test1表。
[postgres@Redhat7 ~]$ vacuumdb --full --verbose --analyze test1;
vacuumdb: could not connect to database test1: FATAL: database "test1" does not exist
[postgres@Redhat7 ~]$ vacuumdb --full --verbose --analyze --table test1 postgres;
vacuumdb: vacuuming database "postgres"
INFO: vacuuming "public.test1"
INFO: "test1": found 0 removable, 4194816 nonremovable row versions in 28343 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.74 s, system: 0.47 s, elapsed: 2.08 s.
INFO: analyzing "public.test1"
INFO: "test1": scanned 22675 of 22675 pages, containing 4194816 live rows and 0 dead rows; 30000 rows in sample, 4194816 estimated total rows
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze-only --table test1 postgres;
vacuumdb: vacuuming database "postgres"
INFO: analyzing "public.test1"
INFO: "test1": scanned 22675 of 22675 pages, containing 4194816 live rows and 0 dead rows; 30000 rows in sample, 4194816 estimated total rows
标准清理与统计更新postgres数据库中的test1表。
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze --table test1 postgres;
统计更新postgres数据库中的test1表。
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze-only --table test1 postgres;
vacuumdb: vacuuming database "postgres"
INFO: analyzing "public.test1"
INFO: "test1": scanned 22675 of 22675 pages, containing 4194816 live rows and 0 dead rows; 30000 rows in sample, 4194816 estimated total rows
2 psql vacuum 命令
完全清理并统计更新数据库
postgres=# vacuum full verbose analyze;
标准清理并统计更新数据库
postgres=# vacuum verbose analyze;
统计更新数据库
postgres=# analyze;
ANALYZE
完全清理并统计更新表test1
postgres=# vacuum full verbose analyze test1;
标准清理并统计更新表test1
postgres=# vacuum verbose analyze test1;
统计更新表
postgres=# analyze test1;
ANALYZE
8.2 索引维护
周期性地使用REINDEX命令或一系列独立重构步骤来重建索引，保持数据库最佳性能。
8.2.1 事例
1 使用客户端
reindexdb [ connection-option...] [ 选项......] [ --schema| -S``schema] ... [ --table| -t``table] ... [ --index| -i``索引] ... [ dbname]
reindexdb [ connection-option...] [ 选项......] --all | -a
reindexdb [ connection-option...] [ 选项......] --system | -s [ dbname]
-v --verbose 在处理时打印详细信息。
重建数据库postgres所有索引
[postgres@Redhat7 ~]$ reindexdb -v postgres
重建数据库postgres中test1的全部索引
[postgres@Redhat7 ~]$ reindexdb --verbose --table test1 postgres
INFO: index "idx_name" was reindexed
DETAIL: CPU: user: 1.53 s, system: 0.55 s, elapsed: 4.06 s
重建数据库postgres中test1中的idx_name索引
[postgres@Redhat7 ~]$ reindexdb --verbose --table test1 --index idx_name postgres
INFO: index "idx_name" was reindexed
DETAIL: CPU: user: 1.61 s, system: 0.34 s, elapsed: 4.00 s
2 psql reindex
大纲 REINDEX [ ( VERBOSE )] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name
postgres=# reindex DATABASE postgres;
REINDEX
postgres=# reindex table test1;
REINDEX
8.3 日志文件维护
把数据库服务器的日志输出保存在一个地方是个好主意，而不是仅仅通过/dev/null丢弃它们。在进行问题诊断的时候，日志输出是非常宝贵的。不过，日志输出可能很庞大(特别是在比较高的调试级别上)，因此你不会希望无休止地保存它们。你需要轮转日志文件，这样在一段合理的时间后会开始新的日志文件并且移除旧的。
如果你简单地把postgres的stderr定向到-一个文件中， 你会得到日志输出，但是截断该日志文件的唯一方法是停止并重起服务器。这样做对于开发环境中使用的PostgreSQL可能是可接受的，但是你肯定不想在生产环境上这么干。
8.4 处理数据库的膨胀bloat
处理bloat 使用pg_repack
Reorganize tables in PostgreSQL databases with minimal locks
PostgreSQL的表或索引发生膨胀后，用户可以使用vacuum full的方式重建表。但是vacuum full都需要持有排它锁，会堵塞读操作。为了减少锁冲突，社区有一个名为pg_reorg或pg_repack的插件，使用了增量的方式重组数据，最后通过切换FILENODE完成数据重组。仅仅在切换FILENODE时需要持有排他锁，非常短暂，影响比VACUUM FULL的方式小多了。
8.4.1 实验数据
postgres=# create database testdb;
CREATE DATABASE
testdb=# create table t1 (i int primary key,name varchar(24));
testdb=# insert into t1 select c,'a'||c from generate_series(1,1000000) as c;
INSERT 0 1000000
testdb=# select * from t1 LIMIT 10;
i | name
----+------
1 | a1
2 | a2
3 | a3
4 | a4
5 | a5
6 | a6
7 | a7
8 | a8
9 | a9
10 | a10
testdb=# select pg_size_pretty(pg_table_size('t1'));
pg_size_pretty
----------------
42 MB
(1 row)
testdb=# delete from t1 where i< 80000;
DELETE 79999
testdb=# select pg_size_pretty(pg_table_size('t1'));
pg_size_pretty
----------------
42 MB
(1 row)
testdb=# update t1 set name= 'c'||i where i<900000;
UPDATE 820000
testdb=# select pg_size_pretty(pg_table_size('t1'));
pg_size_pretty
----------------
77 MB
(1 row)
8.4.2 安装pg_bloat_check 和pg_repack
1 pg_bloat_check 安装
[root@RHCE7 pgstattuple]# cd /opt/pgsql11.4/postgresql-11.4/contrib/pgstattuple
Make
Make install
[postgres@RHCE7 ~]$ pg_ctl restart -D /pgdb
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# create extension pgstattuple;
CREATE EXTENSION
[root@RHCE7 ~]# yum install python-devel* -y
[root@RHCE7 ~]# yum install unzip* -y
[root@RHCE7 bin]# yum install grep python-psycopg2.x86_64 -y
[root@RHCE7 ~]# vim /etc/profile
export PATH=/opt/pgsql11.4/bin:$PATH
export ID_LIBRARY_PATH=/opt/pgsql11.4/lib
下载 pg_bloat_check-2.6.1.tar.gz
[root@RHCE7 opt]# tar zxvf pg_bloat_check-2.6.1.tar.gz
[root@RHCE7 pg_bloat_check-2.6.1]# cp -p pg_bloat_check.py /opt/pgsql11.4/bin/
[postgres@RHCE7 ~]$ export PGHOST=/tmp
[postgres@RHCE7 ~]$ pg_bloat_check.py -c dbname=testdb --create_stats_table
[postgres@RHCE7 ~]$ pg_bloat_check.py -c dbname=testdb -p 20 -s 5242880
-p 20 膨胀率20 –s 表大小大于5M。
1. public.t1 (t) ...............................................................(44.96%) 35 MB wasted
2. public.t1_pkey (p) ..........................................................(57.78%) 33 MB wasted
2 pg_repack 安装
[root@RHCE7 opt]# tar -zxvf pg_repack-ver_1.4.5.tar.gz
[root@RHCE7 pg_repack-ver_1.4.5]# make
[root@RHCE7 pg_repack-ver_1.4.5]# make install
testdb=# create extension pg_repack;
CREATE EXTENSION
[postgres@RHCE7 ~]$ pg_repack -d testdb -t t1
INFO: repacking table "public.t1"
[postgres@RHCE7 ~]$ pg_bloat_check.py -c dbname=testdb -p 20 -s 5242880
空数据
重组整个数据库
[postgres@RHCE7 ~]$ pg_repack testdb
INFO: repacking table "public.t1"
重组表
[postgres@RHCE7 ~]$ pg_repack --no-order --table t1 testdb
INFO: repacking table "public.t1
至重组索引
[postgres@RHCE7 ~]$ pg_repack --table t1 --only-indexes -d testdb;
INFO: repacking indexes of "t1"
INFO: repacking index "public.idx_name"
INFO: repacking index "public.t1_pkey"
8.5 查询慢sql
8.5.1 修改配置文件
logging_collector = on
收集日志打开
log_min_duration_statement = 1000
单位毫秒，如果语句运行超过设置的值，那么语句将被记录在日志里。1000就是1秒。
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h'
log_line_prefix 设置日志输出格式 参考pgbadger的官方文档
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default
8.5.2 安装pgbadger
[root@Redhat7 pgbadger-11.1]# perl Makefile.PL
Can't locate ExtUtils/MakeMaker.pm
[root@Redhat7 pgbadger-11.1]# yum install perl-ExtUtils-MakeMaker* -y
[root@Redhat7 pgbadger-11.1]# perl Makefile.PL
[root@Redhat7 pgbadger-11.1]# make
[root@Redhat7 pgbadger-11.1]# make test
[root@Redhat7 pgbadger-11.1]# make install
生产文件
[root@Redhat7 log]# pgbadger postgresql-2019-10-22_235313.log -o postgresql-2019-10-22.html -f stderr
LOG: Ok, generating html report... 1032 bytes of 1032 (100.00%), queries: 0, events: 1
655
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
