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
