Skip site navigation (1) Skip section navigation (2)

vacuum + autovacuum

From: Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: vacuum + autovacuum
Date: 2010-01-21 07:52:25
Message-ID: 8ea917511001202352l6b9eab18v45e5ff9f6d7e846c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Hi,

i have some questions regarding
- behavior of vacuum and autovacuum after changing configuration and
- interaction between those two

i change the vacuum_cost_delay parameter to 0 in postgresql.conf and
reloaded the server (sighup). does that have an immediate effect on running
vaccuums and autovacuum processes? for me it seems not, but i'd like to know
it to better understand and evaluate the i/o statistics i see.

I changed the vacuum_cost_delay from 200 to 0 without seeing an immediate
effect.
I saw an effect when i startet new vacuums.
But I did not see an effect on new autovacuum processes or running processes
not even after hours. autovacuum_vacuum_cost_delay is set to -1. So for my
understanding it should have an effect on autovacuum.

Then I changed autovacuum_vacuum_cost_delay to 200, reloaded the server, set
it back to -1 and reloaded the server again.
Now I see an effect on autovacuum.

unfortunately I don't have comparable statistics, but in a few hours or days
i'll have them.

The question is if i'm right on my manual observations and if so if this can
be improved. or if there is a workaround for this.
My goal is to configure as generic as possible autovacuum to get the best
throughput - or is there already work on this what could be shared. I'd like
to change settings based on current i/o statistics and planned cronjobs like
backups etc.

the second question i have is about knowing postgres what is doing vacuum
and autocacuum at the same time.
my concern is about running manually a vacuum verbose
tc.b1234competition;and seeing that autovacuum is stating shortly
afterwards exactly the same
sql. So for my understanding it would be great to send autovacuum somehow an
information that there is a manual run of this and that vacuum/analyze.
Does autovacuum look in the the columns pg_stat_all_tables.last_vacuum and
pg_stat_all_tables.last_analyze or does it only use the autovac columns?
Or better: does it make sense to run a manual vacuum if i have autovacuum
running at the same time?

postgres=# select * from pg_stat_activity where current_query<>'<IDLE>' and
usename='postgres';
 datid | datname  | procpid | usesysid | usename
|
current_query                                     | waiting |
xact_start           |          query_start          |
backend_start         | client_addr | client_port
-------+----------+---------+----------+----------+--------------------------------------------------------------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
 16396 | bd       |   11887 |       10 | postgres | autovacuum: ANALYZE
uc.bd_user_session_statistic                                     | f       |
2010-01-21 07:49:43.793259+01 | 2010-01-21 07:49:43.793259+01 | 2010-01-19
17:22:26.260505+01 |             |
 16396 | bd       |     188 |       10 | postgres | vacuum verbose
tc.b1234competition;                                                  |
f       | 2010-01-21 08:08:34.194203+01 | 2010-01-21 08:08:34.194203+01 |
2010-01-21 08:08:34.177298+01 |             |          -1
 16396 | bd       |    1836 |       10 | postgres | autovacuum: VACUUM
tc.b1234competition                                               | t
| 2010-01-21 08:23:19.696658+01 | 2010-01-21 08:23:19.696658+01 | 2010-01-21
08:11:10.398047+01 |             |
 16396 | bd       |    4431 |       10 | postgres | autovacuum: VACUUM
ANALYZE m123service.jms_message_log_entry                         | f
| 2010-01-19 13:12:52.284217+01 | 2010-01-19 13:12:52.284217+01 | 2010-01-19
11:57:21.380041+01 |             |
 11511 | postgres |    5960 |       10 | postgres | select * from
pg_stat_activity where current_query<>'<IDLE>' and usename='postgres'; |
f       | 2010-01-21 08:23:31.67539+01  | 2010-01-21 08:23:31.67539+01  |
2010-01-21 08:13:36.422138+01 |             |          -1
(5 rows)


best regards,
Uwe

pgsql-admin by date

Next:From: Kevin GrittnerDate: 2010-01-21 17:03:57
Subject: Re: last insert/update for a table
Previous:From: Brad NicholsonDate: 2010-01-20 17:12:38
Subject: Re: PostgreSQL backup idea

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group