Skip site navigation (1) Skip section navigation (2)

Re: getting the most of out multi-core systems for repeated complex SELECT statements

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: gnuoytr(at)rcn(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: getting the most of out multi-core systems for repeated complex SELECT statements
Date: 2011-02-03 22:56:57
Message-ID: 4D4B32B9.1020904@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-performance
Scott Marlowe wrote:
> On Thu, Feb 3, 2011 at 8:57 AM,  <gnuoytr(at)rcn(dot)com> wrote:
>   
>> Time for my pet meme to wiggle out of its hole (next to Phil's, and a day later).  For PG to prosper in the future, it has to embrace the multi-core/processor/SSD machine at the query level.  It has to.  And
>>     
>
> I'm pretty sure multi-core query processing is in the TODO list.  Not
> sure anyone's working on it tho.  Writing a big check might help.
>   

Work on the exciting parts people are interested in is blocked behind 
completely mundane tasks like coordinating how the multiple sessions are 
going to end up with a consistent view of the database.  See "Export 
snapshots to other sessions" at 
http://wiki.postgresql.org/wiki/ClusterFeatures for details on that one.

Parallel query works well for accelerating CPU-bound operations that are 
executing in RAM.  The reality here is that while the feature sounds 
important, these situations don't actually show up that often.  There 
are exactly zero clients I deal with regularly who would be helped out 
by this.  The ones running web applications whose workloads do fit into 
memory are more concerned about supporting large numbers of users, not 
optimizing things for a single one.  And the ones who have so much data 
that single users running large reports would seemingly benefit from 
this are usually disk-bound instead.

The same sort of situation exists with SSDs.  Take out the potential 
users whose data can fit in RAM instead, take out those who can't 
possibly get an SSD big enough to hold all their stuff anyway, and 
what's left in the middle is not very many people.  In a database 
context I still haven't found anything better to do with a SSD than to 
put mid-sized indexes on them, ones a bit too large for RAM but not so 
big that only regular hard drives can hold them.

I would rather strongly disagree with the suggestion that embracing 
either of these fancy but not really as functional as they appear at 
first approaches is critical to PostgreSQL's future.  They're 
specialized techniques useful to only a limited number of people.

-- 
Greg Smith   2ndQuadrant US    greg(at)2ndQuadrant(dot)com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2011-02-03 23:00:37
Subject: Re: [HACKERS] Slow count(*) again...
Previous:From: Mladen GogalaDate: 2011-02-03 22:40:17
Subject: Re: [HACKERS] Slow count(*) again...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group