PostgreSQL快取

liguangxian2018發表於2018-06-04
SQL快取

目錄[-]

pg_buffercachepgfincorepg_prewarmdstatLinux ftools使用pg_prewarm預載入關係/索引:pgfincore 輸出: 怎樣重新整理在作業系統快取中的關係/索引 ?使用pg_prewarm的range功能預載入隨機塊.

快取.... !!, 它很難在一篇文章中解釋清楚。但是我會努力分享我從Heikki, Robert Haas, Bruce Momjian那裡學到的知識。在PostgreSQL裡有兩層：PG共享緩衝和作業系統頁面快取,任何讀寫都會通過作業系統快取（迄今為止還沒有其它途徑）。Postgres把資料寫在作業系統頁面快取，使用者覺得資料好像回寫到了磁碟，之後作業系統快取才會寫到對應的物理磁碟位置。PG共享緩衝無法控制系統頁面快取，甚至連繫統快取是什麼都不知道。所以，Postgres DBA或者專家給出的大多數建議都是更快的磁碟讀寫或者更好的快取。

PostgreSQL的快取/緩衝和其它資料庫十分相像並且十分複雜。因為我有Oracle和mindset背景，所以我使用怎麼樣/什麼時候/什麼/為什麼等提問方式，關於資料庫的緩衝快取，固定的緩衝，重新整理資料庫快取，以及預載入資料庫等方面，我都是從這種方式獲得答案的，然而這種方式有點與眾不同。儘管我的問題很煩人，但是他們總是耐心的回答，使我明白擴充套件我的知識，反過來，你才能閱讀這篇博文... :) ..

在一些學習上，我畫了一幅Postgres中資料在記憶體和磁碟之間傳遞的，以及一些重要的工具和Robert Hass提供的新補丁(pg_prewarm).

pg_buffercache

它是一個contrib模組，它會告訴你什麼是PostgreSQL快取。像下面安裝：

?
1
postgres=# CREATEEXTENSION pg_buffercache

pgfincore

它有一個顯示資料在作業系統頁面快取中資訊的功能。Pgfincore和pg_buffercache聯合會十分方便的。現在，它可以同時獲得PG緩衝和作業系統頁面快取資訊。感謝Cerdic Villemain。Pgfincore的主幹是fadvise,fincore,它倆是linux ftools。你可以使用原始碼安裝fincore/fadvise。你可以使用pgfincore contrib模組或者ftools，都會產生同樣的結果。我試了兩者，它們都十分簡單優秀。

安裝：

下載最新版本：

[cpp] view plain copy
  1. http://pgfoundry.org/frs/download.php/3186/pgfincore-v1.1.1.tar.gz  

使用root使用者：

?
1
2
3
4
5
6
exportPATH=/usr/local/pgsql91/bin:$PATH     //設定執行pg_config的路徑
tar-xvf pgfincore-v1.1.1.tar.gz
cdpgfincore-1.1.1
makeclean
make
makeinstall
現在連線到PG，執行下面的命令：
?
1
postgres=# CREATEEXTENSION pgfincore;

pg_prewarm

預載入關係/索引到PG快取中，在PostgreSQL中可能嗎？當然可以了，感謝Robert Hass,他提交一些補丁到社群，期待它能夠在PG 9.2或者PG 9.3中可行。然而，你可以使用這個補丁在PG 9.1做一些測試。
pg_prewarm
有三種模式：

  1. PREFETCH: 非同步獲取資料塊到作業系統快取中，而不是PG緩衝(提示只是作業系統快取)
  2. READ：讀取所有的資料塊到虛擬快取中，然後寫到作業系統快取中（提示只是作業系統快取）
  3. BUFFER：讀取所有的或者一些資料塊到資料庫緩衝中。
安裝：
我把pg_prewarm補丁加入到PG原始碼安裝中，你需要調整你的每步安裝。
  1. 解壓PG原始碼路徑： /usr/local/src/postgresql-9.1.3
  2. PG安裝路徑： /usr/local/pgsql91
  3. 所有下載路徑： /usr/local/src
注意：應用pg_prewarm補丁之前安裝PG
1. 下載補丁，放在/usr/local/src
http://archives.postgresql.org/pgsql-hackers/2012-03/binRVNreQMnK4.bin 
補丁附加郵件地址

http://archives.postgresql.org/message-id/CA+TgmobRrRxCO+t6gcQrw_dJw+Uf9ZEdwf9beJnu+RB5TEBjEw@mail.gmail.com

2. 在下載之後，到PG原始碼目錄，然後執行下面幾步。

?
1
2
3
4
# cd /usr/local/src/postgresql-9.1.3
# patch -p1 < ../pg_prewarm.bin         (在下載之後我重新命名了pg_prewarm)
# make -C contrib/pg_prewarm
# make -C contrib/pg_prewarm instal

3. 上面的命令會在$PGPATH/contrib/extension目錄裡建立檔案。現在準備新增contrib模組了。

?
1
2
3
4
5
6
7
8
9
10
11
postgres=# createEXTENSION pg_prewarm;
CREATEEXTENSION
postgres=# \dx
                          List ofinstalled extensions
      Name     | Version |   Schema  |              Description
----------------+---------+------------+----------------------------------------
 pg_buffercache | 1.0     | public    | examine the shared buffer cache
 pg_prewarm     | 1.0     | public    | prewarm relation data
 pgfincore      | 1.1.1   | public    | examine andmanage the os buffer cache
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)
文件： 
?
1
2
3
<span>/usr/local/src/postgres-9.1.3/doc/src/sqml
[root@localhost sgml]# ll pgpre*
-rw-r--r-- 1 root root 2481 Apr 10 10:15 pgprewarm.sgml</span>

dstat

它是vmstat, netstat, top等工具的組合到一起成了一個"dstat"linux命令。當資料庫表現不正常時，從作業系統級別瞭解語句，我們會開啟好幾個終端來顯示程式，記憶體，磁碟讀寫，網路資訊，但是在這些視窗切換是十分痛苦的。所以，dstat有幾個選項來幫助顯示所有的命令在一個輸出視窗中。

安裝：
Dstat下載連線：（RHEL 6）

[cpp] view plain copy
  1. wget http://pkgs.repoforge.org/dstat/dstat-0.7.2-1.el6.rfx.noarch.rpm  

或者

[cpp] view plain copy
  1. <span style="padding:0px; margin:0px">yum install dstat</span>  
文件：http://dag.wieers.com/home-made/dstat/

Linux ftools

在被設計在現代的linux作業系統中用來呼叫像mincore, fallocate, fadivse等的工具。Ftools它會幫助你找出那些檔案在作業系統快取中。使用perl/python指令碼，你可以獲得作業系統頁面快取資訊在pg_class.relfilenode物件中。pg_fincore就是建立在它之上的。你可以使用pgfincore或者ftools指令碼。 
安裝：
[cpp] view plain copy
  1. 從這個連線下載.tar.gz  
  2. https://github.com/david415/python-ftools  
  3.   
  4. cd python-ftools  
  5. python setup.py build  
  6. export PYTHONPATH=build/lib.linux-x86_64-2.5  
  7. python setup.py install  
  8.   
  9. 注意:你應該在安裝python-ftools之前就已經安裝好了。  

現在，我使用例子來檢驗這些工具。在這個例子中，有一個表，它有一個索引和序列(sequence)，大小為100多MB。

[cpp] view plain copy
  1. postgres=# \d+ cache  
  2. Table "public.cache"  
  3. Column |  Type   |                Modifiers                | Storage  | Description  
  4. --------+---------+-----------------------------------------+----------+-------------  
  5. name   | text    |                                         | extended |  
  6. code   | integer |                                         | plain    |  
  7. id     | integer | default nextval('icache_seq'::regclass) | plain    |  
  8. Indexes:  
  9. "icache" btree (code)  
  10. Has OIDs: no  
使用查詢來了解這表，序列和它的索引所佔的大小. 
[cpp] view plain copy
  1. postgres=# SELECT c.relname AS object_name,  
  2. CASE when c.relkind='r' then 'table'  
  3. when c.relkind='i' then 'index'  
  4. when c.relkind='S' then 'sequence'  
  5. else 'others'  
  6. END AS type,pg_relation_size(c.relname::text) AS size, pg_size_pretty(pg_relation_size(c.relname::text)) AS pretty_size  
  7. FROM pg_class c  
  8. JOIN pg_roles r ON r.oid = c.relowner  
  9. LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
  10. WHERE (c.relkind = ANY (ARRAY['r'::"char", 'i'::"char", 'S'::"char",''::"char"])) AND n.nspname = 'public';  
  11.   
  12. object_name |   type   |   size   | pretty_size  
  13. -------------+----------+----------+-------------  
  14. icache_seq  | sequence |     8192 | 8192 bytes  
  15. cache       | table    | 83492864 | 80 MB  
  16. icache      | index    | 35962880 | 34 MB  
  17. (3 rows)  
  18.   
  19. Total object size 'cache'  
  20.   
  21. postgres=# select pg_size_pretty(pg_total_relation_size('cache'));  
  22. pg_size_pretty  
  23. ----------------  
  24. 114 MB  
  25. (1 row)  
我已經寫了聯合pgfincore和pg_buffercache的一個簡單查詢來獲得PG緩衝和作業系統頁面快取的資訊。我會在這個查詢貫穿整個例子，僅僅複製這個查詢就好了。 
[cpp] view plain copy
  1. select rpad(c.relname,30,' ') as Object_Name,  
  2. case when c.relkind='r' then 'Table' when c.relkind='i' then 'Index' else 'Other' end as Object_Type,   
  3. rpad(count(*)::text,5,' ') as "PG_Buffer_Cache_usage(8KB)",  
  4. split_part(pgfincore(c.relname::text)::text,','::text,5) as "OS_Cache_usage(4KB)"  
  5. from pg_class c inner join pg_buffercache b on b.relfilenode=c.relfilenode  
  6.      inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database() and c.relnamespace=(select oid from pg_namespace where nspname='public'))  
  7. group by c.relname,c.relkind  
  8. order by "PG_Buffer_Cache_usage(8KB)"  
  9. desc limit 10;  
  10.   
  11. object_name | object_type | PG_Buffer_Cache_usage(8KB) | OS_Cache_usage(4KB)  
  12. -------------+-------------+----------------------------+---------------------  
  13. (0 rows)  
  14.   
  15. 注意: 我已經重新整理PG緩衝和作業系統頁面快取。所以，快取/緩衝沒有任何資料.  

使用pg_prewarm預載入關係/索引:

在之前，我重新整理整個"Cache"表的順序遍歷查詢和之前預載入關係/索引的時間。 
[cpp] view plain copy
  1. postgres=# explain analyze select * from cache ;  
  2. QUERY PLAN  
  3. ------------------------------------------------------------------------------------------------------------------  
  4. Seq Scan on cache  (cost=0.00..26192.00 rows=1600000 width=19) (actual time=0.033..354.691 rows=1600000 loops=1)  
  5. Total runtime: 427.769 ms  
  6. (2 rows)  
現在讓我們使用pg_prewarm來預載入關係/索引/序列，然後檢視查詢計劃。 
[cpp] view plain copy
  1. postgres=# select pg_prewarm('cache','main','buffer',null,null);  
  2. pg_prewarm  
  3. ------------  
  4. 10192  
  5. (1 row)  
  6. postgres=# select pg_prewarm('icache','main','buffer',null,null);  
  7. pg_prewarm  
  8. ------------  
  9. 4390  
  10. (1 row)  
  11.   
  12. Output of combined buffers:  
  13. object_name | object_type | PG_Buffer_Cache_usage(8KB) | OS_Cache_usage(4KB)  
  14. -------------+-------------+----------------------------+---------------------  
  15. icache      | Index       | 4390                       | 8780  
  16. cache       | Table       | 10192                      | 20384  
  17. (2 rows)  

pgfincore 輸出: 

[cpp] view plain copy
  1. postgres=# select relname,split_part(pgfincore(c.relname::text)::text,','::text,5) as "In_OS_Cache" from pg_class c where relname ilike '%cache%';  
  2. relname   | In_OS_Cache  
  3. ------------+-------------  
  4. icache_seq | 2  
  5. cache      | 20384  
  6. icache     | 8780  
  7. (3 rows)  
  8.   
  9. or for each object.  
  10.   
  11. postgres=# select * from pgfincore('cache');  
  12. relpath      | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit  
  13. ------------------+---------+--------------+--------------+-----------+-----------+---------------+---------  
  14. base/12780/16790 |       0 |         4096 |        20384 |     20384 |         1 |        316451 |  
  15. (1 row)  
To retrieve similar information using python-ftools script you need to know objects relfilenode number, check below.
使用python-ftools指令碼也會獲得相似的資訊，你應該知道relfilenode物件的編號。像下面一樣檢視。 
[cpp] view plain copy
  1. postgres=# select relfilenode,relname from pg_class where relname ilike '%cache%';  
  2. relfilenode |    relname  
  3. -------------+----------------  
  4. 16787 | icache_seq       /// 你執行的序列  
  5. 16790 | cache            /// 表  
  6. 16796 | icache           /// 索引  
  7. (3 rows)  
使用python-ftools指令碼 


有趣吧....!!!!.

現在比較一下預載入表到緩衝之後的explain plan

[cpp] view plain copy
  1. postgres=# explain analyze select * from cache ;  
  2. QUERY PLAN  
  3. ------------------------------------------------------------------------------------------------------------------  
  4. Seq Scan on cache  (cost=0.00..26192.00 rows=1600000 width=19) (actual time=0.016..141.804 rows=1600000 loops=1)  
  5. Total runtime: 215.100 ms  
  6. (2 rows)  

怎樣重新整理在作業系統快取中的關係/索引 ?

使用pgfadvise,你可以預載入或者重新整理關係到作業系統快取。獲得更多資訊，在終端中使用\df pgfadvise*來顯示所有與pgfadvise相關的函式。下面是重新整理作業系統快取的一個例子。
[cpp] view plain copy
  1. postgres=# select * from pgfadvise_dontneed('cache');  
  2. relpath      | os_page_size | rel_os_pages | os_pages_free  
  3. ------------------+--------------+--------------+---------------  
  4. base/12780/16790 |         4096 |        20384 |        178145  
  5. (1 row)  
  6. postgres=# select * from pgfadvise_dontneed('icache');  
  7. relpath      | os_page_size | rel_os_pages | os_pages_free  
  8. ------------------+--------------+--------------+---------------  
  9. base/12780/16796 |         4096 |         8780 |        187166  
  10. (1 row)  
  11. postgres=# select relname,split_part(pgfincore(c.relname::text)::text,','::text,5) as "In_OS_Cache" from pg_class c where relname ilike '%cache%';  
  12. relname   | In_OS_Cache  
  13. ------------+-------------  
  14. icache_seq | 0  
  15. cache      | 0  
  16. icache     | 0  
  17. (3 rows)  
通過使用dstat，這些資訊顯示在一個視窗中，如你可以檢視讀寫比例。更多資訊使用 dstat --list
dstat -s --top-io --top-bio --top-mem

使用pg_prewarm的range功能預載入隨機塊.

假設，因為一些原因，你想重新整理伺服器，但是有一個很大的表它在快取中，而且執行的很好。在重新整理中，快取 中就沒有了你的表了，要回到重新整理前的狀態，你不得不知道多少表塊在快取中以及使用pg_prewarm的range選項來預載入它。

我通過pg_buffercache來查詢已建立的表，然後我使用pg_prewarm的range選項來傳送塊。通過這樣，共享快取就像先前載入到快取中一樣回來了。請看例子

[cpp] view plain copy
  1. select c.relname,count(*) as buffers from pg_class c   
  2. inner join pg_buffercache b on b.relfilenode=c.relfilenode and c.relname ilike '%cache%'   
  3. inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database())   
  4. group by c.relname   
  5. order by buffers desc;  
  6. relname | buffers  
  7. ---------+---------  
  8. cache   |   10192  
  9. icache  |    4390  
  10. (2 rows)  
  11. Note: These are the blocks in buffer.  
  12.   
  13. postgres=# create table blocks_in_buff (relation, fork, block) as select c.oid::regclass::text, case b.relforknumber when 0 then 'main' when 1 then 'fsm' when 2 then 'vm' end, b.relblocknumber from pg_buffercache b, pg_class c, pg_database d where b.relfilenode = c.relfilenode and b.reldatabase = d.oid and d.datname = current_database() and b.relforknumber in (0, 1, 2);  
  14. SELECT 14716  
重新整理伺服器以及從"blocks_in_buff"表中檢視預載入和表相關的隨機塊的快取。 
[cpp] view plain copy
  1. postgres=# select sum(pg_prewarm(relation, fork, 'buffer', block, block)) from blocks_in_buff;  
  2. sum  
  3. -------  
  4. 14716  
  5. (1 row)  
  6.   
  7. postgres=# select c.relname,count(*) as buffers from pg_class c  
  8. inner join pg_buffercache b on b.relfilenode=c.relfilenode and c.relname ilike '%cache%'  
  9. inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database())  
  10. group by c.relname  
  11. order by buffers desc;  
  12. relname | buffers  
  13. ---------+---------  
  14. cache   |   10192  
  15. icache  |    4390  
  16. (2 rows)  
 看，我的共享快取又回來工作了。 

目錄[-]

pg_buffercachepgfincorepg_prewarmdstatLinux ftools使用pg_prewarm預載入關係/索引:pgfincore 輸出: 怎樣重新整理在作業系統快取中的關係/索引 ?使用pg_prewarm的range功能預載入隨機塊.

快取.... !!, 它很難在一篇文章中解釋清楚。但是我會努力分享我從Heikki, Robert Haas, Bruce Momjian那裡學到的知識。在PostgreSQL裡有兩層：PG共享緩衝和作業系統頁面快取,任何讀寫都會通過作業系統快取（迄今為止還沒有其它途徑）。Postgres把資料寫在作業系統頁面快取，使用者覺得資料好像回寫到了磁碟，之後作業系統快取才會寫到對應的物理磁碟位置。PG共享緩衝無法控制系統頁面快取，甚至連繫統快取是什麼都不知道。所以，Postgres DBA或者專家給出的大多數建議都是更快的磁碟讀寫或者更好的快取。

PostgreSQL的快取/緩衝和其它資料庫十分相像並且十分複雜。因為我有Oracle和mindset背景，所以我使用怎麼樣/什麼時候/什麼/為什麼等提問方式，關於資料庫的緩衝快取，固定的緩衝，重新整理資料庫快取，以及預載入資料庫等方面，我都是從這種方式獲得答案的，然而這種方式有點與眾不同。儘管我的問題很煩人，但是他們總是耐心的回答，使我明白擴充套件我的知識，反過來，你才能閱讀這篇博文... :) ..

在一些學習上，我畫了一幅Postgres中資料在記憶體和磁碟之間傳遞的，以及一些重要的工具和Robert Hass提供的新補丁(pg_prewarm).

pg_buffercache

它是一個contrib模組，它會告訴你什麼是PostgreSQL快取。像下面安裝：

?
1
postgres=# CREATEEXTENSION pg_buffercache

pgfincore

它有一個顯示資料在作業系統頁面快取中資訊的功能。Pgfincore和pg_buffercache聯合會十分方便的。現在，它可以同時獲得PG緩衝和作業系統頁面快取資訊。感謝Cerdic Villemain。Pgfincore的主幹是fadvise,fincore,它倆是linux ftools。你可以使用原始碼安裝fincore/fadvise。你可以使用pgfincore contrib模組或者ftools，都會產生同樣的結果。我試了兩者，它們都十分簡單優秀。

安裝：

下載最新版本：

[cpp] view plain copy
  1. http://pgfoundry.org/frs/download.php/3186/pgfincore-v1.1.1.tar.gz  

使用root使用者：

?
1
2
3
4
5
6
exportPATH=/usr/local/pgsql91/bin:$PATH     //設定執行pg_config的路徑
tar-xvf pgfincore-v1.1.1.tar.gz
cdpgfincore-1.1.1
makeclean
make
makeinstall
現在連線到PG，執行下面的命令：
?
1
postgres=# CREATEEXTENSION pgfincore;

pg_prewarm

預載入關係/索引到PG快取中，在PostgreSQL中可能嗎？當然可以了，感謝Robert Hass,他提交一些補丁到社群，期待它能夠在PG 9.2或者PG 9.3中可行。然而，你可以使用這個補丁在PG 9.1做一些測試。
pg_prewarm
有三種模式：

  1. PREFETCH: 非同步獲取資料塊到作業系統快取中，而不是PG緩衝(提示只是作業系統快取)
  2. READ：讀取所有的資料塊到虛擬快取中，然後寫到作業系統快取中（提示只是作業系統快取）
  3. BUFFER：讀取所有的或者一些資料塊到資料庫緩衝中。
安裝：
我把pg_prewarm補丁加入到PG原始碼安裝中，你需要調整你的每步安裝。
  1. 解壓PG原始碼路徑： /usr/local/src/postgresql-9.1.3
  2. PG安裝路徑： /usr/local/pgsql91
  3. 所有下載路徑： /usr/local/src
注意：應用pg_prewarm補丁之前安裝PG
1. 下載補丁，放在/usr/local/src
http://archives.postgresql.org/pgsql-hackers/2012-03/binRVNreQMnK4.bin 
補丁附加郵件地址

http://archives.postgresql.org/message-id/CA+TgmobRrRxCO+t6gcQrw_dJw+Uf9ZEdwf9beJnu+RB5TEBjEw@mail.gmail.com

2. 在下載之後，到PG原始碼目錄，然後執行下面幾步。

?
1
2
3
4
# cd /usr/local/src/postgresql-9.1.3
# patch -p1 < ../pg_prewarm.bin         (在下載之後我重新命名了pg_prewarm)
# make -C contrib/pg_prewarm
# make -C contrib/pg_prewarm instal

3. 上面的命令會在$PGPATH/contrib/extension目錄裡建立檔案。現在準備新增contrib模組了。

?
1
2
3
4
5
6
7
8
9
10
11
postgres=# createEXTENSION pg_prewarm;
CREATEEXTENSION
postgres=# \dx
                          List ofinstalled extensions
      Name     | Version |   Schema  |              Description
----------------+---------+------------+----------------------------------------
 pg_buffercache | 1.0     | public    | examine the shared buffer cache
 pg_prewarm     | 1.0     | public    | prewarm relation data
 pgfincore      | 1.1.1   | public    | examine andmanage the os buffer cache
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)
文件： 
?
1
2
3
<span>/usr/local/src/postgres-9.1.3/doc/src/sqml
[root@localhost sgml]# ll pgpre*
-rw-r--r-- 1 root root 2481 Apr 10 10:15 pgprewarm.sgml</span>

dstat

它是vmstat, netstat, top等工具的組合到一起成了一個"dstat"linux命令。當資料庫表現不正常時，從作業系統級別瞭解語句，我們會開啟好幾個終端來顯示程式，記憶體，磁碟讀寫，網路資訊，但是在這些視窗切換是十分痛苦的。所以，dstat有幾個選項來幫助顯示所有的命令在一個輸出視窗中。

安裝：
Dstat下載連線：（RHEL 6）

[cpp] view plain copy
  1. wget http://pkgs.repoforge.org/dstat/dstat-0.7.2-1.el6.rfx.noarch.rpm  

或者

[cpp] view plain copy
  1. <span style="padding:0px; margin:0px">yum install dstat</span>  
文件：http://dag.wieers.com/home-made/dstat/

Linux ftools

在被設計在現代的linux作業系統中用來呼叫像mincore, fallocate, fadivse等的工具。Ftools它會幫助你找出那些檔案在作業系統快取中。使用perl/python指令碼，你可以獲得作業系統頁面快取資訊在pg_class.relfilenode物件中。pg_fincore就是建立在它之上的。你可以使用pgfincore或者ftools指令碼。 
安裝：
[cpp] view plain copy
  1. 從這個連線下載.tar.gz  
  2. https://github.com/david415/python-ftools  
  3.   
  4. cd python-ftools  
  5. python setup.py build  
  6. export PYTHONPATH=build/lib.linux-x86_64-2.5  
  7. python setup.py install  
  8.   
  9. 注意:你應該在安裝python-ftools之前就已經安裝好了。  

現在，我使用例子來檢驗這些工具。在這個例子中，有一個表，它有一個索引和序列(sequence)，大小為100多MB。

[cpp] view plain copy
  1. postgres=# \d+ cache  
  2. Table "public.cache"  
  3. Column |  Type   |                Modifiers                | Storage  | Description  
  4. --------+---------+-----------------------------------------+----------+-------------  
  5. name   | text    |                                         | extended |  
  6. code   | integer |                                         | plain    |  
  7. id     | integer | default nextval('icache_seq'::regclass) | plain    |  
  8. Indexes:  
  9. "icache" btree (code)  
  10. Has OIDs: no  
使用查詢來了解這表，序列和它的索引所佔的大小. 
[cpp] view plain copy
  1. postgres=# SELECT c.relname AS object_name,  
  2. CASE when c.relkind='r' then 'table'  
  3. when c.relkind='i' then 'index'  
  4. when c.relkind='S' then 'sequence'  
  5. else 'others'  
  6. END AS type,pg_relation_size(c.relname::text) AS size, pg_size_pretty(pg_relation_size(c.relname::text)) AS pretty_size  
  7. FROM pg_class c  
  8. JOIN pg_roles r ON r.oid = c.relowner  
  9. LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
  10. WHERE (c.relkind = ANY (ARRAY['r'::"char", 'i'::"char", 'S'::"char",''::"char"])) AND n.nspname = 'public';  
  11.   
  12. object_name |   type   |   size   | pretty_size  
  13. -------------+----------+----------+-------------  
  14. icache_seq  | sequence |     8192 | 8192 bytes  
  15. cache       | table    | 83492864 | 80 MB  
  16. icache      | index    | 35962880 | 34 MB  
  17. (3 rows)  
  18.   
  19. Total object size 'cache'  
  20.   
  21. postgres=# select pg_size_pretty(pg_total_relation_size('cache'));  
  22. pg_size_pretty  
  23. ----------------  
  24. 114 MB  
  25. (1 row)  
我已經寫了聯合pgfincore和pg_buffercache的一個簡單查詢來獲得PG緩衝和作業系統頁面快取的資訊。我會在這個查詢貫穿整個例子，僅僅複製這個查詢就好了。 
[cpp] view plain copy
  1. select rpad(c.relname,30,' ') as Object_Name,  
  2. case when c.relkind='r' then 'Table' when c.relkind='i' then 'Index' else 'Other' end as Object_Type,   
  3. rpad(count(*)::text,5,' ') as "PG_Buffer_Cache_usage(8KB)",  
  4. split_part(pgfincore(c.relname::text)::text,','::text,5) as "OS_Cache_usage(4KB)"  
  5. from pg_class c inner join pg_buffercache b on b.relfilenode=c.relfilenode  
  6.      inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database() and c.relnamespace=(select oid from pg_namespace where nspname='public'))  
  7. group by c.relname,c.relkind  
  8. order by "PG_Buffer_Cache_usage(8KB)"  
  9. desc limit 10;  
  10.   
  11. object_name | object_type | PG_Buffer_Cache_usage(8KB) | OS_Cache_usage(4KB)  
  12. -------------+-------------+----------------------------+---------------------  
  13. (0 rows)  
  14.   
  15. 注意: 我已經重新整理PG緩衝和作業系統頁面快取。所以，快取/緩衝沒有任何資料.  

使用pg_prewarm預載入關係/索引:

在之前，我重新整理整個"Cache"表的順序遍歷查詢和之前預載入關係/索引的時間。 
[cpp] view plain copy
  1. postgres=# explain analyze select * from cache ;  
  2. QUERY PLAN  
  3. ------------------------------------------------------------------------------------------------------------------  
  4. Seq Scan on cache  (cost=0.00..26192.00 rows=1600000 width=19) (actual time=0.033..354.691 rows=1600000 loops=1)  
  5. Total runtime: 427.769 ms  
  6. (2 rows)  
現在讓我們使用pg_prewarm來預載入關係/索引/序列，然後檢視查詢計劃。 
[cpp] view plain copy
  1. postgres=# select pg_prewarm('cache','main','buffer',null,null);  
  2. pg_prewarm  
  3. ------------  
  4. 10192  
  5. (1 row)  
  6. postgres=# select pg_prewarm('icache','main','buffer',null,null);  
  7. pg_prewarm  
  8. ------------  
  9. 4390  
  10. (1 row)  
  11.   
  12. Output of combined buffers:  
  13. object_name | object_type | PG_Buffer_Cache_usage(8KB) | OS_Cache_usage(4KB)  
  14. -------------+-------------+----------------------------+---------------------  
  15. icache      | Index       | 4390                       | 8780  
  16. cache       | Table       | 10192                      | 20384  
  17. (2 rows)  

pgfincore 輸出: 

[cpp] view plain copy
  1. postgres=# select relname,split_part(pgfincore(c.relname::text)::text,','::text,5) as "In_OS_Cache" from pg_class c where relname ilike '%cache%';  
  2. relname   | In_OS_Cache  
  3. ------------+-------------  
  4. icache_seq | 2  
  5. cache      | 20384  
  6. icache     | 8780  
  7. (3 rows)  
  8.   
  9. or for each object.  
  10.   
  11. postgres=# select * from pgfincore('cache');  
  12. relpath      | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit  
  13. ------------------+---------+--------------+--------------+-----------+-----------+---------------+---------  
  14. base/12780/16790 |       0 |         4096 |        20384 |     20384 |         1 |        316451 |  
  15. (1 row)  
To retrieve similar information using python-ftools script you need to know objects relfilenode number, check below.
使用python-ftools指令碼也會獲得相似的資訊，你應該知道relfilenode物件的編號。像下面一樣檢視。 
[cpp] view plain copy
  1. postgres=# select relfilenode,relname from pg_class where relname ilike '%cache%';  
  2. relfilenode |    relname  
  3. -------------+----------------  
  4. 16787 | icache_seq       /// 你執行的序列  
  5. 16790 | cache            /// 表  
  6. 16796 | icache           /// 索引  
  7. (3 rows)  
使用python-ftools指令碼 


有趣吧....!!!!.

現在比較一下預載入表到緩衝之後的explain plan

[cpp] view plain copy
  1. postgres=# explain analyze select * from cache ;  
  2. QUERY PLAN  
  3. ------------------------------------------------------------------------------------------------------------------  
  4. Seq Scan on cache  (cost=0.00..26192.00 rows=1600000 width=19) (actual time=0.016..141.804 rows=1600000 loops=1)  
  5. Total runtime: 215.100 ms  
  6. (2 rows)  

怎樣重新整理在作業系統快取中的關係/索引 ?

使用pgfadvise,你可以預載入或者重新整理關係到作業系統快取。獲得更多資訊，在終端中使用\df pgfadvise*來顯示所有與pgfadvise相關的函式。下面是重新整理作業系統快取的一個例子。
[cpp] view plain copy
  1. postgres=# select * from pgfadvise_dontneed('cache');  
  2. relpath      | os_page_size | rel_os_pages | os_pages_free  
  3. ------------------+--------------+--------------+---------------  
  4. base/12780/16790 |         4096 |        20384 |        178145  
  5. (1 row)  
  6. postgres=# select * from pgfadvise_dontneed('icache');  
  7. relpath      | os_page_size | rel_os_pages | os_pages_free  
  8. ------------------+--------------+--------------+---------------  
  9. base/12780/16796 |         4096 |         8780 |        187166  
  10. (1 row)  
  11. postgres=# select relname,split_part(pgfincore(c.relname::text)::text,','::text,5) as "In_OS_Cache" from pg_class c where relname ilike '%cache%';  
  12. relname   | In_OS_Cache  
  13. ------------+-------------  
  14. icache_seq | 0  
  15. cache      | 0  
  16. icache     | 0  
  17. (3 rows)  
通過使用dstat，這些資訊顯示在一個視窗中，如你可以檢視讀寫比例。更多資訊使用 dstat --list
dstat -s --top-io --top-bio --top-mem

使用pg_prewarm的range功能預載入隨機塊.

假設，因為一些原因，你想重新整理伺服器，但是有一個很大的表它在快取中，而且執行的很好。在重新整理中，快取 中就沒有了你的表了，要回到重新整理前的狀態，你不得不知道多少表塊在快取中以及使用pg_prewarm的range選項來預載入它。

我通過pg_buffercache來查詢已建立的表，然後我使用pg_prewarm的range選項來傳送塊。通過這樣，共享快取就像先前載入到快取中一樣回來了。請看例子

[cpp] view plain copy
  1. select c.relname,count(*) as buffers from pg_class c   
  2. inner join pg_buffercache b on b.relfilenode=c.relfilenode and c.relname ilike '%cache%'   
  3. inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database())   
  4. group by c.relname   
  5. order by buffers desc;  
  6. relname | buffers  
  7. ---------+---------  
  8. cache   |   10192  
  9. icache  |    4390  
  10. (2 rows)  
  11. Note: These are the blocks in buffer.  
  12.   
  13. postgres=# create table blocks_in_buff (relation, fork, block) as select c.oid::regclass::text, case b.relforknumber when 0 then 'main' when 1 then 'fsm' when 2 then 'vm' end, b.relblocknumber from pg_buffercache b, pg_class c, pg_database d where b.relfilenode = c.relfilenode and b.reldatabase = d.oid and d.datname = current_database() and b.relforknumber in (0, 1, 2);  
  14. SELECT 14716  
重新整理伺服器以及從"blocks_in_buff"表中檢視預載入和表相關的隨機塊的快取。 
[cpp] view plain copy
  1. postgres=# select sum(pg_prewarm(relation, fork, 'buffer', block, block)) from blocks_in_buff;  
  2. sum  
  3. -------  
  4. 14716  
  5. (1 row)  
  6.   
  7. postgres=# select c.relname,count(*) as buffers from pg_class c  
  8. inner join pg_buffercache b on b.relfilenode=c.relfilenode and c.relname ilike '%cache%'  
  9. inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database())  
  10. group by c.relname  
  11. order by buffers desc;  
  12. relname | buffers  
  13. ---------+---------  
  14. cache   |   10192  
  15. icache  |    4390  
  16. (2 rows)  
 看，我的共享快取又回來工作了

相關文章