We've been experiencing some connection spikes on pgbouncer connected to a postgres database. When we query pg_stat_activity during these spikes we see tons of active queries with wait_event WALWriteLock.

We changed some of our highly inserted tables to unlogged, yet inserts into these tables are still showing up during the spikes with a wait_event of WALWriteLock. I thought that if a table is unlogged, then inserts into it wouldn't get caught up waiting for WALWriteLock. What gives?

Further, any suggestions on how to stop these spikes?

2 Answers

WALWriteLock: accrued by PostgreSQL processes while WAL records are flushed to disk or during a WAL segments switch. synchronous_commit=off removes the wait for disk flush, full_page_writes=off reduces the amount of data to flush. You can try above also mentioned in dec :-

1

One possible answer is that despite of not writing the table itself to WAL, COMMITs are still written to WAL, and if you make lots of tiny transactions this might show up. See if you can group inserts to smaller number of transactions.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.