On Fri, May 2, 2008 at 9:13 AM, Javier Olazaguirre
<javier.olazaguirre@[EMAIL PROTECTED]
> wrote:
>
> I have an application developped by a third party which takes very long
to
> process all the queries.
>
> I use Red Hat 4 and Postgre 8.2.7 on a 64 bit machine.
>
> Checking the log files created by postgre I see that the program is
running
> always the same query:
> The only thing that changes is the parameter at the end ($1).
> This query is executed at least a million times (sometimes several
million
> times) just changing the value of the $1 parameter. Each query takes
between
> 1 and 2 milliseconds to execute in my system. So running a million
queries
> takes quite a lot of minutes.
Is the application preparing and re-executing the same query, or
repreparing each time it executes it? Preparation might be a pretty
significant amount of overhead here.
> Is there any way to speed up the execution of this query?
1-2 milliseconds is pretty fast for an individual query.
> I cannot change the code of the application, I already got it compiled,
so
> as far as I can think of, all I can do is tune the system, change
parameters
> in postgre, etc.
Yeah, we've all been there. Sometimes you can come up with a workaround.
> I already tried changing shared buffers and other parameters in
> postgresql.conf, but performance is still the same.
Yeah, I can't imagine there's a lot of low hanging fruit for tuning
the db for such a simple query.
> When I run a Explain statement with the select I see indices are being
used
> by all subqueries.
> I see my cpu is at 100%, so I believe my bottleneck is not IO or memory
> (8GB, and in "top" I see almost all of it is free).
Yeah, I can't see using more memory helping with this operation. It's
a single small bit at a time. In fact, using more memory would just
mean more to keep track of, likely slowing things down.
> My problem is that of
> all the cores of my processors, postgre is just using one, but I guess
this
> can only be fixed changing the code of the application running the
queries
> on postgre, so this is a different story.
Of course pgsql is using just one. You're only giving it one thing to
do at a time. (btw, it's PostgreSQL, postgres, pgsql, or pg. Postgre
is generally not preferred. No big. and no matter how you spell it,
it's pronounced "Post-Gres-Q-L" :) )
What you might look at doing is having the application run in multiple
instances each instance across a specific data range. This will
likely move your heads all over the place. OTOH, if the app could be
rewritten to send >1 query at a time through multiple connections, it
could likely get faster.
However, running multiple update queries will very quickly saturate
your I/O and you'll suddenly be I/O bound. That can be worked on with
more discs, RAID-10, battery backed RAID controllers, etc...
--
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
|
