Understanding an outage: concurrency control and vacuuming in PostgreSQL(blog-qsgzlar85.duffel.sh)
blog-qsgzlar85.duffel.sh
Understanding an outage: concurrency control and vacuuming in PostgreSQL
https://blog-qsgzlar85.duffel.sh/blog/understanding-outage-concurrency-vacuum-postgresql
4 comments
Interesting! Thanks for sharing. Can you share more on the bug you mentioned?
My memory is a bit hazy on this, so forgive the details but...
Postgres uses something called a multi-xact store to track row locks- iirc, an entry in this store uniquely identifies an entity and all the actors that have locked it: `(id, [pid])`
It happened that our ETL pipeline was routinely hitting issues with a transaction wraparound vacuum, despite the Postgres database the ETL instance was recovered from having fairly aggressive vacuum targets.
Investigation showed it wasn't the standard xid wraparound we were hitting, it was wraparound on the multi-xid counter.
It took me a solid weekend of debugging to figure this out, as multi-xact is (was?) very poorly documented, but reading the code helped me understand that a multi-xact entry is immutable. This means whenever you get a new pid wanting to add itself to the lockers, Postgres would make a copy of the previous entry (and all the users that were holding the lock) and add the new actor to the list.
That means your entries would go something like this:
This wouldn't have been a problem if our codebase hadn't accidentally introduced a pathological pattern where it's sub-transaction would repeatedly lock against a row it had previously locked against, which in Postgres 9.4 would be identified as a different transaction, rather than the same process and locking entity.
It meant a tight loop of code like this:
When you graphed it, you could see the multi-xid counter blast off every time the cron ran. It meant we'd hit the non-optional wraparound threshold almost daily, which was the source of our ETL woes.
I was super happy when I figured this out, and was looking forward to contribute a fix to Postgres that made the locking no-op if the sub-transaction was already in the lock list. When I checked out master, I realised I could no longer reproduce the bug.
It was then I learned a painful lesson about always running the latest version of your database, as this had been fixed in 9.6.
Postgres uses something called a multi-xact store to track row locks- iirc, an entry in this store uniquely identifies an entity and all the actors that have locked it: `(id, [pid])`
It happened that our ETL pipeline was routinely hitting issues with a transaction wraparound vacuum, despite the Postgres database the ETL instance was recovered from having fairly aggressive vacuum targets.
Investigation showed it wasn't the standard xid wraparound we were hitting, it was wraparound on the multi-xid counter.
It took me a solid weekend of debugging to figure this out, as multi-xact is (was?) very poorly documented, but reading the code helped me understand that a multi-xact entry is immutable. This means whenever you get a new pid wanting to add itself to the lockers, Postgres would make a copy of the previous entry (and all the users that were holding the lock) and add the new actor to the list.
That means your entries would go something like this:
1
1, 2
1, 2, 3
1, 3, 3, 4
...
This wouldn't have been a problem if our codebase hadn't accidentally introduced a pathological pattern where it's sub-transaction would repeatedly lock against a row it had previously locked against, which in Postgres 9.4 would be identified as a different transaction, rather than the same process and locking entity.
It meant a tight loop of code like this:
ActiveRecord::Base.transaction do
model.lock!
# Assume many, possibly 500-1000 resources
model.resources.each do |child|
ActiveRecord::Base.transaction(requires_new: true) do
child.lock!
# do something
end
end
end
When you graphed it, you could see the multi-xid counter blast off every time the cron ran. It meant we'd hit the non-optional wraparound threshold almost daily, which was the source of our ETL woes.
I was super happy when I figured this out, and was looking forward to contribute a fix to Postgres that made the locking no-op if the sub-transaction was already in the lock list. When I checked out master, I realised I could no longer reproduce the bug.
It was then I learned a painful lesson about always running the latest version of your database, as this had been fixed in 9.6.
Co-author of the post here. Super happy to answer any questions, and to hear any thoughts people may have.
Cheers!
Cheers!
I had fought this many times before when building ETL processes on top of Postgres, especially back in 9.4 when there was a bug that caused quadratic growth of the multi-transaction IDs.
In fact, this was my only understanding of how vacuum locked interacted with other processes. I had no idea a normal autovacuum process would surrender to standard user operations, so that's a TIL for me.
Thanks for the write-up!