--000325559b32ddaa7c047da7fbc7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
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 kno=
w
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 processe=
s
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, se=
t
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 day=
s
i'll have them.
The question is if i'm right on my manual observations and if so if this ca=
n
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 lik=
e
to change settings based on current i/o statistics and planned cronjobs lik=
e
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 a=
n
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=3D# select * from pg_stat_activity where current_query<>'<IDLE>' a=
nd
usename=3D'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-2=
1
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-1=
9
11:57:21.380041+01 | |
11511 | postgres | 5960 | 10 | postgres | select * from
pg_stat_activity where current_query<>'<IDLE>' and usename=3D'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
--000325559b32ddaa7c047da7fbc7
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Hi,<br><br>i have some questions regarding <br>- behavior of vacuum and aut=
ovacuum after changing configuration and<br>- interaction between those two=
<br><br>i change the vacuum_cost_delay parameter to 0 in postgresql.conf an=
d reloaded the server (sighup). does that have an immediate effect on runni=
ng 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.<br>
<br>I changed the vacuum_cost_delay from 200 to 0 without seeing an immedia=
te effect.<br>I saw an effect when i startet new vacuums.<br>But I did not =
see an effect on new autovacuum processes or running processes not even aft=
er hours. autovacuum_vacuum_cost_delay is set to -1. So for my understandin=
g it should have an effect on autovacuum.<br>
<br>Then I changed autovacuum_vacuum_cost_delay to 200, reloaded the server=
, set it back to -1 and reloaded the server again.<br>Now I see an effect o=
n autovacuum.<br><br>unfortunately I don't have comparable statistics, =
but in a few hours or days i'll have them.<br>
<br>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.<br>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 cha=
nge settings based on current i/o statistics and planned cronjobs like back=
ups etc.<br>
<br>the second question i have is about knowing postgres what is doing vacu=
um and autocacuum at the same time.<br>my concern is about running manually=
a <span style=3D"font-family: courier new,monospace;">vacuum verbose tc.b1=
234competition;</span> and seeing that autovacuum is stating shortly afterw=
ards exactly the same sql. So for my understanding it would be great to sen=
d autovacuum somehow an information that there is a manual run of this and =
that vacuum/analyze.<br>
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?<br=
>Or better: does it make sense to run a manual vacuum if i have autovacuum =
running at the same time?<br>
<span style=3D"font-family: courier new,monospace;"><br>postgres=3D# select=
* from pg_stat_activity where current_query<>'<IDLE>' =
and usename=3D'postgres';</span><br style=3D"font-family: courier n=
ew,monospace;">
<span style=3D"font-family: courier new,monospace;">=A0datid | datname=A0 |=
procpid | usesysid | usename=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0 current_=
query=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | waiting |=A0=A0=A0=A0=A0=A0=A0=A0=
=A0 xact_start=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0 q=
uery_start=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0 backend_sta=
rt=A0=A0=A0=A0=A0=A0=A0=A0 | client_addr | client_port </span><br style=3D"=
font-family: courier new,monospace;">
<span style=3D"font-family: courier new,monospace;">-------+----------+----=
-----+----------+----------+-------------------------------- ---------------=
---------------------------------------+---------+---------- ---------------=
------+-------------------------------+--------------------- ----------+----=
---------+-------------</span><br style=3D"font-family: courier new,monospa=
ce;">
<span style=3D"font-family: courier new,monospace;">=A016396 | bd=A0=A0=A0=
=A0=A0=A0 |=A0=A0 11887 |=A0=A0=A0=A0=A0=A0 10 | postgres | autovacuum: ANA=
LYZE uc.bd_user_session_statistic=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A 0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0 | f=A0=
=A0=A0=A0=A0=A0 | 2010-01-21 07:49:43.793259+01 | 2010-01-21 07:49:43.79325=
9+01 | 2010-01-19 17:22:26.260505+01 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
|=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 </span><br style=3D"font-family: courier=
new,monospace;">
<span style=3D"font-family: courier new,monospace;">=A016396 | bd=A0=A0=A0=
=A0=A0=A0 |=A0=A0=A0=A0 188 |=A0=A0=A0=A0=A0=A0 10 | postgres | vacuum verb=
ose tc.b1234competition;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0 | f=A0=A0=A0=A0=A0=A0 | 2010-01-21 08:08:34.194203+01=
| 2010-01-21 08:08:34.194203+01 | 2010-01-21 08:08:34.177298+01 |=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0 -1</span><br style=
=3D"font-family: courier new,monospace;">
<span style=3D"font-family: courier new,monospace;">=A016396 | bd=A0=A0=A0=
=A0=A0=A0 |=A0=A0=A0 1836 |=A0=A0=A0=A0=A0=A0 10 | postgres | autovacuum: V=
ACUUM tc.b1234competition=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A 0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0 | t=A0=A0=A0=A0=A0=A0 | 2010-01-21 08:23:19.696658+01 | 201=
0-01-21 08:23:19.696658+01 | 2010-01-21 08:11:10.398047+01 |=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 </span><br style=
=3D"font-family: courier new,monospace;">
<span style=3D"font-family: courier new,monospace;">=A016396 | bd=A0=A0=A0=
=A0=A0=A0 |=A0=A0=A0 4431 |=A0=A0=A0=A0=A0=A0 10 | postgres | autovacuum: V=
ACUUM ANALYZE m123service.jms_message_log_entry=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | f=A0=A0=A0=A0=A0=A0 | 2010-=
01-19 13:12:52.284217+01 | 2010-01-19 13:12:52.284217+01 | 2010-01-19 11:57=
:21.380041+01 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0 </span><br style=3D"font-family: courier new,monospace;">
<span style=3D"font-family: courier new,monospace;">=A011511 | postgres |=
=A0=A0=A0 5960 |=A0=A0=A0=A0=A0=A0 10 | postgres | select * from pg_stat_ac=
tivity where current_query<>'<IDLE>' and usename=3D'=
;postgres'; | f=A0=A0=A0=A0=A0=A0 | 2010-01-21 08:23:31.67539+01=A0 | 2=
010-01-21 08:23:31.67539+01=A0 | 2010-01-21 08:13:36.422138+01 |=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0 -1</span><br style=
=3D"font-family: courier new,monospace;">
<span style=3D"font-family: courier new,monospace;">(5 rows)</span><br styl=
e=3D"font-family: courier new,monospace;"><br><br>best regards,<br>Uwe<br><=
br>
--000325559b32ddaa7c047da7fbc7--
