Skip site navigation (1) Skip section navigation (2)

Re: separate drives for WAL or pgdata files

From: David Lang <dlang(at)invendra(dot)net>
To: Anjan Dave <adave(at)vantage(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: separate drives for WAL or pgdata files
Date: 2005-12-20 03:20:56
Message-ID: Pine.LNX.4.62.0512191909240.2807@qnivq.ynat.uz (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 19 Dec 2005, Anjan Dave wrote:

> I am not sure if there's an obvious answer to this...If there's a choice
> of an external RAID10 (Fiber Channel 6 or 8 15Krpm drives) enabled
> drives, what is more beneficial to store on it, the WAL, or the Database
> files? One of the other would go on the local RAID10 (4 drives, 15Krpm)
> along with the OS.

the WAL is small compared to the data, and it's mostly sequential access, 
so it doesn't need many spindles, it just needs them more-or-less 
dedicated to the WAL and not distracted by other things.

the data is large (by comparison), and is accessed randomly, so the more 
spindles that you can throw at it the better.

In your place I would consider making the server's internal drives into 
two raid1 pairs (one for the OS, one for the WAL), and then going with 
raid10 on the external drives for your data

> This is a very busy database with high concurrent connections, random
> reads and writes. Checkpoint segments are 300 and interval is 6 mins.
> Database size is less than 50GB.

this is getting dangerously close to being able to fit in ram. I saw an 
article over the weekend that Samsung is starting to produce 8G DIMM's, 
that can go 8 to a controller (instead of 4 per as is currently done), 
when motherboards come out that support this you can have 64G of ram per 
opteron socket. it will be pricy, but the performance....

in the meantime you can already go 4G/slot * 4 slots/socket and get 64G on 
a 4-socket system. it won't be cheap, but the performance will blow away 
any disk-based system.

for persistant storage you can replicate from your ram-based system to a 
disk-based system, and as long as your replication messages hit disk 
quickly you can allow the disk-based version to lag behind in it's updates 
during your peak periods (as long as it is able to catch up with the 
writes overnight), and as the disk-based version won't have to do the 
seeks for the reads it will be considerably faster then if it was doing 
all the work (especially if you have good, large  battery-backed disk 
caches to go with those drives to consolodate the writes)

> It has become a bit more confusing because I am trying to allot shared
> storage across several hosts, and want to be careful not to overload one
> of the 2 storage processors.

there's danger here, if you share spindles with other apps you run the 
risk of slowing down your database significantly. you may be better off 
with fewer, but dedicated drives rather then more, but shared drives.

David Lang


In response to

Responses

pgsql-performance by date

Next:From: David LangDate: 2005-12-20 03:48:15
Subject: Re: separate drives for WAL or pgdata files
Previous:From: Anjan DaveDate: 2005-12-20 02:08:22
Subject: Re: High context switches occurring

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group