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