On 30.07.24 14:04, Kihaguru Gathura wrote:
Hi,
I am seeking advice on optimizing a PostgreSQL query that is consuming
a significant amount of CPU resources on my Dell PowerEdge T340
server. The server has an Intel Xeon E-2124 CPU @ 3.30GHz (4 cores, no
Hyper-Threading) and 16GB RAM, running OpenBSD 7.3 (GENERIC.MP
<http://GENERIC.MP>) #1125.
This is a reasonably fast CPU and there is lots of RAM. No issues there.
The query in question occasionally utilizes around 40% of the CPU,
with CPU1 and CPU2 being more utilized compared to CPU0 and CPU3. This
suggests that the workload might not be well-balanced across all cores.
As long as the CPU work is getting done, you don't care (or shouldn't
care) which CPU is doing the work.
"top" is not a great tool for measuring CPU consumption of a process.
It
filters the measurements, resulting in a slow change approaching the
actual. The overall cpu consumption (top row or rows) is good.
I am monitoring the system to ensure that CPU usage by PostgreSQL does
not lead to performance issues, especially if more queries are
executed simultaneously. Other processes (such as httpd, relayd, etc.)
are consuming minimal CPU time, indicating they are not contributing
significantly to the overall load.
The critical assumption you have made is that CPU consumption is bad.
This
is not necessarily true, it all depends on your circumstances. If
it is a "monthly summary", no big deal, you run it once a month.
But if it is a "customer summary" and you have hundreds of customers all
running it concurrently that might be something to adjust or adapt to.
Consider the point of view of the end-user. Is the query performance
OK?
If it actually OK then you have nothing to do. CPU measurements are
not visible to the end user and they don't care about them.
If query performance is not "OK" then memory is the first thing to
adjust, followed by indexing (as you've started already).
Configuring and tuning Postgresql is a lifelong ambition. There are
dozens if not hundreds of advice pages available. The first things
to do (you don't say if these are done, or not):
- setup sysctl.conf to larger numbers, for example
kern.seminfo.semmni=60
kern.seminfo.semmns=1024
kern.shminfo.shmall=32768
Consult postgres documents for advice according to number of users
and database size.
- setup postgres for suitable max_connections (leaving lots of margin)
- setup postgres for lots of memory in use, shared_buffers=3GB for
example
Do not use more than about a quarter of RAM. Be sure that login.conf
entries for _postgresql user are suitable for this number.
- setup postgres for temp_buffers larger; but seek tuning advice on
this.
- setup postgres for random_page_cost = 1.0 provided that you have SSD
not
rotating disks.
This is probably the wrong forum for detailed postgresql advice. You
might have to search elsewhere.
good luck
J
