成人系列视频,午夜日韩视频,午夜久久免费观看

PostgreSQL 數據庫維護

　一、恢復磁盤空間：

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

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

　　二、更新規劃器統計：

　　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

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

(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

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

(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

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

(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)

posted on 2014-03-07 10:59 順其自然EVO 閱讀(660) 評論(0) 編輯收藏所屬分類: 數據庫

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: MySQL數字輔助表 PLSQL Developer鮮為人知的快捷鍵淘寶分布式配置管理服務Diamond iOS開發之FMDB AlwaysOn可用性組功能測試(2)-SQL Server群集故障轉移 SQL Server 2008 (R2) 單機版安裝的先決條件 AlwaysOn可用性組功能測試(2)-SQL Server群集故障轉移聊聊Oracle外鍵約束的幾個操作選項 SQL優化中索引列使用函數之靈異事件利用binlog進行數據庫的還原

qileilove

PostgreSQL 數據庫維護

導航

統計

常用鏈接

留言簿(55)

隨筆分類

隨筆檔案

文章分類

文章檔案

搜索

最新評論

閱讀排行榜

評論排行榜