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

Re: filesystem option tuning

From: Shridhar Daithankar <shridhar(at)frodo(dot)hserus(dot)net>
To: share-postgres(at)think42(dot)com
Cc: Richard Huxton <dev(at)archonet(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: filesystem option tuning
Date: 2004-05-29 09:31:45
Message-ID: 200405291501.45229.shridhar@frodo.hserus.net (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
On Wednesday 19 May 2004 13:02, share-postgres(at)think42(dot)com wrote:
> >   - If you can put WAL on separate disk(s), all the better.
>
> Does that mean only the xlog, or also the clog? As far as I understand, the
> clog contains some meta-information on the xlog, so presumably it is
> flushed to disc synchronously together with the xlog? That would mean that
> they each need a separate disk to prevent one disk having to seek too
> often...?

You can put clog and xlog on same drive. That should be enough in most cases. 
xlog is written sequentially and never read back other than for recovery 
after a crash. clog is typically 8KB or a page and should not be an IO 
overhead even in high traffic databases.

> >   - Battery-backed write-cache for your SCSI controller can be a big
> > performance win
>
> I probably won't be able to get such a setup for this project; that's why I
> am bothering about which disk will be seeking how often.

As I said earlier, xlog is written sequentially and if I am not mistaken clog 
as well. So there should not be much seeking if they are on a separate drive.

(Please correct me if I am wrong)

> >   - Tablespaces _should_ be available in the next release of PG, we'll
> > know for sure soon. That might make life simpler for you if you do want
> > to spread your database around by hand,
>
> Ok, I think tablespaces are not the important thing - at least for this
> project of ours.

Well, if you have tablespaces, you don't have to mess with symlinking 
clog/xlog or use location facility which is bit rough. You should be able to 
manage such a setup solely from postgresql. That is an advantage of 
tablespaces.

> Here goes ... we are talking about a database cluster with two tables where
> things are happening, one is a kind of log that is simply "appended" to and
> will expect to reach a size of several million entries in the time window
> that is kept, the other is a persistent backing of application data that
> will mostly see read-modify-writes of single records. Two writers to the
> history, one writer to the data table. The volume of data is not very high
> and RAM is enough...

Even if you have enough RAM, you should use pg_autovacuum so that your tables 
are in shape. This is especially required when your update/insert rate is 
high.

If your history logs needs to be rotated, you can take advantage of the fact 
that DDL's in postgresql are fully transacted. So you can drop the table in a 
transaction but nobody will notice anything unless it is committed. Makes a 
transparent rotation.

HTH

 Shridhar

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-05-29 15:18:06
Subject: Re: filesystem option tuning
Previous:From: rajaguruDate: 2004-05-29 06:28:35
Subject: Logging all query in one seperate File

pgsql-general by date

Next:From: TamerDate: 2004-05-29 12:24:50
Subject: locating postgresql lib and include files for SuSE 9.0 RPMS?
Previous:From: Michal TáborskýDate: 2004-05-29 09:31:27
Subject: Use arrays to store multilanguage texts

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