Remove size limitations of vacuums dead_tuples array

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Remove size limitations of vacuums dead_tuples array

Ants Aasma-2
When dealing with a case where a 2TB table had 3 billion dead tuples I discovered that vacuum currently can't make use of more than 1GB of maintenance_work_mem - 179M tuples. This caused excessive amounts of index scanning even though there was plenty of memory available.

I didn't see any good reason for having this limit, so here is a patch that makes use of MemoryContextAllocHuge, and converts the array indexing to use size_t to lift a second limit at 12GB.

One potential problem with allowing larger arrays is that bsearch might no longer be the best way of determining if a ctid was marked dead. It might pay off to convert the dead tuples array to a hash table to avoid O(n log n) runtime when scanning indexes. I haven't done any profiling yet to see how big of a problem this is.

Second issue I noticed is that the dead_tuples array is always allocated max allowed size, unless the table can't possibly have that many tuples. It may make sense to allocate it based on estimated number of dead tuples and resize if needed.

Regards,
Ants Aasma

0001-Allow-vacuum-to-use-more-than-1GB-of-memory.patch (6K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Remove size limitations of vacuums dead_tuples array

Tomas Vondra-4
On Wed, Oct 09, 2019 at 03:58:11PM +0300, Ants Aasma wrote:

>When dealing with a case where a 2TB table had 3 billion dead tuples I
>discovered that vacuum currently can't make use of more than 1GB of
>maintenance_work_mem - 179M tuples. This caused excessive amounts of index
>scanning even though there was plenty of memory available.
>
>I didn't see any good reason for having this limit, so here is a patch that
>makes use of MemoryContextAllocHuge, and converts the array indexing to use
>size_t to lift a second limit at 12GB.
>
>One potential problem with allowing larger arrays is that bsearch might no
>longer be the best way of determining if a ctid was marked dead. It might
>pay off to convert the dead tuples array to a hash table to avoid O(n log
>n) runtime when scanning indexes. I haven't done any profiling yet to see
>how big of a problem this is.
>
>Second issue I noticed is that the dead_tuples array is always allocated
>max allowed size, unless the table can't possibly have that many tuples. It
>may make sense to allocate it based on estimated number of dead tuples and
>resize if needed.
>

There already was a attempt to make this improvement, see [1]. There was
a fairly long discussion about how to best do that (using other data
structure, not just a simple array). It kinda died about a year ago, but
I suppose there's a lot of relevant info in that thread.

[1] https://www.postgresql.org/message-id/CAGTBQpbDCaR6vv9%3DscXzuT8fSbckf%3Da3NgZdWFWZbdVugVht6Q%40mail.gmail.com


--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Remove size limitations of vacuums dead_tuples array

Ants Aasma-2
On Thu, 10 Oct 2019 at 17:05, Tomas Vondra <[hidden email]> wrote:
There already was a attempt to make this improvement, see [1]. There was
a fairly long discussion about how to best do that (using other data
structure, not just a simple array). It kinda died about a year ago, but
I suppose there's a lot of relevant info in that thread.

[1] https://www.postgresql.org/message-id/CAGTBQpbDCaR6vv9%3DscXzuT8fSbckf%3Da3NgZdWFWZbdVugVht6Q%40mail.gmail.com

Thanks for the pointer, wow that's a long thread. For some reason it did not consider lifting the INT_MAX tuples/12GB limitation. I'll see if I can pick up where that thread left off and push it along.

Regards,
Ants Aasma