From: Mladen Gogala <gogala.mladen@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Catching up with performance & PostgreSQL 15
Date: Mon, 28 Nov 2022 22:39:47 -0500
Message-ID: <7dff29ee-de7c-00b6-4efb-064b9df1c871@gmail.com> (raw)
In-Reply-To: <89e7da59-0dfe-45fe-e082-00f777e200a1@berkus.org>
References: <89e7da59-0dfe-45fe-e082-00f777e200a1@berkus.org>
On 11/28/22 21:59, Josh Berkus wrote:
> Hey, folks:
>
> I haven't configured a PostgreSQL server since version 11 (before
> that, I did quite a few).
>
> What's changed in terms of performance configuration since then? Have
> the fundamentals of shared_buffers/work_mem/max_connections changed at
> all? Which new settings are must-tunes?
>
> I've heard about new parallel stuff an JIT, but neither is that
> applicable to my use-case.
>
Well, well! Long time no see! You'll probably be glad to learn that we
have hints now. Thank you for the following page you created:
https://laptrinhx.com/why-postgresql-doesn-t-have-query-hints-2912445911/
I've used it several times, with great success. It's priceless.
Now, to answer your question: no, fundamentals of shared buffers, work
memory and connections haven't changed. Parallelism works fine, it's
reliable and easy to enable. All you need is to set
max_parallel_workers_per_gather to an integer > 0 and PgSQL 15 will
automatically use parallel plan if the planner decides that it's the
best path. However, to warn you in advance, parallel query is not a
panacea. On OLTP databases, I usually disable it on purpose. Parallel
query will speed up sequential scans, but if your application is OLTP,
sequential scan is a sign of trouble. Parallelism is a data warehouse
only feature. And even then, you don't want it ti be run by multiple
users at the same time. Namely, the number of your CPU resources is
finite and having multiple users launch multiple processes is the best
way to run out of the CPU power fast. Normally, you would package an
output of the parallel query into a materialized view and let the users
query the view.
As for JIT, I've recently asked that question myself. I was told that
PostgreSQL with LLVM enabled performs approximately 25% better than
without it. I haven't measured it so I can't either confirm or deny the
number. I can tell you that there is a noticeable throughput
improvement with PL/PGSQL intensive applications. There was also an
increase in CPU consumption. I wasn't doing benchmarks, I was looking
for a generic settings to install via Ansible so I don't have the
numbers, only the feeling. One way of quantifying the difference would
be to run pgbench with and without JIT.
PS:
I am still an Oracle DBA, just as you wrote in the paper.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gogala.mladen@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Catching up with performance & PostgreSQL 15
In-Reply-To: <7dff29ee-de7c-00b6-4efb-064b9df1c871@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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