Thread: Question regarding autovacuum

Running 8.2.4.

The following is in my postgresql.conf:

# - Query/Index Statistics Collector -

#stats_command_string = on
update_process_title = on

stats_start_collector = on              # needed for block or row stats
                                        # (change requires restart)
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off      # (change requires restart)


# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------

autovacuum = on                         # enable autovacuum subprocess?
                                        # 'on' requires
stats_start_collector
                                        # and stats_row_level to also be on
autovacuum_naptime = 10min              # time between autovacuum runs
#autovacuum_vacuum_threshold = 500      # min # of tuple updates before
autovacuum_vacuum_threshold = 200       # min # of tuple updates before
                                        # vacuum
autovacuum_analyze_threshold = 125      # min # of tuple updates before
#autovacuum_analyze_threshold = 250     # min # of tuple updates before
                                        # analyze
#autovacuum_vacuum_scale_factor = 0.2   # fraction of rel size before
autovacuum_vacuum_scale_factor = 0.1    # fraction of rel size before
                                        # vacuum
autovacuum_analyze_scale_factor = 0.05  # fraction of rel size before
#autovacuum_analyze_scale_factor = 0.1  # fraction of rel size before
                                        # analyze
autovacuum_freeze_max_age = 200000000   # maximum XID age before forced
vacuum
                                        # (change requires restart)
autovacuum_vacuum_cost_delay = -1       # default vacuum cost delay for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1       # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit


How do I know if the autovacuum is actually running?

The reason I believe its not - the database in question is being hit
VERY HARD with both updates and queries.  Its a forum, and there are
updates on essentially every access (user's IP address is updated, time
last "touched" the account is updated, etc)

Anyway, after anywhere from a few hours to a day or so, performance goes
straight in the toilet.  The system starts thrashing the disk hard -
indicating that there's a major problem trying to keep the working set
in memory; if not caught quickly it deteriorates to the point that
access time rises so that the maximum connection limit is hit and then
users get "Dbms connection errors" (while the load average goes sky-high
as well and disk I/O is pinned).

A manual "Vacuum full analyze" fixes it immediately.

But... .shouldn't autovacuum prevent this?  Is there some way to look in
a log somewhere and see if and when the autovacuum is being run - and on
what?

--
Karl Denninger (karl@denninger.net)
http://www.denninger.net




%SPAMBLOCK-SYS: Matched [@postgresql.org+], message ok

Karl Denninger wrote:

> A manual "Vacuum full analyze" fixes it immediately.
>
> But... .shouldn't autovacuum prevent this?  Is there some way to look in a
> log somewhere and see if and when the autovacuum is being run - and on
> what?

Are your FSM settings enough to keep track of the dead space you have?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

I don't know.  How do I check?

Karl Denninger (karl@denninger.net)
http://www.denninger.net

Alvaro Herrera wrote:

Karl Denninger wrote:
 

A manual "Vacuum full analyze" fixes it immediately.

But... .shouldn't autovacuum prevent this?  Is there some way to look in a 
log somewhere and see if and when the autovacuum is being run - and on 
what?   

Are your FSM settings enough to keep track of the dead space you have?
 

Karl Denninger <karl@denninger.net> writes:
> But... .shouldn't autovacuum prevent this?  Is there some way to look in
> a log somewhere and see if and when the autovacuum is being run - and on
> what?

There's no log messages (at the default log verbosity anyway).  But you
could look into the pg_stat views for the last vacuum time for each table.

            regards, tom lane

Karl Denninger wrote:

>> Are your FSM settings enough to keep track of the dead space you have?
>>
> I don't know.  How do I check?

vacuum verbose;

Toward the bottom you will see something like:
...
1200 page slots are required to track all free space.
Current limits are:  453600 page slots, 1000 relations, using 2723 kB.
....

Make sure your current limits have a higher number than the page slots
required.

Cheers,
Steve

Tom Lane wrote:

Karl Denninger <karl@denninger.net> writes: 

But... .shouldn't autovacuum prevent this?  Is there some way to look in 
a log somewhere and see if and when the autovacuum is being run - and on 
what?   

There's no log messages (at the default log verbosity anyway).  But you
could look into the pg_stat views for the last vacuum time for each table.
		regards, tom lane
 

It looks like it IS being run.

Now the problem is, what's going on?  Maybe running out of fsm_map entries?  Hmnmmmmm..... vacuum full fixes it, which I assume reclaims those, yes?  I wonder if increasing that quite radically (I have lots of server RAM and SYSV SHM has been cranked wide) would take care of it....

-- Karl

Steve Crawford wrote:

Karl Denninger wrote:
 

Are your FSM settings enough to keep track of the dead space you have?
     

I don't know.  How do I check?   

vacuum verbose;

Toward the bottom you will see something like:
...
1200 page slots are required to track all free space.
Current limits are:  453600 page slots, 1000 relations, using 2723 kB.
....

Make sure your current limits have a higher number than the page slots
required.

Cheers,
Steve
 

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?

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.

-- Karl Denninger

On 8/28/07, Karl Denninger <karl@denninger.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.

Scott Marlowe wrote:

On 8/28/07, Karl Denninger <karl@denninger.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 systemwill 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 relationsDETAIL:  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

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> 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.

No, that's not true --- the numbers it prints are cluster-wide.
However, they are only guaranteed to be up-to-date with respect
to the DB you just finished vacuuming.  So the right strategy
is to vacuum all your active DBs, using VERBOSE on the last one,
and then believe what it tells you (plus a suitable fudge factor
for future growth, as Scott says).

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

Right.  If you are doing frequent vacuum fulls, you are really trying to
keep the DB smaller than its ideal size --- and that means you might be
getting an unrealistically small estimate from this process.  Try to do
without the VAC FULLs for awhile and see if more frequent plain vacuums
aren't enough.

It seems likely that Karl needs to increase the aggressiveness of
autovac's threshold and scale parameters, so that it runs more often.

            regards, tom lane

How is it possibile to check if autovacuum is running in 8.1.x?

"Show Autovacuum" gives me on and also i see evidence in logs where
autovacuum writes "LOG:  autovacuum: processing database xxxx".

However i have no idea of what tables the autovacuum daemon is
processing because there aren't autovacuum info columns on
pg_stat_all_tables (as there are for 8.2.x).

Also I'm asking this because the size of the pg_clog is >200M and I am
worried about possible transaction  ID wraparound failures...

Thank you,
Denis

Tom Lane ha scritto:
> Karl Denninger <karl@denninger.net> writes:
>
>> But... .shouldn't autovacuum prevent this?  Is there some way to look in
>> a log somewhere and see if and when the autovacuum is being run - and on
>> what?
>>
>
> There's no log messages (at the default log verbosity anyway).  But you
> could look into the pg_stat views for the last vacuum time for each table.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>