文档章节

PostgreSQL学习手册(数据库维护)

Junn
 Junn
发布于 2016/01/07 10:43
字数 2846
阅读 22
收藏 0
点赞 0
评论 0

一、恢复磁盘空间:

    在PostgreSQL中,使用delete和update语句删除或更新的数据行并没有被实际删除,而只是在旧版本数据行的物理地址上将该行的状态置 为已删除或已过期。因此当数据表中的数据变化极为频繁时,那么在一段时间之后该表所占用的空间将会变得很大,然而数据量却可能变化不大。要解决该问题,需 要定期对数据变化频繁的数据表执行VACUUM操作。
    VACUUM命令存在两种形式,VACUUMVACUUM FULL,它们之间的区别见如下表格:

  无VACUUM VACUUM VACUUM FULL
删除大量数据之后 只是将删除数据的状态置为已删除,该空间不能记录被重新使用。 如果删除的记录位于表的末端,其所占用的空间将会被物理释放并归还操作系统。如果不是末端数据,该命令会将指定表或索引中被删除数据所占用空间重新置为可用状态,那么在今后有新数据插入时,将优先使用该空间,直到所有被重用的空间用完时,再考虑使用新增的磁盘页面。 不论被删除的数据是否处于数据表的末端,这些数据所占用的空间都将被物理的释放并归还于操作系统。之后再有新数据插入时,将分配新的磁盘页面以供使用。
执行效率   由于只是状态置为操作,因此效率较高。 在当前版本的PostgreSQL(v9.1)中,该命令会为指定的表或索引重新生成一个数据文件,并将原有文件中可用的数据导入到新文件中,之后再删除原来的数据文件。因此在导入过程中,要求当前磁盘有更多的空间可用于此操作。由此可见,该命令的执行效率相对较低。
被删除的数据所占用的物理空间是否被重新规划给操作系统。 不会 不会
在执行VACUUM命令时,是否可以并发执行针对该表的其他操作。   由于该操作是共享锁,因此可以与其他操作并行进行。 由于该操作需要在指定的表上应用排它锁,因此在执行该操作期间,任何基于该表的操作都将被挂起,知道该操作完成。
推荐使用方式 在进行数据清空是,可以使用truncate操作,因为该操作将会物理的清空数据表,并将其所占用的空间直接归还于操作系统。 为了保证数据表的磁盘页面数量能够保持在一个相对稳定值,可以定期执行该操作,如每天或每周中数据操作相对较少的时段。 考虑到该操作的开销,以及对其他错误的排斥,推荐的方式是,定期监控数据量变化较大的表,只有确认其磁盘页面占有量接近临界值时,才考虑执行一次该操作。即便如此,也需要注意尽量选择数据操作较少的时段来完成该操作。
执行后其它操作的效率 对于查询而言,由于存在大量的磁盘页面碎片,因此效率会逐步降低。 相比于不执行任何VACUUM操作,其效率更高,但是插入的效率会有所降低。 在执行完该操作后,所有基于该表的操作效率都会得到极大的提升。

 

二、更新规划器统计:

    PostgreSQL查询规划器在选择最优路径时,需要参照相关数据表的统计信息用以为查询生成最合理的规划。这些统计是通过ANALYZE命令获得的,你可以直接调用该命令,或者把它当做VACUUM命令里的一个可选步骤来调用,如VACUUM ANAYLYZE table_name, 该命令将会先执行VACUUM再执行ANALYZE。与回收空间(VACUUM)一样,对数据更新频繁的表保持一定频度的ANALYZE,从而使该表的统 计信息始终处于相对较新的状态,这样对于基于该表的查询优化将是极为有利的。然而对于更新并不频繁的数据表,则不需要执行该操作。
    我们可以为特定的表,甚至是表中特定的字段运行ANALYZE命令,这样我们就可以根据实际情况,只对更新比较频繁的部分信息执行ANALYZE操作, 这样不仅可以节省统计信息所占用的空间,也可以提高本次ANALYZE操作的执行效率。这里需要额外说明的是,ANALYZE是一项相当快的操作,即使是 在数据量较大的表上也是如此,因为它使用了统计学上的随机采样的方法进行行采样,而不是把每一行数据都读取进来并进行分析。因此,可以考虑定期对整个数据 库执行该命令。
    事实上,我们甚至可以通过下面的命令来调整指定字段的抽样率,如:
    ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200
    注意:该值的取值范围是0--1000,其中值越低采样比例就越低,分析结果的准确性也就越低,但是ANALYZE命令执行的速度却更快。如果将该值设置为-1,那么该字段的采样比率将恢复到系统当前默认的采样值,我们可以通过下面的命令获取当前系统的缺省采样值。
    postgres=# show default_statistics_target;
     default_statistics_target
    ---------------------------
     100
    (1 row)
    从上面的结果可以看出,该数据库的缺省采样值为100(10%)。


三、VACUUM和ANALYZE的示例:
    

    #1. 创建测试数据表。
    postgres=# CREATE TABLE testtable (i integer);
    CREATE TABLE
    #2. 为测试表创建索引。
    postgres=# CREATE INDEX testtable_idx ON testtable(i);
    CREATE INDEX
    #3. 创建批量插入测试数据的函数。
    postgres=# CREATE OR REPLACE FUNCTION test_insert() returns integer AS $$
    DECLARE
        min integer;
        max integer;
    BEGIN
        SELECT COUNT(*) INTO min from testtable;
        max := min + 10000;
        FOR i IN min..max LOOP
            INSERT INTO testtable VALUES(i);
        END LOOP;
        RETURN 0;
    END;
    $$ LANGUAGE plpgsql;
    CREATE FUNCTION
    #4. 批量插入数据到测试表(执行四次)
    postgres=# SELECT test_insert();
     test_insert
    -------------
               0
    (1 row)
    #5. 确认四次批量插入都成功。
    postgres=# SELECT COUNT(*) FROM testtable;
     count
    -------
     40004
    (1 row)
    #6. 分析测试表,以便有关该表的统计信息被更新到PostgreSQL的系统表。
    postgres=# ANALYZE testtable;
    ANALYZE
    #7. 查看测试表和索引当前占用的页面数量(通常每个页面为8k)。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname       | relfilenode    | relpages
    ---------------+-------------+----------
     testtable        |       17601   |      157
     testtable_idx  |       17604   |       90
    #8. 批量删除数据。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 30003
    #9. 执行vacuum和analyze,以便更新系统表,同时为该表和索引记录高水标记。
    #10. 这里需要额外说明的是,上面删除的数据均位于数据表的前部,如果删除的是末尾部分,
    #      如where i > 10000,那么在执行VACUUM ANALYZE的时候,数据表将会被物理的缩小。
    postgres=# VACUUM ANALYZE testtable;
    ANALYZE
    #11. 查看测试表和索引在删除后,再通过VACUUM ANALYZE更新系统统计信息后的结果(保持不变)。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname      | relfilenode     | relpages
    ---------------+-------------+----------
     testtable        |       17601   |      157
     testtable_idx  |       17604   |       90
    (2 rows)
    #12. 再重新批量插入两次,之后在分析该表以更新其统计信息。
    postgres=# SELECT test_insert(); --执行两次。
     test_insert
    -------------
               0
    (1 row)
    postgres=# ANALYZE testtable;
    ANALYZE
    #13. 此时可以看到数据表中的页面数量仍然为之前的高水标记数量,索引页面数量的增加
    #      是和其内部实现方式有关,但是在后面的插入中,索引所占的页面数量就不会继续增加。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname       | relfilenode    | relpages
    ---------------+-------------+----------
     testtable        |       17601   |      157
     testtable_idx  |       17604   |      173
    (2 rows)
    postgres=# SELECT test_insert();
     test_insert
    -------------
               0
    (1 row)
    postgres=# ANALYZE testtable;
    ANALYZE
    #14. 可以看到索引的页面数量确实没有继续增加。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname      | relfilenode    | relpages
    ---------------+-------------+----------
     testtable        |       17601   |      157
     testtable_idx  |       17604   |      173
    (2 rows)
    #15. 重新批量删除数据。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 19996
    #16. 从后面的查询可以看出,在执行VACUUM FULL命令之后,测试表和索引所占用的页面数量
    #      确实降低了,说明它们占用的物理空间已经缩小了。
    postgres=# VACUUM FULL testtable;
    VACUUM
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname      | relfilenode     | relpages
    ---------------+-------------+----------
     testtable        |       17602   |      118
     testtable_idx  |       17605   |       68
    (2 rows)

四、定期重建索引:

    在PostgreSQL中,为数据更新频繁的数据表定期重建索引 (REINDEX INDEX)是 非常有必要的。对于B-Tree索引,只有那些已经完全清空的索引页才会得到重复使用,对于那些仅部分空间可用的索引页将不会得到重用,如果一个页面中大 多数索引键值都被删除,只留下很少的一部分,那么该页将不会被释放并重用。在这种极端的情况下,由于每个索引页面的利用率极低,一旦数据量显著增加,将会 导致索引文件变得极为庞大,不仅降低了查询效率,而且还存在整个磁盘空间被完全填满的危险。
    对于重建后的索引还存在另外一个性能上的优势,因为在新建立的索引上,逻辑上相互连接的页面在物理上往往也是连在一起的,这样可以提高磁盘页面被连续读取的几率,从而提高整个操作的IO效率。见如下示例:
    #1. 此时已经在该表中插入了大约6万条数据,下面的SQL语句将查询该索引所占用的磁盘空间。    
    postgres=# SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx';
        relname     | size
    ----------------+------
     testtable_idx | 1240K
    (1 row)
     #2. 删除数据表中大多数的数据。
     postgres=# DELETE FROM testtable WHERE i > 20000;
    DELETE 50006
     #3. 分析一个该表,以便于后面的SQL语句继续查看该索引占用的空间。
     postgres=# ANALYZE testtable;
    ANALYZE
     #4. 从该查询结果可以看出,该索引所占用的空间并未减少,而是和之前的完全一样。
     postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
     size
    ------
     1240K
    (1 row)
    #5. 重建索引。
     postgres=# REINDEX INDEX testtable_idx;
    REINDEX
     #6. 查看重建后的索引实际占用的空间,从结果中可以看出索引的尺寸已经减少。
     postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
     size
    ------
     368K
    (1 row)
     #7. 最后一点需要记住的是,在索引重建后一定要分析数据表。
     postgres=# ANALYZE testtable;
    ANALYZE

 五、观察磁盘使用情况:

     1. 查看数据表所占用的磁盘页面数量。
    #relpages只能被VACUUM、ANALYZE和几个DDL命令更新,如CREATE INDEX。通常一个页面的长度为8K字节。
    postgres=# SELECT relfilenode, relpages FROM pg_class WHERE relname = 'testtable';
     relfilenode | relpages
    -------------+----------
           16412 |       79
    (1 row)
    
     2. 查看指定数据表的索引名称和索引占用的磁盘页面数量。
    postgres=# SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i
        WHERE c.relname = 'testtable' AND c.oid = i.indrelid AND c2.oid = i.indexrelid
        ORDER BY c2.relname;
        relname    | relpages
    ---------------+----------
     testtable_idx |       46
    (1 row)

本文转载自:http://www.cnblogs.com/stephen-liu74/archive/2011/12/27/2304155.html

共有 人打赏支持
Junn
粉丝 145
博文 372
码字总数 288918
作品 0
海淀
高级程序员
Oracle DBA 增值 PostgreSQL,Greenplum 学习计划

标签 PostgreSQL , Oracle , Greenplum 背景 去O很大程度上是国家层面的战略考虑,比如斯诺登事件,最近贸易战的“中兴”事件,使得去O成为一个不可不做的事情。 但是去O喊了若干年,并没有真...

德哥 ⋅ 05/06 ⋅ 0

PostgreSQL 数据库初体验

高强,“DBA+济南群”联合发起人。现就职于山东华鲁科技发展股份有限公司。擅长Oracle、AIX、Linux、PostgreSQL和DB2等产品的实施、运维和故障处理。曾是一名存储工程师,负责实施存储、双机...

高强 ⋅ 2015/10/15 ⋅ 0

数据库案例集锦 - 开发者的《如来神掌》

标签 PostgreSQL , PG DBA cookbook , PG Oracle兼容性 , PG 架构师 cookbook , PG 开发者 cookbook , PG 应用案例 背景 「剑魔独孤求败,纵横江湖三十馀载,杀尽仇寇,败尽英雄,天下更无抗...

德哥 ⋅ 2017/06/09 ⋅ 0

PostgreSQL 11 preview - Allow on-line enabling and disabling of data checksums

标签 PostgreSQL , checksum , online modify , pgverifychecksums , pgenabledatachecksums , pgdisabledatachecksums 背景 PostgreSQL的数据文件是以数据块组织的,由于数据块可能比文件系......

德哥 ⋅ 05/06 ⋅ 0

PostgreSQL和MySQL

翻译来源:https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/ PostgreSQL和MySQL 之间有着根本的区别。在评估两个系统之间的差异和折衷之后,必须做出明智的决定。 我们已经提...

悟道之客 ⋅ 05/04 ⋅ 0

Windows下安装postgresql10的详细步骤解析

本例安装postgresql-10.4-1-windows-x64 postgresql中文社区下载地址:http://www.postgres.cn/download/ postgresql官网下载地址:https://www.postgresql.org/download/ 由于网络问题,本例......

dxzou ⋅ 06/11 ⋅ 0

PostgreSQL 相似文本检索与去重 - (银屑病怎么治?银屑病怎么治疗?银屑病怎么治疗好?银屑病怎么能治疗好?)

标签 PostgreSQL , 相似字符串 , 全文检索 , 去重 , 相似问题 , 医疗 , plr , plpython , madlib , 文本处理 背景 在云栖社区的问答区,有一位网友提到有一个问题: 解这个问题的思路 1. 首先...

德哥 ⋅ 04/18 ⋅ 0

PostgreSQL 类微博FEED系统 - 设计与性能指标

标签 PostgreSQL , feed , 微博 , 推送 , 分区 , 分片 , UDF , 挖掘 , 文本挖掘 背景 类微博系统,最频繁用到的功能: 之前写过一篇《三体高可用PCC大赛 - facebook微博 like场景 - 数据库设...

德哥 ⋅ 04/18 ⋅ 0

PostgreSQL 恢复大法 - 恢复部分数据库、跳过坏块、修复无法启动的数据库

标签 PostgreSQL , 恢复部分数据库 , 跳过坏块 , 修复无法启动的数据库 , 时间点恢复 , 逻辑备份 , 连续备份 背景 一个较大的数据库,如何只恢复一部分数据(例如只恢复某个DB)。 如果访问有...

德哥 ⋅ 04/18 ⋅ 0

科普一种可以将PG变成通用SQL引擎的技术

作者介绍 伊翼,网名“小wing”,野生PG爱好者,从事数据库相关工作已近十年,目前供职于全球最大的通讯设备供应商。 原标题:《当FDW遇上GO》 FDW(Foreign Data Wrapper)是PostgreSQL(下...

伊翼 ⋅ 06/14 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Qt中的坑--QTreeWidget添加item 不能显示出来

QTreeWidget* pTree = ui.TreeCheckList; QTreeWidgetItem* item = new QTreeWidgetItem(pTree) ;item->setText ( 0, "test" );pTree->addTopLevelItem (item ); 原因是因为创建一个......

k91191 ⋅ 18分钟前 ⋅ 0

使用Guava的RateLimiter做限流

场景: 1. 在日常生活中,我们肯定收到过不少不少这样的短信,“京东最新优惠卷…”,“天猫送您…”。这种类型的短信是属于推广性质的短信。这种短信一般群发量会到千万级别。然而,要完成这...

wind2012 ⋅ 18分钟前 ⋅ 0

QSlider重新enterEvent

#ifndef DIALOG_H#define DIALOG_H#include <QDialog>namespace Ui {class Dialog;}class Dialog : public QDialog{ Q_OBJECTpublic: explicit Dialog(QW......

xxdd ⋅ 19分钟前 ⋅ 0

生产环境redis备份与恢复

生产环境redis备份与恢复 Tyrant0532 0人评论 1563人阅读 2018-02-01 20:34:10 redis是一个开源(BSD许可),内存存储的数据结构服务器,可用作数据库,高速缓存和消息队列代理。生产中我们主...

rootliu ⋅ 21分钟前 ⋅ 0

nginx中出现403forbidden错误

nginx “403 Forbidden” 错误 出现这个错误一般是因为以下原因: 网站禁止特定的用户访问所有内容,例:网站屏蔽某个ip访问。 访问禁止目录浏览的目录,例:设置autoindex off后访问目录。 ...

河图再现 ⋅ 21分钟前 ⋅ 0

上海云栖:金融政企行业的CDN最佳实践

摘要: 在刚刚结束的上海云栖大会飞天技术汇分论坛上,阿里云视频云产品架构师罗小飞进行了《阿里云CDN——面向金融政企的CDN最佳实践》主题分享,为上海的嘉宾介绍CDN的解决方案与技术服务体...

猫耳m ⋅ 26分钟前 ⋅ 0

docker 基本操作

docker介绍 Docker项目提供了构建在Linux内核功能之上,协同在一起的的高级工具。其目标是帮助开发和运维人员更容易地跨系统跨主机交付应用程序和他们的依赖。Docker通过Docker容器,一个安全...

haoyuehong ⋅ 27分钟前 ⋅ 0

上海云栖:金融政企行业的CDN最佳实践

摘要: 在刚刚结束的上海云栖大会飞天技术汇分论坛上,阿里云视频云产品架构师罗小飞进行了《阿里云CDN——面向金融政企的CDN最佳实践》主题分享,为上海的嘉宾介绍CDN的解决方案与技术服务体...

阿里云云栖社区 ⋅ 30分钟前 ⋅ 0

安装与配置hadoop

一、CentOS7安装 java8,参考centos7.0 安装java1.8,tomcat 二、安装hadoop 版本V3.03 1、下载并解压hadoop # mkdir /usr/local/app# mkdir /usr/local/app/hadoop# cd /usr/local/app/had......

iturtle ⋅ 31分钟前 ⋅ 0

Idea设置Serializable自动生成

File --> Settings --> Editor --> Inspections ->Serialization issues,在该项下找到“Serializable class without 'serialVersionUID' ”并勾选...

Gmupload ⋅ 34分钟前 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部