pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedparallelizing slow queries for multiple cores (PostgreSQL + Gearman)
3+ messages / 3 participants
[nested] [flat]
* parallelizing slow queries for multiple cores (PostgreSQL + Gearman)
@ 2009-03-17 21:57 Marinos Yannikos <mjy@geizhals.at>
0 siblings, 1 reply; 3+ messages in thread
From: Marinos Yannikos @ 2009-03-17 21:57 UTC (permalink / raw)
To: pgsql-performance
We have a few slow queries that use sequential scans on tables that have
plenty of indexes (for other queries), on a box with a lot of RAM and 13
active cores (don't ask), so I was curious to find out how to put this
environment to better use. The result is (maybe) interesting, esp. since
PostgreSQL is getting better at executing many queries in parallel
lately and we will have more than 16 cores in typical servers very soon.
The simplified scenario was a query like
select * from t where foo ~ 'bla';
on a table with approx. 9m rows, taking around 12 seconds in the best
case. The table had a bigserial primary key "eid" with btree index,
which seemed to be the most suitable starting point.
The current value range of eid was partitioned into intervals of equal
size and depending on the number of rows in these intervals, one or more
of them were assigned to worker processes (this is done once per day,
not before each query!):
worker 1: select * from t where foo ~ 'bla' and (eid >= 300000 and eid <
400000)
worker 2: select * from t where foo ~ 'bla' and (eid >= 500000 and eid <
600000 or eid >= 1100000 and eid < 1200000 ...)
...
Instead of a sequential scan, a bunch of worker processes (implemented
with Gearman/Perl) would then execute queries (one each) with a plan like:
Bitmap Heap Scan on t ...
Recheck Cond: eid >= 300000 and eid < 400000 ..
Filter: foo ~ 'bla'
Bitmap Index Scan on t_pkey ...
This led to a speedup factor of 2 when the box was idle, i.e. the time
to split the query, distribute the jobs to worker processes, execute the
queries in parallel, collect results and send them back to the client
was now ~6 seconds.
Observations:
- currently, the speedup is almost the same for anything between ~10 and
80+ workers (~12s down to ~7s on average, best run ~ 6s)
- the effective processing speed of the workers varied greatly (fastest
~3x to ~10x the rows/second of the slowest - real time divided by rows
to work on)
- the fastest workers went as fast as the sequential scans (in rows per
second) - sometimes, but not always (most likely they were actually
running alone then for some reason)
- in each new run, the workers finished in a completely different order
(even though they had the same parts of the table to work on and thus
identical queries) so perhaps the partitioning of the work load is quite
good already and it's more of a scheduling issue (Gearman? Shared buffer
contention?)
- the Linux I/O scheduler had a visible effect, "noop" was better than
"deadline" (others not tried yet) ~10%, but this is typical for random
writes and RAID controllers that manage their writeback cache as they
like (it's a wasted effort to reorder writes before hitting the RAID
controller)
- CLUSTER might help a lot (the workers should hit fewer pages and need
fewer shared resources?) but I haven't tested it
- our query performance is not limited by disk I/O (as is usually the
case I guess), since we have most of the tables/indexes in RAM. Whether
it scales as well (or better?) with a proper disk subsystem and less
RAM, is unknown.
I hope there is some room for improvement so these queries can execute
faster in parallel for better scaling, these first results are quite
encouraging. I'd love to put 32+ cores to use for single queries.
Perhaps something like this could be built into PostgreSQL at some
point? There's no complicated multithreading/locking involved and
Postgres has enough statistics available to distribute work even better.
It should be easy to implement this for any of the various connection
pooling solutions also.
Has anyone done similar work in the light of upcoming many-core
CPUs/systems? Any better results than 2x improvement?
Apologies if this is a well-known and widely used technique already. ;-)
Marinos.
PS. yes, for the example query we could use tsearch2 etc., but it has
drawbacks in our specific case (indexing overhead, no real regexps
possible) and it's only an example anyway ...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: parallelizing slow queries for multiple cores (PostgreSQL + Gearman)
@ 2009-03-17 22:38 Greg Smith <gsmith@gregsmith.com>
parent: Marinos Yannikos <mjy@geizhals.at>
0 siblings, 0 replies; 3+ messages in thread
From: Greg Smith @ 2009-03-17 22:38 UTC (permalink / raw)
To: Marinos Yannikos <mjy@geizhals.at>; +Cc: pgsql-performance
On Tue, 17 Mar 2009, Marinos Yannikos wrote:
> It should be easy to implement this for any of the various connection
> pooling solutions also.
pgpool-II has something very similar in its "Parallel Mode", and the
example given at
http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html looks
just like your example. I wonder if anyone has tried using that feature
but just pointing pgpool-II at the same database instance multiple times?
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 3+ messages in thread
* parallelizing slow queries for multiple cores (PostgreSQL + Gearman)
@ 2009-03-18 12:52 henk de wit <henk53602@hotmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: henk de wit @ 2009-03-18 12:52 UTC (permalink / raw)
To: pgsql-performance; mjy@geizhals.at
Hi,
>Has anyone done similar work in the light of upcoming many-core CPUs/systems? Any better results than 2x improvement?
Yes, in fact I've done a very similar thing on a quad CPU box a while back. In my case the table in question had about 26 million rows. I did nothing special to the table (no cluster, no partitioning, nothing, of course the table did had the appropriate indexes). Queries on this table are analytic/reporting kind of queries. Basically they are just aggregations over a large number of rows. E.g. "the sum of column1 and the sum of column2 where time is some time and columnA has some value and columnB has some other value", that kind of thing. From analysis the queries appeared to be nearly 100% CPU bound.
In my (Java) application I divided a reporting query for say the last 60 days in 2 equal portions: day 1 to 30 and day 31 to 60 and assigned these to two worker threads. The results of these worker threads was merged using a simple resultset merge (the end result is simply the total of all rows returned by thread1 and thread2). The speed up I measured on the quad box was a near perfect factor 2. I then divided the workload in 4 equal portions: day 1 to 15, 16 to 30, 31 to 45 and 46 till 60. The speed up I measured was only a little less then a factor 4. I my situation too, the time I measured included dispatching the jobs to a thread pool and merging their results.
Of course, such a scheme can only be easily used when all workers return individual rows that are directly part of the end result. If some further calculation has to be done on those rows, which happens to be the same calculation that is also done in the query you are parallelizing, then in effect you are duplicating logic. If you do that a lot in your code you can easily create a maintenance nightmare. Also, you have to be aware that without additional measures, every worker lives in its own transaction. Depending on the nature of the data this could potentially result in inconsistent data being returned. In your case, on tables generated once per day this wouldn't be the case, but as a general technique you have to be aware of this.
Anyway, it's very clear that computers are moving to many-core architectures. Simple entry level servers already come these days with 8 cores. I've asked a couple of times on this list whether PG is going to support using multiple cores for a single query anytime soon, but this appears to be very unlikely. Until then it seems the only way to utilize multiple cores for a single query is doing it at the application level or by using something like pgpool-II.
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-03-18 12:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-17 21:57 parallelizing slow queries for multiple cores (PostgreSQL + Gearman) Marinos Yannikos <mjy@geizhals.at>
2009-03-17 22:38 ` Greg Smith <gsmith@gregsmith.com>
2009-03-18 12:52 parallelizing slow queries for multiple cores (PostgreSQL + Gearman) henk de wit <henk53602@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox