Thread: PostgreSQL on RAM Disk / tmpfs
I'm working on a postgres instance (8.1.2 running on Solaris 10) where the data directory (including WAL) is being mounted on tmpfs. Based on this, and with knowledge that fsync is disabled, I'm operating under the assumption that recoverability is not a precondition for optimized performance. With that assumption, I have several questions, some performance-related, others internals-related:
1. Should there be any perceptible difference between using a RAM disk and tmpfs? Would the fact that the RAM disk were fixed-size make it at all faster?
2. Would there be any benefit to having WAL on a separate RAM disk? I.e., would RAM access be contentious in anything resembling the way disk access is contentious? One possible setup would be a fixed-size RAM disk for data and WAL on tmpfs, for instance.
3. In this scenario, is the strategy of keeping checkpoints far apart still a valid performance consideration? I would expect that there would be an optimal spot on the curve somewhere on the axes of checkpoint distance and available memory. During testing on the RAM disk, one problem encountered was that increasing checkpoint_segments caused the RAM disk to fill up, which suggests one potential benefit of pushing WAL off to tmpfs but possibly leaving data on the RAM disk. There would be no reason to push for aggressive checkpointing, would there?
4. Considering that recoverability is not a precondition, is there an easy patch that could be applied to the 8.1.x series from 8.1.4 on that would allow disabling full_page_writes? For a database in RAM with high write volume, is this setting even likely to make a difference?
5. Does wal_debug provide insight into whether LogInsert is being forced to write WAL buffers? This would seem to be a useful indicator as to whether wal_buffers was set high enough. (A side note: I couldn't find reference to LogInsert or LogFlush in the source; would it make sense to have the documentation refer to the actual functions?) Unfortunately, I don't have access to a system that can be easily recompiled for me to test this. A corollary question: if data and pg_xlog are both in RAM, does LogInsert still spill to the kernel cache, or would it spill to RAM?
--
Thomas F. O'Connell
Sitening, LLC
3004B Poston Avenue
Nashville, TN 37203-1314
615-469-5150 x802
615-469-5151 (fax)On 8/2/06, Thomas F. O'Connell <tfo@sitening.com> wrote: > I'm working on a postgres instance (8.1.2 running on Solaris 10) where the > data directory (including WAL) is being mounted on tmpfs. Based on this, and > with knowledge that fsync is disabled, I'm operating under the assumption > that recoverability is not a precondition for optimized performance. With > that assumption, I have several questions, some performance-related, others > internals-related: to be honest, I think the best approach is to simply write to the traditional filesystem and leave fsync off. writing to a ramdisk might be a bit faster, but you deprive the server memory from doing other things like caching and sorting. this might be more true for some o/s than others though. i'm just curious, what led you to do ramdisk implementation (most people who ask questions about ramdisk have no idea what they are talking about, although you seem to). > 4. Considering that recoverability is not a precondition, is there an easy > patch that could be applied to the 8.1.x series from 8.1.4 on that would > allow disabling full_page_writes? For a database in RAM with high write > volume, is this setting even likely to make a difference? I would suggest pulling 8.2dev (shortly beta) if you want this and experiment. it is perfectly stable. looking at the todo list, 8.2 also gets the multiple insert syntax, which is nice. if have super high write volumes, consider writing your insert call in C. prepare your statement, and use the parameterized version....ExecPrepared(...). merlin
On Aug 3, 2006, at 1:26 PM, Merlin Moncure wrote: > On 8/2/06, Thomas F. O'Connell <tfo@sitening.com> wrote: >> I'm working on a postgres instance (8.1.2 running on Solaris 10) >> where the >> data directory (including WAL) is being mounted on tmpfs. Based on >> this, and >> with knowledge that fsync is disabled, I'm operating under the >> assumption >> that recoverability is not a precondition for optimized >> performance. With >> that assumption, I have several questions, some performance- >> related, others >> internals-related: > > to be honest, I think the best approach is to simply write to the > traditional filesystem and leave fsync off. writing to a ramdisk > might be a bit faster, but you deprive the server memory from doing > other things like caching and sorting. this might be more true for > some o/s than others though. i'm just curious, what led you to do > ramdisk implementation (most people who ask questions about ramdisk > have no idea what they are talking about, although you seem to). That was how I found it. :) I think, though, that it was the result of benchmarking a variety of on-disk RAID configurations with an eye toward ever increasing write throughput. >> 4. Considering that recoverability is not a precondition, is there >> an easy >> patch that could be applied to the 8.1.x series from 8.1.4 on that >> would >> allow disabling full_page_writes? For a database in RAM with high >> write >> volume, is this setting even likely to make a difference? > > I would suggest pulling 8.2dev (shortly beta) if you want this and > experiment. it is perfectly stable. looking at the todo list, 8.2 > also gets the multiple insert syntax, which is nice. > > if have super high write volumes, consider writing your insert call in > C. prepare your statement, and use the parameterized > version....ExecPrepared(...). Can you point to a good example of this anywhere in the docs? I don't see ExecPrepared anywhere in the core documentation. -- Thomas F. O'Connell Sitening, LLC http://www.sitening.com/ 3004B Poston Avenue Nashville, TN 37203-1314 615-469-5150 x802 615-469-5151 (fax)
On 8/8/06, Thomas F. O'Connell <tfo@sitening.com> wrote: > On Aug 3, 2006, at 1:26 PM, Merlin Moncure wrote: > > if have super high write volumes, consider writing your insert call in > > C. prepare your statement, and use the parameterized > > version....ExecPrepared(...). > > Can you point to a good example of this anywhere in the docs? I don't > see ExecPrepared anywhere in the core documentation. well, it's actually PQexecPrepared() http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html do some tests and you should see a nice improvement over PQexec(). regards, merlin
On Aug 8, 2006, at 1:10 PM, Merlin Moncure wrote: > On 8/8/06, Thomas F. O'Connell <tfo@sitening.com> wrote: >> On Aug 3, 2006, at 1:26 PM, Merlin Moncure wrote: >> > if have super high write volumes, consider writing your insert >> call in >> > C. prepare your statement, and use the parameterized >> > version....ExecPrepared(...). >> >> Can you point to a good example of this anywhere in the docs? I don't >> see ExecPrepared anywhere in the core documentation. > > well, it's actually PQexecPrepared() > http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html > > do some tests and you should see a nice improvement over PQexec(). Thanks! I remain curious, though: in the event that a RAM-disk-based architecture remains in place, do all traditional disk-based considerations go out the window? For instance, does trying to cluster same-table statements together in a transaction in an effort to reduce disk activity make any difference? And is the overall strategy of attempting to keep distance between checkpoints somewhat high (especially since the need for checkpointing overall is reduced) still a good basis? -- Thomas F. O'Connell Sitening, LLC http://www.sitening.com/ 3004B Poston Avenue Nashville, TN 37203-1314 615-469-5150 x802 615-469-5151 (fax)
On 8/8/06, Thomas F. O'Connell <tfo@sitening.com> wrote: > > On Aug 8, 2006, at 1:10 PM, Merlin Moncure wrote: > > > On 8/8/06, Thomas F. O'Connell <tfo@sitening.com> wrote: > >> On Aug 3, 2006, at 1:26 PM, Merlin Moncure wrote: > >> > if have super high write volumes, consider writing your insert > >> call in > >> > C. prepare your statement, and use the parameterized > >> > version....ExecPrepared(...). > >> > >> Can you point to a good example of this anywhere in the docs? I don't > >> see ExecPrepared anywhere in the core documentation. > > > > well, it's actually PQexecPrepared() > > http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html > > > > do some tests and you should see a nice improvement over PQexec(). > > Thanks! > > I remain curious, though: in the event that a RAM-disk-based > architecture remains in place, do all traditional disk-based > considerations go out the window? For instance, does trying to if you are not syncing, the computer essentially is a giant ram disk. the operating system just pages ram to disk here and there to free up more ram for temporary demands. while kernel buffers are slower than local process memory, they are still awfully fast compared to disk drives. operating systems thes days are pretty good about deciding what should and should not stay in ram, better than most people believe. it's *usually* a rookie sysadmin move to force things into memory this way. another common sysadmin misadventure is to turn off the o/s swap file. > cluster same-table statements together in a transaction in an effort > to reduce disk activity make any difference? there is still a small savings due to optimizng transaction cleanup although you have to handle failures which can be a downside. > And is the overall strategy of attempting to keep distance between > checkpoints somewhat high (especially since the need for > checkpointing overall is reduced) still a good basis? depends. with fsync=off, usually the only time you are worried about checkpoints is during bullk load, and i think in these cases you will want to increase wal segments and leave the bgwriter alone. merlin