The logs were helpful. You may want to see the statements around the errors, as more detail may be there such as the SQL statement associated with the error.
It definitely looks like locking issues which is why you don't see high CPU. IIRC you might see high system CPU usage, as opposed to userspace CPU, where the kernel is getting overloaded. The `top` command will help to show that.
The disks could be saturated by the write ahead log (WAL) handling of all the transactions. More about WAL here: https://www.postgresql.org/docs/10/wal-internals.html You could consider moving that directory somewhere else using a symbolic link (conf. the link)
> That wait event according to documentation is "Waiting to access the > multixact member SLRU cache." SLRU = segmented least recently used > cache
I see, thanks!
> if you are low on memory, it can slow down the allocation of > buffers. Do you have a query that is a "select for update" running > somewhere? If your disk is low on space `df -h` that might explain > the issue.
- There aren't any queries that are running for longer than the selects shown earlier; definitely not "select for update" since I don't ever use that in my code. - Both disk and RAM utilization is relatively low.
> Is there an ERROR: multixact something in your postgres log?
There isn't, but while checking I saw some other concerning errors including "deadlock detected", "could not map dynamic shared memory segment" and "could not attach to dynamic shared area". (full logs here: https://paste.sr.ht/blob/9ced99b119c3fce1ecfd71e8554946e7845a44dd )
> Another thing to look at is `iostat -x -y` and look at disk util %. > This is an indicator, but not definitive, of how much disk access is > going on. It may be your drives are just saturated although your > IOWait looks ok in your attachment.
I didn't specifically look at that, but I did notice *very* high disk utilization in at least one instance of the stuck queries, as I mentioned previously. Why would the disks be getting saturated? The query count isn't noticeably higher than average, and the database is not autovacuuming, so not sure what could cause that.
