public inbox for [email protected]
help / color / mirror / Atom feedvacuum process running for more than 2 days, still in scanning heap phase
6+ messages / 3 participants
[nested] [flat]
* vacuum process running for more than 2 days, still in scanning heap phase
@ 2025-11-12 18:09 Sbob <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Sbob @ 2025-11-12 18:09 UTC (permalink / raw)
To: [email protected]
All;
We have a vacuum process that has been running for 2 days, the table is
12GB in total size and vacuum_cost_delay is at 0
The process shows as active in pg_stat_activity, and pg_blocking_pids
returns no blockers
select pg_blocking_pids(1581295);
pg_blocking_pids
------------------
{}
(1 row)
A select from from pg_stat_progress_vacuum where pid = 1581295; shows
the phase as 'scanning heap' and the heap_blks_total, heap_blks_scanned,
max_dead_tuples and num_dead_tuples have shown the same values for at
least the past hour
pid | 1581295
datid | 16398
datname | prod_server_1
relid | 22532
phase | scanning heap
heap_blks_total | 571437
heap_blks_scanned | 344577
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples | 155388267
num_dead_tuples | 199013
We actually tried a pg_terminate_backend on it and it does not die
Thanks in advance for any advice
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum process running for more than 2 days, still in scanning heap phase
@ 2025-11-12 18:26 Álvaro Herrera <[email protected]>
parent: Sbob <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Álvaro Herrera @ 2025-11-12 18:26 UTC (permalink / raw)
To: Sbob <[email protected]>; +Cc: [email protected]
On 2025-Nov-12, Sbob wrote:
> We have a vacuum process that has been running for 2 days, the table
> is 12GB in total size and vacuum_cost_delay is at 0
Is it autovacuum? Because if so, the autovacuum_vacuum_cost_delay
setting would be used instead of this one. Also check the table config
(\d+) in case there are autovacuum settings there, which could make
vacuum slower on this particular table.
What PG version again?
> heap_blks_total | 571437
> heap_blks_scanned | 344577
> heap_blks_vacuumed | 0
> index_vacuum_count | 0
> max_dead_tuples | 155388267
> num_dead_tuples | 199013
Yeah, this seems really slow. Maybe have a look at the wait events in
pg_stat_activity to see if you can figure out what is holding it back.
> We actually tried a pg_terminate_backend on it and it does not die
Hmm, maybe there's something going wrong with it. I've seen corrupted
btree indexes make a vacuum go into infinite loops because of loops in
the index structure. I would take a few backtraces with gdb or such.
Maybe if an index is corrupt in that way, it would also explain why it
doesn't interrupt.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar
al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum process running for more than 2 days, still in scanning heap phase
@ 2025-11-12 18:33 Sbob <[email protected]>
parent: Álvaro Herrera <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Sbob @ 2025-11-12 18:33 UTC (permalink / raw)
To: ; +Cc: [email protected]
On 11/12/25 11:26 AM, Álvaro Herrera wrote:
> On 2025-Nov-12, Sbob wrote:
>
>> We have a vacuum process that has been running for 2 days, the table
>> is 12GB in total size and vacuum_cost_delay is at 0
> Is it autovacuum? Because if so, the autovacuum_vacuum_cost_delay
> setting would be used instead of this one. Also check the table config
> (\d+) in case there are autovacuum settings there, which could make
> vacuum slower on this particular table.
>
> What PG version again?
>
> This is not autovacuum, we ran a vacuum freeze manyally
>
> Version 15
>> heap_blks_total | 571437
>> heap_blks_scanned | 344577
>> heap_blks_vacuumed | 0
>> index_vacuum_count | 0
>> max_dead_tuples | 155388267
>> num_dead_tuples | 199013
> Yeah, this seems really slow. Maybe have a look at the wait events in
> pg_stat_activity to see if you can figure out what is holding it back.
pg_stat_activity shows no wait events and shows state as active
>> We actually tried a pg_terminate_backend on it and it does not die
> Hmm, maybe there's something going wrong with it. I've seen corrupted
> btree indexes make a vacuum go into infinite loops because of loops in
> the index structure. I would take a few backtraces with gdb or such.
> Maybe if an index is corrupt in that way, it would also explain why it
> doesn't interrupt.
I'll give it a shot, thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum process running for more than 2 days, still in scanning heap phase
@ 2025-11-12 18:59 Pavan Kumar <[email protected]>
parent: Sbob <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Pavan Kumar @ 2025-11-12 18:59 UTC (permalink / raw)
To: Sbob <[email protected]>; +Cc: [email protected]
Hello Sbob,
try to use pg_repack. you need to install extension in the database.
SET maintenance_work_mem = '2GB';
VACUUM (VERBOSE, PARALLEL 4) my_table; >> adjust parallel based on your
CPU's
I hope this helps
On Wed, Nov 12, 2025 at 12:33 PM Sbob <[email protected]> wrote:
>
> On 11/12/25 11:26 AM, Álvaro Herrera wrote:
> > On 2025-Nov-12, Sbob wrote:
> >
> >> We have a vacuum process that has been running for 2 days, the table
> >> is 12GB in total size and vacuum_cost_delay is at 0
> > Is it autovacuum? Because if so, the autovacuum_vacuum_cost_delay
> > setting would be used instead of this one. Also check the table config
> > (\d+) in case there are autovacuum settings there, which could make
> > vacuum slower on this particular table.
> >
> > What PG version again?
> >
> > This is not autovacuum, we ran a vacuum freeze manyally
> >
> > Version 15
>
>
>
> >> heap_blks_total | 571437
> >> heap_blks_scanned | 344577
> >> heap_blks_vacuumed | 0
> >> index_vacuum_count | 0
> >> max_dead_tuples | 155388267
> >> num_dead_tuples | 199013
> > Yeah, this seems really slow. Maybe have a look at the wait events in
> > pg_stat_activity to see if you can figure out what is holding it back.
> pg_stat_activity shows no wait events and shows state as active
> >> We actually tried a pg_terminate_backend on it and it does not die
> > Hmm, maybe there's something going wrong with it. I've seen corrupted
> > btree indexes make a vacuum go into infinite loops because of loops in
> > the index structure. I would take a few backtraces with gdb or such.
> > Maybe if an index is corrupt in that way, it would also explain why it
> > doesn't interrupt.
>
>
> I'll give it a shot, thanks
>
>
>
>
>
>
--
*Regards,#! Pavan Kumar----------------------------------------------*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell # 267-799-3182 # pavan.dba27 (Gtalk) *
*India # 9000459083*
*Take Risks; if you win, you will be very happy. If you lose you will be
Wise *
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum process running for more than 2 days, still in scanning heap phase
@ 2025-11-12 19:01 Álvaro Herrera <[email protected]>
parent: Sbob <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Álvaro Herrera @ 2025-11-12 19:01 UTC (permalink / raw)
To: Sbob <[email protected]>; +Cc: [email protected]
On 2025-Nov-12, Sbob wrote:
> This is not autovacuum, we ran a vacuum freeze manyally
Oh, well, I guess this might be very slow if it decides to freeze a lot
of tuples. Maybe have a look at the rate at which it generates WAL.
Why did you run vacuum freeze anyway?
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"We’ve narrowed the problem down to the customer’s pants being in a situation
of vigorous combustion" (Robert Haas, Postgres expert extraordinaire)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum process running for more than 2 days, still in scanning heap phase
@ 2025-11-12 19:08 Sbob <[email protected]>
parent: Álvaro Herrera <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Sbob @ 2025-11-12 19:08 UTC (permalink / raw)
To: ; +Cc: [email protected]
On 11/12/25 12:01 PM, Álvaro Herrera wrote:
> On 2025-Nov-12, Sbob wrote:
>
>> This is not autovacuum, we ran a vacuum freeze manyally
> Oh, well, I guess this might be very slow if it decides to freeze a lot
> of tuples. Maybe have a look at the rate at which it generates WAL.
>
> Why did you run vacuum freeze anyway?
working with a new client, have not yet uncovered why but they run these
vacuum freeze ops daily
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-11-12 19:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-12 18:09 vacuum process running for more than 2 days, still in scanning heap phase Sbob <[email protected]>
2025-11-12 18:26 ` Álvaro Herrera <[email protected]>
2025-11-12 18:33 ` Sbob <[email protected]>
2025-11-12 18:59 ` Pavan Kumar <[email protected]>
2025-11-12 19:01 ` Álvaro Herrera <[email protected]>
2025-11-12 19:08 ` Sbob <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox