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

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.
>>>
>>>