f 在EDB 10上使用記憶體預載模組pg_prewarm ~ 迪貝之家

Pages

在EDB 10上使用記憶體預載模組pg_prewarm

今天早上的操作過程
因為要降規
從32core 128G將到8core 32G
當然shared_buffers也得調整
之前我設了40G

重開之後,shared_buffers的值
我們來看一下,未預載前的數據
不過得先把pg_buffercache的extension先建出來
create extension pg_buffercache;
select c.relname,count(*) as buffers
from pg_class c
inner join pg_buffercache b on b.relfilenode=c.relfilenode
inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database())
group by c.relname order by buffers desc ;

進行預載吧~
SELECT pg_prewarm('pesc.ac_daily_su_invoice_202006');
SELECT pg_prewarm('pesc.ac_daily_su_invoice_202006_pk');
SELECT pg_prewarm('pesc.msg_gui_trans_record_202006');
SELECT pg_prewarm('pesc.msg_gui_online_detail_202006');
SELECT pg_prewarm('pesc.msg_gui_online_detail_202006_pk');
SELECT pg_prewarm('pesc.msg_gui_online_detail_202006_idx1');
SELECT pg_prewarm('pesc.msg_gui_query_detail_202006');
SELECT pg_prewarm('pesc.msg_gui_confirm_status_202006');
SELECT pg_prewarm('pesc.msg_gui_confirm_status_202006_pk');
SELECT pg_prewarm('pesc.msg_gui_confirm_status_202006_idx1');
SELECT pg_prewarm('pesc.msg_gui_cancel_status_202006');
SELECT pg_prewarm('pesc.ivm_invoice_prize_detail_202006');
SELECT pg_prewarm('pesc.ivm_invoice_prize_detail_202006_pk');
SELECT pg_prewarm('pesc.ivm_invoice_prize_detail_202006_idx1');
SELECT pg_prewarm('pesc.ivm_sc_invoice_prize_detail_202006');
SELECT pg_prewarm('pesc.ivm_sc_invoice_prize_detail_202006_pk');
SELECT pg_prewarm('pesc.ivm_sc_invoice_prize_detail_202006_idx1');
SELECT pg_prewarm('pesc.ivm_fisc_invoice_prize_detail_202006');
SELECT pg_prewarm('pesc.ivm_fisc_invoice_prize_detail_202006_pk');
SELECT pg_prewarm('pesc.ivm_fisc_invoice_prize_detail_202006_idx1');
SELECT pg_prewarm('pesc.ivm_fisc_settlement_detail_202006');
SELECT pg_prewarm('pesc.ivm_fisc_settlement_detail_202006_pk');
SELECT pg_prewarm('pesc.ivm_fisc_settlement_detail_202006_idx1');

再看一次buffer cache的數據
不過這一次跑起來就花了點時間
應該是要掃所有用過的cache block
relname | buffers
-------------------------------------------+---------
msg_gui_trans_record_202006 | 939128
msg_gui_online_detail_202006 | 45580
msg_gui_confirm_status_202006 | 42538
ivm_invoice_prize_detail_202006 | 38470
ivm_fisc_invoice_prize_detail_202006 | 35801
ivm_sc_invoice_prize_detail_202006 | 30228
ivm_fisc_settlement_detail_202006 | 24278
ac_daily_su_invoice_202006_pk | 23651
ivm_invoice_prize_detail_202006_pk | 23039
ac_daily_su_invoice_202006 | 22090
ivm_sc_invoice_prize_detail_202006_pk | 20554
ivm_fisc_invoice_prize_detail_202006_pk | 17708
ivm_invoice_prize_detail_202006_idx1 | 15618
msg_gui_online_detail_202006_pk | 11830
msg_gui_confirm_status_202006_pk | 11762
ivm_fisc_settlement_detail_202006_pk | 9884
ivm_fisc_invoice_prize_detail_202006_idx1 | 9256
msg_gui_online_detail_202006_idx1 | 6943
ivm_sc_invoice_prize_detail_202006_idx1 | 5985
msg_gui_confirm_status_202006_idx1 | 5347
ivm_fisc_settlement_detail_202006_idx1 | 4541
msg_gui_cancel_status_202006 | 743
ivm_410_ch_ol_t | 32
pg_statistic | 23
pg_operator | 15
msg_gui_query_detail_202006 | 14
pg_depend | 14
pg_depend_reference_index | 14
pg_index | 11
pg_depend_depender_index | 7
pg_amop | 6
pg_index_indexrelid_index | 5
pg_description | 5
pg_extension | 5
pg_init_privs | 5
pg_operator_oprname_l_r_n_index | 5
pg_rewrite | 5
pg_amproc | 4
pg_index_indrelid_index | 4
pg_amop_opr_fam_index | 4
pg_statistic_relid_att_inh_index | 4
pg_operator_oid_index | 4
有路用...阿是沒路用... 阿災
早跨麥的災
反正我做好我該做就對了
這篇文章PostgreSQL Buffer Cache
應該是我看過最詳細的
這是當初寫這個工具的人Robert Haas講出來的緣由
pg_prewarm