Re: Low CPU Usage

I forgot to tell this plan was with Postgres 8.1.9 in the new server with postgres 8.2.4 in the new server the plan is the same as with te old one (the little difference in rows retrieved is that the database is yesterday snapshot).
This is the plan for the new server with postgres 8.2.4:
Hash Join (cost=449.55..8846.67 rows=135786 width=904) (actual time=10.823..467.746 rows=135786 loops=1)
Hash Cond: (fvr.producto_std_producto_sk = dpp.producto_sk)
-> Seq Scan on fact_ven_renta fvr (cost=0.00..6020.86 rows=135786 width=228) (actual time=0.007..81.268 rows=135786 loops=1)
-> Hash (cost=403.69..403.69 rows=3669 width=676) (actual time=10.733..10.733 rows=3669 loops=1)
-> Seq Scan on dim_producto_std_producto dpp (cost=0.00..403.69 rows=3669 width=676) (actual time=0.004..2.995 rows=3669 loops=1)
Total runtime: 513.747 ms

This query is running for about 200 seconds, doing dstat I don't see anything weird (regards to low cpu usage 2% or 3%) and normal i/o. In the old server I have 30% of cpu usage an high i/o and run faster!!!
This is really weird.

----- Mensaje original ----
De: "brauagustin-susc(at)yahoo(dot)com(dot)ar" <brauagustin-susc(at)yahoo(dot)com(dot)ar>
Para: pgsql-performance(at)postgresql(dot)org
Enviado: viernes 21 de septiembre de 2007, 14:30:45
Asunto: Re: [PERFORM] Low CPU Usage

I'm doing several tests.
Right now I did a VACUUM FULL ANALYZE in both servers.
In the old one vacuum runs for about 354 seconds and in the new one 59 seconds.

Then I have ran
EXPLAIN ANALYZE
SELECT *
FROM fact_ven_renta fvr, dim_producto_std_producto dpp
WHERE
fvr.producto_std_producto_sk = dpp.producto_sk

I have found that the plans aren't exactly the same.
This is the plan for the old server:
Hash Join (cost=449.55..8879.24 rows=136316 width=904) (actual time=50.734..1632.491 rows=136316 loops=1)
Hash Cond: (fvr.producto_std_producto_sk = dpp.producto_sk)
-> Seq Scan on fact_ven_renta fvr (cost=0.00..6044.16 rows=136316
width=228) (actual time=0.029..452.716 rows=136316 loops=1)
-> Hash (cost=403.69..403.69 rows=3669 width=676) (actual time=50.582..50.582 rows=3669 loops=1)
-> Seq Scan on dim_producto_std_producto dpp (cost=0.00..403.69 rows=3669 width=676) (actual time=0.023..19.776 rows=3669 loops=1)
Total runtime: 2022.293 ms

And this is the plan for the new server:
Hash Join (cost=412.86..9524.13 rows=136316 width=905) (actual time=9.421..506.376 rows=136316 loops=1)
Hash Cond: ("outer".producto_std_producto_sk = "inner".producto_sk)
-> Seq Scan on fact_ven_renta fvr (cost=0.00..6044.16 rows=136316 width=228) (actual time=0.006..107.318 rows=136316 loops=1)
-> Hash (cost=403.69..403.69 rows=3669 width=677) (actual time=9.385..9.385 rows=3669 loops=1)

-> Seq Scan on dim_producto_std_producto dpp (cost=0.00..403.69 rows=3669 width=677) (actual time=0.003..3.157 rows=3669 loops=1)
Total runtime: 553.619 ms

I see an "outer" join in the plan for the new server. This is weird!!! There are the same databases in both servers.
The old one runs this query for about 37 seconds and for the new one for about 301 seconds.
Why are plans different? May the backup recovery process have had an error in the new server when restoring?

I appreciate some help.
Regards Agustin

----- Mensaje original ----
De: "brauagustin-susc(at)yahoo(dot)com(dot)ar" <brauagustin-susc(at)yahoo(dot)com(dot)ar>
Para: pgsql-performance(at)postgresql(dot)org
Enviado: miércoles 19 de septiembre de 2007, 14:38:13
Asunto: [PERFORM] Low CPU Usage

Hi all.
Recently I have installed a brand new server with a Pentium IV 3.2 GHz, SATA Disk, 2GB of Ram in Debian 4.0r1 with PostgreSQL 8.2.4 (previously a 8.1.9).
I have other similar server with an IDE disk, Red Hat EL 4 and PostgreSQL 8.2.3

I have almost the same postgresql.conf in both servers, but in the new one (I have more work_mem than the other one) things go really slow. I began to monitor i/o disk and it's really ok, I have test disk with hdparm and it's 5 times faster than the IDE one.
Running the same queries in both servers in the new one it envolves almost 4 minutes instead of 18 seconds in the old one.
Both databases are the same, I have vacuum them and I don't know how to manage this issue.
The only weird thing is than in the older server running
the query it uses 30% of CPU instead of 3 o 5 % of the new one!!!
What's is happening with this server? I upgrade from 8.1.9 to 8.2.4 trying to solve this issue but I can't find a solution.

Any ideas?
Regards
Agustin

El Mundial de Rugby 2007
Las últimas noticias en Yahoo! Deportes:

http://ar.sports.yahoo.com/mundialderugby

Los referentes más importantes en compra/venta de autos se juntaron:
Demotores y Yahoo!.
Ahora comprar o vender tu auto es más fácil.
Visitá http://ar.autos.yahoo.com/

Los referentes más importantes en compra/ venta de autos se juntaron:
Demotores y Yahoo!
Ahora comprar o vender tu auto es más fácil. Vistá ar.autos.yahoo.com/