X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9D5959DC9C6 for ; Fri, 10 Mar 2006 10:27:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76873-02 for ; Fri, 10 Mar 2006 10:27:50 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.193]) by postgresql.org (Postfix) with ESMTP id 52C889DCAC0 for ; Fri, 10 Mar 2006 10:27:44 -0400 (AST) Received: by zproxy.gmail.com with SMTP id x7so729009nzc for ; Fri, 10 Mar 2006 06:27:48 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=JJXL4c3fK6hXBszfUJX6PJWYram38Oa4NWtwWQsxNxusISkoPb5TE5+UqM6v/Zd08PEI+qIostnk22gPlR1c+6VZxWnD7L2fOfFOC/aVy4bJsJRGWtmb2CY4c59qRKZ6JBxBxXO3SKUk1c67+54G2csSA7XG1uRa6ejgdWvJopw= Received: by 10.36.250.47 with SMTP id x47mr1030173nzh; Fri, 10 Mar 2006 06:27:48 -0800 (PST) Received: by 10.36.60.2 with HTTP; Fri, 10 Mar 2006 06:27:48 -0800 (PST) Message-ID: Date: Fri, 10 Mar 2006 09:27:48 -0500 From: "Daniel Blaisdell" To: pgsql-performance@postgresql.org Subject: Re: x206-x225 In-Reply-To: <441181C6.3040401@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14519_25417823.1142000868631" References: <441181C6.3040401@archonet.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.097 required=5 tests=[AWL=0.096, HTML_MESSAGE=0.001] X-Spam-Score: 0.097 X-Spam-Level: X-Archive-Number: 200603/124 X-Sequence-Number: 17561 ------=_Part_14519_25417823.1142000868631 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline The primary slow down is probably between your system bus from main memory to your disk storage. If you notice from your statistics that the select statements are very close. This is because all the data you need is already in system memory. The primary bottle neck is probably disk I/O. Scsi will always be faster than ATA. Scsi devices have dedicated hardware for getting data to and from the disc to the main system bus without requiring a trip through the CPU. You may be able to speed up the ata disc by enabling DMA by using hdparm. hdparm -d1 /dev/hda (or whatever your device is) -Daniel -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (GNU/Linux) iD8DBQBEEYzX9SJ2nhowvKERAoiFAKCLR+7a7ReZ2mjjPjpONHLGIQD1SgCeNNON V1kbyATIFVPWuf1W6Ji0IFg=3D =3D5Msr -----END PGP SIGNATURE----- On 3/10/06, Richard Huxton wrote: > > H.J. Sanders wrote: > > X206 IBM X226 > > ---------------------- ------------------- > > processor Pentium 4 3.2 > > Ghz Xeon 3.0 Ghz > > main memory 1.25 > > GB 4 GB > > discs 2 x SCSI RAID1 10000RPM > > 1 x ATA 7200 RPM > > Noting that the SCSI discs are on the *slower* machine. > > > Time at X206 Time at X226 > > -------------------- ------------------ > > insert record (1 to 10000) 6 sec. 41 sec. > > select record (1 to 10000) 4 4 > > delete record (1 to 10000) 6 41 > > > > > > This is ofcourse a totally unexpected results (you should think off the > > opposite). > > Your ATA disk is lying about disk caching being turned off. Assuming > each insert is in a separate transaction, then it's not going to do > 10,000 / 6 =3D 1667 transactions/sec - that's faster than it's rotational > speed. > > > Funny is that the select time is the same for both machines. > > Because you're limited by the speed to read from RAM. > > By the way - these sort of tests are pretty much meaningless in any > practical terms. > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > ------=_Part_14519_25417823.1142000868631 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline The primary slow down is probably between your system bus from main memory to your disk storage. If you notice from your statistics that the select statements are very close. This is because all the data you need is already in system memory. The primary bottle neck is probably disk I/O. Scsi will always be faster than ATA. Scsi devices have dedicated hardware for getting data to and from the disc to the main system bus without requiring a trip through the CPU.

You may be able to speed up the ata disc by enabling DMA by using hdparm.
hdparm -d1 /dev/hda  (or whatever your device is)

-Daniel

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQBEEYzX9SJ2nhowvKERAoiFAKCLR+7a7ReZ2mjjPjpONHLGIQD1SgCeNNON
V1kbyATIFVPWuf1W6Ji0IFg=3D
=3D5Msr
-----END PGP SIGNATURE-----

On 3/10/06, Richard Huxton <dev@archonet= .com> wrote:
H.J. Sanders wrote:
> X206            = ;            &n= bsp;            = ;  IBM X226
> ----------------------         = ;            &n= bsp;      -------------------
> processor           =             &nb= sp;Pentium 4 3.2
> Ghz            =             &nb= sp;   Xeon 3.0 Ghz
> main memory           &nb= sp;        1.25
> GB            &= nbsp;           &nbs= p;            &= nbsp;  4 GB
> discs           &nbs= p;            &= nbsp;     2 x SCSI RAID1  10000RPM
> 1 x ATA 7200 RPM

Noting tha= t the SCSI discs are on the *slower* machine.

> Time at X206 = ;           Time at = X226
> --------------------       =  ------------------
> insert record (1 to 10000)  &nbs= p;       6 sec.    4= 1 sec.
> select record (1 to 10000)          4 &n= bsp;        4
> delete record (1 to 10000)          6 &n= bsp;       41
>
>
> This is ofcourse a totally unexpected results (y= ou should think off the
> opposite).

Your ATA disk is lying ab= out disk caching being turned off. Assuming
each insert is in a separate= transaction, then it's not going to do
10,000 / 6 =3D 1667 transactions/sec - that's faster than it's rotation= al
speed.

> Funny is that the select time is the same for both= machines.

Because you're limited by the speed to read from RAM.

By the way - these sort of tests are pretty much meaningless in any
= practical terms.
--
   Richard Huxton
   Archo= net Ltd

---------------------------(end of broadcast)---------------= ------------
TIP 6: explain analyze is your friend

------=_Part_14519_25417823.1142000868631--