Bounty: 50
I have a very odd situation with one of my Postgres databases. The server itself is quite powerful, it’s 24 SSD NAS divided into 2 LUNs of 12 drives each – a very good IOPS setup. It is connected via 10g network card to the 128-threaded server with 256 GB of RAM, running on Win 2019.
During the day, randomly, I am affected by the sudden slowdown of transaction inserts, a query like this:
INSERT INTO public.transactions( col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12)
VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
RETURNING id1
Under normal circumstances, time is close to 0. In the period of problems, 2-3 mins, it can be between 5 and 20 secs, destroying the transaction system. Then suddenly it recovers and works super stable. CPU, RAM, disk queues are not a problem. I don’t spot in the logs any significant slow down of other queries, no vacuuming taking place. I have even installed some pg extensions to get execution plans of queries long than x ms, but in case of INSERT you can only get:
Insert on transactions(cost=0.00..0.01 rows=1 width=313)
Lock monitoring only shows that the same queries are blocking each other, but it’s quite odd as they are independent.
I don’t expect a solution to my problem, as I know it’s impossible, but maybe some more hints how I can investigate such issue? I think I tried every approach but still feel blind and lost when it happens.