今天早上的操作過程 |
因為要降規 |
從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 |