Thread: killing vacuum analyze process

Julie Nishimura <juliezain@hotmail.com> 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

P {margin-top:0;margin-bottom:0;}

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 <tgl@sss.pgh.pa.us>
Sent: Tuesday, September 3, 2019 4:11 PM
To: Julie Nishimura <juliezain@hotmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: killing vacuum analyze process

 

Julie Nishimura <juliezain@hotmail.com> 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

Julie Nishimura <juliezain@hotmail.com> writes:
> Thank you Tom. I can see bunch of old processes running ROLLBACK... Should I kill them or they only way to clear
thoseis 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