Re: [ADMIN] Abnormal storage consumption with autovacuum enabled
What do you have max_fsm_pages set to? Currently, max_fsm_pages=2 Running VACUUM VERBOSE reports: NOTICE: number of page slots needed (237312) exceeds max_fsm_pages (2) HINT: Consider increasing the configuration parameter max_fsm_pages to a value over 237312. Is this measurement reliable? We are considering to set: max_fsm_pages=40 Could this have some sort of impact? thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Abnormal storage consumption with autovacuum enabled
Nicola Mauri [EMAIL PROTECTED] writes: What do you have max_fsm_pages set to? Currently, max_fsm_pages=2 Running VACUUM VERBOSE reports: NOTICE: number of page slots needed (237312) exceeds max_fsm_pages (2) HINT: Consider increasing the configuration parameter max_fsm_pages to a value over 237312. Yup, there's your problem (or a huge chunk of it anyway). Is this measurement reliable? We are considering to set: max_fsm_pages=40 Could this have some sort of impact? It will require a slightly larger shared-memory segment than you have now. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] Abnormal storage consumption with autovacuum enabled
I'm using 8.1.4 on linux. Autovacuum is enable with these settings: stats_start_collector = on stats_row_level = on autovacuum = on autovacuum_naptime = 60 autovacuum_vacuum_threshold = 1000 autovacuum_analyze_threshold = 500 Data stored in the database didn't groove very much in the last months (pg_dump still generates a 4GB compressed file), but data files now occupies 20 GB on disk. I know that a vacuum full or a full dump/restore is needed to reclaim filesystem space. However, my question is: how this could have been happened? I'm quite sure that data in tables never reached that size, during normal operations. Could be autovacuum not working properly? How to diagnose or make sure that autovacuum is doing its job? Thanks in advance! Nicola ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Abnormal storage consumption with autovacuum enabled
Nicola Mauri [EMAIL PROTECTED] writes: Data stored in the database didn't groove very much in the last months (pg_dump still generates a 4GB compressed file), but data files now occupies 20 GB on disk. I know that a vacuum full or a full dump/restore is needed to reclaim filesystem space. However, my question is: how this could have been happened? What do you have max_fsm_pages set to? If it's too small, you'll get bloat regardless of whether autovac is doing its thing. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
