Re: [PERFORM] Message queue table - strange performance drop with changing limit size.
Jesper Krogh wrote: So what I see is that top 10 takes 1ms, top 50 takes over 500 times more, and top 1000 only 1.5 times more than top 50. What can the reason be for the huge drop between limit 10 and limit 50 be? Normally this means you're hitting much higher performing cached behavior with the smaller amount that's degrading significantly once you've crossed some threshold. L1 and L2 CPUs caches vs. regular RAM, shared_buffers vs. OS cache changes, and cached in RAM vs. read from disk are three transition spots where you can hit a large drop in performance just by crossing some boundary, going from just fits to doesn't fit and data thrashes around. Larger data sets do not take a linearly larger amount of time to run queries against--they sure can degrade order of magnitude faster than that. Indexes: job_funcid_key UNIQUE, btree (funcid, uniqkey) funcid_coalesce_priority btree (funcid, coalesce, priority) funcid_prority_idx2 btree (funcid, priority) job_jobid_idx btree (jobid) There may very well be an underlying design issue here though. Indexes are far from free to maintain. You've got a fair number of them with a lot of redundant information, which is adding a significant amount of overhead for questionable gains. If you added those just from the theory of those are the fields combinations I search via, you really need to benchmarking that design decision--it's rarely that easy to figure out what works best. For example, if on average there are a small number of things attached to each funcid, the middle two indexes here are questionable--it may be more efficient to the system as a whole to just grab them all rather than pay the overhead to maintain all these indexes. This is particularly true if you're deleting or updating entries ito remove them from this queue, which is going to add a lot of VACUUM-related cleanup here as well. In your situation, I might try dropping both funcid_coalesce_priority and then funcid_prority_idx2 and watching what happens to your performance and plans, just to learn more about whether they're really needed. A look at the various pg_stat_* view to help determine what physical I/O and index use is actually going on might be useful too. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Message queue table - strange performance drop with changing limit size.
Jesper Krogh jes...@krogh.cc writes: I have a message queue table, that contains in the order of 1-10m messages. It is implemented using TheSchwartz: http://search.cpan.org/~bradfitz/TheSchwartz-1.07/lib/TheSchwartz.pm One way to approach queueing efficiently with PostgreSQL is to rely on PGQ. New upcoming 3.0 version (alpha1 has been released) contains the basics for having cooperative consumers, stable version (2.1.10) only allows multiple consumers to all do the same work (think replication). http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/PGQ_Tutorial Regards, -- dim -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Message queue table - strange performance drop with changing limit size.
Hi.
I have a message queue table, that contains in the order of 1-10m
messages. It is implemented using TheSchwartz:
http://search.cpan.org/~bradfitz/TheSchwartz-1.07/lib/TheSchwartz.pm
So when a worker picks the next job it goes into the job table an
select the top X highest priority messages with the funcid that it can
work on. The table looks like this:
db=# \d workqueue.job
Table workqueue.job
Column | Type | Modifiers
---+--+---
jobid | integer | not null default
nextval('workqueue.job_jobid_seq'::regclass)
funcid| integer | not null
arg | bytea|
uniqkey | text |
insert_time | integer |
run_after | integer | not null
grabbed_until | integer | not null
priority | smallint |
coalesce | text |
Indexes:
job_funcid_key UNIQUE, btree (funcid, uniqkey)
funcid_coalesce_priority btree (funcid, coalesce, priority)
funcid_prority_idx2 btree (funcid, priority)
job_jobid_idx btree (jobid)
efam=# explain ANALYZe select jobid from workqueue.job where job.funcid
in (3) order by priority asc limit 1000;
QUERY
PLAN
Limit (cost=0.00..2008.53 rows=1000 width=6) (actual
time=0.077..765.169 rows=1000 loops=1)
- Index Scan using funcid_prority_idx2 on job
(cost=0.00..7959150.95 rows=3962674 width=6) (actual time=0.074..763.664
rows=1000 loops=1)
Index Cond: (funcid = 3)
Total runtime: 766.104 ms
(4 rows)
efam=# explain ANALYZe select jobid from workqueue.job where job.funcid
in (3) order by priority asc limit 50;
QUERY
PLAN
--
Limit (cost=0.00..100.43 rows=50 width=6) (actual time=0.037..505.765
rows=50 loops=1)
- Index Scan using funcid_prority_idx2 on job
(cost=0.00..7959150.95 rows=3962674 width=6) (actual time=0.035..505.690
rows=50 loops=1)
Index Cond: (funcid = 3)
Total runtime: 505.959 ms
(4 rows)
efam=# explain ANALYZe select jobid from workqueue.job where job.funcid
in (3) order by priority asc limit 10;
QUERY
PLAN
Limit (cost=0.00..20.09 rows=10 width=6) (actual time=0.056..0.653
rows=10 loops=1)
- Index Scan using funcid_prority_idx2 on job
(cost=0.00..7959152.95 rows=3962674 width=6) (actual time=0.054..0.640
rows=10 loops=1)
Index Cond: (funcid = 3)
Total runtime: 0.687 ms
(4 rows)
So what I see is that top 10 takes 1ms, top 50 takes over 500 times
more, and top 1000 only 1.5 times more than top 50.
What can the reason be for the huge drop between limit 10 and limit 50 be?
--
Jesper
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Message queue table - strange performance drop with changing limit size.
Jesper --
I apologize for top-quoting -- a challenged reader.
This doesn't directly address your question, but I can't help but notice that
the estimates for rows is _wildly_ off the actual number in each and every
query. How often / recently have you run ANALYZE on this table ?
Are the timing results consistent over several runs ? It is possible that
caching effects are entering into the time results.
Greg Williamson
- Original Message
From: Jesper Krogh jes...@krogh.cc
To: pgsql-performance@postgresql.org
Sent: Fri, January 1, 2010 3:48:43 AM
Subject: [PERFORM] Message queue table - strange performance drop with changing
limit size.
Hi.
I have a message queue table, that contains in the order of 1-10m
messages. It is implemented using TheSchwartz:
http://search.cpan.org/~bradfitz/TheSchwartz-1.07/lib/TheSchwartz.pm
So when a worker picks the next job it goes into the job table an
select the top X highest priority messages with the funcid that it can
work on. The table looks like this:
db=# \d workqueue.job
Table workqueue.job
Column | Type | Modifiers
---+--+---
jobid | integer | not null default
nextval('workqueue.job_jobid_seq'::regclass)
funcid| integer | not null
arg | bytea|
uniqkey | text |
insert_time | integer |
run_after | integer | not null
grabbed_until | integer | not null
priority | smallint |
coalesce | text |
Indexes:
job_funcid_key UNIQUE, btree (funcid, uniqkey)
funcid_coalesce_priority btree (funcid, coalesce, priority)
funcid_prority_idx2 btree (funcid, priority)
job_jobid_idx btree (jobid)
efam=# explain ANALYZe select jobid from workqueue.job where job.funcid
in (3) order by priority asc limit 1000;
QUERY
PLAN
Limit (cost=0.00..2008.53 rows=1000 width=6) (actual
time=0.077..765.169 rows=1000 loops=1)
- Index Scan using funcid_prority_idx2 on job
(cost=0.00..7959150.95 rows=3962674 width=6) (actual time=0.074..763.664
rows=1000 loops=1)
Index Cond: (funcid = 3)
Total runtime: 766.104 ms
(4 rows)
efam=# explain ANALYZe select jobid from workqueue.job where job.funcid
in (3) order by priority asc limit 50;
QUERY
PLAN
--
Limit (cost=0.00..100.43 rows=50 width=6) (actual time=0.037..505.765
rows=50 loops=1)
- Index Scan using funcid_prority_idx2 on job
(cost=0.00..7959150.95 rows=3962674 width=6) (actual time=0.035..505.690
rows=50 loops=1)
Index Cond: (funcid = 3)
Total runtime: 505.959 ms
(4 rows)
efam=# explain ANALYZe select jobid from workqueue.job where job.funcid
in (3) order by priority asc limit 10;
QUERY
PLAN
Limit (cost=0.00..20.09 rows=10 width=6) (actual time=0.056..0.653
rows=10 loops=1)
- Index Scan using funcid_prority_idx2 on job
(cost=0.00..7959152.95 rows=3962674 width=6) (actual time=0.054..0.640
rows=10 loops=1)
Index Cond: (funcid = 3)
Total runtime: 0.687 ms
(4 rows)
So what I see is that top 10 takes 1ms, top 50 takes over 500 times
more, and top 1000 only 1.5 times more than top 50.
What can the reason be for the huge drop between limit 10 and limit 50 be?
--
Jesper
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Message queue table..
Hi. I have this message queue table.. currently with 8m+ records. Picking the top priority messages seem to take quite long.. it is just a matter of searching the index.. (just as explain analyze tells me it does). Can anyone digest further optimizations out of this output? (All records have funcid=4) # explain analyze SELECT job.jobid, job.funcid, job.arg, job.uniqkey, job.insert_time, job.run_after, job.grabbed_until, job.priority, job.coalesce FROM workqueue.job WHERE (job.funcid = 4) AND (job.run_after = 1208442668) AND (job.grabbed_until = 1208442668) AND (job.coalesce = 'Efam') ORDER BY funcid, priority ASC LIMIT 1 ; QUERY PLAN -- Limit (cost=0.00..0.09 rows=1 width=106) (actual time=245.273..245.274 rows=1 loops=1) - Index Scan using workqueue_job_funcid_priority_idx on job (cost=0.00..695291.80 rows=8049405 width=106) (actual time=245.268..245.268 rows=1 loops=1) Index Cond: (funcid = 4) Filter: ((run_after = 1208442668) AND (grabbed_until = 1208442668) AND (coalesce = 'Efam'::text)) Total runtime: 245.330 ms (5 rows) -- Jesper -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Message queue table..
Jesper Krogh wrote: Hi. I have this message queue table.. currently with 8m+ records. Picking the top priority messages seem to take quite long.. it is just a matter of searching the index.. (just as explain analyze tells me it does). Can anyone digest further optimizations out of this output? (All records have funcid=4) You mean all records of interest, right, not all records in the table? What indexes do you have in place? What's the schema? Can you post a \d tablename from psql? # explain analyze SELECT job.jobid, job.funcid, job.arg, job.uniqkey, job.insert_time, job.run_after, job.grabbed_until, job.priority, job.coalesce FROM workqueue.job WHERE (job.funcid = 4) AND (job.run_after = 1208442668) AND (job.grabbed_until = 1208442668) AND (job.coalesce = 'Efam') ORDER BY funcid, priority ASC LIMIT 1 ; QUERY PLAN -- Limit (cost=0.00..0.09 rows=1 width=106) (actual time=245.273..245.274 rows=1 loops=1) - Index Scan using workqueue_job_funcid_priority_idx on job (cost=0.00..695291.80 rows=8049405 width=106) (actual time=245.268..245.268 rows=1 loops=1) Index Cond: (funcid = 4) Filter: ((run_after = 1208442668) AND (grabbed_until = 1208442668) AND (coalesce = 'Efam'::text)) Total runtime: 245.330 ms (5 rows) Without seeing the schema and index definitions ... maybe you'd benefit from a multiple column index. I'd experiment with an index on (funcid,priority) first. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Message queue table..
Craig Ringer wrote: Jesper Krogh wrote: Hi. I have this message queue table.. currently with 8m+ records. Picking the top priority messages seem to take quite long.. it is just a matter of searching the index.. (just as explain analyze tells me it does). Can anyone digest further optimizations out of this output? (All records have funcid=4) You mean all records of interest, right, not all records in the table? Actually all the records.. since all the other virtual queues currently are empty. What indexes do you have in place? What's the schema? Can you post a \d tablename from psql? # explain analyze SELECT job.jobid, job.funcid, job.arg, job.uniqkey, job.insert_time, job.run_after, job.grabbed_until, job.priority, job.coalesce FROM workqueue.job WHERE (job.funcid = 4) AND (job.run_after = 1208442668) AND (job.grabbed_until = 1208442668) AND (job.coalesce = 'Efam') ORDER BY funcid, priority ASC LIMIT 1 I found that removing the funcid from the order by made it use a better index. (priority, run_after, grabbed_until) that probably makes sense since the funcid doesnt give any value in the index at all. thanks for leading me back on track. Jesper -- Jesper -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Message queue table..
Jesper Krogh [EMAIL PROTECTED] writes: I have this message queue table.. currently with 8m+ records. Picking the top priority messages seem to take quite long.. it is just a matter of searching the index.. (just as explain analyze tells me it does). Limit (cost=0.00..0.09 rows=1 width=106) (actual time=245.273..245.274 rows=1 loops=1) - Index Scan using workqueue_job_funcid_priority_idx on job (cost=0.00..695291.80 rows=8049405 width=106) (actual time=245.268..245.268 rows=1 loops=1) Index Cond: (funcid = 4) Filter: ((run_after = 1208442668) AND (grabbed_until = 1208442668) AND (coalesce = 'Efam'::text)) Total runtime: 245.330 ms Well, what that's doing in English is: scan all the rows with funcid = 4, in priority order, until we hit the first one satisfying the filter conditions. Apparently there are a lot of low-priority rows that have funcid = 4 but not the other conditions. If it's the coalesce condition that's the problem, an index on (funcid, coalesce, priority) --- or (coalesce, funcid, priority) --- would probably help. I'm not sure there's a simple fix if it's the other conditions that are really selective. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Message queue table..
[EMAIL PROTECTED] (Jesper Krogh) writes: I have this message queue table.. currently with 8m+ records. Picking the top priority messages seem to take quite long.. it is just a matter of searching the index.. (just as explain analyze tells me it does). Can anyone digest further optimizations out of this output? (All records have funcid=4) # explain analyze SELECT job.jobid, job.funcid, job.arg, job.uniqkey, job.insert_time, job.run_after, job.grabbed_until, job.priority, job.coalesce FROM workqueue.job WHERE (job.funcid = 4) AND (job.run_after = 1208442668) AND (job.grabbed_until = 1208442668) AND (job.coalesce = 'Efam') ORDER BY funcid, priority ASC LIMIT 1 ; There might be value in having one or more extra indices... Here are *plausible* candidates: 1. If funcid = 4 is highly significant (e.g. - you are always running this query, and funcid often 4), then you might add a functional index such as: create index job_funcid_run_after on workqueue.job (run_after) where funcid = 4; create index job_funcid_grabbeduntil on workqueue.job (grabbed_until) where funcid = 4; 2. Straight indices like the following: create index job_run_after on workqueue.job(run_after); create index job_grabbed_until on workqueue.job(grabbed_until); create index job_funcid on workqueue.job(funcid); create index job_coalesce on workqueue.job(coalesce); Note that it is _possible_ (though by no means guaranteed) that all three might prove useful, if you're running 8.1+ where PostgreSQL supports bitmap index scans. Another possibility... 3. You might change your process to process multiple records in a run so that you might instead run the query (perhaps via a cursor?) with LIMIT [Something Bigger than 1]. It does seem mighty expensive to run a 245ms query to find just one record. It seems quite likely that you could return the top 100 rows (LIMIT 100) without necessarily finding it runs in any more time. Returning 100 tuples in 245ms seems rather more acceptable, no? :-) -- (format nil [EMAIL PROTECTED] cbbrowne linuxfinances.info) http://linuxdatabases.info/info/linuxdistributions.html Rules of the Evil Overlord #32. I will not fly into a rage and kill a messenger who brings me bad news just to illustrate how evil I really am. Good messengers are hard to come by. http://www.eviloverlord.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
