X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 714DBD80C2 for ; Wed, 5 Oct 2005 12:03:07 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32061-02 for ; Wed, 5 Oct 2005 15:03:05 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.206]) by svr1.postgresql.org (Postfix) with ESMTP id C0CFAD6F47 for ; Wed, 5 Oct 2005 12:03:03 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 71so74654wra for ; Wed, 05 Oct 2005 08:03:03 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ZpP4rzqWpFjSc4389daL63oJcuPc3QMfBvt1OfU/E7iOWvk2lUSAQF00vCSGpPuws6fdc4YRQmXouc/Ul1MqyzYthww5qCzLhe4f1Lhapsy3gr8xpKn3s4DZbwpgcskxMYxNZ/NV96Yr2L67NgVhKkDM/j1YcGF1D2aPvzGInxQ= Received: by 10.54.69.8 with SMTP id r8mr407693wra; Wed, 05 Oct 2005 08:03:03 -0700 (PDT) Received: by 10.54.84.8 with HTTP; Wed, 5 Oct 2005 08:03:03 -0700 (PDT) Message-ID: <33c6269f0510050803w72097bb5v526a0126af876af4@mail.gmail.com> Date: Wed, 5 Oct 2005 11:03:03 -0400 From: Alex Turner Reply-To: Alex Turner To: emil@baymountain.com Subject: Re: Indexes on ramdisk Cc: pgsql-performance@postgresql.org In-Reply-To: <200510042341.23264.emil@baymountain.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4616_25671259.1128524583287" References: <200510042109.01849.emil@baymountain.com> <33c6269f0510042023t24175e33vf84d9af609a8ccd7@mail.gmail.com> <200510042341.23264.emil@baymountain.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.355 required=5 tests=[AWL=-0.174, HTML_20_30=0.504, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/101 X-Sequence-Number: 14869 ------=_Part_4616_25671259.1128524583287 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline What kind of order of improvement do you need to see? What period are these number for? Were they collected over 1 hour, 1 day, 1 month? How much Cache do you have on the controller? You can certainly get more speed by adding more disk and possibly by adding more controller RAM/a second controller. 10 disks isn't really that many fo= r a totally kick-ass DB server. You can acheive more block writes with RAID 10s than with RAID 1s. Wether it's cost effective is dependant on lots of factors like your chassis and drive enclosures etc. vs SSD. SSD will be faster, but last I heard was expensive, and I checked a few websites but couldn't get much price info. Normaly when you can't get price info, thats = a bad sign ;). If you are doing large chunks of writes to a small number of tables, then you might be better off with a single large RAID 10 for your tablespace than with seperate RAID 1s. If you are writing 5 to 1 more table data than index data, you are hurting yourself by seperating on to multiple RAID 1s instead of a single RAID 10 which could write at 2-3x for the 5, an= d 2-3x for the 1 and only suffer a single seek penalty but get data onto disk twice to three times as fast (depending how many RAID 1s you join). Try unseperating RAID 1s, and combine to a RAID 10. for indexes and tablespaces= . The controller will re-sequence your writes/reads to help with effeciency, and dbwriter is there to make things go easier. You can at least get some idea by doing an iostat and see how many IOs and how much throughput is happening. That will rappidly help determine if you are bound by IOs or by MB/sec. Worst case I'm wrong, but IMHO it's worth a try. Alex Turner NetEconomist On 10/4/05, Emil Briggs wrote: > > > Talk about your IO system a bit. There might be obvious ways to improve= . > > > > What System/Motherboard are you using? > > What Controller Cards are you using? > > What kind of Disks do you have (SATA, SCSI 7.6k 10k 15k) > > What denominations (9, 18, 36, 72, 143, 80, 160, 200 240Gig)? > > What kind of RAIDs do you have setup (How many drives what stripe sizes= , > > how many used for what). > > What levels of RAID are you using (0,1,10,5,50)? > > > > It's a quad opteron system. RAID controller is a 4 channel LSILogic > Megaraid > 320 connected to 10 15k 36.7G SCSI disks. The disks are configured in 5 > mirrored partitions. The pg_xlog is on one mirror and the data and indexe= s > are spread over the other 4 using tablespaces. These numbers from > pg_stat_user_tables are from about 2 hours earlier today on this one > table. > > > idx_scan 20578690 > idx_tup_fetch 35866104841 > n_tup_ins 1940081 > n_tup_upd 1604041 > n_tup_del 1880424 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ------=_Part_4616_25671259.1128524583287 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline What kind of order of improvement do you need to see?

What period are these number for?  Were they collected over 1 hour, 1 = day, 1 month?

How much Cache do you have on the controller?

You can certainly get more speed by adding more disk and possibly by adding more controller RAM/a second controller.  10 disks isn't really that many for a totally kick-ass DB server.  You can acheive more block writes with RAID 10s than with RAID 1s.  Wether it's cost effective is dependant on lots of factors like your chassis and drive enclosures etc. vs SSD.  SSD will be faster, but last I heard was expensive, and I checked a few websites but couldn't get much price info.  Normaly when you can't get price info, thats a bad sign ;).  If you are doing large chunks of writes to a small number of tables, then you might be better off with a single large RAID 10 for your tablespace than with seperate RAID 1s.  If you are writing 5 to 1 more table data than index data, you are hurting yourself by seperating on to multiple RAID 1s instead of a single RAID 10 which could write at 2-3x for the 5, and 2-3x for the 1 and only suffer a single seek penalty but get data onto disk twice to three times as fast (depending how many RAID 1s you join).  Try unseperating RAID 1s, and combine to a RAID 10. for indexes and tablespaces.  The controller will re-sequence your writes/reads to help with effeciency, and dbwriter is there to make things go easier.

You can at least get some idea by doing an iostat and see how many IOs and how much throughput is happening. That will rappidly help determine if you are bound by IOs or by MB/sec.

Worst case I'm wrong, but IMHO it's worth a try.

Alex Turner
NetEconomist

On 10/4/05, Emil Briggs <emil@baymountain.com> wrote:
> Talk about your IO system a bit. There might be obvious ways to improv= e.
>
> What System/Motherboard are you using?
> What Cont= roller Cards are you using?
> What kind of Disks do you have (SATA, S= CSI=20 7.6k 10k 15k)
> What denominations (9, 18, 36, 72, 143, 80, 160, 200 = 240Gig)?
> What kind of RAIDs do you have setup (How many drives what= stripe sizes,
> how many used for what).
> What levels of RAID= are you using (0,1,10,5,50)?
>

It's a quad opteron system. RAID controller is a 4 channel = LSILogic Megaraid
320 connected to 10 15k 36.7G SCSI disks. The disks ar= e configured in 5
mirrored partitions. The pg_xlog is on one mirror and = the data and indexes
are spread over the other 4 using tablespaces. These numbers from
pg= _stat_user_tables are from about 2 hours earlier today on this one table.

idx_scan         &nb= sp; 20578690
idx_tup_fetch  35866104841
n_tup_ins        1940081
n_tup_u= pd       1604041
n_tup_del  &nbs= p;     1880424



--------------------= -------(end of broadcast)---------------------------
TIP 4: Have you sea= rched our list archives?

            &nb= sp;  http://archives.postgr= esql.org

------=_Part_4616_25671259.1128524583287--