Thread: Several queries consume 100 % processor

Hi,

I have an app working over LAN , XP + Postgres 8.2. The problem is that a client send several queries and the processor show me 100 % usage. (P IV 3 Ghz with 1 Gb RAM) and stops all its tasks until queries finishes.

This queries are send from inside app code, should I create procedures for every one of the queries inside Postgres ?, How much response time will be improved with this ?

Any help will be useful for me, thanks.


----------------------
J. Oriol Mujica F.
----------------------
Bucaramanga - COLOMBIA

__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
Regístrate ya - http://correo.espanol.yahoo.com/

On Wed, May 23, 2007 at 07:50:42AM -0500, J. ORIOL wrote:
> Hi,
>
> I have an app working over LAN , XP + Postgres 8.2. The problem is
> that a client send several queries and the processor show me 100 %
> usage. (P IV 3 Ghz with 1 Gb RAM) and stops all its tasks until
> queries finishes.
>
> This queries are send from inside app code, should I create
> procedures for every one of the queries inside Postgres ?, How much
> response time will be improved with this ?

I doubt making them procedures will make much difference to
performance. If you want more recommendations, please post to the list
more details about the actual queries and tables involved (you have got
logging setup to detect the errant queries, right?)

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

J. ORIOL wrote:
> Hi,
>
> I have an app working over LAN , XP + Postgres 8.2.

OK - so that's on the server, yes?

 > The problem is
> that a client send several queries and the processor show me 100 %
> usage. (P IV 3 Ghz with 1 Gb RAM)

That's what's supposed to happen. Any process will either:
  1. Use 100% of CPU
  2. Be waiting for disk/network/etc
  3. Be sleeping, waiting for an event
If it's using 100% CPU then it's calculating (probably sorting) and
isn't having to read a lot of data from the disk.

 > and stops all its tasks until
> queries finishes.

I doubt that. It might slow down processing on other tasks, but there's
nothing in PostgreSQL that should prevent other processes getting a
chance at running. What are these tasks, and how do you know they are
"stopped"?

> This queries are send from inside app code, should I create
> procedures for every one of the queries inside Postgres ?, How much
> response time will be improved with this ?

Why would creating procedures have any effect on the response time?

> Any help will be useful for me, thanks.

You'll probably need to supply:
1. The SQL for the query/queries that are causing problems.
2. Details of the tables involved and their sizes
3. EXPLAIN ANALYSE output for those queries.

That would let people help if it's a problem with specific queries.

--
   Richard Huxton
   Archonet Ltd

Thanks by your replies, Richard, Martijn.

The queries are very normal, neither complex or large. My tables are pretty small too. (My SQL skills are the same, I'm afraid)

So, what I'm doing with my app:

I'm filling a 40 x 10 matrix of data (A string grid), that's 400 cells being calculated aproximately with 6 to 8 queries each one of them. That's 4800 queries to fill the matrix. In the server, this task delays 8 secs. but in the client are 40 secs.

The server slows down all its processes, sorry, does'nt "stop" tasks, but in the practice, its the same, I think.

Certainly, I want to know if I have serious design errors or if I have reached my HW limits; my hope was implementing functions in plpgsql, but you guys said this will not improve the process time as well.

Thanks by your help.


Richard Huxton <dev@archonet.com> escribió:

J. ORIOL wrote:
> Hi,
>
> I have an app working over LAN , XP + Postgres 8.2.

OK - so that's on the server, yes?

> The problem is
> that a client send several queries and the processor show me 100 %
> usage. (P IV 3 Ghz with 1 Gb RAM)

That's what's supposed to happen. Any process will either:
1. Use 100% of CPU
2. Be waiting for disk/network/etc
3. Be sleeping, waiting for an event
If it's using 100% CPU then it's calculating (probably sorting) and
isn't having to read a lot of data from the disk.

> and stops all its tasks until
> queries finishes.

I doubt that. It might slow down processing on other tasks, but there's
nothing in PostgreSQL that should prevent other processes getting a
chance at running. What are these tasks, and how do you know they are
"stopped"?

> This queries are send from inside app code, should I create
> procedures for every one of the queries inside Postgres ?, How much
> response time will be improved with this ?

Why would creating procedures have any effect on the response time?

> Any help will be useful for me, thanks.

You'll probably need to supply:
1. The SQL for the query/queries that are causing problems.
2. Details of the tables involved and their sizes
3. EXPLAIN ANALYSE output for those queries.

That would let people help if it's a problem with specific queries.

--
Richard Huxton
Archonet Ltd

------------------------------------------------------------
J. Oriol Mujica F.

__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
Regístrate ya - http://correo.espanol.yahoo.com/

J. ORIOL wrote:

>
> I'm filling a 40 x 10 matrix of data (A string grid), that's 400
> cells being calculated aproximately with 6 to 8 queries each one of
> them. That's 4800 queries to fill the matrix. In the server, this
> task delays 8 secs. but in the client are 40 secs.

What takes 40 seconds? If you're just running updates then the client
should receive acknowledgement.

> The server slows down all its processes, sorry, does'nt "stop" tasks,
> but in the practice, its the same, I think.
>
> Certainly, I want to know if I have serious design errors or if I
> have reached my HW limits; my hope was implementing functions in
> plpgsql, but you guys said this will not improve the process time as
> well.

You'll need to tell us something about your design. See my first reply
for what people will need.

As to whether your hardware is a limit - nobody can say without knowing
what you're doing with it.

Four obvious things leap out though:
1. Why does it take 6-8 queries to calculate one value?
2. What makes it necessary to calculate one cell at a time rather than
all at once?
3. Are you vacuuming enough, and how have you established this?
4. Why are you doing this in a database at all? For 4800 values I'd just
calculate them all in my application and then store the lot in one go.

--
   Richard Huxton
   Archonet Ltd