Re: Several queries consume 100 % processor

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