On Tue, 2020-08-18 at 19:52 -0400, Jim Jarvie wrote: > I have a system which implements a message queue with the basic pattern that a process selects a group of, > for example 250, rows for processing via SELECT .. LIMIT 250 FOR UPDATE SKIP LOCKED. > > When there are a small number of concurrent connections to process the queue, this seems to work as > expected and connections quickly obtain a unique block of 250 rows for processing. > However, as I scale up the number of concurrent connections, I see a spike in CPU (to 100% across 80 cores) > when the SELECT FOR UPDATE SKIP LOCKED executes and the select processes wait for multiple minutes > (10-20 minutes) before completing. My use case requires around 256 concurrent processors for the queue > but I've been unable to scale beyond 128 without everything grinding to a halt. > > The queue table itself fits in RAM (with 2M hugepages) and during the wait, all the performance counters > drop to almost 0 - no disk read or write (semi-expected due to the table fitting in memory) with 100% > buffer hit rate in pg_top and row read around 100/s which is much smaller than expected. > > After processes complete the select and the number of waiting selects starts to fall, CPU load falls and > then suddenly the remaining processes all complete within a few seconds and things perform normally until > the next time there are a group of SELECT FOR UPDATE statements which bunch together and things then repeat. > > I found that performing extremely frequent vacuum analyze (every 30 minutes) helps a small amount but > this is not that helpful so problems are still very apparent. > > I've exhausted all the performance tuning and analysis results I can find that seem even a little bit > relevant but cannot get this cracked. > > Is anyone on the list able to help with suggestions of what I can do to track why this CPU hogging happens > as this does seem to be the root of the problem? You should - check with "pgstattuple" if the table is bloated. - use "perf" to see where the CPU time is spent. - look at "pg_stat_activity" for wait events (unlikely if the CPU is busy). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
