pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
filesystem option tuning 7+ messages / 5 participants [nested] [flat]
* filesystem option tuning @ 2004-05-15 10:52 share-postgres@think42.com 0 siblings, 1 reply; 7+ messages in thread From: share-postgres@think42.com @ 2004-05-15 10:52 UTC (permalink / raw) To: pgsql-general@postgresql.org Hi All, I have recently started evaluating Postgresql 7.4.2 to replace some *cough* more proprietary database systems... Thanks to the _excellent_ documentation (a point I cannot overemphasize) I was up and running in no time, and got a first test application running on the native C interface. There is just one point where I found the documentation lacking any description and practical hints (as opposed to all other topics), namely that of how to tune a setup for maximum performance regarding the layout of partitions on hard-disks and their mount options. I gather that the pg_xlog directory contains the transaction log and would benefit greatly from being put on a separate partition. I would then mount that partition with the noatime and forcedirectio options (on Solaris, the latter to circumvent the OS' buffer cache)? On the other hand the data partition should not be mounted with direct io, since Postgresql is documented as relying heavily on the OS' cache? Then I was wondering whether the fsync option refers only to the wal log (is that another name for the xlog, or is one a subset of the other?), or also to data write operations? With forcedirectio for the wal, do I still need fsync (or O_SYNC...) because otherwise I could corrupt the data? Are there any other directories that might benefit from being put on a dedicated disk, and with which mount options? Even without things like tablespaces there should be some headroom over having everything on one partition like in the default setup. What I should add is that reliability is a premium for us, we do not want to sacrifice integrity for speed, and that we are tuning for a high commit rate of small, simple transactions... I would be greatly thankful if somebody could give me some hints or pointers to further documentation as my search on the web did not show up much. Regards, Colin ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: filesystem option tuning @ 2004-05-17 17:04 Richard Huxton <dev@archonet.com> parent: share-postgres@think42.com 0 siblings, 1 reply; 7+ messages in thread From: Richard Huxton @ 2004-05-17 17:04 UTC (permalink / raw) To: share-postgres@think42.com; +Cc: pgsql-general@postgresql.org share-postgres@think42.com wrote: > Hi All, > > I have recently started evaluating Postgresql 7.4.2 to replace some *cough* > more proprietary database systems... Thanks to the _excellent_ documentation > (a point I cannot overemphasize) I was up and running in no time, and got a > first test application running on the native C interface. In no official capacity whatsoever, welcome aboard. > There is just one point where I found the documentation lacking any > description and practical hints (as opposed to all other topics), namely > that of how to tune a setup for maximum performance regarding the layout of > partitions on hard-disks and their mount options. I'm not a Sun user, so I can't give any OS-specific notes, but in general: - Don't bypass the filesystem, but feel free to tinker with mount options if you think it will help - If you can put WAL on separate disk(s), all the better. - The general opinion seems to be RAID5 is slower than RAID10 unless you have a lot of disks - Battery-backed write-cache for your SCSI controller can be a big performance win - 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, > What I should add is that reliability is a premium for us, we do not want to > sacrifice integrity for speed, and that we are tuning for a high commit rate > of small, simple transactions... Make sure the WAL is on fast disks I'd suggest. At a guess that'll be your bottleneck. For more info, your best bet is to check the archives on the plpgsql-performance list, and then post there. People will probably want to know more about your database size/number of concurrent transactions/disk systems etc. HTH -- Richard Huxton Archonet Ltd ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: filesystem option tuning @ 2004-05-19 07:32 share-postgres@think42.com parent: Richard Huxton <dev@archonet.com> 0 siblings, 1 reply; 7+ messages in thread From: share-postgres@think42.com @ 2004-05-19 07:32 UTC (permalink / raw) To: Richard Huxton <dev@archonet.com>; pgsql-performance Hi! On Mon, May 17, 2004 at 06:04:54PM +0100, Richard Huxton wrote: > share-postgres@think42.com wrote: > > [...] > > In no official capacity whatsoever, welcome aboard. Thanks ;-) > > There is just one point where I found the documentation lacking any > > description and practical hints (as opposed to all other topics), namely > > that of how to tune a setup for maximum performance regarding the layout of > > partitions on hard-disks and their mount options. > > I'm not a Sun user, so I can't give any OS-specific notes, but in general: > - Don't bypass the filesystem, but feel free to tinker with mount > options if you think it will help Right, raw partitions are too low-level for me these days anyhow... I assume that all postgres partitions can be mounted with noatime? > - 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...? > - 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. > - 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. > > What I should add is that reliability is a premium for us, we do not want to > > sacrifice integrity for speed, and that we are tuning for a high commit rate > > of small, simple transactions... > > Make sure the WAL is on fast disks I'd suggest. At a guess that'll be > your bottleneck. > > For more info, your best bet is to check the archives on the > plpgsql-performance list, and then post there. People will probably want > to know more about your database size/number of concurrent > transactions/disk systems etc. 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... If any more information is required feel free to ask - I would really appreciate getting this disk layout sorted out. Thanks, Colin ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: filesystem option tuning @ 2004-05-29 09:31 Shridhar Daithankar <shridhar@frodo.hserus.net> parent: share-postgres@think42.com 0 siblings, 1 reply; 7+ messages in thread From: Shridhar Daithankar @ 2004-05-29 09:31 UTC (permalink / raw) To: share-postgres@think42.com; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance On Wednesday 19 May 2004 13:02, share-postgres@think42.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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: filesystem option tuning @ 2004-05-29 15:18 Tom Lane <tgl@sss.pgh.pa.us> parent: Shridhar Daithankar <shridhar@frodo.hserus.net> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2004-05-29 15:18 UTC (permalink / raw) To: shridhar@frodo.hserus.net; +Cc: share-postgres@think42.com, Richard Huxton <dev@archonet.com>; pgsql-performance Shridhar Daithankar <shridhar@frodo.hserus.net> writes: > On Wednesday 19 May 2004 13:02, share-postgres@think42.com wrote: > - If you can put WAL on separate disk(s), all the better. >> >> 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. The entire argument for giving xlog its own drive revolves around the fact that xlog is written sequentially, and so if it has its own spindle then you have near-zero seek requirements. As soon as you give that drive any other work to do, you start losing the low-seek property. Now as Shridhar says, clog is not a very high-I/O-volume thing, so in one sense it doesn't much matter which drive you put it on. But it seems to me that clog acts much more like ordinary table files than it acts like xlog. regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: filesystem option tuning @ 2004-06-04 14:21 CH <share-postgres@think42.com> 0 siblings, 1 reply; 7+ messages in thread From: CH @ 2004-06-04 14:21 UTC (permalink / raw) To: 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: filesystem option tuning @ 2004-06-04 15:33 Tom Lane <tgl@sss.pgh.pa.us> parent: CH <share-postgres@think42.com> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2004-06-04 15:33 UTC (permalink / raw) To: CH <share-postgres@think42.com>; +Cc: pgsql-performance CH <share-postgres@think42.com> writes: > So the clog is not written to every time the xlog is written to? No. One clog page holds 32000 transactions' worth of transaction status values, so on average we need only one clog page write per 32000 transactions. > On a related issue, what's the connection between the "fsync" and the > "wal_sync_method" configuration switches? fsync is the master circuit breaker: turn it off, there is no power at the wal_sync_method socket ;-). We stop doing anything special about enforcing write ordering for any files, but just assume that the kernel + hardware can be trusted not to lose data they've been given. With fsync on, wal_sync_method means something. fsync on also enables fsync/sync for the data files at checkpoint times. We don't need to force writes for those between checkpoints, so wal_sync_method doesn't apply to data files (nor clog). regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-06-04 15:33 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-05-15 10:52 filesystem option tuning share-postgres@think42.com 2004-05-17 17:04 ` Richard Huxton <dev@archonet.com> 2004-05-19 07:32 ` share-postgres@think42.com 2004-05-29 09:31 ` Shridhar Daithankar <shridhar@frodo.hserus.net> 2004-05-29 15:18 ` Tom Lane <tgl@sss.pgh.pa.us> 2004-06-04 14:21 Re: filesystem option tuning CH <share-postgres@think42.com> 2004-06-04 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox