Re: Tuning scenarios (was Changing the default

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Tuning scenarios (was Changing the default
Date: 2003-02-20 23:35:44
Message-ID: 1045784144.15881.92.camel@camel
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-advocacy pgsql-hackers pgsql-performance

On Thu, 2003-02-20 at 17:33, Josh Berkus wrote:
>
> Robert,
>
> > > 1) Location of the pg_xlog for heavy-update databases.
> >
> > I see you put this up pretty high on the list. Do you feel this is the
> > most important thing you can do? For example, if you had a two drive
> > installation, would you load the OS and main database files on 1 disk
> > and put the pg_xlog on the second disk above all other configurations?
>
> Yes, actually. On machines with 2 IDE disks, I've found that this can make
> as much as 30% difference in speed of serial/large UPDATE statements.

Do you know how well those numbers hold up under scsi and/ or raid based
system? (I'd assume anyone doing serious work would run scsi)

>
> > Ideally I recommend 3 disks, one for os, one for data, one for xlog; but
> > if you only had 2 would the added speed benefits be worth the additional
> > recovery complexity (if you data/xlog are on the same disk, you have 1
> > point of failure, one disk for backing up)
>
> On the other hand, with the xlog on a seperate disk, the xlog and the database
> disks are unlikely to fail at the same time. So I don't personally see it as
> a recovery problem, but a benefit.
>

ok (playing a bit of devil's advocate here), but you have two possible
points of failure, the data disk and the xlog disk. If either one goes,
your in trouble. OTOH if you put the OS disk on one drive and it goes,
your database and xlog are still safe on the other drive.

Robert Treat

In response to

Responses

Browse pgsql-advocacy by date

  From Date Subject
Next Message Andrew Sullivan 2003-02-20 23:49:28 Re: Tuning scenarios (was Changing the default
Previous Message Josh Berkus 2003-02-20 22:33:02 Re: Tuning scenarios (was Changing the default configuration)

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Sullivan 2003-02-20 23:49:28 Re: Tuning scenarios (was Changing the default
Previous Message Brandon Craig Rhodes 2003-02-20 23:13:57 possibly spurious `EXCEPT ... may not refer to other relation...'

Browse pgsql-performance by date

  From Date Subject
Next Message Andrew Sullivan 2003-02-20 23:49:28 Re: Tuning scenarios (was Changing the default
Previous Message Josh Berkus 2003-02-20 22:33:02 Re: Tuning scenarios (was Changing the default configuration)