Обсуждение: vacuumdb idle processes

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

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

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

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