Обсуждение: Vacuum stops with misleading max_fsm_pages error
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
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/
"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
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?
> "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
> 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
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