PostgreSQL教程(十四)：數(shù)據(jù)庫(kù)維護(hù)

更新時(shí)間：2015年05月04日 10:49:20 投稿：junjie

這篇文章主要介紹了PostgreSQL教程(十四)：數(shù)據(jù)庫(kù)維護(hù),本文講解了恢復(fù)磁盤空間、更新規(guī)劃器統(tǒng)計(jì)、VACUUM和ANALYZE的示例、定期重建索引等內(nèi)容,需要的朋友可以參考下

一、恢復(fù)磁盤空間：

在PostgreSQL中，使用delete和update語(yǔ)句刪除或更新的數(shù)據(jù)行并沒有被實(shí)際刪除，而只是在舊版本數(shù)據(jù)行的物理地址上將該行的狀態(tài)置為已刪除或已過期。因此當(dāng)數(shù)據(jù)表中的數(shù)據(jù)變化極為頻繁時(shí)，那么在一段時(shí)間之后該表所占用的空間將會(huì)變得很大，然而數(shù)據(jù)量卻可能變化不大。要解決該問題，需要定期對(duì)數(shù)據(jù)變化頻繁的數(shù)據(jù)表執(zhí)行VACUUM操作。

VACUUM命令存在兩種形式，VACUUM和VACUUM FULL，它們之間的區(qū)別見如下表格：

	無(wú)VACUUM	VACUUM	VACUUM FULL
刪除大量數(shù)據(jù)之后	只是將刪除數(shù)據(jù)的狀態(tài)置為已刪除，該空間不能記錄被重新使用。	如果刪除的記錄位于表的末端，其所占用的空間將會(huì)被物理釋放并歸還操作系統(tǒng)。如果不是末端數(shù)據(jù)，該命令會(huì)將指定表或索引中被刪除數(shù)據(jù)所占用空間重新置為可用狀態(tài)，那么在今后有新數(shù)據(jù)插入時(shí)，將優(yōu)先使用該空間，直到所有被重用的空間用完時(shí)，再考慮使用新增的磁盤頁(yè)面。	不論被刪除的數(shù)據(jù)是否處于數(shù)據(jù)表的末端，這些數(shù)據(jù)所占用的空間都將被物理的釋放并歸還于操作系統(tǒng)。之后再有新數(shù)據(jù)插入時(shí)，將分配新的磁盤頁(yè)面以供使用。
執(zhí)行效率		由于只是狀態(tài)置為操作，因此效率較高。	在當(dāng)前版本的PostgreSQL(v9.1)中，該命令會(huì)為指定的表或索引重新生成一個(gè)數(shù)據(jù)文件，并將原有文件中可用的數(shù)據(jù)導(dǎo)入到新文件中，之后再刪除原來的數(shù)據(jù)文件。因此在導(dǎo)入過程中，要求當(dāng)前磁盤有更多的空間可用于此操作。由此可見，該命令的執(zhí)行效率相對(duì)較低。
被刪除的數(shù)據(jù)所占用的物理空間是否被重新規(guī)劃給操作系統(tǒng)。	不會(huì)	不會(huì)	會(huì)
在執(zhí)行VACUUM命令時(shí)，是否可以并發(fā)執(zhí)行針對(duì)該表的其他操作。		由于該操作是共享鎖，因此可以與其他操作并行進(jìn)行。	由于該操作需要在指定的表上應(yīng)用排它鎖，因此在執(zhí)行該操作期間，任何基于該表的操作都將被掛起，知道該操作完成。
推薦使用方式	在進(jìn)行數(shù)據(jù)清空是，可以使用truncate操作，因?yàn)樵摬僮鲗?huì)物理的清空數(shù)據(jù)表，并將其所占用的空間直接歸還于操作系統(tǒng)。	為了保證數(shù)據(jù)表的磁盤頁(yè)面數(shù)量能夠保持在一個(gè)相對(duì)穩(wěn)定值，可以定期執(zhí)行該操作，如每天或每周中數(shù)據(jù)操作相對(duì)較少的時(shí)段。	考慮到該操作的開銷，以及對(duì)其他錯(cuò)誤的排斥，推薦的方式是，定期監(jiān)控?cái)?shù)據(jù)量變化較大的表，只有確認(rèn)其磁盤頁(yè)面占有量接近臨界值時(shí)，才考慮執(zhí)行一次該操作。即便如此，也需要注意盡量選擇數(shù)據(jù)操作較少的時(shí)段來完成該操作。
執(zhí)行后其它操作的效率	對(duì)于查詢而言，由于存在大量的磁盤頁(yè)面碎片，因此效率會(huì)逐步降低。	相比于不執(zhí)行任何VACUUM操作，其效率更高，但是插入的效率會(huì)有所降低。	在執(zhí)行完該操作后，所有基于該表的操作效率都會(huì)得到極大的提升。

二、更新規(guī)劃器統(tǒng)計(jì)：

PostgreSQL查詢規(guī)劃器在選擇最優(yōu)路徑時(shí)，需要參照相關(guān)數(shù)據(jù)表的統(tǒng)計(jì)信息用以為查詢生成最合理的規(guī)劃。這些統(tǒng)計(jì)是通過ANALYZE命令獲得的，你可以直接調(diào)用該命令，或者把它當(dāng)做VACUUM命令里的一個(gè)可選步驟來調(diào)用，如VACUUM ANAYLYZE table_name，該命令將會(huì)先執(zhí)行VACUUM再執(zhí)行ANALYZE。與回收空間(VACUUM)一樣，對(duì)數(shù)據(jù)更新頻繁的表保持一定頻度的ANALYZE，從而使該表的統(tǒng)計(jì)信息始終處于相對(duì)較新的狀態(tài)，這樣對(duì)于基于該表的查詢優(yōu)化將是極為有利的。然而對(duì)于更新并不頻繁的數(shù)據(jù)表，則不需要執(zhí)行該操作。

我們可以為特定的表，甚至是表中特定的字段運(yùn)行ANALYZE命令，這樣我們就可以根據(jù)實(shí)際情況，只對(duì)更新比較頻繁的部分信息執(zhí)行ANALYZE操作，這樣不僅可以節(jié)省統(tǒng)計(jì)信息所占用的空間，也可以提高本次ANALYZE操作的執(zhí)行效率。這里需要額外說明的是，ANALYZE是一項(xiàng)相當(dāng)快的操作，即使是在數(shù)據(jù)量較大的表上也是如此，因?yàn)樗褂昧私y(tǒng)計(jì)學(xué)上的隨機(jī)采樣的方法進(jìn)行行采樣，而不是把每一行數(shù)據(jù)都讀取進(jìn)來并進(jìn)行分析。因此，可以考慮定期對(duì)整個(gè)數(shù)據(jù)庫(kù)執(zhí)行該命令。

事實(shí)上，我們甚至可以通過下面的命令來調(diào)整指定字段的抽樣率，如：

復(fù)制代碼代碼如下:

ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200

注意：該值的取值范圍是0--1000，其中值越低采樣比例就越低，分析結(jié)果的準(zhǔn)確性也就越低，但是ANALYZE命令執(zhí)行的速度卻更快。如果將該值設(shè)置為-1，那么該字段的采樣比率將恢復(fù)到系統(tǒng)當(dāng)前默認(rèn)的采樣值，我們可以通過下面的命令獲取當(dāng)前系統(tǒng)的缺省采樣值。

復(fù)制代碼代碼如下:

    postgres=# show default_statistics_target;

     default_statistics_target

    ---------------------------

     100

    (1 row)

從上面的結(jié)果可以看出，該數(shù)據(jù)庫(kù)的缺省采樣值為100(10%)。

三、VACUUM和ANALYZE的示例：

復(fù)制代碼代碼如下:

    #1. 創(chuàng)建測(cè)試數(shù)據(jù)表。
    postgres=# CREATE TABLE testtable (i integer);
    CREATE TABLE
    #2. 為測(cè)試表創(chuàng)建索引。
    postgres=# CREATE INDEX testtable_idx ON testtable(i);
    CREATE INDEX
    #3. 創(chuàng)建批量插入測(cè)試數(shù)據(jù)的函數(shù)。
    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. 批量插入數(shù)據(jù)到測(cè)試表(執(zhí)行四次)
    postgres=# SELECT test_insert();
     test_insert
    -------------
               0
    (1 row)
    #5. 確認(rèn)四次批量插入都成功。
    postgres=# SELECT COUNT(*) FROM testtable;
     count
    -------
     40004
    (1 row)
    #6. 分析測(cè)試表，以便有關(guān)該表的統(tǒng)計(jì)信息被更新到PostgreSQL的系統(tǒng)表。
    postgres=# ANALYZE testtable;
    ANALYZE
    #7. 查看測(cè)試表和索引當(dāng)前占用的頁(yè)面數(shù)量(通常每個(gè)頁(yè)面為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. 批量刪除數(shù)據(jù)。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 30003
    #9. 執(zhí)行vacuum和analyze，以便更新系統(tǒng)表，同時(shí)為該表和索引記錄高水標(biāo)記。
    #10. 這里需要額外說明的是，上面刪除的數(shù)據(jù)均位于數(shù)據(jù)表的前部，如果刪除的是末尾部分，
    #      如where i > 10000，那么在執(zhí)行VACUUM ANALYZE的時(shí)候，數(shù)據(jù)表將會(huì)被物理的縮小。
    postgres=# VACUUM ANALYZE testtable;
    ANALYZE
    #11. 查看測(cè)試表和索引在刪除后，再通過VACUUM ANALYZE更新系統(tǒng)統(tǒng)計(jì)信息后的結(jié)果(保持不變)。
    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. 再重新批量插入兩次，之后在分析該表以更新其統(tǒng)計(jì)信息。
    postgres=# SELECT test_insert(); --執(zhí)行兩次。
     test_insert
    -------------
               0
    (1 row)
    postgres=# ANALYZE testtable;
    ANALYZE
    #13. 此時(shí)可以看到數(shù)據(jù)表中的頁(yè)面數(shù)量仍然為之前的高水標(biāo)記數(shù)量，索引頁(yè)面數(shù)量的增加
    #      是和其內(nèi)部實(shí)現(xiàn)方式有關(guān)，但是在后面的插入中，索引所占的頁(yè)面數(shù)量就不會(huì)繼續(xù)增加。
    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. 可以看到索引的頁(yè)面數(shù)量確實(shí)沒有繼續(xù)增加。
    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. 重新批量刪除數(shù)據(jù)。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 19996
    #16. 從后面的查詢可以看出，在執(zhí)行VACUUM FULL命令之后，測(cè)試表和索引所占用的頁(yè)面數(shù)量
    #      確實(shí)降低了，說明它們占用的物理空間已經(jīng)縮小了。
    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中，為數(shù)據(jù)更新頻繁的數(shù)據(jù)表定期重建索引(REINDEX INDEX)是非常有必要的。對(duì)于B-Tree索引，只有那些已經(jīng)完全清空的索引頁(yè)才會(huì)得到重復(fù)使用，對(duì)于那些僅部分空間可用的索引頁(yè)將不會(huì)得到重用，如果一個(gè)頁(yè)面中大多數(shù)索引鍵值都被刪除，只留下很少的一部分，那么該頁(yè)將不會(huì)被釋放并重用。在這種極端的情況下，由于每個(gè)索引頁(yè)面的利用率極低，一旦數(shù)據(jù)量顯著增加，將會(huì)導(dǎo)致索引文件變得極為龐大，不僅降低了查詢效率，而且還存在整個(gè)磁盤空間被完全填滿的危險(xiǎn)。
    對(duì)于重建后的索引還存在另外一個(gè)性能上的優(yōu)勢(shì)，因?yàn)樵谛陆⒌乃饕?，邏輯上相互連接的頁(yè)面在物理上往往也是連在一起的，這樣可以提高磁盤頁(yè)面被連續(xù)讀取的幾率，從而提高整個(gè)操作的IO效率。見如下示例：
    #1. 此時(shí)已經(jīng)在該表中插入了大約6萬(wàn)條數(shù)據(jù)，下面的SQL語(yǔ)句將查詢?cè)撍饕加玫拇疟P空間。

復(fù)制代碼代碼如下:

    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. 刪除數(shù)據(jù)表中大多數(shù)的數(shù)據(jù)。

    postgres=# DELETE FROM testtable WHERE i > 20000;

    DELETE 50006

    #3. 分析一個(gè)該表，以便于后面的SQL語(yǔ)句繼續(xù)查看該索引占用的空間。

    postgres=# ANALYZE testtable;

    ANALYZE

    #4. 從該查詢結(jié)果可以看出，該索引所占用的空間并未減少，而是和之前的完全一樣。

    postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;

     size

    ------

     1240K

    (1 row)

    #5. 重建索引。

    postgres=# REINDEX INDEX testtable_idx;

    REINDEX

    #6. 查看重建后的索引實(shí)際占用的空間，從結(jié)果中可以看出索引的尺寸已經(jīng)減少。

    postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;

     size

    ------

     368K

    (1 row)

    #7. 最后一點(diǎn)需要記住的是，在索引重建后一定要分析數(shù)據(jù)表。

    postgres=# ANALYZE testtable;

    ANALYZE

五、觀察磁盤使用情況：

1. 查看數(shù)據(jù)表所占用的磁盤頁(yè)面數(shù)量。

復(fù)制代碼代碼如下:

    #relpages只能被VACUUM、ANALYZE和幾個(gè)DDL命令更新，如CREATE INDEX。通常一個(gè)頁(yè)面的長(zhǎng)度為8K字節(jié)。

    postgres=# SELECT relfilenode, relpages FROM pg_class WHERE relname = 'testtable';

     relfilenode | relpages

    -------------+----------

           16412 |       79

    (1 row)

2. 查看指定數(shù)據(jù)表的索引名稱和索引占用的磁盤頁(yè)面數(shù)量。

復(fù)制代碼代碼如下:

    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)

您可能感興趣的文章: