Обсуждение: autovacumm not working ?

Tomasz Rakowski wrote:
>  I have problem with frequently updated table (around 30.000 records and
> 1000 updates/minute, few indexes).
> After a while all queries to that table become much slower then at the
> begining
> (the number of records in the table is quite stable all the time).
>
> I can see that autovacuum is executed (select * from pg_stat_all_tables)
> so it should update statistics and free unused space.


Sounds like autovacuum is running, but not often enough to keep up with
this highly active table.  You may be able to get better results by
setting table specific autovacuum thresholds for this table so that it
get vacuumed more often.  However if your table is *very* active then
autovacuum may not be able to keep up even with the more aggressive
settings, this is a known problem which is hopefully addressed in 8.3,
some people solve this by turning off autovacuum for the highly active
table and using a cron script to vacuum a table every minute or so.

Tomasz Rakowski wrote:
> Matthew,
>
>   Thank you for reply.
>
>   I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01).
>   Then waited for autovacuum to be activated for given table
>   (watching  Server status window; about 1000 updates/min, 30.000 rows
>   in the table, so didn't have to wait too long).

Did you reload (pg_ctl reload) after changing the postgresql.conf
settings?  Also note that you can alter values for a specific table by
putting them in the pg_autovacuum table.

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this."                               (Fotis)
               (http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php)

Tomasz Rakowski wrote:
> Alvaro,
>
>   I changed autovacuum parametrs for this specific table in pg_autovacuum
>
> insert into pg_autovacuum    (vacrelid,enabled,vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor,
 
>                                             vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age)
>     values ( (select oid from pg_class where relname='t_ais_position'), True, 100, 0.01 , 100, 0.02, -1, -1, -1, -1 )
>
> Should I somehow let autovacuum deamon know about new table
> configuration or above insert is enough ?

The insert should be enough.  You do see the autovacuum process starting
on that database, right?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Tomasz Rakowski wrote:
>    I see autovacuum process starting in avarage every 5 minutes
> (table contains 30.000 records, and update rate is about 1000records /min).
>
>  But what is strange is that the number of pages allocated to the table
> are constant (at least for this hour: 500)
> and number of pages allocated to indexes are constantly growing (109 ->
> 145, 92 -> 250!!!, 194->256)
>
> But as I stated in first post after a while it gets worse and worse
> (thousands of allocated pages for the same number of records..)

You might want to reduce the autovacuum naptime.  Every 5 min may not be
frequent enough for you.  Also, you might want make sure your FSM
settings are high enough.

Tomasz Rakowski <mourawi@yahoo.com> writes:
> The strange thing is that number of pages allocated for "t_ais_position" table and "t_ais_position_pkey" index
haven'tchanged 
> (so autovacuum works ok on them) , but  the number of pages allocated to "ix_t_ais_position_update_time" index
increased 
> from 250  to 2218 (x 9 times).

> "ix_t_ais_position_update_time" index is created as "create index ix_t_ais_position_update_time  on
t_ais_position(update_time)",so it is suppose to be updated very frequently (as main table). The other index
"t_ais_position_pkey"is on primary key and values doesn't change at all .... 

I think you may be running into one of the usage patterns that btree
vacuuming doesn't clean up very well.  Do you initially create a lot of
rows with nearby update_times, and eventually remove all but a few?
The worst case is where you're leaving only one live row per index page.
The current VACUUM code can only recycle index pages that have become
totally empty ...

            regards, tom lane

Tomasz Rakowski wrote:
>
>  How restart of database server influances autovacuum process ?
>
> I think that somewhere on this mailing list I read that autovacuum in
> such case looses some important information and after database server
> restart will not behave as expected until VACUUM ANALYZE is executed.
> Is it true ?

No, this was only true in versions previous to 8.1.

Unless you stop the database in "immediate" mode.  If you do that, then
all the information that autovacuum uses is lost.  If you use "smart" or
"fast" it should work fine.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Porque francamente, si para saber manejarse a uno mismo hubiera que
rendir examen... ¿Quién es el machito que tendría carnet?"  (Mafalda)