DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=googlemail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=SMgEMudNg4z3yEeFMDMiBq8Fakjh6zEDi+yCDsqHKFzvgthYh+tqFbnfqgagjt/QObtB/EWTSj80cfG3/URG0Lh7qEva+9uz2P3BE6I0AJXeNUW8NeNQgkUF0oqhDU+nkjnVK3shxTVmxHkmtxk0tXAas2uxBEKlQsm25V4CdbI=
Message-ID: <684362e10603210610n709e223ds@mail.gmail.com>
Date: Tue, 21 Mar 2006 20:10:56 +0600
From: "Markus Bertheau" <mbertheau.pg@googlemail.com>
To: "Edoardo Serra" <osdevel@webrainstorm.it>
Subject: Re: Postmaster using only 4-5% CPU
Cc: pgsql-performance@postgresql.org
In-Reply-To: <7.0.0.16.2.20060321115738.060f9078@webrainstorm.it>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <7.0.0.16.2.20060321115738.060f9078@webrainstorm.it>

The low end server by chance doesn't have an IDE disk that lies about
write completion, or a battery backed disk controller? Try disabling
fsync on the new server to get comparable figures.

Markus Bertheau

2006/3/21, Edoardo Serra <osdevel@webrainstorm.it>:
> Hi all,
>          I'm having a very strange performance
> problems on a fresh install of postgres 8.1.3
> I've just installed it with default option and
> --enable-thread-safety without tweaking config files yet.
>
> The import of a small SQL files into the DB (6
> tables with 166.500 total records, INSERT syntax)
> took me more than 18 minutes as shown below
> (output of  "time ./psql benchmarks < dump.sql")
>
> real 18m33.062s
> user 0m10.386s
> sys 0m7.707s
>
> The server is an
> - Intel(R) Xeon(TM) CPU 3.60GHz - 1MB L2
> - 1 GB RAM
> - 2x HDD SCSI U320 RAID 1 Hardware (HP 6i controller)
>
> The same import, tried on an another low-end
> server with a fresh install of postgres 8.1.3 gave me:
>
> real 2m4.497s
> user 0m6.234s
> sys 0m6.148s
>
> During the test, the postmaster on the first
> server (the slow one) uses only a 4% CPU, while
> on the second one it reaches 50% cpu usage
>
> I was thinking on a IO bandwidth saturation, but
> "vmstat 1" during the import shows me small values for io/bo column
>
> Searching the archive of the ml I found a Disk IO
> test I suddenly ran on the slower server as follow
>
> # time bash -c "dd if=3D/dev/zero of=3Dbigfile bs=3D8k
> count=3D200000 && sync" (write test)
> # time dd if=3Dbigfile of=3D/dev/null bs=3D8k (read test)
>
> output of "vmstat 1" during the above test follows:
>
> procs -----------memory---------- ---swap-- -----io---- --system-- ----cp=
u----
>   r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy=
 id wa
>
> Write test
>   0 11    540   2344  12152 863456    4    0   340 27848 1709   695  6 53=
  0 41
>   0 11    540   2344  12180 863516    4    0    44 45500 1623   386  0  2=
  0 98
>   0  5    540   3168  12200 862520    0    0   264 44888 1573   315  1  2=
  0 97
>
> Read test
>   0  2    440   2328   6076 849120    0    0 94552     0 1550   624  3 10=
  0 87
>   0  2    440   2248   6104 848936    0    0 94508     0 1567   715  7 10=
  0 83
>   0  3    440   2824   6148 847828    0    0
> 102540   448 1511   675 14 11  0 75
>
> Values of io/(bi-bo) during the disk test are a
> lot higher than during the import operation....
>
> I really have no more clues .... :(
>
> Do you have any ideas ?
>
> Tnx in advance
>
> Regards
>
>
> Edoardo Serra
> WeBRainstorm S.r.l.
> IT, Internet services & consulting
> Via Pio Fo=E0 83/C
> 10126 Torino
> Tel: +39 011 6966881
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>