pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Vacuum stops with misleading max_fsm_pages error
7+ messages / 5 participants
[nested] [flat]

* Vacuum stops with misleading max_fsm_pages error
@ 2007-04-16 21:51 Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
  2007-04-16 22:08 ` Re: Vacuum stops with misleading max_fsm_pages error Joshua D. Drake <jd@commandprompt.com>
  2007-04-16 22:14 ` Re: Vacuum stops with misleading max_fsm_pages error Matthew T. O'Connor <matthew@zeut.net>
  0 siblings, 2 replies; 7+ messages in thread

From: Kieran Cooper, Lyris UK @ 2007-04-16 21:51 UTC (permalink / raw)
  To: pgsql-admin

Hi there,

We're managing a database on version 8.2.3 (running Lyris ListManager email 
software). I ran a 'vacuum analyze full verbose' (we haven't been able to 
get autovacuum to work properly on Lyris installs so we need to run a full 
vacuum every week or so, and we'd been doing some work on this machine so it 
needed it). It ran happily for quite a while - about 2 thirds of the way 
through the database - but then ended with the lines

INFO:  free space map contains 20914 pages in 61 relations
DETAIL:  A total of 14992 page slots are in use (including overhead).
14992 page slots are required to track all free space.
Current limits are:  900000 page slots, 6000 relations, using 5659 kB.

This happens (at more or less the same point but with slightly different 
figures) each time I try to run this command. It also happens even if I only 
do vacuum verbose.

I can vacuum individual tables quite happily but I'm not sure I can manage 
to do all of them individually.

As you see, I've upped the page slots and relations (there are 131 tables in 
the database in total) and it seems to me like there is more than enough.

Any ideas would be gratefully received!
Thanks
Kieran Cooper
Lyris UK 





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Vacuum stops with misleading max_fsm_pages error
  2007-04-16 21:51 Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
@ 2007-04-16 22:08 ` Joshua D. Drake <jd@commandprompt.com>
  2007-04-16 22:36   ` Re: Vacuum stops with misleading max_fsm_pages error Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 7+ messages in thread

From: Joshua D. Drake @ 2007-04-16 22:08 UTC (permalink / raw)
  To: Kieran Cooper, Lyris UK <kieran@lyris.co.uk>; +Cc: pgsql-admin

Kieran Cooper, Lyris UK wrote:
> Hi there,
> 
> We're managing a database on version 8.2.3 (running Lyris ListManager 
> email software). I ran a 'vacuum analyze full verbose' (we haven't been 
> able to get autovacuum to work properly on Lyris installs so we need to 
> run a full vacuum every week or so, and we'd been doing some work on 
> this machine so it needed it)

Why not just set up a regular vacuum to run twice a day?


. It ran happily for quite a while - about
> 2 thirds of the way through the database - but then ended with the lines
> 
> INFO:  free space map contains 20914 pages in 61 relations
> DETAIL:  A total of 14992 page slots are in use (including overhead).
> 14992 page slots are required to track all free space.
> Current limits are:  900000 page slots, 6000 relations, using 5659 kB.
> 
> This happens (at more or less the same point but with slightly different 
> figures) each time I try to run this command. It also happens even if I 
> only do vacuum verbose.

I am not sure what your question is. The above looks perfectly reasonable.

> 
> I can vacuum individual tables quite happily but I'm not sure I can 
> manage to do all of them individually.

See comment above about using a vacuum/vacuum analyze twice a day.

> 
> As you see, I've upped the page slots and relations (there are 131 
> tables in the database in total) and it seems to me like there is more 
> than enough.

Yep.

> 
> Any ideas would be gratefully received!

...? The only thing I would say is that you are entirely too many 
max_fsm_pages and max_fsm_relations. I would drop it back dow:

max_fsm_pages = 100000
max_fsm_relations = 1000

But that doesn't really answer your question as much as state that you 
don't need as much as you have.

Sincerely,

Joshua D. Drake



> Thanks
> Kieran Cooper
> Lyris UK
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 


-- 

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Vacuum stops with misleading max_fsm_pages error
  2007-04-16 21:51 Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
  2007-04-16 22:08 ` Re: Vacuum stops with misleading max_fsm_pages error Joshua D. Drake <jd@commandprompt.com>
@ 2007-04-16 22:36   ` Tom Lane <tgl@sss.pgh.pa.us>
  2007-04-17 07:35     ` Re: Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2007-04-16 22:36 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Kieran Cooper, Lyris UK <kieran@lyris.co.uk>; pgsql-admin

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Kieran Cooper, Lyris UK wrote:
>> INFO:  free space map contains 20914 pages in 61 relations
>> DETAIL:  A total of 14992 page slots are in use (including overhead).
>> 14992 page slots are required to track all free space.
>> Current limits are:  900000 page slots, 6000 relations, using 5659 kB.

> I am not sure what your question is. The above looks perfectly reasonable.

I think he's wondering why the second number is less than the first.
AFAICT that should be impossible after a VACUUM FULL, but there are
probably tables that haven't been touched by the VACUUM FULL --- stuff
in other databases being one obvious possibility.  As for the vacuum
not having done every table in the current database, did you run it
as superuser?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Vacuum stops with misleading max_fsm_pages error
  2007-04-16 21:51 Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
  2007-04-16 22:08 ` Re: Vacuum stops with misleading max_fsm_pages error Joshua D. Drake <jd@commandprompt.com>
  2007-04-16 22:36   ` Re: Vacuum stops with misleading max_fsm_pages error Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-17 07:35     ` Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
  0 siblings, 0 replies; 7+ messages in thread

From: Kieran Cooper, Lyris UK @ 2007-04-17 07:35 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin


> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Kieran Cooper, Lyris UK wrote:
>>> INFO:  free space map contains 20914 pages in 61 relations
>>> DETAIL:  A total of 14992 page slots are in use (including overhead).
>>> 14992 page slots are required to track all free space.
>>> Current limits are:  900000 page slots, 6000 relations, using 5659 kB.
>
>> I am not sure what your question is. The above looks perfectly 
>> reasonable.

> I think he's wondering why the second number is less than the first.
> AFAICT that should be impossible after a VACUUM FULL, but there are
> probably tables that haven't been touched by the VACUUM FULL --- stuff
> in other databases being one obvious possibility.  As for the vacuum
> not having done every table in the current database, did you run it
> as superuser?
>
> regards, tom lane

Thanks for your messages Tom and Josh.

The problem I'm finding is that the Vacuum finishes before it has touched 
all the tables in the database. When I run it on the same database in the 
same way on other servers, it does all the tables. There is only 1 database 
on this machine (in addition to template1 and template2). I'm running it 
from within the postgres command line, logged in as postgres.

Thanks
Kieran 





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Vacuum stops with misleading max_fsm_pages error
  2007-04-16 21:51 Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
@ 2007-04-16 22:14 ` Matthew T. O'Connor <matthew@zeut.net>
  2007-04-17 07:51   ` Re: Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
  1 sibling, 1 reply; 7+ messages in thread

From: Matthew T. O'Connor @ 2007-04-16 22:14 UTC (permalink / raw)
  To: Kieran Cooper, Lyris UK <kieran@lyris.co.uk>; +Cc: pgsql-admin

Kieran Cooper, Lyris UK wrote:
> We're managing a database on version 8.2.3 (running Lyris ListManager 
> email software). I ran a 'vacuum analyze full verbose' (we haven't been 
> able to get autovacuum to work properly on Lyris installs so we need to 
> run a full vacuum every week or so, and we'd been doing some work on 
> this machine so it needed it). It ran happily for quite a while - about 
> 2 thirds of the way through the database - but then ended with the lines


What is it about autovacuum that isn't working for you?



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Vacuum stops with misleading max_fsm_pages error
  2007-04-16 21:51 Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
  2007-04-16 22:14 ` Re: Vacuum stops with misleading max_fsm_pages error Matthew T. O'Connor <matthew@zeut.net>
@ 2007-04-17 07:51   ` Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
  2007-04-17 13:13     ` Re: Vacuum stops with misleading max_fsm_pages error Matthew O'Connor <matthew@zeut.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Kieran Cooper, Lyris UK @ 2007-04-17 07:51 UTC (permalink / raw)
  To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: pgsql-admin

> Kieran Cooper, Lyris UK wrote:
>> We're managing a database on version 8.2.3 (running Lyris ListManager 
>> email software). I ran a 'vacuum analyze full verbose' (we haven't been 
>> able to get autovacuum to work properly on Lyris installs so we need to 
>> run a full vacuum every week or so, and we'd been doing some work on this 
>> machine so it needed it). It ran happily for quite a while - about 2 
>> thirds of the way through the database - but then ended with the lines
>
>
> What is it about autovacuum that isn't working for you?

Hi Matthew. I have to admit that I haven't tested full yet, but here's the 
scenario:
When Lyris sends a mailing there is an intense amount of database activity - 
particularly on two tables. When I had auto vacuum set up, the database just 
wasn't responding fast enough, so the mailing speed dropped as Lyris 
dynamically adjusted based on the speed of db response. I guess what I need 
to do is tweak the intervals and the sleep time so that vacuum backs off 
when the database is really busy - do you have any thoughts on what settings 
I should try in order to acheive that?
Thanks so much
Kieran 





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Vacuum stops with misleading max_fsm_pages error
  2007-04-16 21:51 Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
  2007-04-16 22:14 ` Re: Vacuum stops with misleading max_fsm_pages error Matthew T. O'Connor <matthew@zeut.net>
  2007-04-17 07:51   ` Re: Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
@ 2007-04-17 13:13     ` Matthew O'Connor <matthew@zeut.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Matthew O'Connor @ 2007-04-17 13:13 UTC (permalink / raw)
  To: Kieran Cooper, Lyris UK <kieran@lyris.co.uk>; +Cc: pgsql-admin

Kieran Cooper, Lyris UK wrote:
>> What is it about autovacuum that isn't working for you?
> 
> Hi Matthew. I have to admit that I haven't tested full yet, but here's 
> the scenario:
> When Lyris sends a mailing there is an intense amount of database 
> activity - particularly on two tables. When I had auto vacuum set up, 
> the database just wasn't responding fast enough, so the mailing speed 
> dropped as Lyris dynamically adjusted based on the speed of db response. 
> I guess what I need to do is tweak the intervals and the sleep time so 
> that vacuum backs off when the database is really busy - do you have any 
> thoughts on what settings I should try in order to acheive that?


The sleep intervals are one thing, but what you probably really want to 
play with are they vacuum cost delay and limit settings.  This is 
throttle down how much IO autovacuum can consume.

http://www.postgresql.org/docs/8.2/interactive/runtime-config-autovacuum.html



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2007-04-17 13:13 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-16 21:51 Vacuum stops with misleading max_fsm_pages error Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
2007-04-16 22:08 ` Joshua D. Drake <jd@commandprompt.com>
2007-04-16 22:36   ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-17 07:35     ` Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
2007-04-16 22:14 ` Matthew T. O'Connor <matthew@zeut.net>
2007-04-17 07:51   ` Kieran Cooper, Lyris UK <kieran@lyris.co.uk>
2007-04-17 13:13     ` Matthew O'Connor <matthew@zeut.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox