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%).
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 <mark.lewis@xxxxxxxx>
Para: brauagustin-susc@xxxxxxxxxxxx
CC: pgsql-performance@xxxxxxxxxxxxxx
Enviado: miércoles 19 de septiembre de 2007, 15:03:06
Asunto: Re: Low CPU Usage
De: Mark Lewis <mark.lewis@xxxxxxxx>
Para: brauagustin-susc@xxxxxxxxxxxx
CC: pgsql-performance@xxxxxxxxxxxxxx
Enviado: miércoles 19 de septiembre de 2007, 15:03:06
Asunto: Re: Low CPU Usage
On Wed, 2007-09-19 at 10:38 -0700, brauagustin-susc@xxxxxxxxxxxx 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
> 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
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/
