Postgresql
Postgres - 小表中的自動真空
- 什麼是 autovacuum 對該表起作用的合適設置？
我們有一個“sequencia”表，它完成了
sequencepostgres 應該做的事情，但也許通過使用其他 DB，開發人員選擇了這種方法，即使用數據庫中的一個表來儲存插入到其他表中的最後一個 id。表“sequencia”的範例：
id_entidade no_sequencia ------------------------------- --------------- CLIENTEGESTAO 33325146 VEICULOREVISAO 14541831 DADOSBOOK 11627492 HISTORICOQUILOMETRAGEM 9992701 RESULTADOINTEGRACAO 4089476 CAMPOSREGISTROINTERFACE 2892807 REGISTROINTEGRACAO 1731981 ACAO 1585661 ENCAMINHAMENTO 1263190 REQUISICAOOS 948967 MOTIVOEVENTO 916872 EVENTO 818115 VEICULO -35041 CLIENTE -64414數據庫中任何記錄的下一個 ID 將是“no_sequencia”+1，因此該表接收大量更新，大約每分鐘 100 次，很少有一些 INSERT，每年可能更少，我的疑問仍然存在autovacuum，今天我使用以下配置：
`autovacuum_vacuum_scale_factor` = 0.20 `autovacuum_vacuum_threshold` = 50如果 autovacuum 可用，當達到 51 個更新的元組時將清除表，在更一般的上下文中，如果我們忽略此表，則 autovacuum 每隔 30 分鐘左右執行一次：
relname n_live_tup % live n_dead_tup Tamanho Registros Configuracao last_autovacuum autovacuum_count ------------------------- ----------- ------- ------------ ---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------------ sequencia 191 65 99 752 KB 191 {autovacuum_vacuum_scale_factor=0.20,autovacuum_vacuum_threshold=50} 17/01/2018 19:41:31 13806 campanha 3541 98 67 3400 KB 3821 {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100} 17/01/2018 19:40:49 194 sms 2071256 99 1026 773160 KB 2071070 {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000} 17/01/2018 19:24:31 20 campanhaexecucao 1948 96 61 136 KB 1980 {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100} 17/01/2018 19:05:48 10 telefonema 8957384 99 1792 1086424 KB 8957050 {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000} 17/01/2018 18:22:45 17 email 1059515 99 3776 945368 KB 1056970 {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000} 17/01/2018 18:11:53 3 oportunidade 698662 99 317 105992 KB 698440 {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000} 17/01/2018 18:06:54 1 interesse 698254 99 550 93936 KB 698043 {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000} 17/01/2018 18:06:46 1 encaminhamento 8277408 99 1998 1187264 KB 8277230 {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000} 17/01/2018 18:03:24 39 evento 3689441 99 5672 2376448 KB 3688780 {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000} 17/01/2018 17:06:11 36 sessao 1736 94 102 216 KB 1736 {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100} 17/01/2018 16:22:44 18 acao 14931180 99 6635 4250392 KB 14924700 {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=25000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=50000} 17/01/2018 15:25:32 10 parametroempresa 349 81 77 48 KB 348 {autovacuum_vacuum_scale_factor=0.20,autovacuum_vacuum_threshold=50} 17/01/2018 14:22:41 22 tipoevento 4565 99 19 3656 KB 4565 {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100} 17/01/2018 14:08:40 3 usuariotipoevento 64254 98 992 4544 KB 64270 {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100} 17/01/2018 14:03:41 5 registrointerface 4050719 99 1194 549800 KB 4049100 {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000} 17/01/2018 13:30:35 2 regraleadmontadora 117 90 13 48 KB 117 {autovacuum_vacuum_scale_factor=0.20,autovacuum_vacuum_threshold=50} 17/01/2018 11:58:37 6 usuarioformacontato 78593 98 1389 5944 KB 79230 {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100} 17/01/2018 11:22:39 3 arquivointegracao 1843 93 134 296 KB 1787 {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100} 17/01/2018 02:00:50 12在上面的例子中，我們看到
n_dead_tup已經過了 51，autovacuum 正在休眠 60 秒。name ---------------------------------- setting ----- unit autovacuum on autovacuum_analyze_scale_factor 0.1 autovacuum_analyze_threshold 50 autovacuum_freeze_max_age 200000000 autovacuum_max_workers 3 autovacuum_multixact_freeze_max_age 400000000 autovacuum_naptime 60 s autovacuum_vacuum_cost_delay 20 ms autovacuum_vacuum_cost_limit -1 autovacuum_vacuum_scale_factor 0.2 autovacuum_vacuum_threshold 50 autovacuum_work_mem -1 kB
- 什麼是 autovacuum 對該表起作用的合適設置？
該過程執行良好，這只是一項預防措施，如果您需要更多資訊，請告知。
這裡沒有任何跡象表明需要改變。
請注意，目前門檻值不僅取決於基本門檻值，還取決於比例因子。
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples在您的情況下，這將達到 50 + 0.2 * 一些接近 200 的未知數字