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
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@xxxxxxxxxxxx" <brauagustin-susc@xxxxxxxxxxxx>
Para: pgsql-performance@xxxxxxxxxxxxxx
Enviado: miércoles 19 de septiembre de 2007, 14:38:13
Asunto: Low CPU Usage
El Mundial de Rugby 2007
Las últimas noticias en Yahoo! Deportes:
http://ar.sports.yahoo.com/mundialderugby
De: "brauagustin-susc@xxxxxxxxxxxx" <brauagustin-susc@xxxxxxxxxxxx>
Para: pgsql-performance@xxxxxxxxxxxxxx
Enviado: miércoles 19 de septiembre de 2007, 14:38:13
Asunto: 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
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/
