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

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.

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