Thread: Vacuum and FSM page size
hi We have an installation of Postgres 8.1.2 (32bit on Solaris 9) with a DB size of about 250GB on disk. The DB is subject to fair amount of inserts, deletes and updates per day. Running VACUUM VERBOSE tells me that I should allocate around 20M pages to FSM (max_fsm_pages)! This looks like a really large amount to me. Has anyone gone ever that high with max_fsm_pages? The other question is why such a large number is required in the first place. Auto vacuum is enabled. Here are the settings: autovacuum = true autovacuum_naptime = 900 autovacuum_vacuum_threshold = 2000 autovacuum_analyze_threshold = 1000 autovacuum_vacuum_scale_factor = 0.25 autovacuum_analyze_scale_factor = 0.18 autovacuum_vacuum_cost_delay = 150 autovacuum_vacuum_cost_limit = 120 A manual vacuum takes very long (around 4 days), so maybe the cost delay and limit or too high. Any suggestions anyone? Cheers, -- Tom.
On Jan 23, 2008, at 1:29 PM, Thomas Lozza wrote: > We have an installation of Postgres 8.1.2 (32bit on Solaris 9) with > a DB > size of about 250GB on disk. The DB is subject to fair amount of > inserts, deletes and updates per day. > > Running VACUUM VERBOSE tells me that I should allocate around 20M > pages > to FSM (max_fsm_pages)! This looks like a really large amount to me. > > Has anyone gone ever that high with max_fsm_pages? wow. you must have a *lot* of pages with empty space in them.... it sounds to me like your autovacuum is not running frequently enough.
Vivek Khera <khera@kcilink.com> writes:
> On Jan 23, 2008, at 1:29 PM, Thomas Lozza wrote:
>> We have an installation of Postgres 8.1.2 (32bit on Solaris 9) with
>> ...
> it sounds to me like your autovacuum is not running frequently enough.
Yeah. The default autovac settings in 8.1 are extremely conservative
(in the direction of not letting autovac eat many cycles), so a
high-traffic installation will need to adjust them to keep from falling
behind.
regards, tom lane
On Jan 23, 2008 12:29 PM, Thomas Lozza <thomas.lozza@nexustelecom.com> wrote: > Auto vacuum is enabled. Here are the settings: > > autovacuum = true > autovacuum_naptime = 900 > autovacuum_vacuum_threshold = 2000 > autovacuum_analyze_threshold = 1000 > autovacuum_vacuum_scale_factor = 0.25 > autovacuum_analyze_scale_factor = 0.18 > autovacuum_vacuum_cost_delay = 150 > autovacuum_vacuum_cost_limit = 120 > > A manual vacuum takes very long (around 4 days), so maybe the cost delay > and limit or too high. Your autovacuum_vacuum_cost_delay is REALLY high. Try setting it to 10 or 20 and see if that helps. What is your plain old vacuum_cost_delay set to?
Thanks for the advice. I used the default settings before, thought though that vacuum was a bit aggressive, ie, using too many resources. Now its taking very long. So will have to find reasonable settings in between I guess. On the other hand, if I keep the fsm_page number high enough, the system should be fine with a low number of vacuum cycles, right. As memory is not really scarce (16G, 32 bit PG though) an x million fsm_page entry should be ok. Any thoughts on that? cheers, -- tom. -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Thursday, 24. January, 2008 10:48 To: Thomas Lozza Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Vacuum and FSM page size On Jan 23, 2008 12:29 PM, Thomas Lozza <thomas.lozza@nexustelecom.com> wrote: > Auto vacuum is enabled. Here are the settings: > > autovacuum = true > autovacuum_naptime = 900 > autovacuum_vacuum_threshold = 2000 > autovacuum_analyze_threshold = 1000 > autovacuum_vacuum_scale_factor = 0.25 > autovacuum_analyze_scale_factor = 0.18 autovacuum_vacuum_cost_delay = > 150 autovacuum_vacuum_cost_limit = 120 > > A manual vacuum takes very long (around 4 days), so maybe the cost > delay and limit or too high. Your autovacuum_vacuum_cost_delay is REALLY high. Try setting it to 10 or 20 and see if that helps. What is your plain old vacuum_cost_delay set to?
On Jan 27, 2008 5:01 PM, Thomas Lozza <thomas.lozza@nexustelecom.com> wrote: > Thanks for the advice. > I used the default settings before, thought though that vacuum was a bit > aggressive, ie, using too many resources. Now its taking very long. So > will have to find reasonable settings in between I guess. > > On the other hand, if I keep the fsm_page number high enough, the system > should be fine with a low number of vacuum cycles, right. As memory is > not really scarce (16G, 32 bit PG though) an x million fsm_page entry > should be ok. Any thoughts on that? The issue you then run into is bloat, where you have a table or index that is 90% or so empty space, and performance on selects might suffer, especially on larger tables or indexes. What often works best is to let autovacuum handle most of your tables, then schedule individual tables to be vacuumed by cron, setting the nap time for vacuum at 20 or 30 milliseconds so they don't chew up all of your I/O
On Wed, Jan 23, 2008 at 07:29:16PM +0100, Thomas Lozza wrote: > hi > > We have an installation of Postgres 8.1.2 (32bit on Solaris 9) with a DB > size of about 250GB on disk. The DB is subject to fair amount of > inserts, deletes and updates per day. > > Running VACUUM VERBOSE tells me that I should allocate around 20M pages > to FSM (max_fsm_pages)! This looks like a really large amount to me. > > Has anyone gone ever that high with max_fsm_pages? No, that's telling me that you have a lot of bloat. A 250G database is about 31M pages. If you have 20M pages with free space then you've got a lot of bloat. Ideally, with a autovac_vacuum_scale_factor of .25 you should only need 4M FSM pages. At most you should only need 8M. > The other question is why such a large number is required in the first > place. > Auto vacuum is enabled. Here are the settings: > > autovacuum = true > autovacuum_naptime = 900 Why'd you change that? That's pretty high. > autovacuum_vacuum_threshold = 2000 > autovacuum_analyze_threshold = 1000 Both of those seem high... > autovacuum_vacuum_scale_factor = 0.25 That means that 12.5% of your database (on average) will be dead space... I'd probably cut that back to 0.2. > autovacuum_analyze_scale_factor = 0.18 This also seems pretty high. > autovacuum_vacuum_cost_delay = 150 Woah, that's *really* high. That means at most you'll get 6 vacuum rounds in per second; with default cost settings that means you'd be able to actually vacuum about 50 dirty pages per second, tops. Of course not all pages will be dirty, but still... I normally use between 10 and 20 for cost_delay (lower values for faster drive arrays). > autovacuum_vacuum_cost_limit = 120 Why'd you reduce this? I'd put it back to 200... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828