Re: Catching up with performance & PostgreSQL 15

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux