killing vacuum analyze process

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

killing vacuum analyze process

Julie Nishimura
Hello there,
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu
It seems like we have one process running since 8/19. Is it any repercussion if we kill it?
postgres=# select * from pg_stat_activity where pid = '18986';
-[ RECORD 1 ]----+-------------------------------------------------------------------------
datid            | 2342921
datname          | symphony_middleware
pid              | 18986
usesysid         | 10
usename          | postgres
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2019-08-19 02:00:59.683198+00
xact_start       | 2019-08-19 02:03:54.191741+00
query_start      | 2019-08-19 02:03:54.191741+00
state_change     | 2019-08-19 02:03:54.191742+00
wait_event_type  | BufferPin
wait_event       | BufferPin
state            | active
backend_xid      |
backend_xmin     | 1075730757
query            | autovacuum: VACUUM ANALYZE public.resource_build (to prevent wraparound)

Reply | Threaded
Open this post in threaded view
|

Re: killing vacuum analyze process

Tom Lane-2
Julie Nishimura <[hidden email]> writes:
> PostgreSQL 9.6.2 on x86_64-pc-linux-gnu
> It seems like we have one process running since 8/19. Is it any repercussion if we kill it?

> postgres=# select * from pg_stat_activity where pid = '18986';
> -[ RECORD 1 ]----+-------------------------------------------------------------------------
> datid            | 2342921
> datname          | symphony_middleware
> pid              | 18986
> usesysid         | 10
> usename          | postgres
> application_name |
> client_addr      |
> client_hostname  |
> client_port      |
> backend_start    | 2019-08-19 02:00:59.683198+00
> xact_start       | 2019-08-19 02:03:54.191741+00
> query_start      | 2019-08-19 02:03:54.191741+00
> state_change     | 2019-08-19 02:03:54.191742+00
> wait_event_type  | BufferPin
> wait_event       | BufferPin
> state            | active
> backend_xid      |
> backend_xmin     | 1075730757
> query            | autovacuum: VACUUM ANALYZE public.resource_build (to prevent wraparound)

Since this is an anti-wraparound vacuum, autovacuum is just going to
launch another one pretty soon if you kill this one.  Assuming that
the buffer pin blockage is real and not some kind of broken shared
memory state, the new one will hang up at the same spot.  You'd be
better advised to find out what's pinning that buffer and kill that.

Admittedly this is easier said than done, since there's not much
infrastructure for seeing what's happening at that level.  But you
could look for transactions that are at least as old as this one and
have some kind of lock on that table (according to pg_locks).

If there are no such transactions, then the corrupt-shared-memory
hypothesis becomes likely, and a postmaster restart is indicated.

BTW, you really ought to be running something newer than 9.6.2.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: killing vacuum analyze process

Julie Nishimura
Thank you Tom. I can see bunch of old processes running ROLLBACK... Should I kill them or they only way to clear those is to restart the server?

postgres=# select * from pg_stat_activity where datname ='symphony_middleware' and  query_start < '08/20/2019' and query like '%ROLLBACK%';
  datid  |       datname       |  pid   | usesysid |       usename       | application_name | client_addr  | client_hostname | client_port |         backend_start         | xact_start |          query_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |  query  
---------+---------------------+--------+----------+---------------------+------------------+--------------+-----------------+-------------+-------------------------------+------------+-------------------------------+-------------------------------+-----------------+------------+-------+-------------+--------------+----------
 2342921 | symphony_middleware |  72165 |    16407 | symphony_middleware |                  | 10.24.33.226 |                 |       37252 | 2019-06-04 13:17:56.204483+00 |            | 2019-06-24 13:51:52.834401+00 | 2019-06-24 13:51:52.834479+00 |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 159407 |    16407 | symphony_middleware |                  | 10.24.33.6   |                 |       45482 | 2019-07-29 16:23:36.271366+00 |            | 2019-08-16 18:28:27.924116+00 | 2019-08-16 18:28:27.92419+00  |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 135443 |    16407 | symphony_middleware |                  | 10.24.33.226 |                 |       47712 | 2019-05-31 16:17:55.143017+00 |            | 2019-08-13 15:17:01.685057+00 | 2019-08-13 15:17:01.685103+00 |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 135442 |    16407 | symphony_middleware |                  | 10.24.33.226 |                 |       47710 | 2019-05-31 16:17:55.132574+00 |            | 2019-08-13 15:17:32.973151+00 | 2019-08-13 15:17:32.97322+00  |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 135440 |    16407 | symphony_middleware |                  | 10.24.33.226 |                 |       47706 | 2019-05-31 16:17:55.082091+00 |            | 2019-06-24 17:23:15.519127+00 | 2019-06-24 17:23:15.519181+00 |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware |  38211 |    16407 | symphony_middleware |                  | 10.24.33.6   |                 |       43318 | 2019-06-04 10:33:19.742976+00 |            | 2019-06-24 17:23:09.212067+00 | 2019-06-24 17:23:09.212117+00 |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 136304 |    16407 | symphony_middleware |                  | 10.24.33.6   |                 |       51012 | 2019-05-31 16:20:23.911493+00 |            | 2019-08-19 10:17:33.284189+00 | 2019-08-19 10:17:33.284256+00 |                 |            | idle  |             |              | ROLLBACK
(7 rows)


From: Tom Lane <[hidden email]>
Sent: Tuesday, September 3, 2019 4:11 PM
To: Julie Nishimura <[hidden email]>
Cc: [hidden email] <[hidden email]>
Subject: Re: killing vacuum analyze process
 
Julie Nishimura <[hidden email]> writes:
> PostgreSQL 9.6.2 on x86_64-pc-linux-gnu
> It seems like we have one process running since 8/19. Is it any repercussion if we kill it?

> postgres=# select * from pg_stat_activity where pid = '18986';
> -[ RECORD 1 ]----+-------------------------------------------------------------------------
> datid            | 2342921
> datname          | symphony_middleware
> pid              | 18986
> usesysid         | 10
> usename          | postgres
> application_name |
> client_addr      |
> client_hostname  |
> client_port      |
> backend_start    | 2019-08-19 02:00:59.683198+00
> xact_start       | 2019-08-19 02:03:54.191741+00
> query_start      | 2019-08-19 02:03:54.191741+00
> state_change     | 2019-08-19 02:03:54.191742+00
> wait_event_type  | BufferPin
> wait_event       | BufferPin
> state            | active
> backend_xid      |
> backend_xmin     | 1075730757
> query            | autovacuum: VACUUM ANALYZE public.resource_build (to prevent wraparound)

Since this is an anti-wraparound vacuum, autovacuum is just going to
launch another one pretty soon if you kill this one.  Assuming that
the buffer pin blockage is real and not some kind of broken shared
memory state, the new one will hang up at the same spot.  You'd be
better advised to find out what's pinning that buffer and kill that.

Admittedly this is easier said than done, since there's not much
infrastructure for seeing what's happening at that level.  But you
could look for transactions that are at least as old as this one and
have some kind of lock on that table (according to pg_locks).

If there are no such transactions, then the corrupt-shared-memory
hypothesis becomes likely, and a postmaster restart is indicated.

BTW, you really ought to be running something newer than 9.6.2.

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: killing vacuum analyze process

Ron-2
Those are all idle.  I've got a cron job to kill all idle processes more than 60 (or 30, I don't remember) minutes old.

On 9/3/19 6:29 PM, Julie Nishimura wrote:
Thank you Tom. I can see bunch of old processes running ROLLBACK... Should I kill them or they only way to clear those is to restart the server?

postgres=# select * from pg_stat_activity where datname ='symphony_middleware' and  query_start < '08/20/2019' and query like '%ROLLBACK%';
  datid  |       datname       |  pid   | usesysid |       usename       | application_name | client_addr  | client_hostname | client_port |         backend_start         | xact_start |          query_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |  query  
---------+---------------------+--------+----------+---------------------+------------------+--------------+-----------------+-------------+-------------------------------+------------+-------------------------------+-------------------------------+-----------------+------------+-------+-------------+--------------+----------
 2342921 | symphony_middleware |  72165 |    16407 | symphony_middleware |                  | 10.24.33.226 |                 |       37252 | 2019-06-04 13:17:56.204483+00 |            | 2019-06-24 13:51:52.834401+00 | 2019-06-24 13:51:52.834479+00 |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 159407 |    16407 | symphony_middleware |                  | 10.24.33.6   |                 |       45482 | 2019-07-29 16:23:36.271366+00 |            | 2019-08-16 18:28:27.924116+00 | 2019-08-16 18:28:27.92419+00  |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 135443 |    16407 | symphony_middleware |                  | 10.24.33.226 |                 |       47712 | 2019-05-31 16:17:55.143017+00 |            | 2019-08-13 15:17:01.685057+00 | 2019-08-13 15:17:01.685103+00 |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 135442 |    16407 | symphony_middleware |                  | 10.24.33.226 |                 |       47710 | 2019-05-31 16:17:55.132574+00 |            | 2019-08-13 15:17:32.973151+00 | 2019-08-13 15:17:32.97322+00  |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 135440 |    16407 | symphony_middleware |                  | 10.24.33.226 |                 |       47706 | 2019-05-31 16:17:55.082091+00 |            | 2019-06-24 17:23:15.519127+00 | 2019-06-24 17:23:15.519181+00 |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware |  38211 |    16407 | symphony_middleware |                  | 10.24.33.6   |                 |       43318 | 2019-06-04 10:33:19.742976+00 |            | 2019-06-24 17:23:09.212067+00 | 2019-06-24 17:23:09.212117+00 |                 |            | idle  |             |              | ROLLBACK
 2342921 | symphony_middleware | 136304 |    16407 | symphony_middleware |                  | 10.24.33.6   |                 |       51012 | 2019-05-31 16:20:23.911493+00 |            | 2019-08-19 10:17:33.284189+00 | 2019-08-19 10:17:33.284256+00 |                 |            | idle  |             |              | ROLLBACK
(7 rows)


From: Tom Lane [hidden email]
Sent: Tuesday, September 3, 2019 4:11 PM
To: Julie Nishimura [hidden email]
Cc: [hidden email] [hidden email]
Subject: Re: killing vacuum analyze process
 
Julie Nishimura [hidden email] writes:
> PostgreSQL 9.6.2 on x86_64-pc-linux-gnu
> It seems like we have one process running since 8/19. Is it any repercussion if we kill it?

> postgres=# select * from pg_stat_activity where pid = '18986';
> -[ RECORD 1 ]----+-------------------------------------------------------------------------
> datid            | 2342921
> datname          | symphony_middleware
> pid              | 18986
> usesysid         | 10
> usename          | postgres
> application_name |
> client_addr      |
> client_hostname  |
> client_port      |
> backend_start    | 2019-08-19 02:00:59.683198+00
> xact_start       | 2019-08-19 02:03:54.191741+00
> query_start      | 2019-08-19 02:03:54.191741+00
> state_change     | 2019-08-19 02:03:54.191742+00
> wait_event_type  | BufferPin
> wait_event       | BufferPin
> state            | active
> backend_xid      |
> backend_xmin     | 1075730757
> query            | autovacuum: VACUUM ANALYZE public.resource_build (to prevent wraparound)

Since this is an anti-wraparound vacuum, autovacuum is just going to
launch another one pretty soon if you kill this one.  Assuming that
the buffer pin blockage is real and not some kind of broken shared
memory state, the new one will hang up at the same spot.  You'd be
better advised to find out what's pinning that buffer and kill that.

Admittedly this is easier said than done, since there's not much
infrastructure for seeing what's happening at that level.  But you
could look for transactions that are at least as old as this one and
have some kind of lock on that table (according to pg_locks).

If there are no such transactions, then the corrupt-shared-memory
hypothesis becomes likely, and a postmaster restart is indicated.

BTW, you really ought to be running something newer than 9.6.2.

                        regards, tom lane

--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: killing vacuum analyze process

Tom Lane-2
In reply to this post by Julie Nishimura
Julie Nishimura <[hidden email]> writes:
> Thank you Tom. I can see bunch of old processes running ROLLBACK... Should I kill them or they only way to clear those is to restart the server?

As Ron pointed out, those processes aren't actually running ROLLBACK;
that was just their last query.  They're idle (and not "idle in
transaction") so in theory they're not holding any locks.

After thinking more about it I realize that the process holding the
buffer pin doesn't have to be as old as the VACUUM; so you probably
should just look at pg_locks and see what else has got a lock on
the table the VACUUM is trying to process.  The hypothetical offender
is probably somewhat old, or you'd not have noticed the blockage,
but we don't have any clear evidence as to how old.

                        regards, tom lane