Comments (1)
Important clarification to add to the discussion:
The issue isn't just the length of time it takes to run the single query. It averages approximately 4ms
for us - not murderous. The problem is compounded by the number of calls made and its aggregate cost.
The problem occurs when we use pq
to iterates over get()
in a loop for a non-existent q_name in the table. This can happen in practice if old records for the q_name are purged from the table. A single worker thread makes ~1000/calls per minute in this scenario in our experience. This behavior is not present in situations where there is nothing to dequeue for q_name
that still contains previously dequeued rows.
We could address this on our end by invoking a sleep
when queue.get() returns None
. But, we would like to ensure it can't be addressed in an upstream fashion beforehand.
Example Explains:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Update on tasks (cost=115.98..124.00 rows=1 width=1122)
CTE candidates
-> Limit (cost=0.42..115.24 rows=2 width=740)
-> Index Scan using priority_idx2 on tasks tasks_1 (cost=0.42..4478.40 rows=78 width=740)
Index Cond: (q_name = 'faxer'::text)
CTE selected
-> Limit (cost=0.07..0.07 rows=1 width=24)
-> Sort (cost=0.07..0.07 rows=1 width=24)
Sort Key: candidates.schedule_at NULLS FIRST, candidates.expected_at NULLS FIRST
-> CTE Scan on candidates (cost=0.00..0.06 rows=1 width=24)
Filter: (pg_try_advisory_xact_lock(id) AND ((schedule_at <= now()) OR (schedule_at IS NULL)))
CTE next_timeout
-> Aggregate (cost=0.06..0.07 rows=1 width=8)
-> CTE Scan on candidates candidates_1 (cost=0.00..0.05 rows=1 width=8)
Filter: (schedule_at >= now())
InitPlan 4 (returns $3)
-> CTE Scan on next_timeout (cost=0.00..0.02 rows=1 width=8)
InitPlan 5 (returns $4)
-> CTE Scan on selected (cost=0.00..0.02 rows=1 width=8)
-> Index Scan using tasks_pkey on tasks (cost=0.56..8.58 rows=1 width=1122)
Index Cond: (id = $4)
Filter: (dequeued_at IS NULL)
(22 rows)
from pq.
Related Issues (20)
- connection pool exhausted HOT 3
- DuplicatePreparedStatement error HOT 10
- Prepared statement name not properly escaped HOT 1
- Question: why am I seeing "NOTICE: function pq_notify does not exist" in the logs? HOT 3
- Delete executed tasks?! HOT 1
- Wait on a job? HOT 3
- get() timeout not honoured HOT 1
- Any interest in porting to cockroach HOT 3
- Pipenv using old create.sql HOT 5
- Get id of current task HOT 2
- Performance with a very large queue HOT 9
- lost trigger when setting up multiple queue-tables within the same schema
- Adding recurring tasks? HOT 4
- queue.put() inside a transaction sets enqueued_at to the transaction start time, not the current time HOT 4
- Adding name of the job producer HOT 2
- Dashboard for PQ HOT 3
- Any plans to migrate to Psycopg 3? HOT 1
- get items sometimes doesn't work
- 2.x roadmap
- Logging message not explicit
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pq.