Re: [GENERAL] Vacuum and state_change

On 06/09/2017 09:13 AM, armand pirvu wrote:
>
>> On Jun 9, 2017, at 11:01 AM, Adrian Klaver <adrian.klaver@aklaver.com
>> <mailto:adrian.klaver@aklaver.com>> wrote:
>>
>> On 06/09/2017 08:45 AM, armand pirvu wrote:
>>> Hi
>>> Had a couple of processes blocking the vacuum so I terminated them using
>>> select pg_terminate_backend(pid);
>>> Running the following
>>> select distinct pid, backend_start, query_start, state_change, state,
>>> query from pg_catalog.pg_stat_activity order by 1;
>>>   pid  |         backend_start         |          query_start
>>>          |         state_change          | state  | query
>>>
-------+-------------------------------+-------------------------------+-------------------------------+--------+--------------------------------------------------------------------------------------------------------------------------
>>>  10677 | 2017-06-09 10:25:49.189848-05 | 2017-06-09
>>> 10:33:43.598805-05 | 2017-06-09 10:33:43.599091-05 | idle   | SELECT 1
>>>  11096 | 2017-06-09 10:27:03.686588-05 | 2017-06-09 10:33:56.28736-05
>>>  | 2017-06-09 10:33:56.287364-05 | active | select distinct pid,
>>> backend_start, query_start, state_change, state, query from
>>> pg_catalog.pg_stat_activity order by 1;
>>>  13277 | 2017-06-09 07:48:49.506686-05 | 2017-06-09
>>> 07:48:52.887185-05 | 2017-06-09 07:48:52.887188-05 | active |
>>> autovacuum: VACUUM csischema.tf_purchased_badge
>>>  13484 | 2017-06-09 10:31:54.127672-05 | 2017-06-09
>>> 10:33:47.137938-05 | 2017-06-09 10:33:47.138226-05 | idle   | SELECT 1
>>>  16886 | 2017-06-09 07:56:49.033893-05 | 2017-06-09
>>> 07:56:49.078369-05 | 2017-06-09 07:56:49.078371-05 | active |
>>> autovacuum: VACUUM csischema.tf_purchases_person
>>>  25387 | 2017-06-09 05:32:08.079397-05 | 2017-06-09
>>> 05:32:08.385728-05 | 2017-06-09 05:32:08.385731-05 | active |
>>> autovacuum: VACUUM csischema.tf_demographic_response_person
>>>  37465 | 2017-06-09 08:50:58.992002-05 | 2017-06-09
>>> 08:51:21.506829-05 | 2017-06-09 08:51:21.506831-05 | active |
>>> autovacuum: VACUUM csischema.tf_transaction_item_person
>>> I did notice that state_change did not change one bit
>>
>> Did the state change?
>>
>
> No and that was what got me worried

Are these large tables?


If you are on Postgres 9.6:

https://www.postgresql.org/docs/9.6/static/progress-reporting.html

>
>
>>> Does that mean that something is not quite right with the vacuums ?
>>
>> Might want to take a look at:
>>
>> https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW
>>
>>
>>> Thank you
>>> Armand
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>


--
Adrian Klaver
adrian.klaver@aklaver.com