Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id C34C0133740B for ; Thu, 3 Feb 2011 18:57:07 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 63139-08 for ; Thu, 3 Feb 2011 22:57:00 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.8]) by mail.postgresql.org (Postfix) with ESMTP id F02A41337B96 for ; Thu, 3 Feb 2011 18:56:59 -0400 (AST) Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mreu1) with ESMTP (Nemesis) id 0M6yZd-1Q89CZ2KEn-00xAxu; Thu, 03 Feb 2011 23:56:59 +0100 Message-ID: <4D4B32B9.1020904@2ndquadrant.com> Date: Thu, 03 Feb 2011 17:56:57 -0500 From: Greg Smith User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: Scott Marlowe CC: gnuoytr@rcn.com, pgsql-performance@postgresql.org Subject: Re: getting the most of out multi-core systems for repeated complex SELECT statements References: <20110203105704.BAX82905@ms14.lnh.mail.rcn.net> In-Reply-To: Content-Type: multipart/alternative; boundary="------------040002010200020104050208" X-Provags-ID: V02:K0:3StqFgnPlQ/XaxMToRdJ456KTrWUD9WRvZEM2lY0kDs N5oMf6PK7KuGZMncI58EsNsPYuHueqnNcijFVQ5zByq58xhLiz j413jn60MCXqe8h3c4sDF0h+tqnqk6AxOdE5d1eziMFIOHCetz P8zeYWJL0P/BHzsYYushewisvOj4S3wngPD4CrPwZo5tPstwn2 4tW4Qyl1u+H77Gx2Bc/AEWsI+D8LZYihDC+ycN9wuo= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201102/124 X-Sequence-Number: 42262 This is a multi-part message in MIME format. --------------040002010200020104050208 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Scott Marlowe wrote: > On Thu, Feb 3, 2011 at 8:57 AM, 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@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books --------------040002010200020104050208 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Scott Marlowe wrote: 
On Thu, Feb 3, 2011 at 8:57 AM,  <gnuoytr@rcn.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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
--------------040002010200020104050208--