pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHigh QPS, random index writes and vacuum
8+ messages / 5 participants
[nested] [flat]
* High QPS, random index writes and vacuum
@ 2023-04-18 00:35 peter plachta <pplachta@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: peter plachta @ 2023-04-18 00:35 UTC (permalink / raw)
To: pgsql-performance
Hi all
The company I work for has a large (50+ instances, 2-4 TB each) Postgres
install. One of the key problems we are facing in vanilla Postgres is
vacuum behavior on high QPS (20K writes/s), random index access on UUIDs.
In one case the table is 50Gb and has 3 indexes which are also 50Gb each.
It takes 20 hours to vacuum the entire thing, where bulk of the time is
spent doing 'index vacuuming'. The table is then instantly vacuumed again.
I increased work_mem to 2Gb, decreased sleep threshold to 2ms and increased
the IO limit to 2000. I also changed the autovacuum thresholds for this
table.
I understand that doing random index writes is not a good strategy, but, 20
hours to vacuum 200Gb is excessive.
My question is: what is the recommended strategy to deal with such cases in
Postgres?
Thanks very much!!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: High QPS, random index writes and vacuum
@ 2023-04-18 00:38 Peter Geoghegan <pg@bowt.ie>
parent: peter plachta <pplachta@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Peter Geoghegan @ 2023-04-18 00:38 UTC (permalink / raw)
To: peter plachta <pplachta@gmail.com>; +Cc: pgsql-performance
On Mon, Apr 17, 2023 at 5:35 PM peter plachta <pplachta@gmail.com> wrote:
> My question is: what is the recommended strategy to deal with such cases in Postgres?
You didn't say what version of Postgres you're using...
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: High QPS, random index writes and vacuum
@ 2023-04-18 02:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: peter plachta <pplachta@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-04-18 02:01 UTC (permalink / raw)
To: peter plachta <pplachta@gmail.com>; +Cc: pgsql-performance
peter plachta <pplachta@gmail.com> writes:
> The company I work for has a large (50+ instances, 2-4 TB each) Postgres
> install. One of the key problems we are facing in vanilla Postgres is
> vacuum behavior on high QPS (20K writes/s), random index access on UUIDs.
Indexing on a UUID column is an antipattern, because you're pretty much
guaranteed the worst-case random access patterns for both lookups and
insert/delete/maintenance cases. Can you switch to timestamps or
the like?
There are proposals out there for more database-friendly ways of
generating UUIDs than the traditional ones, but nobody's gotten
around to implementing that in Postgres AFAIK.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: High QPS, random index writes and vacuum
@ 2023-04-18 02:40 David Rowley <dgrowleyml@gmail.com>
parent: peter plachta <pplachta@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: David Rowley @ 2023-04-18 02:40 UTC (permalink / raw)
To: peter plachta <pplachta@gmail.com>; +Cc: pgsql-performance
On Tue, 18 Apr 2023 at 12:35, peter plachta <pplachta@gmail.com> wrote:
> I increased work_mem to 2Gb
maintenance_work_mem is the configuration option that vacuum uses to
control how much memory it'll make available for storage of dead
tuples. I believe 1GB would allow 178,956,970 tuples to be stored
before multiple passes would be required. The chunk of memory for dead
tuple storage is capped at 1GB.
David
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: High QPS, random index writes and vacuum
@ 2023-04-18 02:43 peter plachta <pplachta@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: peter plachta @ 2023-04-18 02:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thank you Tom.
Version: I sheepishly admit it's 9.6, 10 and 11 (it's Azure Single Server,
that's another story).
I am definitely looking at redoing the way we do UUIDs... but that' s not a
trivial change given the volume of data we have + 24/7 workload.
I was trying to understand whether there are any known workarounds for
random access + index vacuums. Are my vacuum times 'normal' ?
On Mon, Apr 17, 2023 at 7:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> peter plachta <pplachta@gmail.com> writes:
> > The company I work for has a large (50+ instances, 2-4 TB each) Postgres
> > install. One of the key problems we are facing in vanilla Postgres is
> > vacuum behavior on high QPS (20K writes/s), random index access on UUIDs.
>
> Indexing on a UUID column is an antipattern, because you're pretty much
> guaranteed the worst-case random access patterns for both lookups and
> insert/delete/maintenance cases. Can you switch to timestamps or
> the like?
>
> There are proposals out there for more database-friendly ways of
> generating UUIDs than the traditional ones, but nobody's gotten
> around to implementing that in Postgres AFAIK.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: High QPS, random index writes and vacuum
@ 2023-04-18 02:44 peter plachta <pplachta@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: peter plachta @ 2023-04-18 02:44 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-performance
Thank you David -- I increased this to 1GB as well (seeing as that was the
max). We are doing mostly single passes now.
On Mon, Apr 17, 2023 at 7:40 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Tue, 18 Apr 2023 at 12:35, peter plachta <pplachta@gmail.com> wrote:
> > I increased work_mem to 2Gb
>
> maintenance_work_mem is the configuration option that vacuum uses to
> control how much memory it'll make available for storage of dead
> tuples. I believe 1GB would allow 178,956,970 tuples to be stored
> before multiple passes would be required. The chunk of memory for dead
> tuple storage is capped at 1GB.
>
> David
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: High QPS, random index writes and vacuum
@ 2023-04-18 02:49 Thomas Munro <thomas.munro@gmail.com>
parent: peter plachta <pplachta@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Thomas Munro @ 2023-04-18 02:49 UTC (permalink / raw)
To: peter plachta <pplachta@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Tue, Apr 18, 2023 at 2:43 PM peter plachta <pplachta@gmail.com> wrote:
> I was trying to understand whether there are any known workarounds for random access + index vacuums. Are my vacuum times 'normal' ?
Ah, it's not going to help on the old versions you mentioned, but for
what it's worth: I remember noticing that I could speed up vacuum of
uncorrelated indexes using parallel vacuum (v13), huge_pages=on,
maintainance_work_mem=BIG, min_dynamic_shared_memory=BIG (v14),
because then the memory that is binary-searched in random order avoids
thrashing the TLB.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: High QPS, random index writes and vacuum
@ 2023-04-18 02:52 Peter Geoghegan <pg@bowt.ie>
parent: peter plachta <pplachta@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Peter Geoghegan @ 2023-04-18 02:52 UTC (permalink / raw)
To: peter plachta <pplachta@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Mon, Apr 17, 2023 at 7:43 PM peter plachta <pplachta@gmail.com> wrote:
> Version: I sheepishly admit it's 9.6, 10 and 11 (it's Azure Single Server, that's another story).
If you can upgrade to 14, you'll find that there is much improved
management of index updates on that version:
https://www.postgresql.org/docs/current/btree-implementation.html#BTREE-DELETION
But it's not clear what the problem really is here. If the problem is
that you're dependent on vacuum to get acceptable response times by
holding back index bloat, then an upgrade could easily help a lot. But
an upgrade might not make VACUUM take less time, given that you've
already tuned it fairly aggressively. It depends.
An upgrade might make VACUUM go faster if you set
vacuum_cost_page_miss to 2, which is the default on later versions
anyway -- looks like you didn't touch that. And, as Thomas said, later
versions do have parallel VACUUM, though that cannot be used by
autovacuum workers.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-04-18 02:52 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-18 00:35 High QPS, random index writes and vacuum peter plachta <pplachta@gmail.com>
2023-04-18 00:38 ` Peter Geoghegan <pg@bowt.ie>
2023-04-18 02:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-18 02:43 ` peter plachta <pplachta@gmail.com>
2023-04-18 02:49 ` Thomas Munro <thomas.munro@gmail.com>
2023-04-18 02:52 ` Peter Geoghegan <pg@bowt.ie>
2023-04-18 02:40 ` David Rowley <dgrowleyml@gmail.com>
2023-04-18 02:44 ` peter plachta <pplachta@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox