pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCan postgres run autovacuum tasks when autovacuum is disabled?
9+ messages / 4 participants
[nested] [flat]
* Can postgres run autovacuum tasks when autovacuum is disabled?
@ 2014-07-30 07:12 Andrey Zhidenkov <pensnarik@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Andrey Zhidenkov @ 2014-07-30 07:12 UTC (permalink / raw)
To: pgsql-admin
I'm confused about this:
[postgres@srv log]$ grep autovacuum ~/data/postgresql.conf
autovacuum = off # Enable autovacuum subprocess? 'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
autovacuum_naptime = 10min # time between autovacuum runs
So, as you can see, autovacuum is disabled now and I could not seen any
autovacuum tasks about few hours. But now
I can see this:
srv=# select backend_start, query from pg_stat_activity where query ilike
'%vacuum%';
backend_start | query
-------------------------------+---------------------------------------------------------------------------------
2014-07-29 21:38:42.882383+04 | select backend_start, query from
pg_stat_activity where query ilike '%vacuum%';
2014-07-30 10:21:51.5511+04 | autovacuum: VACUUM ANALYZE customer_email
2014-07-30 10:43:19.49936+04 | autovacuum: VACUUM ANALYZE cdr_tech
(3 rows)
The problem is that table cdr_tech is very large (70Gb) and I couldn't wait
while autovacuum is finished, I would like to run vacuum manually at night
time.
Whan I do wrong?
Best regards, Andrey Zhidenkov
_
ASCII ribbon campaign ( )
against HTML e-mail X
/ \
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can postgres run autovacuum tasks when autovacuum is disabled?
@ 2014-07-30 07:39 David G Johnston <david.g.johnston@gmail.com>
parent: Andrey Zhidenkov <pensnarik@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: David G Johnston @ 2014-07-30 07:39 UTC (permalink / raw)
To: pgsql-admin
Andrey Zhidenkov wrote
> I'm confused about this:
>
> [postgres@srv log]$ grep autovacuum ~/data/postgresql.conf
> autovacuum = off # Enable autovacuum subprocess? 'on'
> log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
> autovacuum_naptime = 10min # time between autovacuum runs
>
> So, as you can see, autovacuum is disabled now and I could not seen any
> autovacuum tasks about few hours. But now
> I can see this:
>
> srv=# select backend_start, query from pg_stat_activity where query ilike
> '%vacuum%';
> backend_start |
> query
>
> -------------------------------+---------------------------------------------------------------------------------
> 2014-07-29 21:38:42.882383+04 | select backend_start, query from
> pg_stat_activity where query ilike '%vacuum%';
> 2014-07-30 10:21:51.5511+04 | autovacuum: VACUUM ANALYZE customer_email
> 2014-07-30 10:43:19.49936+04 | autovacuum: VACUUM ANALYZE cdr_tech
> (3 rows)
>
> The problem is that table cdr_tech is very large (70Gb) and I couldn't
> wait
> while autovacuum is finished, I would like to run vacuum manually at night
> time.
>
> Whan I do wrong?
>
> Best regards, Andrey Zhidenkov
>
> _
> ASCII ribbon campaign ( )
> against HTML e-mail X
> / \
The obvious question is whether you sighup/rebooted the server after making
the change?
It helps to show what your session sees as the current guc value.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Can-postgres-run-autovacuum-tasks-when-autovacuum-is-disable...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can postgres run autovacuum tasks when autovacuum is disabled?
@ 2014-07-30 07:45 Andrey Zhidenkov <pensnarik@gmail.com>
parent: David G Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Andrey Zhidenkov @ 2014-07-30 07:45 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
I run pgctl after making a change and saw in the log that new value of
"autovacuum" = off
On Wed, Jul 30, 2014 at 11:39 AM, David G Johnston <
david.g.johnston@gmail.com> wrote:
> Andrey Zhidenkov wrote
> > I'm confused about this:
> >
> > [postgres@srv log]$ grep autovacuum ~/data/postgresql.conf
> > autovacuum = off # Enable autovacuum subprocess? 'on'
> > log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
> > autovacuum_naptime = 10min # time between autovacuum runs
> >
> > So, as you can see, autovacuum is disabled now and I could not seen any
> > autovacuum tasks about few hours. But now
> > I can see this:
> >
> > srv=# select backend_start, query from pg_stat_activity where query ilike
> > '%vacuum%';
> > backend_start |
> > query
> >
> >
> -------------------------------+---------------------------------------------------------------------------------
> > 2014-07-29 21:38:42.882383+04 | select backend_start, query from
> > pg_stat_activity where query ilike '%vacuum%';
> > 2014-07-30 10:21:51.5511+04 | autovacuum: VACUUM ANALYZE
> customer_email
> > 2014-07-30 10:43:19.49936+04 | autovacuum: VACUUM ANALYZE cdr_tech
> > (3 rows)
> >
> > The problem is that table cdr_tech is very large (70Gb) and I couldn't
> > wait
> > while autovacuum is finished, I would like to run vacuum manually at
> night
> > time.
> >
> > Whan I do wrong?
> >
> > Best regards, Andrey Zhidenkov
> >
> > _
> > ASCII ribbon campaign ( )
> > against HTML e-mail X
> > / \
>
> The obvious question is whether you sighup/rebooted the server after making
> the change?
>
> It helps to show what your session sees as the current guc value.
>
> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Can-postgres-run-autovacuum-tasks-when-autovacuum-is-disable...
> Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
С уважением, Андрей Жиденков.
_
ASCII ribbon campaign ( )
against HTML e-mail X
/ \
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can postgres run autovacuum tasks when autovacuum is disabled?
@ 2014-07-30 07:47 Scott Marlowe <scott.marlowe@gmail.com>
parent: Andrey Zhidenkov <pensnarik@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2014-07-30 07:47 UTC (permalink / raw)
To: Andrey Zhidenkov <pensnarik@gmail.com>; +Cc: pgsql-admin
On Wed, Jul 30, 2014 at 1:12 AM, Andrey Zhidenkov <pensnarik@gmail.com> wrote:
> I'm confused about this:
>
> [postgres@srv log]$ grep autovacuum ~/data/postgresql.conf
> autovacuum = off # Enable autovacuum subprocess? 'on'
> log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
> autovacuum_naptime = 10min # time between autovacuum runs
>
> So, as you can see, autovacuum is disabled now and I could not seen any
> autovacuum tasks about few hours. But now
> I can see this:
>
> srv=# select backend_start, query from pg_stat_activity where query ilike
> '%vacuum%';
> backend_start | query
> -------------------------------+---------------------------------------------------------------------------------
> 2014-07-29 21:38:42.882383+04 | select backend_start, query from
> pg_stat_activity where query ilike '%vacuum%';
> 2014-07-30 10:21:51.5511+04 | autovacuum: VACUUM ANALYZE customer_email
> 2014-07-30 10:43:19.49936+04 | autovacuum: VACUUM ANALYZE cdr_tech
> (3 rows)
>
> The problem is that table cdr_tech is very large (70Gb) and I couldn't wait
> while autovacuum is finished, I would like to run vacuum manually at night
> time.
>
> Whan I do wrong?
Are you sure these aren't to prevent wrap-around?
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can postgres run autovacuum tasks when autovacuum is disabled?
@ 2014-07-30 07:57 David G Johnston <david.g.johnston@gmail.com>
parent: Andrey Zhidenkov <pensnarik@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: David G Johnston @ 2014-07-30 07:57 UTC (permalink / raw)
To: pgsql-admin
Please don't top post
Andrey Zhidenkov wrote
> I run pgctl after making a change and saw in the log that new value of
> "autovacuum" = off
>
>
> On Wed, Jul 30, 2014 at 11:39 AM, David G Johnston <
> david.g.johnston@
>> wrote:
>
>> Andrey Zhidenkov wrote
>> > I'm confused about this:
>> >
>> > [postgres@srv log]$ grep autovacuum ~/data/postgresql.conf
>> > autovacuum = off # Enable autovacuum subprocess? 'on'
>> > log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
>> > autovacuum_naptime = 10min # time between autovacuum runs
>> >
>> > So, as you can see, autovacuum is disabled now and I could not seen any
>> > autovacuum tasks about few hours. But now
>> > I can see this:
>> >
>> > srv=# select backend_start, query from pg_stat_activity where query
>> ilike
>> > '%vacuum%';
>> > backend_start |
>> > query
>> >
>> >
>> -------------------------------+---------------------------------------------------------------------------------
>> > 2014-07-29 21:38:42.882383+04 | select backend_start, query from
>> > pg_stat_activity where query ilike '%vacuum%';
>> > 2014-07-30 10:21:51.5511+04 | autovacuum: VACUUM ANALYZE
>> customer_email
>> > 2014-07-30 10:43:19.49936+04 | autovacuum: VACUUM ANALYZE cdr_tech
>> > (3 rows)
>> >
>> > The problem is that table cdr_tech is very large (70Gb) and I couldn't
>> > wait
>> > while autovacuum is finished, I would like to run vacuum manually at
>> night
>> > time.
>> >
>> > Whan I do wrong?
>> >
>> > Best regards, Andrey Zhidenkov
>> >
>> > _
>> > ASCII ribbon campaign ( )
>> > against HTML e-mail X
>> > / \
>>
>> The obvious question is whether you sighup/rebooted the server after
>> making
>> the change?
>>
>> It helps to show what your session sees as the current guc value.
>>
>> David J.
>>
It would be nice to see all of the view columns.
I'm also confused how you were able to see into the future...if you can
manage this for a period longer than about 13 hours you might be better off
getting a job on a psychic hotline.
My theory is that the queries you are seeing are those that are already
competed (though then why your own query doesn't show up confuses me).
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Can-postgres-run-autovacuum-tasks-when-autovacuum-is-disable...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Can postgres run autovacuum tasks when autovacuum is disabled?
@ 2014-07-30 08:00 David G Johnston <david.g.johnston@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G Johnston @ 2014-07-30 08:00 UTC (permalink / raw)
To: pgsql-admin
Scott Marlowe-2 wrote
> On Wed, Jul 30, 2014 at 1:12 AM, Andrey Zhidenkov <
> pensnarik@
> > wrote:
>> I'm confused about this:
>>
>> [postgres@srv log]$ grep autovacuum ~/data/postgresql.conf
>> autovacuum = off # Enable autovacuum subprocess? 'on'
>> log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
>> autovacuum_naptime = 10min # time between autovacuum runs
>>
>> So, as you can see, autovacuum is disabled now and I could not seen any
>> autovacuum tasks about few hours. But now
>> I can see this:
>>
>> srv=# select backend_start, query from pg_stat_activity where query ilike
>> '%vacuum%';
>> backend_start |
>> query
>> -------------------------------+---------------------------------------------------------------------------------
>> 2014-07-29 21:38:42.882383+04 | select backend_start, query from
>> pg_stat_activity where query ilike '%vacuum%';
>> 2014-07-30 10:21:51.5511+04 | autovacuum: VACUUM ANALYZE
>> customer_email
>> 2014-07-30 10:43:19.49936+04 | autovacuum: VACUUM ANALYZE cdr_tech
>> (3 rows)
>>
>> The problem is that table cdr_tech is very large (70Gb) and I couldn't
>> wait
>> while autovacuum is finished, I would like to run vacuum manually at
>> night
>> time.
>>
>> Whan I do wrong?
>
> Are you sure these aren't to prevent wrap-around?
This is my other theory, and probably the correct one. Is this something
that can be discovered from the logs (since nothing in the query provides
this detail)?
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Can-postgres-run-autovacuum-tasks-when-autovacuum-is-disable...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] Can postgres run autovacuum tasks when autovacuum is disabled?
@ 2014-07-30 12:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2014-07-30 12:38 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin; pgsql-bugs@postgresql.org
David G Johnston <david.g.johnston@gmail.com> writes:
> Scott Marlowe-2 wrote
>> Are you sure these aren't to prevent wrap-around?
> This is my other theory, and probably the correct one. Is this something
> that can be discovered from the logs (since nothing in the query provides
> this detail)?
In all recent versions of Postgres, the pg_stat_activity line should
include "(to prevent wraparound)" if that's what autovac thinks it's
doing.
However ... it looks to me like if the autovac launcher gets launched
forcibly because of a possible wraparound issue, it will keep running
normally after that even if autovacuum is off :-(. You need to SIGHUP
it to get it to quit again. (A second bug is that it will then honor the
SIGHUP and quit, even if the wraparound emergency is still present.)
So I'm betting that what happened, particularly given the OP's apparent
insistence on managing vacuums manually, is that a wraparound vacuum
was forced on some relatively small table and then the launcher kept
doing its thing after that. Since log_autovacuum_min_duration was
set to 0, there should be some evidence for this theory in the logs
(although it looks like the log entries *don't* say whether the
vacuum was for wraparound :-().
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] Can postgres run autovacuum tasks when autovacuum is disabled?
@ 2014-07-30 15:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2014-07-30 15:09 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin; pgsql-bugs@postgresql.org
I wrote:
> However ... it looks to me like if the autovac launcher gets launched
> forcibly because of a possible wraparound issue, it will keep running
> normally after that even if autovacuum is off :-(.
Nope, scratch that: I missed the bit before the launcher's main loop,
where it fires off one child and exits if it's launched when autovacuum
is already false.
Still, if it does fire one child process, it looks like that child will
vacuum everything that seems to need vacuuming in the target database.
We should probably adjust that so that tables not in danger of wraparound
get skipped.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] Can postgres run autovacuum tasks when autovacuum is disabled?
@ 2014-07-31 05:56 Andrey Zhidenkov <pensnarik@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Andrey Zhidenkov @ 2014-07-31 05:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G Johnston <david.g.johnston@gmail.com>; pgsql-admin; pgsql-bugs@postgresql.org
Thank you for your fix. It is really seems that this bug appears only when
some autovacuum jobs are active.
Andrey Zhidenkov.
On Wed, Jul 30, 2014 at 7:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> > However ... it looks to me like if the autovac launcher gets launched
> > forcibly because of a possible wraparound issue, it will keep running
> > normally after that even if autovacuum is off :-(.
>
> Nope, scratch that: I missed the bit before the launcher's main loop,
> where it fires off one child and exits if it's launched when autovacuum
> is already false.
>
> Still, if it does fire one child process, it looks like that child will
> vacuum everything that seems to need vacuuming in the target database.
> We should probably adjust that so that tables not in danger of wraparound
> get skipped.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
С уважением, Андрей Жиденков.
_
ASCII ribbon campaign ( )
against HTML e-mail X
/ \
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2014-07-31 05:56 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-07-30 07:12 Can postgres run autovacuum tasks when autovacuum is disabled? Andrey Zhidenkov <pensnarik@gmail.com>
2014-07-30 07:39 ` David G Johnston <david.g.johnston@gmail.com>
2014-07-30 07:45 ` Andrey Zhidenkov <pensnarik@gmail.com>
2014-07-30 07:57 ` David G Johnston <david.g.johnston@gmail.com>
2014-07-30 07:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2014-07-30 08:00 ` David G Johnston <david.g.johnston@gmail.com>
2014-07-30 12:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-07-30 15:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-07-31 05:56 ` Andrey Zhidenkov <pensnarik@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox