pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuumdb idle processes
9+ messages / 4 participants
[nested] [flat]
* vacuumdb idle processes
@ 2021-06-11 17:58 Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-11 18:33 ` Re: vacuumdb idle processes Tom Lane <tgl@sss.pgh.pa.us>
2021-06-13 14:09 ` Re: vacuumdb idle processes Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Nikhil Shetty @ 2021-06-11 17:58 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have done a setup to run vacuumdb with 8 parallel jobs daily. It has been
running quite well.
Just today I saw there is an aggressive autovacuum process running(to
prevent wraparound) on one of the table. vacuumdb which started later
spawned the 8 connections. One connection (doing vacuum on the table on
which an aggressive autovacuum is running) is waiting for "autovacuum(to
prevent wraparound)" to complete while the other 7 connections are just
sitting idle.
I am okay that one connection is waiting since an aggressive autovacuum is
running on that table but how come other connections have not released the
sesion yet? Any reason for this?
Thanks,
Nikhil
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb idle processes
2021-06-11 17:58 vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
@ 2021-06-11 18:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-12 07:04 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2021-06-11 18:33 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: pgsql-admin
Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> I have done a setup to run vacuumdb with 8 parallel jobs daily. It has been
> running quite well.
Apparently not all that well, if it failed to keep us out of an
autovacuum-to-prevent-wraparound situation. I suppose you had autovacuum
disabled because you thought this lashup was sufficient?
> Just today I saw there is an aggressive autovacuum process running(to
> prevent wraparound) on one of the table. vacuumdb which started later
> spawned the 8 connections. One connection (doing vacuum on the table on
> which an aggressive autovacuum is running) is waiting for "autovacuum(to
> prevent wraparound)" to complete while the other 7 connections are just
> sitting idle.
> I am okay that one connection is waiting since an aggressive autovacuum is
> running on that table but how come other connections have not released the
> sesion yet? Any reason for this?
Perhaps this is the last remaining table so vacuumdb has nothing
else for them to do.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb idle processes
2021-06-11 17:58 vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-11 18:33 ` Re: vacuumdb idle processes Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-06-12 07:04 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-13 12:43 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Nikhil Shetty @ 2021-06-12 07:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi Tom,
Apparently not all that well, if it failed to keep us out of an
> autovacuum-to-prevent-wraparound situation. I suppose you had autovacuum
> disabled because you thought this lashup was sufficient?
No, autovacuum was enabled but seems it was not able to catch up with the
amount of transaction or it might have been delayed due to postgres
favouring other txns that conflict -- Need to check this though
Perhaps this is the last remaining table so vacuumdb has nothing
> else for them to do.
>
Does this mean vacuumdb will release all db connections(jobs - 8 in this
case) only after all connections have performed their vacuum and then
disconnected? Even if one is pending the others will be still connected but
in idle state? Is my understanding correct?
Thanks and Regards,
Nikhil
On Sat, Jun 12, 2021 at 12:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> > I have done a setup to run vacuumdb with 8 parallel jobs daily. It has
> been
> > running quite well.
>
> Apparently not all that well, if it failed to keep us out of an
> autovacuum-to-prevent-wraparound situation. I suppose you had autovacuum
> disabled because you thought this lashup was sufficient?
>
> > Just today I saw there is an aggressive autovacuum process running(to
> > prevent wraparound) on one of the table. vacuumdb which started later
> > spawned the 8 connections. One connection (doing vacuum on the table on
> > which an aggressive autovacuum is running) is waiting for "autovacuum(to
> > prevent wraparound)" to complete while the other 7 connections are just
> > sitting idle.
>
> > I am okay that one connection is waiting since an aggressive autovacuum
> is
> > running on that table but how come other connections have not released
> the
> > sesion yet? Any reason for this?
>
> Perhaps this is the last remaining table so vacuumdb has nothing
> else for them to do.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb idle processes
2021-06-11 17:58 vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-11 18:33 ` Re: vacuumdb idle processes Tom Lane <tgl@sss.pgh.pa.us>
2021-06-12 07:04 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
@ 2021-06-13 12:43 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-15 20:20 ` Re: vacuumdb idle processes Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Nikhil Shetty @ 2021-06-13 12:43 UTC (permalink / raw)
To: pgsql-admin; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Hi,
I tested this scenario and it seems if vacuumdb is started with multiple
jobs and one of the jobs doesn't complete due to a lock or whatever reason,
other jobs will stay idle and don't release the connection until the stuck
job is finished.
For my understanding, why do we need this behaviour?
Thanks and Regards,
Nikhil
On Sat, Jun 12, 2021 at 12:34 PM Nikhil Shetty <nikhil.dba04@gmail.com>
wrote:
> Hi Tom,
>
> Apparently not all that well, if it failed to keep us out of an
>> autovacuum-to-prevent-wraparound situation. I suppose you had autovacuum
>> disabled because you thought this lashup was sufficient?
>
>
> No, autovacuum was enabled but seems it was not able to catch up with the
> amount of transaction or it might have been delayed due to postgres
> favouring other txns that conflict -- Need to check this though
>
> Perhaps this is the last remaining table so vacuumdb has nothing
>> else for them to do.
>>
>
> Does this mean vacuumdb will release all db connections(jobs - 8 in this
> case) only after all connections have performed their vacuum and then
> disconnected? Even if one is pending the others will be still connected but
> in idle state? Is my understanding correct?
>
> Thanks and Regards,
> Nikhil
>
> On Sat, Jun 12, 2021 at 12:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Nikhil Shetty <nikhil.dba04@gmail.com> writes:
>> > I have done a setup to run vacuumdb with 8 parallel jobs daily. It has
>> been
>> > running quite well.
>>
>> Apparently not all that well, if it failed to keep us out of an
>> autovacuum-to-prevent-wraparound situation. I suppose you had autovacuum
>> disabled because you thought this lashup was sufficient?
>>
>> > Just today I saw there is an aggressive autovacuum process running(to
>> > prevent wraparound) on one of the table. vacuumdb which started later
>> > spawned the 8 connections. One connection (doing vacuum on the table on
>> > which an aggressive autovacuum is running) is waiting for "autovacuum(to
>> > prevent wraparound)" to complete while the other 7 connections are just
>> > sitting idle.
>>
>> > I am okay that one connection is waiting since an aggressive autovacuum
>> is
>> > running on that table but how come other connections have not released
>> the
>> > sesion yet? Any reason for this?
>>
>> Perhaps this is the last remaining table so vacuumdb has nothing
>> else for them to do.
>>
>> regards, tom lane
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb idle processes
2021-06-11 17:58 vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-11 18:33 ` Re: vacuumdb idle processes Tom Lane <tgl@sss.pgh.pa.us>
2021-06-12 07:04 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-13 12:43 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
@ 2021-06-15 20:20 ` Jeff Janes <jeff.janes@gmail.com>
2021-06-15 20:52 ` Re: vacuumdb idle processes Ron <ronljohnsonjr@gmail.com>
2021-06-16 06:26 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Jeff Janes @ 2021-06-15 20:20 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: pgsql-admin; Tom Lane <tgl@sss.pgh.pa.us>
On Sun, Jun 13, 2021 at 8:43 AM Nikhil Shetty <nikhil.dba04@gmail.com>
wrote:
> Hi,
>
> I tested this scenario and it seems if vacuumdb is started with multiple
> jobs and one of the jobs doesn't complete due to a lock or whatever reason,
> other jobs will stay idle and don't release the connection until the stuck
> job is finished.
>
> For my understanding, why do we need this behaviour?
>
I don't think we **need** this behavior, it is just a simple way to wait
for each one to finish and then close it; waiting for each specific one in
the order it is present in the list. Is there an important reason we need
a more complex behavior, closing each one as soon as it becomes idle once
the work queue is empty?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb idle processes
2021-06-11 17:58 vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-11 18:33 ` Re: vacuumdb idle processes Tom Lane <tgl@sss.pgh.pa.us>
2021-06-12 07:04 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-13 12:43 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-15 20:20 ` Re: vacuumdb idle processes Jeff Janes <jeff.janes@gmail.com>
@ 2021-06-15 20:52 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Ron @ 2021-06-15 20:52 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/15/21 3:20 PM, Jeff Janes wrote:
> On Sun, Jun 13, 2021 at 8:43 AM Nikhil Shetty <nikhil.dba04@gmail.com
> <mailto:nikhil.dba04@gmail.com>> wrote:
>
> Hi,
>
> I tested this scenario and it seems if vacuumdb is started with
> multiple jobs and one of the jobs doesn't complete due to a lock or
> whatever reason, other jobs will stay idle and don't release the
> connection until the stuck job is finished.
>
> For my understanding, why do we need this behaviour?
>
>
> I don't think we **need** this behavior, it is just a simple way to wait
> for each one to finish and then close it; waiting for each specific one in
> the order it is present in the list. Is there an important reason we need
> a more complex behavior, closing each one as soon as it becomes idle once
> the work queue is empty?
"Disconnect when you're finished" is Best Practice, no?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb idle processes
2021-06-11 17:58 vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-11 18:33 ` Re: vacuumdb idle processes Tom Lane <tgl@sss.pgh.pa.us>
2021-06-12 07:04 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-13 12:43 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-15 20:20 ` Re: vacuumdb idle processes Jeff Janes <jeff.janes@gmail.com>
@ 2021-06-16 06:26 ` Nikhil Shetty <nikhil.dba04@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Nikhil Shetty @ 2021-06-16 06:26 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-admin; Tom Lane <tgl@sss.pgh.pa.us>
Hi Jeff,
I don't think we **need** this behavior, it is just a simple way to wait
> for each one to finish and then close it; waiting for each specific one in
> the order it is present in the list. Is there an important reason we need
> a more complex behavior, closing each one as soon as it becomes idle once
> the work queue is empty?
>
Reason for releasing connections in my case would be the "autovacuum (to
prevent wraparound)" taking time to complete . This process should finish
to completion to prevent any issues in the databases due to wraparound and
it will take some time if the table is big and contains many dead tuples.
If vacuumdb starts with 10 parallel jobs during this period, one of the
jobs will be locked(by autovacuum) and the other nine will be idle(after
finishing their part) for days just wasting connections until we terminate
the locked process.
Thanks and Regards,
Nikhil
On Wed, Jun 16, 2021 at 1:50 AM Jeff Janes <jeff.janes@gmail.com> wrote:
> On Sun, Jun 13, 2021 at 8:43 AM Nikhil Shetty <nikhil.dba04@gmail.com>
> wrote:
>
>> Hi,
>>
>> I tested this scenario and it seems if vacuumdb is started with multiple
>> jobs and one of the jobs doesn't complete due to a lock or whatever reason,
>> other jobs will stay idle and don't release the connection until the stuck
>> job is finished.
>>
>> For my understanding, why do we need this behaviour?
>>
>
> I don't think we **need** this behavior, it is just a simple way to wait
> for each one to finish and then close it; waiting for each specific one in
> the order it is present in the list. Is there an important reason we need
> a more complex behavior, closing each one as soon as it becomes idle once
> the work queue is empty?
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb idle processes
2021-06-11 17:58 vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
@ 2021-06-13 14:09 ` Jeff Janes <jeff.janes@gmail.com>
2021-06-13 15:11 ` Re: vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Jeff Janes @ 2021-06-13 14:09 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: pgsql-admin
On Fri, Jun 11, 2021 at 1:58 PM Nikhil Shetty <nikhil.dba04@gmail.com>
wrote:
> Hi,
>
> I have done a setup to run vacuumdb with 8 parallel jobs daily. It has
> been running quite well.
>
What version of PostgreSQL is this? What options do you run vacuumdb
with? Is it run as a superuser?
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb idle processes
2021-06-11 17:58 vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-13 14:09 ` Re: vacuumdb idle processes Jeff Janes <jeff.janes@gmail.com>
@ 2021-06-13 15:11 ` Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Nikhil Shetty @ 2021-06-13 15:11 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-admin
Hi Jeff,
What version of PostgreSQL is this?
>
Postgres version is 11.7.
What options do you run vacuumdb with?
Command is vacuumdb -avz -j 4
Is it run as a superuser?
Yes, it is run as a superuser
On Sun, Jun 13, 2021 at 7:39 PM Jeff Janes <jeff.janes@gmail.com> wrote:
>
>
> On Fri, Jun 11, 2021 at 1:58 PM Nikhil Shetty <nikhil.dba04@gmail.com>
> wrote:
>
>> Hi,
>>
>> I have done a setup to run vacuumdb with 8 parallel jobs daily. It has
>> been running quite well.
>>
>
> What version of PostgreSQL is this? What options do you run vacuumdb
> with? Is it run as a superuser?
>
> Cheers,
>
> Jeff
>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2021-06-16 06:26 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-11 17:58 vacuumdb idle processes Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-11 18:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-12 07:04 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-13 12:43 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-15 20:20 ` Jeff Janes <jeff.janes@gmail.com>
2021-06-15 20:52 ` Ron <ronljohnsonjr@gmail.com>
2021-06-16 06:26 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2021-06-13 14:09 ` Jeff Janes <jeff.janes@gmail.com>
2021-06-13 15:11 ` Nikhil Shetty <nikhil.dba04@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