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