Request a Demo
Home   >   mailing lists

Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes - Mailing list pgsql-hackers

From Radim Marek
Subject Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes
Date
Msg-id CAJgoLkJLJOjtiAdoZTkRwL7rEsfZZ6aAFX8n7ou6xrOmKLyXMw@mail.gmail.com
Whole thread
In response to Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes  (shihao zhong <zhong950419@gmail.com>)
Responses Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes
List pgsql-hackers
Tree view
Hello, 


> This seems like a rather low limit that will definitely affect
> folks in the field.

I am not a heavy user of pg_repack, just curious, why do we think
105 million is a very small number?

As a medium-heavy repack user over the last decade, I'd say this limit is acceptable for now. Online repacking of a large table is rarely done without planning, and what decides whether you hit the limit is storage throughput (how long the run takes) times the rate of change on the table.

I put numbers on that in [1]. The budget is roughly 29k changed rows/s divided by the run's duration in hours. Generous for a 250 GB table, tight (circa 1.5k rows/s) for 5 TB on a modest cloud VM. But at that size a run takes most of a day, and you are already planning around everything else boing on hold - vacuum, disk saturation, WAL volume, etc. The change budget is more or less "just" one more item on that list.

My point in starting this thread was to make sure people affected on v19 know the limit exists, and the fact it might attract new audience that previously was not using repacking heavily. The worst case is losing all that work at the very last step, quite possibly while you're already dealing with something close to an incident. If 19 ships with it, I think it deserves a sentence in the REPACK docs, next to the disk space note.

Radim

PS: I've been using pg_squeeze for exactly those scenarios as pg_repack has some limitations (ironically one of them was FOR ALL TABLES without exception when logical replication is already used). For me personally REPACK (CONCURRENTLY) as now planned for v19 is improvement in all operational aspects.

> An "easy" fix for this might be to mark that allocation as
> using palloc_huge() somehow.

The request that fails is the comboCids array in GetComboCommandId(),
not the hash table. 8 bytes times 209,715,200 entries is the
1677721600 in Radim's error. I do not see a limit in the hash table.

I tried repalloc_array_extended() there. With 110 million replayed updates
master fails with that error, and the patched build completes with
correct data. It only changes runs that fail today. From reading the
code, the next limits are the int array size at about 1.7 billion
entries, and then 2^32 commands.

It does not lower the memory use. I measured 48 bytes for each
change, so the limit today also caps this at about 5 GB. Without it,
1 billion changes need about 50 GB, and a host with less gets the
OOM killer, not an ERROR.

Experimental patch attached.

Thanks,
Shihao


pgsql-hackers by date:

Previous
From: Chao Li
Date:
Subject: Re: pg_walinspect: add functions to locate and list WAL by time and LSN
Next
From: Andrey Borodin
Date:
Subject: Re: Compression of bigger WAL records