Re: filesystem option tuning - Mailing list pgsql-performance

Hi!

> > 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.

For many small commits it's usually the disk seek, the moving of the head,
that produces the overhead. Even if there is only a single byte changed in
the clog, it's an independent operation for the harddisk to seek to, and
write, the block.

> 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.

Right, hadn't thought of that.

> > 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.

I haven't looked at pg_autovacuum yet, but had planned on doing a vacuum or
vacuum flush once every day (or rather, once every night).

[next message]

> > > Does that mean only the xlog, or also the clog?
>
> > You can put clog and xlog on same drive.
>
> You can, but I think you shouldn't. [...]

So the clog is not written to every time the xlog is written to?

On a related issue, what's the connection between the "fsync" and the
"wal_sync_method" configuration switches? Does fsync apply to the wal,
to checkpointing, to regular data writes (assuming data blocks are
written to between checkpoints if there's "nothing better to do"), or
to all? Does it select the wal_sync_method to be used for other writes
too?

(The other issue I am trying to sort out is the interference with journalled
filesystems, in this case SUN UFS with the logging option. That's another
layer of transaction log, and I do not know all details yet to get an
impression of how it affects safety and performance wrt. all these partition
questions...)

(Why are these not common questions covered in the documentation? Is nobody
interested, or does everybody have battery-backed cache on the SCSI HA and
does not care how often the disk would have to seek without?)

Thanks & Regards,
Colin