Re: Several queries consume 100 % processor

From: "J(dot) ORIOL" <famach(at)yahoo(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>, Martijn <kleptog(at)svana(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Several queries consume 100 % processor
Date: 2007-05-23 13:53:43
Message-ID: 70857.95869.qm@web34713.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)archonet(dot)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/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2007-05-23 14:05:26 Re: Several queries consume 100 % processor
Previous Message André Volpato 2007-05-23 13:49:21 Faster data type for one-length values