Re: BUG #16691: Autovacuum stops processing certain databases until postgresql rebooted

pá 30. 10. 2020 v 10:13 odesílatel Max Vikharev <bm(dot)kinder(at)gmail(dot)com>
napsal:

> Ok, next time it happens I'll do this debug.
> Maybe anything more that i can collect?
> Last time it happened i enabled DEBUG logging and that's what autovacuum
> process do (see attach or
> https://gist.github.com/mvikharev/62e2aa690cd3d256fa1880d44b169313)
>

you can check a pg_locks - if table are not locked

> пт, 30 окт. 2020 г. в 10:29, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>
>>
>>
>> pá 30. 10. 2020 v 8:23 odesílatel Max Vikharev <bm(dot)kinder(at)gmail(dot)com>
>> napsal:
>>
>>> He Pavel,
>>>
>>> > Did you check pg_stat_activity if there are some sessions in state
>>> "idle in transactions" before restart?
>>> Of course, we discovered all known widely possible reasons:
>>> 1. We monitor long transactions and there are no active transactions
>>> or opened sessions with idle in transaction state. Also we have
>>> statement_timeout
>>> 2. There are no active transactons on slave and hot_standby_feedback=off
>>> 3. We do not use prepared transactions
>>> 4. There are no repliaction_slots.
>>>
>>>
>>
>> maybe there are some lock leak - this is probably postgres bug, but
>> unfortunately it can be detected only in your environment - if you are not
>> able to prepare reproducer test.
>>
>> can you attach autovacuum process by gdb and read whot this process does?
>>
>>
>>
>>
>>
>>
>>>
>>> пт, 30 окт. 2020 г. в 07:48, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>
>>>>
>>>>
>>>> čt 29. 10. 2020 v 22:44 odesílatel PG Bug reporting form <
>>>> noreply(at)postgresql(dot)org> napsal:
>>>>
>>>>> The following bug has been logged on the website:
>>>>>
>>>>> Bug reference: 16691
>>>>> Logged by: Max Vikharev
>>>>> Email address: bm(dot)kinder(at)gmail(dot)com
>>>>> PostgreSQL version: 12.3
>>>>> Operating system: Ubuntu 16.04.5 LTS
>>>>> Description:
>>>>>
>>>>> Hi,
>>>>>
>>>>> Previously i've reported BUG # 16620. But there is no answer on my new
>>>>> mails, so i have to open this new report.
>>>>>
>>>>> Currently we have to restart postgresql 12.3 every 5-7 days to make
>>>>> it work
>>>>> properly on 5 terabyte cluster with ~ 50 databases. The problem is that
>>>>> autovacuum stops to process certain databases and cycles on the only
>>>>> one...
>>>>> Until we restart cluster.
>>>>>
>>>>> We've made a trigger and now detecting very situation when it happen.
>>>>> And it
>>>>> happen every 5-7 day.
>>>>> So postgresql is loosing actual statistic, we see not effecttive
>>>>> plans, we
>>>>> need to restart postgresql and also we have to run vacuum full
>>>>> explicitly
>>>>> frequently. So this all is huge headake.
>>>>>
>>>>
>>>> please, check open transactions. Restart, close all sessions, all
>>>> transactions.
>>>>
>>>> Did you check pg_stat_activity if there are some sessions in state
>>>> "idle in transactions" before restart?
>>>>
>>>> Regards
>>>>
>>>> Pavel
>>>>
>>>>>
>>>>> Please help us investigate the roots of problem.
>>>>>
>>>>>