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.stehule@gmail.com>:

pá 30. 10. 2020 v 8:23 odesílatel Max Vikharev <bm.kinder@gmail.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.stehule@gmail.com>:

čt 29. 10. 2020 v 22:44 odesílatel PG Bug reporting form <noreply@postgresql.org> napsal:

The following bug has been logged on the website:

Bug reference:      16691
Logged by:          Max Vikharev
Email address:      bm.kinder@gmail.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.