Content-Type: multipart/alternative;
 boundary="------------QmLvaXL8v8suWbJkmBTUseFH"
Message-ID: <7dff29ee-de7c-00b6-4efb-064b9df1c871@gmail.com>
Date: Mon, 28 Nov 2022 22:39:47 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.5.0
Subject: Re: Catching up with performance & PostgreSQL 15
Content-Language: en-US
To: pgsql-performance@lists.postgresql.org
References: <89e7da59-0dfe-45fe-e082-00f777e200a1@berkus.org>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: <89e7da59-0dfe-45fe-e082-00f777e200a1@berkus.org>
Archived-At: 
 <https://www.postgresql.org/message-id/7dff29ee-de7c-00b6-4efb-064b9df1c871%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------QmLvaXL8v8suWbJkmBTUseFH
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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

--------------QmLvaXL8v8suWbJkmBTUseFH
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 11/28/22 21:59, Josh Berkus wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:89e7da59-0dfe-45fe-e082-00f777e200a1@berkus.org">Hey,
      folks:
      <br>
      <br>
      I haven't configured a PostgreSQL server since version 11 (before
      that, I did quite a few).
      <br>
      <br>
      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?
      <br>
      <br>
      I've heard about new parallel stuff an JIT, but neither is that
      applicable to my use-case.
      <br>
      <br>
    </blockquote>
    <p>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:</p>
    <p><a class="moz-txt-link-freetext" href="https://laptrinhx.com/why-postgresql-doesn-t-have-query-hints-2912445911/">https://laptrinhx.com/why-postgresql-doesn-t-have-query-hints-2912445911/</a></p>
    <p>I've used it several times, with great success. It's priceless.</p>
    <p>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 &gt; 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.<br>
    </p>
    <p>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. <br>
    </p>
    <p>PS:</p>
    <p>I am still an Oracle DBA, just as you wrote in the paper.<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------QmLvaXL8v8suWbJkmBTUseFH--