Hi J, Thank you for your detailed response and the practical advice.
To address your points: *> CPU Utilization*: I understand that CPU consumption is not inherently bad and can be indicative of the system doing its job. The query performance from an end-user perspective is acceptable at the moment, but I'm preparing for increased load and want to ensure scalability. *> Performance Context*: The query in question is more frequent than a monthly summary but isn't run concurrently by a large number of users. Nevertheless, I am cautious about future growth and potential concurrent usage spikes. *> System Configuration*: I have not yet adjusted the sysctl.conf parameters, but I will look into tuning these based on your recommendations and PostgreSQL documentation. Specifically: kern.seminfo.semmni=60; kern.seminfo.semmns=1024; kern.shminfo.shmall=32768 *> PostgreSQL Settings*: Currently, max_connections and shared_buffers settings are at their default values. I will look into increasing shared_buffers to a quarter of RAM, considering our 16GB of memory. Adjusting temp_buffers and random_page_cost based on SSD usage will also be on my list. *> Further Tuning*: I will explore more detailed PostgreSQL tuning resources and forums for advice tailored to our setup and usage patterns. Your insights have been incredibly helpful. I will implement the suggested configurations and monitor the system's performance to ensure it meets our current and future needs. Thank you once again for your guidance. Best regards, Kihaguru On Sun, Aug 4, 2024 at 8:19 PM <j...@bitminer.ca> wrote: > 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 >
