From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-performance@postgresql.org
Subject: Re: pg9.0.3 explain analyze running very slow compared to a different box with much less configuration
Date: Thu, 24 Mar 2011 11:11:03 +0200
Message-ID: <201103241111.03360.achill@matrix.gatewaynet.com> (raw)
In-Reply-To: <AANLkTinqz7XpmQHr-Utr4GAR1Ld0PKObxdvF+95xtgbW@mail.gmail.com>
References: <AANLkTinqz7XpmQHr-Utr4GAR1Ld0PKObxdvF+95xtgbW@mail.gmail.com>
You might take a look here:
http://archives.postgresql.org/pgsql-admin/2011-01/msg00050.php
My problem had to do with the speed of gettimeofday. You might want to do some special setting regarding
your box's way of reading time for the hw clock.
Στις Thursday 24 March 2011 04:04:21 ο/η DM έγραψε:
> Hi All,
>
> pg9.0.3 explain analyze running very slow compared to old box with much less
> configuration.
>
> But actual query is performing much better than the old server.
>
> ============old Server===============
> OS: CentOS release 5.4 (Final)
> Linux Server 2.6.18-164.6.1.el5 #1 SMP Tue Nov 3 16:12:36 EST 2009 x86_64
> x86_64 x86_64 GNU/Linux
>
> RAM - 16GB
> CPU - 8 Core
> disk - 300GB
> RAID10 on the disk
>
> Postgresql 9.0.3
>
> Postgres Config:
> shared_buffers = 6GB
> work_mem = 32MB
> maintenance_work_mem = 512MB
> effective_cache_size = 12GB
>
> #explain analyze select * from photo;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
> Seq Scan on photo (cost=0.00..8326849.24 rows=395405824 width=168) (actual
> time=5.632..157757.284 rows=395785382 loops=1)
> Total runtime: 187443.850 ms
> (2 rows)
>
> ============newServer===============
>
> CentOS release 5.4 (Final)
> Linux Server 2.6.18-164.6.1.el5 #1 SMP Tue Nov 3 16:12:36 EST 2009 x86_64
> x86_64 x86_64 GNU/Linux
>
> RAM - 64GB
> CPU - 12 Core
> disk - 1TB
> RAID10 on the disk
>
> Postgresql 9.0.3
> Postgres Config:
> shared_buffers = 16GB
> work_mem = 32MB
> maintenance_work_mem = 1024MB
> effective_cache_size = 12GB
>
>
> # explain analyze select * from photo;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
> Seq Scan on photo (cost=0.00..8326810.24 rows=395579424 width=165) (actual
> time=0.051..316879.928 rows=395648020 loops=1)
> Total runtime: 605703.206 ms
> (2 rows)
>
>
> I read other articles about the same issue but could not find the exact
> solution.
>
>
> I ran gettimeofday() on both machines and got the below results:
>
> Results:
>
> *[Old Server]# time /tmp/gtod*
>
> real 0m0.915s
>
> user 0m0.914s
>
> sys 0m0.001s
>
> *[New Server]# time /tmp/gtod*
>
> real 0m7.542s
>
> user 0m7.540s
>
> sys 0m0.001s
>
>
> I am not sure how to fix this issue, any help would be in great assistance.
>
>
> Thanks
>
> Deepak
>
--
Achilleas Mantzios
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: pg9.0.3 explain analyze running very slow compared to a different box with much less configuration
In-Reply-To: <201103241111.03360.achill@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox