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



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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/


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> "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



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> 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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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/i...utovacuum.html

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match