Re: Create and drop temp table in 8.3.4

To others that may stumble upon this thread:
Note that Write Barriers can be very important for data integrity when power
loss or hardware failure are a concern. Only disable them if you know the
consequences are mitigated by other factors (such as a BBU + db using the
WAL log with sync writes), or if you accept the additional risk to data
loss. Also note that LVM prevents the possibility of using write barriers,
and lowers data reliability as a result. The consequences are application
dependent and also highly file system dependent.

On Temp Tables:
I am a bit ignorant on the temp table relationship to file creation -- it
makes no sense to me at all that a file would even be created for a temp
table unless it spills out of RAM or is committed. Inside of a transaction,
shouldn't they be purely in-memory if there is space? Is there any way to
prevent the file creation? This seems like a total waste of time for many
temp table use cases, and explains why they were so slow in some exploratory
testing we did a few months ago.

On Thu, Nov 6, 2008 at 11:35 AM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> >>> "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
> > On Thu, 2008-11-06 at 13:02 -0600, Kevin Grittner wrote:
> >> the new kernel
> >> defaulted to using write barriers, while the old kernel didn't.
> Since
> >> we have a BBU RAID controller, we will add nobarrier to the fstab
> >> entries. This makes file creation and unlink each about 20 times
> >> faster.
> >
> > Woah... which version of the kernel was old and new?
>
> old:
>
> kgrittn(at)DBUTL-PG:/var/pgsql/data/test> cat /proc/version
> Linux version 2.6.5-7.287.3-bigsmp (geeko(at)buildhost) (gcc version 3.3.3
> (SuSE Linux)) #1 SMP Tue Oct 2 07:31:36 UTC 2007
> kgrittn(at)DBUTL-PG:/var/pgsql/data/test> uname -a
> Linux DBUTL-PG 2.6.5-7.287.3-bigsmp #1 SMP Tue Oct 2 07:31:36 UTC 2007
> i686 i686 i386 GNU/Linux
> kgrittn(at)DBUTL-PG:/var/pgsql/data/test> cat /etc/SuSE-release
> SUSE LINUX Enterprise Server 9 (i586)
> VERSION = 9
> PATCHLEVEL = 3
>
> new:
>
> kgrittn(at)SAWYER-PG:~> cat /proc/version
> Linux version 2.6.16.60-0.27-smp (geeko(at)buildhost) (gcc version 4.1.2
> 20070115 (SUSE Linux)) #1 SMP Mon Jul 28 12:55:32 UTC 2008
> kgrittn(at)SAWYER-PG:~> uname -a
> Linux SAWYER-PG 2.6.16.60-0.27-smp #1 SMP Mon Jul 28 12:55:32 UTC 2008
> x86_64 x86_64 x86_64 GNU/Linux
> kgrittn(at)SAWYER-PG:~> cat /etc/SuSE-release
> SUSE Linux Enterprise Server 10 (x86_64)
> VERSION = 10
> PATCHLEVEL = 2
>
> To be clear, file create and unlink speeds are almost the same between
> the two kernels without write barriers; the difference is that they
> were in effect by default in the newer kernel.
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>