SEO

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGVDqlIB7bNG8LQkwRAjwMAJ0cj8puU9AeS0Pba0sWOP WqW7B9lwCcCiMJ
dcOQQwZSNfFv+S2dZIHWz74=
=taoZ
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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/