Thread: Query using cursors using 100% CPU
Hi chaps,
We use a 3rd party driver to connect our some of our old ISAM
applications into postgres (on linux), and as far as I'm aware the
driver uses cursors.
I've noticed that on some of our applications that read a lot of data
the CPU usage for the postmaster serving it rockets up to between 80
and 100%. If I run multiple instances of the same application then I
get two processes each using 80 -100% effectively pushing the load
average of the server up above 2.
Is there any way I can see what the driver is doing in any more
detail from postgres?
Looking at the activity on the server I just see the select satement.
And doing that select statement on its own and churning the results
into a file like so:
# \o results.txt
# select * from "Events"."SEATS2" where ("SHOW" = 58919 AND "TYPE" =
99 and "BEST" = 3 and "BLOCK" = '3');
# \o
.... the CPU usage only goes up to about 26%
Any ideas? should I be worried?
__________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
On Tuesday 12 February 2008 14:21:35 you wrote:
> Hi chaps,
>
> We use a 3rd party driver to connect our some of our old ISAM
> applications into postgres (on linux), and as far as I'm aware the
> driver uses cursors.
>
> I've noticed that on some of our applications that read a lot of data
> the CPU usage for the postmaster serving it rockets up to between 80
> and 100%. If I run multiple instances of the same application then I
> get two processes each using 80 -100% effectively pushing the load
> average of the server up above 2.
>
> Is there any way I can see what the driver is doing in any more
> detail from postgres?
>
> Looking at the activity on the server I just see the select satement.
> And doing that select statement on its own and churning the results
> into a file like so:
>
> # \o results.txt
> # select * from "Events"."SEATS2" where ("SHOW" = 58919 AND "TYPE" =
> 99 and "BEST" = 3 and "BLOCK" = '3');
> # \o
>
> .... the CPU usage only goes up to about 26%
>
> Any ideas? should I be worried?
Hi Glyn,
In order to determine whether or not your driver is using cursors, the easiest
way is to alter postgresql.conf so that individual SQL statements are
recorded in the server log. You should then be able to see statements of the
form DECLARE CURSOR foo FOR SELECT... if cursors are being used.
Looking at the differences in CPU usage, could it be that when you run your
query in psql, you are running psql on the database server itself while your
application is running on a separate server and sends its queries over the
network? If so, the extra CPU usage may be involved with sending/receiving
large datasets across the network.
ATB,
Mark.
--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063
--- Mark Cave-Ayland <mark.cave-ayland@siriusit.co.uk> wrote:
> Hi Glyn,
>
> In order to determine whether or not your driver is using cursors,
> the easiest
> way is to alter postgresql.conf so that individual SQL statements
> are
> recorded in the server log. You should then be able to see
> statements of the
> form DECLARE CURSOR foo FOR SELECT... if cursors are being used.
>
Thanks Mark,
I've turned this on and I never see a DECLARE CURSOR so I presume I
was wrong and it is not using cursors.
I do see a DEALLOCATE though, although no PREPARE. Before this it
does lots of statements that are limited to 100 records, and all the
statements are named and preceded by execute, so I presume it is
using prepared statements getting little chunks of data and relying
on the first execute to prepare them?
> Looking at the differences in CPU usage, could it be that when you
> run your
> query in psql, you are running psql on the database server itself
> while your
> application is running on a separate server and sends its queries
> over the
> network? If so, the extra CPU usage may be involved with
> sending/receiving
> large datasets across the network.
>
Both tests were run over the network.
__________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
On Tuesday 12 February 2008 16:03:31 Glyn Astill wrote: > Thanks Mark, > > I've turned this on and I never see a DECLARE CURSOR so I presume I > was wrong and it is not using cursors. > > I do see a DEALLOCATE though, although no PREPARE. Before this it > does lots of statements that are limited to 100 records, and all the > statements are named and preceded by execute, so I presume it is > using prepared statements getting little chunks of data and relying > on the first execute to prepare them? Yup, it's using prepared queries. Unfortunately prepared queries are not always a good thing, because if your data is non-uniformly distributed then the planner has guess what will be the best plan without knowing what parameters you are passing in at run-time. Hence you may get a plan that is optimal for one set of values, but not for others. > Both tests were run over the network. Okay. From what you mention above, it's likely that what you're seeing is a bad query plan choice anyway. ATB, Mark. -- Mark Cave-Ayland Sirius Corporation - The Open Source Experts http://www.siriusit.co.uk T: +44 870 608 0063