From: Andy Colson <andy@squeakycode.net>
To: Mark Stosberg <mark@summersault.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: getting the most of out multi-core systems for repeated complex SELECT statements
Date: Thu, 03 Feb 2011 09:44:03 -0600
Message-ID: <4D4ACD43.6050000@squeakycode.net> (raw)
In-Reply-To: <iiegdj$jtg$1@dough.gmane.org>
References: <iiegdj$jtg$1@dough.gmane.org>
On 2/3/2011 9:08 AM, Mark Stosberg wrote:
>
> Each night we run over a 100,000 "saved searches" against PostgreSQL
> 9.0.x. These are all complex SELECTs using "cube" functions to perform a
> geo-spatial search to help people find adoptable pets at shelters.
>
> All of our machines in development in production have at least 2 cores
> in them, and I'm wondering about the best way to maximally engage all
> the processors.
>
> Now we simply run the searches in serial. I realize PostgreSQL may be
> taking advantage of the multiple cores some in this arrangement, but I'm
> seeking advice about the possibility and methods for running the
> searches in parallel.
>
> One naive I approach I considered was to use parallel cron scripts. One
> would run the "odd" searches and the other would run the "even"
> searches. This would be easy to implement, but perhaps there is a better
> way. To those who have covered this area already, what's the best way
> to put multiple cores to use when running repeated SELECTs with PostgreSQL?
>
> Thanks!
>
> Mark
>
>
1) I'm assuming this is all server side processing.
2) One database connection will use one core. To use multiple cores you
need multiple database connections.
3) If your jobs are IO bound, then running multiple jobs may hurt
performance.
Your naive approach is the best. Just spawn off two jobs (or three, or
whatever). I think its also the only method. (If there is another
method, I dont know what it would be)
-Andy
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, mark@summersault.com
Subject: Re: getting the most of out multi-core systems for repeated complex SELECT statements
In-Reply-To: <4D4ACD43.6050000@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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