0

good afternoon! I have my database in Azure, it is Postgres in version 14.9, flexible. his ability: General Purpose, D16ds_v4, 16 vCores, 64 GiB RAM, 8192 GiB storage iops: p60 (16000iops)

I take care of the administration of the database for the client, before it ran with a CPU consumption of around 40% and the same memory. After a few days, the application team activated new loads, where we had a huge increase in consumption, now the database has a constant consumption of 97%. I noticed that several queries with the same user are being executed. It's worth noting that before we didn't have as many loads on the database as we are having now, so I believe that if there is some way to limit the number of queries that a user can execute, it would be better. example: 5216 [email protected] 2024-04-01 16:47:53 -03 active
LWLock: BufferMapping INSERT INTO XXXX 2024-04-01 17:04:03 -03 2024-04-01 17:04:03 -03 client backend
-42.82

I have a total of 60 active queries, only 19 queries with "LWLock: BufferMapping".

What can I do to improve this? I consider that the bank is working with good performance.

maintenance_work_mem 2097151 kb

max_worker_processes 32

max_parallel_maintenance_workers 64

max_parallel_workers 64

max_parallel_workers_per_gather 10

segment_size 131072kb

work_mem 2097151 kb

shared_buffers 2097152kb

server_version 14.9

server_version_num 140009

I apologize for the English, as I am using Google Translate.

6
  • Analyze your qorkload using pg_stat_statements. Apr 2 at 7:33
  • Several of your settings don't seem very sensible, but are also unlikely to be the cause. Like having max_parallel_workers be greater than max_worker_processes, and having max_worker_processes much greater than actual CPUs.
    – jjanes
    Apr 2 at 15:44
  • If 19 are "LWLock: BufferMapping, what are the other 41?
    – jjanes
    Apr 2 at 15:45
  • What is the problem? That it is using 97% of the CPUs you are paying for is not a problem. If it is using 2.5 times the CPU to get 2.5 time the work done, that also is not a problem. Or is it using 2.5 times the CPU but not doing 2.5 times the work?
    – jjanes
    Apr 2 at 15:57
  • Hi, how are you? Exactly, there is no problem with using 97%, but isn't it a very high value? I understand that the ideal would be around 80%. I noticed that after the insertions and updates that were being carried out, the database now dropped to 10%, that is, there is no more load, it was something very specific, but it overloaded the database a lot. Apr 2 at 17:22

0