6 ms·
> As dead tuples continue to accumulate in the index, Postgres enters a hot loop as it searches the B-tree, comes up with an invisible tuple, and repeats the pr
by random_comment 9y ago
> As dead tuples continue to accumulate in the index, Postgres enters a hot loop as it searches the B-tree, comes up with an invisible tuple, and repeats the process again and again, surfacing empty-handed every time. By the end of the experiment illustrated in the charts above, every worker trying to lock a job would cycle through this loop 100,000 times.
1. I am curious if the author tried setting the autovacuum more appropriately for a table that is in continuous use at 50 new rows/second.
This article (http://okigiveup.net/postgresql-vacuuming-an-introduction-for-busy-devs/ http://okigiveup.net/postgresql-vacuuming-an-introduction-fo...) suggests:
ALTER TABLE person SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE person SET (autovacuum_vacuum_threshold = 4000);
This should result in entries being removed from the index and table regularly.
2. If I understand correctly, it seems the other part of the problem is that there are lots of rows to be individually tested to see if they can be locked, based on the way the author has implemented their queue. While lock attempts are being made on a set of rows, they aren't being deleted, since they're 'in use'. This non-deletion causes things to get out of control.
As far as I know, 9.5's SKIP LOCKED is meant to help address that (https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/ https://blog.2ndquadrant.com/what-is-select-skip-locked-for-...). Though it appears to be doing something kinda similar under the hood.
If the author of the original article (@ brandur.org) is following this discussion I would be grateful if they could evaluate these two options in the context of their test implementation.
- craigkerstiens 9y agoDropped the original author a note, unfortunately he's not near a machine at the moment, but does plan to check back in when he can.
- random_comment 9y agoThanks craig
- rosser 9y agoThe problem with the first point is that autovacuum can't mark "dead" tuples as "dead" if there are open transactions that are "older" (in MVCC terms) than those tuples, no matter how aggressively it's tuned. "SKIP LOCKED" (or, pre-9.5, "NOWAIT") will definitely help with the locking issue, but not with the bloat.
- random_comment 9y agoThank you for a good summary. The real problem in my understanding was that I wasn't seeing that the article author REALLY IS using long-running PG transactions to as a way to enforce external queue items running to completion (or not). I know they say it in the article, indeed it's the point of the article, but it seems so strange. Hence he's getting these long-running transactions in PG in the first place. The phrase 'sledgehammer to crack a marshmallow' comes to mind. At 50 items/second I guess I'm totally baffled over why you wouldn't simply use e.g. an exclusive table lock each time you connect to the DB to add/take/remove a task from a task table, with a timestamp to allow aborting and rescheduling of tasks. I just ran a test to check the performance of exclusive table locking for this purpose, and despite the slowness of having everything dumping to the console while running, and the slowness of setting up a completely new psql session for each connection (i.e. no pgpool etc), using a BASH script, and running on a crappy 6-year-old mac, I got over 100TPS.