Re: Question regarding autovacuum

Scott Marlowe wrote:
> On 8/28/07, Karl Denninger <karl(at)denninger(dot)net> wrote:
>
>> Am I correct in that this number will GROW over time? Or is what I see
>> right now (with everything running ok) all that the system
>> will ever need?
>>
>
> They will grow at first to accomodate your typical load of dead tuples
> created between regular vacuums.
>
> Then they should reach a steady state where they will slowly grow as
> your activity levels increase.
>
> So it's a good idea to allocate 20 to 50% more than what vacuum
> verbose says you'll need for overhead. also keep in mind that vacuum
> verbose only tells you what the one db in the server needs. If you
> have multiple dbs in your postgresql service, you'll need to run
> vacuum verbose on all of them after X time (typical time between your
> vacuums) and add the needed free space together to get the total
> needed.
>
>
>> If the latter, then I'm WELL within limits and I guess I need to tune the
>> autovacuum parameters to be more aggressive; system views show it IS being
>> run.
>>
>> INFO: free space map contains 5895 pages in 639 relations
>> DETAIL: A total of 14976 page slots are in use (including overhead).
>> 14976 page slots are required to track all free space.
>> Current limits are: 179200 page slots, 1000 relations, using 1115 kB.
>>
>
> Yeah, that looks good. Note that the preferred state for pgsql is to
> have 10-25% free space in frequently updated tables, rather than
> removing it all with reindex / vacuum full. This keeps the files from
> getting fragmented AND keeps the OS from having to constantly allocate
> more space for the tables. Just cron up something to run vacuum
> verbose everynight and email it to you to peruse over coffee in the
> morning, and compare to previous nights. that'll give you an idea of
> how you're fsm is holding up.
>
>
That implies, however, that I need to make autovacuum more aggressive -
in other words, it means that in all probability the fsm maps are not
the problem.

What I have noticed is that after a half-day or so of normal use the
system get notably slower on the same queries, but a vacuum full analyze
puts it right back to where it was.

So SOMETHING is getting clogged up.......

-- Karl