精品久久影院,亚洲第一区中文99精品,亚洲国产高清在线观看视频

blog已經(jīng)轉(zhuǎn)移至github,大家請(qǐng)?jiān)L問(wèn) http://qaseven.github.io/

PostgreSQL 數(shù)據(jù)庫(kù)維護(hù)

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

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

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

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

　　PostgreSQL查詢規(guī)劃器在選擇最優(yōu)路徑時(shí)，需要參照相關(guān)數(shù)據(jù)表的統(tǒng)計(jì)信息用以為查詢生成最合理的規(guī)劃。這些統(tǒng)計(jì)是通過(guò)ANALYZE命令獲得的，你可以直接調(diào)用該命令，或者把它當(dāng)做VACUUM命令里的一個(gè)可選步驟來(lái)調(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í)行效率。這里需要額外說(shuō)明的是，ANALYZE是一項(xiàng)相當(dāng)快的操作，即使是在數(shù)據(jù)量較大的表上也是如此，因?yàn)樗褂昧私y(tǒng)計(jì)學(xué)上的隨機(jī)采樣的方法進(jìn)行行采樣，而不是把每一行數(shù)據(jù)都讀取進(jìn)來(lái)并進(jìn)行分析。因此，可以考慮定期對(duì)整個(gè)數(shù)據(jù)庫(kù)執(zhí)行該命令。

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

　　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)的采樣值，我們可以通過(guò)下面的命令獲取當(dāng)前系統(tǒng)的缺省采樣值。

postgres=# show default_statistics_target;

default_statistics_target

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

100

(1 row)

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

#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

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

(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. 這里需要額外說(shuō)明的是，上面刪除的數(shù)據(jù)均位于數(shù)據(jù)表的前部，如果刪除的是末尾部分，

# 如where i > 10000，那么在執(zhí)行VACUUM ANALYZE的時(shí)候，數(shù)據(jù)表將會(huì)被物理的縮小。

postgres=# VACUUM ANALYZE testtable;

ANALYZE

#11. 查看測(cè)試表和索引在刪除后，再通過(guò)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

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

(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

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

(1 row)

postgres=# ANALYZE testtable;

ANALYZE

#14. 可以看到索引的頁(yè)面數(shù)量確實(shí)沒(méi)有繼續(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í)降低了，說(shuō)明它們占用的物理空間已經(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è)磁盤(pán)空間被完全填滿的危險(xiǎn)。

　　對(duì)于重建后的索引還存在另外一個(gè)性能上的優(yōu)勢(shì)，因?yàn)樵谛陆⒌乃饕?，邏輯上相互連接的頁(yè)面在物理上往往也是連在一起的，這樣可以提高磁盤(pán)頁(yè)面被連續(xù)讀取的幾率，從而提高整個(gè)操作的IO效率。見(jiàn)如下示例：

#1. 此時(shí)已經(jīng)在該表中插入了大約6萬(wàn)條數(shù)據(jù)，下面的SQL語(yǔ)句將查詢?cè)撍饕加玫拇疟P(pán)空間。

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

　　五、觀察磁盤(pán)使用情況：

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

#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ù)表的索引名稱和索引占用的磁盤(pán)頁(yè)面數(shù)量。

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 閱讀(668) 評(píng)論(0) 編輯收藏所屬分類: 數(shù)據(jù)庫(kù)

新用戶注冊(cè) 刷新評(píng)論列表


只有注冊(cè)用戶登錄后才能發(fā)表評(píng)論。




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

qileilove

PostgreSQL 數(shù)據(jù)庫(kù)維護(hù)

導(dǎo)航

統(tǒng)計(jì)

常用鏈接

留言簿(55)

隨筆分類

隨筆檔案

文章分類

文章檔案

搜索

最新評(píng)論

閱讀排行榜

評(píng)論排行榜