Unaccounted regression from postgresql 11 in later versions
Hello
We have an application (https://dhis2.org) which has been using postgresql
as a backend for the past 15 years or so. Gradually moving through pg
versions 8,9,10 etc as the years went by. At the moment a large number of
our implementations are using versions 13, 14 and 15. Unfortunately we
have recently discovered that, despite most operations performing
considerably better on later versions, there is a particular type of query
that is very much slower (up to 100x) than it was on postgresql 11. We
have seen this regression in 13, 14 and 15. Unfortunately I dont have
stats on version 12 yet.
The query is not beautifully crafted. It is automatically generated from a
custom expression language. We know that it can probably be improved, but
at the moment we would really like to know if there is anything we can
configure with the SQL as-is to get performance like we had back on pg11.
The example below is a typical such query. I've attached below that, links
to the results of EXPLAIN (ANALYZE, BUFFERS). for pg11 and pg15 on the same
physical environment loaded with the same database. I would appreciate
some help trying to understand what we are seeing with the EXPLAIN output
and whether there is anything to be done.
EXPLAIN ANALYZE
select
count(pi) as value,
'2022W21' as Weekly
from
analytics_enrollment_gr3uwzvzpqt as ax
where
cast(
(
select
"IEMtgZapP2s"
from
analytics_event_gr3uWZVzPQT
where
analytics_event_gr3uWZVzPQT.pi = ax.pi
and "IEMtgZapP2s" is not null
and ps = 'XO45JBGJcXJ'
order by
executiondate desc
limit
1
) as date
) < cast('2022-05-30' as date)
and cast(
(
select
"IEMtgZapP2s"
from
analytics_event_gr3uWZVzPQT
where
analytics_event_gr3uWZVzPQT.pi = ax.pi
and "IEMtgZapP2s" is not null
and ps = 'XO45JBGJcXJ'
order by
executiondate desc
limit
1
) as date
) >= cast('2022-05-23' as date)
and (uidlevel1 = 'Plmg8ikyfrK')
and (
coalesce(
(
select
"QEbYS2QOXLf"
from
analytics_event_gr3uWZVzPQT
where
analytics_event_gr3uWZVzPQT.pi = ax.pi
and "QEbYS2QOXLf" is not null
and ps = 'XO45JBGJcXJ'
order by
executiondate desc
limit
1
):: text,
''
) = 'FIN_CASE_CLASS_CONFIRM_LAB'
)
limit 1;
-- limit 21;
The EXPLAIN result for postgresql 11 is here:
https://explain.depesz.com/s/3QfC
The same query on postgresql 15 is here:
https://explain.depesz.com/s/BzpA#html
Whereas the first example takes 23s, the pg15 one takes 243s (this time
difference is even more stark when you remove BUFFERS from the explain).
During execution the pg15 query consumes 100% of a CPU core throughout
indicating it is probably cpu bound rather than IO.
The plan selected in both cases seems to be exactly the same. But pg15
seems to make a lot of work of the final aggregation step. Anecdotally I
understand that the same difference is there with pg13 and 14. The only
significant factor I could think of relating to new behaviour in pg13 is
the new hash_mem_multiplier configuration and it its relation to work_mem
availbale for hash tables. I have attempted to turn up both
hash_mem_multilier and work_mem to ridiculous values and I see no change
whatsoever on pg15.
I also removed the LIMIT and tested again with no significant difference:
https://explain.depesz.com/s/K9Lq
Does anyone have a theory of why pg15 should behave so differently to pg11
here? Better still, any suggestions for configuration that might make pg15
behave more like pg10. I am really dreading the prospect of stepping our
many live implementations back to pg11 :-(.
Regards
Bob
Re: Unaccounted regression from postgresql 11 in later versions
> > Does anyone have a theory of why pg15 should behave so differently to pg11 > here? Better still, any suggestions for configuration that might make pg15 > behave more like pg10. I am really dreading the prospect of stepping our > many live implementations back to pg11 :-(. > One major factor here appears to be JIT compilation, which is off by default in pg11, but on by default in pg12+. You can see at the bottom of your slowest query plan that about 233s of the 240s are JIT related. There is good info in the docs about tuning, or turning off, JIT: https://www.postgresql.org/docs/current/jit-decision.html
Re: Unaccounted regression from postgresql 11 in later versions
Wow Michael you are absolutely right. Turning jit off results in a query execution about twice as fast as pg11. That is a huge relief. I will read the jit related docs and see if there is anything smarter I should be doing other than disabling jit entirely, but it works a treat for this query. Regards Bob On Wed, 31 May 2023 at 11:11, Michael Christofides wrote: > Does anyone have a theory of why pg15 should behave so differently to pg11 >> here? Better still, any suggestions for configuration that might make pg15 >> behave more like pg10. I am really dreading the prospect of stepping our >> many live implementations back to pg11 :-(. >> > > One major factor here appears to be JIT compilation, which is off by > default in pg11, but on by default in pg12+. > > You can see at the bottom of your slowest query plan that about 233s of > the 240s are JIT related. > > There is good info in the docs about tuning, or turning off, JIT: > https://www.postgresql.org/docs/current/jit-decision.html >
How to reduce latency with fast short queries in Postgresql 15.3 on a NUMA server
Hi guys,
I've been configuring a new server and tuning Postgresql 15.3, but I'm
struggling with a latency I'm consistently seeing with this new server when
running fast short queries, compared to the other server.
We're running two different versions of Postgresql:
- Server A: Postgresql 9.3
- Server B: Postgresql 15.3
Server B is the new server and is way more powerful than server A:
- Server A: 1x Intel Xeon E3-1270 3.5GHz, 2x 8GB DDR3, RAID0
- Server B: 2x Intel Xeon Platinum 8260 2.4GHz, 4x 16GB DDR4, RAID1
We're running Linux Ubuntu 20.04 on server B and I've been tweaking some
settings in Linux and Postgresql 15.3. With the current setup, Postgresql
15.3 is able to process more than 1 million transactions per second running
pgbench:
# pgbench --username postgres --select-only --client 100 --jobs 10
--time 20 test
pgbench (15.3 (Ubuntu 15.3-1.pgdg20.04+1))
starting vacuum...end.
transaction type:
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 10
maximum number of tries: 1
duration: 20 s
number of transactions actually processed: 23039950
number of failed transactions: 0 (0.000%)
latency average = 0.087 ms
initial connection time = 62.536 ms
tps = 1155053.135317 (without initial connection time)
As shown in pgbench, the performance is great. Also when testing individual
queries, heavy queries (those taking a few ms) run faster on server B than
server A. Unfortunately when we run fast short SELECT queries (< 1 ms),
server A is consistently running faster than server B, even if the query
plans are the same:
Server A:
# EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT 1 AS "a" FROM "foobar"
WHERE ("foobar"."id" = 1) LIMIT 1;
QUERY PLAN
Limit (cost=0.42..8.44 rows=1 width=0) (actual time=0.008..0.008
rows=1 loops=1)
Output: (1)
Buffers: shared hit=5
-> Index Only Scan using foobar_pkey on public.foobar
(cost=0.42..8.44 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)
Output: 1
Index Cond: (foobar.id = 1)
Heap Fetches: 1
Buffers: shared hit=5
Total runtime: 0.017 ms
(9 rows)
Time: 0.281 ms
Server B:
# EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT 1 AS "a" FROM "foobar"
WHERE ("foobar"."id" = 1) LIMIT 1;
QUERY PLAN
Limit (cost=0.00..1.11 rows=1 width=4) (actual time=0.019..0.021
rows=1 loops=1)
Output: 1
Buffers: shared hit=4
-> Index Only Scan using foobar_pkey on public.foobar
(cost=0.00..1.11 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=1)
Output: 1
Index Cond: (foobar.id = 1)
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.110 ms
Execution Time: 0.045 ms
(10 rows)
Time: 0.635 ms
RAID1 could add some latency on server B if it was reading from disk, but
I've confirmed that these queries are hitting the buffer/cache and
therefore reading data from memory and not from disk. I've checked the hit
rate with the following query:
SELECT 'cache hit rate' AS name, sum(heap_blks_hit) /
(sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio FROM
pg_statio_user_tables;
The hit rate was over 95% and it increased as soon as I ran those queries.
Same thing with the index hit rate.
I've been playing with some parameters in Postgresql, decreasing/increasing
the number of workers, shared buffers, work_mem, JIT, cpu_*_cost variables,
etc, but nothing did help to reduce that latency.
Here are the settings I'm currently using with Postgresql 15.3 after a lot
of work experimenting with different values:
checkpoint_completion_target = 0.9
checkpoint_timeout = 900
cpu_index_tuple_cost = 0.1
cpu_operator_cost = 0.1
effective_cache_size = 12GB
effective_io_concurrency = 200
jit = off
listen_addresses = 'localhost'
maintenance_work_mem = 1GB
max_connections = 100
max_parallel_maintenance_workers = 4
max_parallel_workers = 12
max_parallel_workers_per_gather = 4
max_wal_size = 4GB
max_worker_processes = 12
min_wal_size = 1GB
random_page_cost = 1.1
shared_buffers = 4GB
ssl = off
timezone = 'UTC'
wal_buffers = 16MB
work_mem = 64MB
Some notes about those settings:
- We're running other services on this server, that's why I'm not using
more resources.
- Tweaking the cpu_*_cost parameters was crucial to improve the query
plan. With the default values Postgresql was consistently using a slower
query plan.
I've been looking at some settings in Linux as well:
- Swappiness is set to t
Re: How to reduce latency with fast short queries in Postgresql 15.3 on a NUMA server
On Wed, May 31, 2023 at 02:40:05PM +0200, Sergio Rus wrote: > Hi guys, > > I've been configuring a new server and tuning Postgresql 15.3, but I'm > struggling with a latency I'm consistently seeing with this new server when > running fast short queries, compared to the other server. > > We're running two different versions of Postgresql: > > - Server A: Postgresql 9.3 > - Server B: Postgresql 15.3 > > Server B is the new server and is way more powerful than server A: > > - Server A: 1x Intel Xeon E3-1270 3.5GHz, 2x 8GB DDR3, RAID0 > - Server B: 2x Intel Xeon Platinum 8260 2.4GHz, 4x 16GB DDR4, RAID1 > ... > Conclusion: > > As you can see, server B has 2 CPUs and is using NUMA on Linux. And the > CPU clock is slower on server B than server A. Maybe any of those are > causing that latency? > Hi Sergio, This really looks like it is caused by the CPU clock speed difference. The E3 is 1.6X faster at the base frequency. Many times that is the trade-off when going to many more cores. Simple short will run faster on the older CPU even though overall the new CPU has much more total capacity. Regards, Ken
Re: How to reduce latency with fast short queries in Postgresql 15.3 on a NUMA server
Thanks for your reply, Ken. With such a big server I was convinced that we should see a boost everywhere, but after spending so much time tweaking and looking at so many parameters on Linux, Postgresql and our current setup, I started to think that maybe that latency was intrinsic to the hardware and therefore inevitable. So after all, the CPU clock speed still counts these days! I think we're many just looking at the number of CPU cores and forgetting that the clock speed is still relevant for many tasks. I guess those simple short queries are very sensible to the hardware specs and there is no room for improving as much as the heavy queries in recent versions of Postgres, as I have seen in my tests. On Wed, 31 May 2023 at 15:47, Kenneth Marshall wrote: > On Wed, May 31, 2023 at 02:40:05PM +0200, Sergio Rus wrote: > > Hi guys, > > > > I've been configuring a new server and tuning Postgresql 15.3, but I'm > > struggling with a latency I'm consistently seeing with this new server > when > > running fast short queries, compared to the other server. > > > > We're running two different versions of Postgresql: > > > > - Server A: Postgresql 9.3 > > - Server B: Postgresql 15.3 > > > > Server B is the new server and is way more powerful than server A: > > > > - Server A: 1x Intel Xeon E3-1270 3.5GHz, 2x 8GB DDR3, RAID0 > > - Server B: 2x Intel Xeon Platinum 8260 2.4GHz, 4x 16GB DDR4, RAID1 > > ... > > Conclusion: > > > > As you can see, server B has 2 CPUs and is using NUMA on Linux. And the > > CPU clock is slower on server B than server A. Maybe any of those are > > causing that latency? > > > > Hi Sergio, > > This really looks like it is caused by the CPU clock speed difference. > The E3 is 1.6X faster at the base frequency. Many times that is the > trade-off when going to many more cores. Simple short will run faster on > the older CPU even though overall the new CPU has much more total > capacity. > > Regards, > Ken >
Re: How to reduce latency with fast short queries in Postgresql 15.3 on a NUMA server
> Server B is the new server and is way more powerful than server A: > ... > So after all, the CPU clock speed still counts these days! Hi Sergio, Maybe "powerful" + "powersave"? as I see Sever B : Processor Base Frequency : 2.40 GHz AND * Max Turbo Frequency : 3.90 GHz* Could you verify this by running the 'cpupower frequency-info' command and checking the governor line?" read more: *"But If we haven’t emphasised it enough, firstly whatever database benchmark you are running * *make sure that your CPUs are not configured to run in powersave mode."* https://www.hammerdb.com/blog/uncategorized/how-to-maximize-cpu-performance-for-postgresql-12-0-benchmarks-on-linux/ regards, Imre Sergio Rus ezt írta (időpont: 2023. máj. 31., Sze, 18:03): > Thanks for your reply, Ken. > > With such a big server I was convinced that we should see a boost > everywhere, but after spending so much time tweaking and looking at so many > parameters on Linux, Postgresql and our current setup, I started to think > that maybe that latency was intrinsic to the hardware and therefore > inevitable. So after all, the CPU clock speed still counts these days! I > think we're many just looking at the number of CPU cores and forgetting > that the clock speed is still relevant for many tasks. > > I guess those simple short queries are very sensible to the hardware specs > and there is no room for improving as much as the heavy queries in recent > versions of Postgres, as I have seen in my tests. >
Re: How to reduce latency with fast short queries in Postgresql 15.3 on a NUMA server
Hi, On 2023-05-31 14:40:05 +0200, Sergio Rus wrote: > I've been configuring a new server and tuning Postgresql 15.3, but I'm > struggling with a latency I'm consistently seeing with this new server when > running fast short queries, compared to the other server. > > We're running two different versions of Postgresql: > > - Server A: Postgresql 9.3 > - Server B: Postgresql 15.3 > > Server B is the new server and is way more powerful than server A: > > - Server A: 1x Intel Xeon E3-1270 3.5GHz, 2x 8GB DDR3, RAID0 > - Server B: 2x Intel Xeon Platinum 8260 2.4GHz, 4x 16GB DDR4, RAID1 > > We're running Linux Ubuntu 20.04 on server B and I've been tweaking some > settings in Linux and Postgresql 15.3. With the current setup, Postgresql > 15.3 is able to process more than 1 million transactions per second running > pgbench: > > # pgbench --username postgres --select-only --client 100 --jobs 10 > --time 20 test Could you post the pgbench results for both systems? Which one is this from? > As shown in pgbench, the performance is great. Also when testing individual > queries, heavy queries (those taking a few ms) run faster on server B than > server A. Unfortunately when we run fast short SELECT queries (< 1 ms), > server A is consistently running faster than server B, even if the query > plans are the same: One explanation for this can be the powersaving settings. Newer CPUs can throttle down a lot further than the older ones. Increasing the clock speed has a fair bit of latency - for a long running query that's not really visible, but if you run a short query in isolation, it'll likely complete before the clock speed has finished ramping up. You can query that with cpupower frequency-info Another thing is that you're comparing a two socket system with a one socket system. Latency between a program running on one node and a client on another, and similarly, a program running on one node and memory attached to the other CPU, is higher. You could check what happens if you bind both server and client to the same CPU socket. numactl --membind 1 --cpunodebind 1 forces programs to allocate memory and run on a specific CPU socket. Greetings, Andres Freund
Re: How to reduce latency with fast short queries in Postgresql 15.3 on a NUMA server
Em qua., 31 de mai. de 2023 às 09:40, Sergio Rus escreveu: > As you can see, server B has 2 CPUs and is using NUMA on Linux. And the > CPU clock is slower on server B than server A. Maybe any of those are > causing that latency? > > Any suggestions or ideas where to look? I'd really appreciate your help. > If this is cpu bound, linux perf can show the difference. https://wiki.postgresql.org/wiki/Profiling_with_perf regards, Ranier Vilela
Re: thousands of CachedPlan entry per backend
On Thu, 2023-06-01 at 03:36 +, James Pang (chaolpan) wrote: > PG V14.8-1 , client using Postgresql JDBC driver we found 40MB process memory > per > backend, from Operating system and memorycontext dump “Grand total:”, both > mached. > But from details, we found almost of entry belong to “CacheMemoryContext”, > from this line CacheMemoryContext: 8737352 total in 42 blocks; 1021944 free > (215 chunks); 7715408 used, > but there are thousands of lines of it’s child, the sum of blocks much more > than “8737352” total in 42 blocks > > Our application use Postgresql JDBC driver with default > parameters(maxprepared statement 256), > there are many triggers, functions in this database, and a few functions run > sql by an extension > pg_background. We have thousands of connections and have big concern why > have thousands of entrys > of cached SQL ? that will consume huge memory , anyway to limit the cached > plan entry to save memory > consumption? Or it looks like an abnormal behavior or bug to see so many > cached plan lines. If you have thousands of connections, that's your problem. You need effective connection pooling. Then 40MB per backend won't be a problem at all. Having thousands of connections will cause other, worse, problems for you. See for example https://www.cybertec-postgresql.com/en/tuning-max_connections-in-postgresql/ If you want to use functions, but don't want to benefit from plan caching, you can set the configuration parameter "plan_cache_mode" to "force_custom_plan". Yours, Laurenz Albe
