From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Javier Olazaguirre" <javier(dot)olazaguirre(at)nexustelecom(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Speed up repetitive queries |
Date: | 2008-05-04 04:19:33 |
Message-ID: | dcc563d10805032119u373dcd95i4aff318fd1c3f67d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, May 2, 2008 at 9:13 AM, Javier Olazaguirre
<javier(dot)olazaguirre(at)nexustelecom(dot)com> 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...
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick TJ McPhee | 2008-05-04 04:29:11 | Re: Unloading a table consistently |
Previous Message | Nathan Thatcher | 2008-05-04 03:39:04 | Custom C function - is palloc broken? |