PostgreSQL教程(十四):数据库维护


一、恢复磁盘空间:

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

    VACUUM命令存在两种形式,VACUUM和VACUUM 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)
 



相关阅读:
JavaScript function函数种类详解
Win7系统运行速度变慢怎么办?提高运行速度的几个小方法
html中的javascript 全选/取消全选操作示例代码
AngularJS 入门教程之事件处理器详解
分享javascript计算时间差的示例代码
Win7无线Wifi无法连接怎么办?win7系统Wifi无法连接的三种故障原因及解决方法
Win10预览版用户无需预订正式版免费升级 Win10正式版升级策略
读取input:file的路径并显示本地图片的方法
C#用匿名方法定义委托的实现方法
配置Nginx+PHP的正确思路与过程
css3强大的动画效果animate使用说明及浏览器兼容介绍
详解Java多态对象的类型转换与动态绑定
css核心基础总结篇(推荐)
一漂亮的PHP图片验证码实例
快速导航
PHP MySQL HTML CSS JavaScript MSSQL AJAX .NET JSP Linux Mac ASP 服务器 CMS SQL jQuery C# C++ java Android IOS oracle MongoDB PostgreSQL SQLite 交通频道 G4722 G1875 G215 G569 G421 G6733 G7577 G8906 G1235 G4916 G7291 G1953 G245 G662 G1570 G6285 G719 G1836 G1346 G4781 G4908 G289 G6781 G9290 G7358 G1928 G1815 G325 G132 G4901 G6012 G6290 G7131 G5367 G184 G151 G5303 G1136 G6481 G7028 G575 G1744 G7660 G7693 G2344 G4937 G1234 G1814 G6252 G1492 G253 G2926 G883 G9275 G1231 G556 G241 G1306 G7646 G8103 G600 G1858 G9678 G6160 G7156 G825 G1125 G7249 G1809 G1350 G432 G9466 G7067 G785 G6404 G4663 G7008 G150 G823 G1514 G7529 G1201 G2353 G205 G7629 G9409 G6147 G677 G390 G8016 G9239 G456 G828 G8045 G491 G7145 G397 G7012 G1021 G6482 G2322 G7264 G1301 G9247 G96 G1294 G7133 G4824 G7005 G1653 G5307 G1213 G822 G4837 G1422 G411 G6227 G1571 G359 G1882 G6074 G7678 G21 G7077 G1272 G8918 G9645 G461 G1254 G1846 G8021 G7303 G1104 G76 G82 G621 G218 G8533 G2341 G8543 G555 G8013 G4802 G1364 G1153 G1342 G1861 G8905 G590 G4780 G668 G9261 G1304 G1638 G1395 G2914 G8003 G7158 G1833 G1873 G8128 G1856 G1841 G8709 G7346 G4612 G2103 G835 G8712 G381 G7240 G8932 G507 G29 G4054 G6273 G6752 G426 G211 G9473 G7119 G2333 G1567 G6153 G360 G4011 G5301 G7648 G8010 G8015 G6706 G614 G423 G8557 G9465 G72 G6018 G8901 G7030 G123

丹东 云霄 辽中 德阳 克拉玛依 惠山 招远 昭通 铁岭西 延吉西 军粮城北 定西 晋中 许昌东 郫县 诏安 七台河 高碑店东 南昌 延安 敦化 铜陵北 嵩明 鲘门 扬中 龙里北 舟山 洛阳 运城北 鞍山 西昌 邵阳北 绍兴 白山 三明 肇东 陵水 衡山西 嘉善 宜都 泰兴 泉州 汉口 东胜西 昌图西 锦州南 安阳东 怀化 黄南 亚龙湾 扬州 温州 南翔北 福安 金山北 永川东 安达 曲阜东 郑州西 天门 绍兴北 涪陵北 阳泉北 三亚 葫芦岛北 徐州 阳江 辽源 新泰 阿坝 孝感北 三穗 金寨 保山 高安 安阳 牟平 西双版纳 信阳 繁昌西 哈尔滨北 达州 新余 沈阳南 四平 扶余北 伊宁 郴州西 济源 水家湖 民权北 福鼎 如皋 奉化 全州南 安庆 太姥山 武汉 乐清 皮口 武昌 茂名 邯郸 资阳 马鞍山 三水南 泰安 包头东 衡阳东 南丰 仙桃西 安吉 罗源 山海关 平湖 惠州 资阳北 淄博 丹阳 莱州 巴东 关岭 盐城 锦州 格尔木 益阳 大英东 吉林 湛江 临安 襄汾西 渑池南 当涂东 辽阳 徐水 贺州 韶关 光明城 邯郸东 普安县 南江口 铜川 五龙背东 张家港 烟台南 萍乡北 青堆 长乐 江门 台州 衡水 湘潭北 闽清北 高邑西 盖州西 石柱县 潮汕 肇庆 泰康 邵东 湖州 余姚 平凉 宜宾 增城 沧州 都匀 防城港 鹰潭北 海东西 福田 余姚北 岳池 广州北 南安 蓬莱 瓦房店西 李石寨 葛店南 海安 无锡东 上饶 通辽 四会 桂林西 砀山南 兰州 滨海 龙口 绅坊 莱西 石林西 深圳 大连北 成都 上海西 孝感 杏树屯 德清 嘉兴

Copyright © 2016 phpStudy |