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

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)

пт, 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.
>>>>
>>>>