Re: Query using cursors using 100% CPU

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