Re: [PERFORM] Low CPU Usage
Hi Greg this is my Bonnie result. Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP insaubi 8G 25893 54 26762 9 14146 3 36846 68 43502 3 102.8 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP 16 + +++ + +++ + +++ + +++ + +++ + +++ insaubi,8G,25893,54,26762,9,14146,3,36846,68,43502,3,102.8,0,16,+,+++,+,+++,+,+++,+,+++,+,+++,+,+++ If I compare this against my laptop (SATA disk too) is really better, but I don't know if this result is a good one or not. I don't know where to continue looking for the cause of the problem, I think there is a bug or something missconfigured with Debian 4.0r1 and Postgres. I unppluged the server from the network with the same results. I have the server mapped as localhost in PgAdmin III, there shouldn't be network traffic and there isn't (monitoring the network interface). I'm really lost with this weird behaviour. I really apreciate your help Regards Agustin - Mensaje original De: Greg Smith [EMAIL PROTECTED] Para: [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Enviado: sábado 22 de septiembre de 2007, 3:29:17 Asunto: Re: [PERFORM] Low CPU Usage On Thu, 20 Sep 2007, [EMAIL PROTECTED] wrote: Which other test can I do to find if this is a hardware, kernel o postgres issue? The little test hdparm does is not exactly a robust hard drive benchmark. If you want to rule out hard drive transfer speed issues, take at look at the tests suggested at http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm and see how your results compare to the single SATA disk example I give there. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD Las últimas noticias sobre el Mundial de Rugby 2007 están en Yahoo! Deportes. ¡Conocelas! http://ar.sports.yahoo.com/mundialderugby
Re: [PERFORM] Low CPU Usage
I have found the reason!!! I begin to see line by line postgresql.conf and saw ssl = true. I have disabled ssl and then I have restarted the server and that's all. It's 4 or 5 times faster than the old server. I don't understand why PgAdmin is connecting using ssl if I have leave this field empty!!! Debian by default installs Postgres with ssl enabled. Thank you very much all of you to help me to find the causes. Regards Agustin - Mensaje original De: [EMAIL PROTECTED] [EMAIL PROTECTED] Para: Greg Smith [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Enviado: lunes 24 de septiembre de 2007, 10:59:26 Asunto: Re: [PERFORM] Low CPU Usage Hi Greg this is my Bonnie result. Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP insaubi 8G 25893 54 26762 9 14146 3 36846 68 43502 3 102.8 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP 16 + +++ + +++ + +++ + +++ + +++ + +++ insaubi,8G,25893,54,26762,9,14146,3,36846,68,43502,3,102.8,0,16,+,+++,+,+++,+,+++,+,+++,+,+++,+,+++ If I compare this against my laptop (SATA disk too) is really better, but I don't know if this result is a good one or not. I don't know where to continue looking for the cause of the problem, I think there is a bug or something missconfigured with Debian 4.0r1 and Postgres. I unppluged the server from the network with the same results. I have the server mapped as localhost in PgAdmin III, there shouldn't be network traffic and there isn't (monitoring the network interface). I'm really lost with this weird behaviour. I really apreciate your help Regards Agustin - Mensaje original De: Greg Smith [EMAIL PROTECTED] Para: [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Enviado: sábado 22 de septiembre de 2007, 3:29:17 Asunto: Re: [PERFORM] Low CPU Usage On Thu, 20 Sep 2007, [EMAIL PROTECTED] wrote: Which other test can I do to find if this is a hardware, kernel o postgres issue? The little test hdparm does is not exactly a robust hard drive benchmark. If you want to rule out hard drive transfer speed issues, take at look at the tests suggested at http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm and see how your results compare to the single SATA disk example I give there. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD 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. Vistá ar.autos.yahoo.com/
Re: [PERFORM] Low CPU Usage
On Thu, 20 Sep 2007, [EMAIL PROTECTED] wrote: Which other test can I do to find if this is a hardware, kernel o postgres issue? The little test hdparm does is not exactly a robust hard drive benchmark. If you want to rule out hard drive transfer speed issues, take at look at the tests suggested at http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm and see how your results compare to the single SATA disk example I give there. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
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: [EMAIL PROTECTED] [EMAIL PROTECTED] Para: pgsql-performance@postgresql.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. Vistá ar.autos.yahoo.com/
Re: [PERFORM] Low CPU Usage
On Fri, Sep 21, 2007 at 12:30 PM, in message [EMAIL PROTECTED], [EMAIL PROTECTED] wrote: 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) . . . 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) . . . 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. That's just a matter of labeling the tables with role rather than alias. The plans look the same to me. The old one runs this query for about 37 seconds and for the new one for about 301 seconds. That's not what it looks like based on the EXPLAIN ANALYZE output. It looks like run time dropped from two seconds to half a second. It seems as though you either have a network delay delivering the results, or your application is slow to read them. Exactly how are you arriving at those timings you're reporting to us? -Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 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: [EMAIL PROTECTED] [EMAIL PROTECTED] Para: pgsql-performance@postgresql.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: [EMAIL PROTECTED] [EMAIL PROTECTED] Para: pgsql-performance@postgresql.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/
Re: [PERFORM] Low CPU Usage
That's not what it looks like based on the EXPLAIN ANALYZE output. It looks like run time dropped from two seconds to half a second. It seems as though you either have a network delay delivering the results, or your application is slow to read them. Exactly how are you arriving at those timings you're reporting to us? I have noticed this in a daly process I run which involves normally 45 minutes and with the new server takes 1:40. Some days ago I began to do some tests with no success, then I opened PgAdmin with this simply query to read 2 big tables and then compare disk access. SELECT * FROM fact_ven_renta fvr, dim_producto_std_producto dpp WHERE fvr.producto_std_producto_sk = dpp.producto_sk fact_ven_renta has 136316 rows dim_producto_std_producto has 3669 rows I have made all possible combinations pgadmin (running in the same server each query, in the old one, in the new one), without difference and I only retrieve the first 100 records (I didn't count the network time in any case). But the weird thing is running the query in the new server the are many disk access and cpu usage. And with other applications in the same server are a lot of disks access. Seguí de cerca a la Selección Argentina de Rugby en el Mundial de Francia 2007. http://ar.sports.yahoo.com/mundialderugby
Re: [PERFORM] Low CPU Usage
In response to [EMAIL PROTECTED]: That's not what it looks like based on the EXPLAIN ANALYZE output. It looks like run time dropped from two seconds to half a second. It seems as though you either have a network delay delivering the results, or your application is slow to read them. Exactly how are you arriving at those timings you're reporting to us? I have noticed this in a daly process I run which involves normally 45 minutes and with the new server takes 1:40. Some days ago I began to do some tests with no success, then I opened PgAdmin with this simply query to read 2 big tables and then compare disk access. SELECT * FROM fact_ven_renta fvr, dim_producto_std_producto dpp WHERE fvr.producto_std_producto_sk = dpp.producto_sk fact_ven_renta has 136316 rows dim_producto_std_producto has 3669 rows Run the tests from psql on the same server. As Kevin pointed out, the _server_ is faster, but it appears as if the connection between PGadmin and this new server is slower somehow. Are you sure of your speed/duplex settings on the network side? That's the most common cause of this kind of thing in my experience. Try doing a raw FTP transfer between the client and server and see if you get the speed you should. I have made all possible combinations pgadmin (running in the same server each query, in the old one, in the new one), without difference and I only retrieve the first 100 records (I didn't count the network time in any case). But the weird thing is running the query in the new server the are many disk access and cpu usage. And with other applications in the same server are a lot of disks access. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Low CPU Usage
That's not what it looks like based on the EXPLAIN ANALYZE output. It looks like run time dropped from two seconds to half a second. It seems as though you either have a network delay delivering the results, or your application is slow to read them. Exactly how are you arriving at those timings you're reporting to us? I have noticed this in a daly process I run which involves normally 45 minutes and with the new server takes 1:40. Some days ago I began to do some tests with no success, then I opened PgAdmin with this simply query to read 2 big tables and then compare disk access. SELECT * FROM fact_ven_renta fvr, dim_producto_std_producto dpp WHERE fvr.producto_std_producto_sk = dpp.producto_sk fact_ven_renta has 136316 rows dim_producto_std_producto has 3669 rows Run the tests from psql on the same server. As Kevin pointed out, the _server_ is faster, but it appears as if the connection between PGadmin and this new server is slower somehow. It runs quickly!!! But I don't know how to compare because looks like it retrieve fields by demand, when I put ctrl+end (go to the last record) it use a lot of CPU and disk, run quickly anyway. Correct me if am I wrong but, executing PgAdmin in the same server there aren't networks delays! And when the server is processing the query there isn't network traffic because is processing the result. Are you sure of your speed/duplex settings on the network side? That's the most common cause of this kind of thing in my experience. Try doing a raw FTP transfer between the client and server and see if you get the speed you should. This isn't a dedicated database server, client application and server are running in the same machine!!! I have stop the client application too with same results. Anyway I will do some network test to find a solution. Seguí de cerca a la Selección Argentina de Rugby en el Mundial de Francia 2007. http://ar.sports.yahoo.com/mundialderugby
Re: [PERFORM] Low CPU Usage
In response to [EMAIL PROTECTED]: That's not what it looks like based on the EXPLAIN ANALYZE output. It looks like run time dropped from two seconds to half a second. It seems as though you either have a network delay delivering the results, or your application is slow to read them. Exactly how are you arriving at those timings you're reporting to us? I have noticed this in a daly process I run which involves normally 45 minutes and with the new server takes 1:40. Some days ago I began to do some tests with no success, then I opened PgAdmin with this simply query to read 2 big tables and then compare disk access. SELECT * FROM fact_ven_renta fvr, dim_producto_std_producto dpp WHERE fvr.producto_std_producto_sk = dpp.producto_sk fact_ven_renta has 136316 rows dim_producto_std_producto has 3669 rows Run the tests from psql on the same server. As Kevin pointed out, the _server_ is faster, but it appears as if the connection between PGadmin and this new server is slower somehow. It runs quickly!!! But I don't know how to compare because looks like it retrieve fields by demand, when I put ctrl+end (go to the last record) it use a lot of CPU and disk, run quickly anyway. That's pretty odd. If you use \timing in psql, you can get execution time for each query, if it helps you track things down. Correct me if am I wrong but, executing PgAdmin in the same server there aren't networks delays! Not network, no. But the results of your explains seem to show that the query is executing much faster on the new system than the old, so the problem still becomes, what is happening after the query completes that is so slow? It's just that networking is ruled out. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Low CPU Usage
That's not what it looks like based on the EXPLAIN ANALYZE output. It looks like run time dropped from two seconds to half a second. It seems as though you either have a network delay delivering the results, or your application is slow to read them. Exactly how are you arriving at those timings you're reporting to us? I have noticed this in a daly process I run which involves normally 45 minutes and with the new server takes 1:40. Some days ago I began to do some tests with no success, then I opened PgAdmin with this simply query to read 2 big tables and then compare disk access. SELECT * FROM fact_ven_renta fvr, dim_producto_std_producto dpp WHERE fvr.producto_std_producto_sk = dpp.producto_sk fact_ven_renta has 136316 rows dim_producto_std_producto has 3669 rows Run the tests from psql on the same server. As Kevin pointed out, the _server_ is faster, but it appears as if the connection between PGadmin and this new server is slower somehow. It runs quickly!!! But I don't know how to compare because looks like it retrieve fields by demand, when I put ctrl+end (go to the last record) it use a lot of CPU and disk, run quickly anyway. That's pretty odd. If you use \timing in psql, you can get execution time for each query, if it helps you track things down. Yes, in the new server running with \timing it consumes 5.6 seconds and in the old server it consumes 25 seconds. Correct me if am I wrong but, executing PgAdmin in the same server there aren't networks delays! Not network, no. But the results of your explains seem to show that the query is executing much faster on the new system than the old, so the problem still becomes, what is happening after the query completes that is so slow? It's just that networking is ruled out. Is connected to full 100Mb, it transfers many things quick to clients. Is running Apache adn JBoss, transfer rate is good, I did scp to copy many archives and is as quick as the old server. I have no idea how to continue researching this problem. Now I'm going to do some networks tests. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 Las últimas noticias sobre el Mundial de Rugby 2007 están en Yahoo! Deportes. ¡Conocelas! http://ar.sports.yahoo.com/mundialderugby
Re: [PERFORM] Low CPU Usage
From: [EMAIL PROTECTED] Subject: Re: [PERFORM] Low CPU Usage I have no idea how to continue researching this problem. Now I'm going to do some networks tests. I would go back to the slow program and try to capture the slow queries in the log file. Once you have some queries which are running slow then you can run EXPLAIN ANALYZE to see what the bottle neck is. It seems like you've found pgAdmin is slow sending across the network, but we don't know if that has anything to do with your original problems. Just my 2 cents. Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Low CPU Usage
[EMAIL PROTECTED] wrote: That's pretty odd. If you use \timing in psql, you can get execution time for each query, if it helps you track things down. Yes, in the new server running with \timing it consumes 5.6 seconds and in the old server it consumes 25 seconds. Correct me if am I wrong but, executing PgAdmin in the same server there aren't networks delays! Not network, no. But the results of your explains seem to show that the query is executing much faster on the new system than the old, so the problem still becomes, what is happening after the query completes that is so slow? It's just that networking is ruled out. Is connected to full 100Mb, it transfers many things quick to clients. Is running Apache adn JBoss, transfer rate is good, I did scp to copy many archives and is as quick as the old server. I have no idea how to continue researching this problem. Now I'm going to do some networks tests. See if this can give some help to you: I was experienced some problems with networks with win98 and winXP stations, the application was running with good performance almost of the time, but in suddenly the performance slow down. We noticed that the problem was with the time to connect with the server, that was very slow. The problem occurs also when the internet link down. Well, I don't know why but when we exclude win98 stations from network, the problem disappears. I think that was some DNS problem (but not sure), because one time we cleared nameserver clauses in the /etc/resolv.conf the performance return to the normal. But we reinstalled win98 machines with winXP too, so I don't know what happened exactly. The server OS was a Mandriva Linux running postgres ( 8.0, I guess) and samba. Workstations connect via odbc (informing the IP of server or the name to connect the problem persists). -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Low CPU Usage
Luiz K. Matsumura wrote: Is connected to full 100Mb, it transfers many things quick to clients. Is running Apache adn JBoss, transfer rate is good, I did scp to copy many archives and is as quick as the old server. I have no idea how to continue researching this problem. Now I'm going to do some networks tests. Any chance this is your desktop machine, and you're also using it for audio? Microsoft built in a feature (!) that reduces network speed by 90% when music is playing: http://it.slashdot.org/article.pl?sid=07/08/26/1628200from=rss Craig ---(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
Re: [PERFORM] Low CPU Usage
On Fri, Sep 21, 2007 at 3:40 PM, in message [EMAIL PROTECTED], Luiz K. Matsumura [EMAIL PROTECTED] wrote: but in suddenly the performance slow down. We noticed that the problem was with the time to connect with the server, that was very slow. I think that was some DNS problem (but not sure), because one time we cleared nameserver clauses in the /etc/resolv.conf the performance return to the normal. You may have it there. In some versions of Java, on Windows, connection times are horribly slow unless the machine's IP address has a reverse DNS entry. Perhaps the new machine lacks such an entry, or there's a different version of Java in use? -Kevin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Low CPU Usage
It's a Debian 4.0r1 server without sound (alsa is disabled), I'm running the querys locally. It's happening running the query locally with PgAdmin and by jdbc locally too. Yes I have win98, XP machines on my network, I will unplugged from the net and test again. On monday I'll give you my answer. Last thing I did was disabling ipv6 and with the same results. Thank you very much for your help. - Mensaje original De: Craig James [EMAIL PROTECTED] Para: Luiz K. Matsumura [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; pgsql-performance@postgresql.org Enviado: viernes 21 de septiembre de 2007, 18:15:40 Asunto: Re: [PERFORM] Low CPU Usage Luiz K. Matsumura wrote: Is connected to full 100Mb, it transfers many things quick to clients. Is running Apache adn JBoss, transfer rate is good, I did scp to copy many archives and is as quick as the old server. I have no idea how to continue researching this problem. Now I'm going to do some networks tests. Any chance this is your desktop machine, and you're also using it for audio? Microsoft built in a feature (!) that reduces network speed by 90% when music is playing: http://it.slashdot.org/article.pl?sid=07/08/26/1628200from=rss Craig Las últimas noticias sobre el Mundial de Rugby 2007 están en Yahoo! Deportes. ¡Conocelas! http://ar.sports.yahoo.com/mundialderugby
Re: [PERFORM] Low CPU Usage
Hola Beto. I have no idea where to look for that configuration or settings. Yesterday I red about some drivers problems with SATA disk working togheter with IDE devices with DMA. Mi server server is a Pentium VI 3.3 with hyper threading (enabled in BIOS), HP Proliant ML 110. Then I entered to the BIOS and saw in IDE Configuration: ATA/IDE Configuration[Enhanced] Configure SATA as [IDE] = it has RAID option too I have any idea how to continue!!! I don't know if this a SATA problem, a configuration problem or what else. I have installed several servers beggining with postgres 6.4 and I've neved had this kind of problems (always with IDE disks). I think this is a problem with SATA disk i/o, but I don't see how to measure that (I have already set postgresql.conf). Regards Agustin - Mensaje original De: Norberto Meijome [EMAIL PROTECTED] Para: [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Enviado: jueves 20 de septiembre de 2007, 7:53:05 Asunto: Re: [PERFORM] Low CPU Usage On Wed, 19 Sep 2007 12:13:33 -0700 (PDT) [EMAIL PROTECTED] wrote: max_stack_depth = 7MB #in the old server is 8MB but if I set in here give me the ulimit error Hola Agustin :) otro argentino en el extranjero x aca ;) anyway, back to English ;) a long shot but... check if you have any limits set on the host for CPU usage... you may be limited to x number of secs / % by the OS scheduler. When you query your CPU, it will say u are only using 5% or so... chau, Beto _ Norberto Meijome Octantis Pty Ltd Intelligence: Finding an error in a Knuth text. Stupidity: Cashing that $2.56 check you got. NOTICE: The contents of this email and its attachments are confidential and intended only for the individuals or entities named above. If you have received this message in error, please advise the sender by reply email and immediately delete the message and any attachments without using, copying or disclosing the contents. Thank you. 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/
Re: [PERFORM] Low CPU Usage
[EMAIL PROTECTED] wrote: Hola Beto. I have no idea where to look for that configuration or settings. In postgreSQL, the main settings are in .../pgsql/data/postgresql.conf Yesterday I red about some drivers problems with SATA disk working togheter with IDE devices with DMA. Mi server server is a Pentium VI 3.3 with hyper threading (enabled in BIOS), HP Proliant ML 110. Then I entered to the BIOS and saw in IDE Configuration: ATA/IDE Configuration[Enhanced] Configure SATA as [IDE] = it has RAID option too I have any idea how to continue!!! I don't know if this a SATA problem, a configuration problem or what else. I have installed several servers beggining with postgres 6.4 and I've neved had this kind of problems (always with IDE disks). I think this is a problem with SATA disk i/o, but I don't see how to measure that (I have already set postgresql.conf). Are you sure you are really having a problem with insufficient CPU time being devoted to your program(s)? When I run postgreSQL and do the initial populating of my database, which takes several hours due to the nature of the input data, it runs just 25% to 50% of one CPU, even though I have two 3.06 GHz hyperthreaded Xeon processors and six 10,000 rpm Ultra/320 SCSI hard drives on two SCSI controllers. If I look at the results of the Linux top command, and iostat and vmstat, I see that I am in io-wait state 100% of the time. The transfer rate to the hard drives averages about 2 Megabytes/second even though I have seen 90 Megabytes/second at times (when doing a database restore). So the IO system can be quite fast when it is not waiting (for seeks, no doubt). If the postgreSQL processes wanted more CPU time, they could have it as the machine does not do much else most of the time. Actually, it runs a four BOINC processes, but they run at nice level 19, so they run only if no other process wants processing time. When I do a database backup, it will run more than 100% of a CPU (remember I have two or four processors, depending on how you count them) for extended periods, so the OS is certainly capable of supplying CPU power when I need it. And postgreSQL runs multiple processes at once, so in theory, they could gert 400% of a processor if they needed it. They do not seem to need to do this for me. Regards Agustin - Mensaje original De: Norberto Meijome [EMAIL PROTECTED] Para: [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Enviado: jueves 20 de septiembre de 2007, 7:53:05 Asunto: Re: [PERFORM] Low CPU Usage On Wed, 19 Sep 2007 12:13:33 -0700 (PDT) [EMAIL PROTECTED] wrote: max_stack_depth = 7MB #in the old server is 8MB but if I set in here give me the ulimit error Hola Agustin :) otro argentino en el extranjero x aca ;) anyway, back to English ;) a long shot but... check if you have any limits set on the host for CPU usage... you may be limited to x number of secs / % by the OS scheduler. When you query your CPU, it will say u are only using 5% or so... -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 08:15:01 up 6 days, 42 min, 1 user, load average: 4.24, 4.25, 4.14 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Low CPU Usage
My new server postgresql.conf is equal to the old one. I'm doubting this is a hardware issue. Googling with my hard HP Proliant ML 110 G3 I saw that IHC7 controller has some problems, but looking and testing with hdparm it looks ok. hdparm -tT /dev/sdaç Timing cached reads: 1722 MB in 2.00 seconds = 860.38 MB/sec Timing buffered disks reads: 164 MB in 3.01 seconds = 54.53 MB/sec Doing hdparm -I /dev/sda DMA has * in udma5 Which other test can I do to find if this is a hardware, kernel o postgres issue? Regards Agustin - Mensaje original De: Jean-David Beyer [EMAIL PROTECTED] Para: pgsql-performance@postgresql.org Enviado: jueves 20 de septiembre de 2007, 9:31:36 Asunto: Re: [PERFORM] Low CPU Usage [EMAIL PROTECTED] wrote: Hola Beto. I have no idea where to look for that configuration or settings. In postgreSQL, the main settings are in .../pgsql/data/postgresql.conf Yesterday I red about some drivers problems with SATA disk working togheter with IDE devices with DMA. Mi server server is a Pentium VI 3.3 with hyper threading (enabled in BIOS), HP Proliant ML 110. Then I entered to the BIOS and saw in IDE Configuration: ATA/IDE Configuration[Enhanced] Configure SATA as [IDE] = it has RAID option too I have any idea how to continue!!! I don't know if this a SATA problem, a configuration problem or what else. I have installed several servers beggining with postgres 6.4 and I've neved had this kind of problems (always with IDE disks). I think this is a problem with SATA disk i/o, but I don't see how to measure that (I have already set postgresql.conf). Are you sure you are really having a problem with insufficient CPU time being devoted to your program(s)? When I run postgreSQL and do the initial populating of my database, which takes several hours due to the nature of the input data, it runs just 25% to 50% of one CPU, even though I have two 3.06 GHz hyperthreaded Xeon processors and six 10,000 rpm Ultra/320 SCSI hard drives on two SCSI controllers. If I look at the results of the Linux top command, and iostat and vmstat, I see that I am in io-wait state 100% of the time. The transfer rate to the hard drives averages about 2 Megabytes/second even though I have seen 90 Megabytes/second at times (when doing a database restore). So the IO system can be quite fast when it is not waiting (for seeks, no doubt). If the postgreSQL processes wanted more CPU time, they could have it as the machine does not do much else most of the time. Actually, it runs a four BOINC processes, but they run at nice level 19, so they run only if no other process wants processing time. When I do a database backup, it will run more than 100% of a CPU (remember I have two or four processors, depending on how you count them) for extended periods, so the OS is certainly capable of supplying CPU power when I need it. And postgreSQL runs multiple processes at once, so in theory, they could gert 400% of a processor if they needed it. They do not seem to need to do this for me. Regards Agustin - Mensaje original De: Norberto Meijome [EMAIL PROTECTED] Para: [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Enviado: jueves 20 de septiembre de 2007, 7:53:05 Asunto: Re: [PERFORM] Low CPU Usage On Wed, 19 Sep 2007 12:13:33 -0700 (PDT) [EMAIL PROTECTED] wrote: max_stack_depth = 7MB #in the old server is 8MB but if I set in here give me the ulimit error Hola Agustin :) otro argentino en el extranjero x aca ;) anyway, back to English ;) a long shot but... check if you have any limits set on the host for CPU usage... you may be limited to x number of secs / % by the OS scheduler. When you query your CPU, it will say u are only using 5% or so... -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 08:15:01 up 6 days, 42 min, 1 user, load average: 4.24, 4.25, 4.14 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Seguí de cerca a la Selección Argentina de Rugby en el Mundial de Francia 2007. http://ar.sports.yahoo.com/mundialderugby
[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 Seguí de cerca a la Selección Argentina de Rugby en el Mundial de Francia 2007. http://ar.sports.yahoo.com/mundialderugby
Re: [PERFORM] Low CPU Usage
[EMAIL PROTECTED] wrote: 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. Have you ANALYZEd all tables? Try running EXPLAIN ANALYZE on the query on both servers, and see if they're using the same plan. 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!!! That implies that it's doing much more I/O on the new server, so the CPU just sits and waits for data to arrive from the disk. That does iostat say about disk utilization on both servers? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Low CPU Usage
On Wed, 2007-09-19 at 10:38 -0700, [EMAIL PROTECTED] wrote: 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? It could be a planning issue. Have you analyzed the new database to gather up-to-date statistics? A comparison of EXPLAIN ANALYZE results for an example query in both systems should answer that one. Another possibility because you're dealing with lower-end drives is that you have a case of one drive lying about fsync where the other is not. If possible, try running your test with fsync=off on both servers. If there's a marked improvement on the new server but no significant change on the old server then you've found your culprit. -- Mark Lewis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Low CPU Usage
No, changing to fsync off didn't improve performance at all. Settings work_mem = 64MB max_stack_depth = 7MB #in the old server is 8MB but if I set in here give me the ulimit error max_fsm_pages = 204800 effective_cache_size = 512MB Atuvacuum is off. I have run vacuum full and vacuum analyze. The databases are freezed (there are no insert, update or delete operations)!!! In both servers the query plans are identical with the same costs too!!! It's really weird, I don't see high values monitoring disk. Cpu usage is about 5% and sometimes a tips of 40 % of disk usage when the query is finishing (the first 3 minutes there are some tips of 11 to 16%). - Mensaje original De: Mark Lewis [EMAIL PROTECTED] Para: [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Enviado: miércoles 19 de septiembre de 2007, 15:03:06 Asunto: Re: [PERFORM] Low CPU Usage On Wed, 2007-09-19 at 10:38 -0700, [EMAIL PROTECTED] wrote: 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? It could be a planning issue. Have you analyzed the new database to gather up-to-date statistics? A comparison of EXPLAIN ANALYZE results for an example query in both systems should answer that one. Another possibility because you're dealing with lower-end drives is that you have a case of one drive lying about fsync where the other is not. If possible, try running your test with fsync=off on both servers. If there's a marked improvement on the new server but no significant change on the old server then you've found your culprit. -- Mark Lewis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Las últimas noticias sobre el Mundial de Rugby 2007 están en Yahoo! Deportes. ¡Conocelas! http://ar.sports.yahoo.com/mundialderugby
Re: [PERFORM] Low CPU Usage
On 9/19/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: No, changing to fsync off didn't improve performance at all. Settings work_mem = 64MB max_stack_depth = 7MB #in the old server is 8MB but if I set in here give me the ulimit error max_fsm_pages = 204800 effective_cache_size = 512MB Atuvacuum is off. I have run vacuum full and vacuum analyze. The databases are freezed (there are no insert, update or delete operations)!!! In both servers the query plans are identical with the same costs too!!! It's really weird, I don't see high values monitoring disk. Cpu usage is about 5% and sometimes a tips of 40 % of disk usage when the query is finishing (the first 3 minutes there are some tips of 11 to 16%). (please don't top post) This sounds like you've got problems somewhere in your I/O system. hdparm etc may or may not be seeing the issue. What do you see in /var/log/messages or dmesg? ---(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
Re: [PERFORM] Low CPU Usage
This is my dmesg file, I see there are some errors but I don't know how to manage!!! What do you mean with don't top post? Sorry but I'm new with this kind of mailing list and I don't want to botter some others. Sorry my bad English too. Thanks for your help - Mensaje original De: Scott Marlowe [EMAIL PROTECTED] Para: [EMAIL PROTECTED] [EMAIL PROTECTED] CC: pgsql-performance@postgresql.org Enviado: miércoles 19 de septiembre de 2007, 16:41:45 Asunto: Re: [PERFORM] Low CPU Usage On 9/19/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: No, changing to fsync off didn't improve performance at all. Settings work_mem = 64MB max_stack_depth = 7MB #in the old server is 8MB but if I set in here give me the ulimit error max_fsm_pages = 204800 effective_cache_size = 512MB Atuvacuum is off. I have run vacuum full and vacuum analyze. The databases are freezed (there are no insert, update or delete operations)!!! In both servers the query plans are identical with the same costs too!!! It's really weird, I don't see high values monitoring disk. Cpu usage is about 5% and sometimes a tips of 40 % of disk usage when the query is finishing (the first 3 minutes there are some tips of 11 to 16%). (please don't top post) This sounds like you've got problems somewhere in your I/O system. hdparm etc may or may not be seeing the issue. What do you see in /var/log/messages or dmesg? 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/ dmesg Description: Binary data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Low CPU Usage
On 9/19/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This is my dmesg file, I see there are some errors but I don't know how to manage!!! nothing too horrible. Just wanted to make sure you weren't getting lots of bad sectors or timeouts. Nothing too bad looking there. What do you mean with don't top post? It means when you put your post at the top of mine. The way I'm answering provides context. I.e. this paragraph I'm writing goes with the paragraph you wrote that I'm answering. It makes it easier to keep track of long busy conversations. Sorry but I'm new with this kind of mailing list and I don't want to botter some others. Sorry my bad English too. No bother, and your english is way better than my Spanish. or Russian, or Japanese, etc... ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
