Re: Create and drop temp table in 8.3.4 - Mailing list pgsql-performance
| From | Scott Carey |
|---|---|
| Subject | Re: Create and drop temp table in 8.3.4 |
| Date | |
| Msg-id | a1ec7d000811061305n7b28800od469c1411e1a9928@mail.gmail.com Whole thread Raw |
| In response to | Re: Create and drop temp table in 8.3.4 ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
| Responses |
Re: Create and drop temp table in 8.3.4
Re: Create and drop temp table in 8.3.4 |
| List | pgsql-performance |
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.
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.Grittner@wicourts.gov> wrote:
>>> "Joshua D. Drake" <jd@commandprompt.com> wrote:
> On Thu, 2008-11-06 at 13:02 -0600, Kevin Grittner wrote:>> the new kernelold:
>> 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?
kgrittn@DBUTL-PG:/var/pgsql/data/test> cat /proc/version
Linux version 2.6.5-7.287.3-bigsmp (geeko@buildhost) (gcc version 3.3.3
(SuSE Linux)) #1 SMP Tue Oct 2 07:31:36 UTC 2007
kgrittn@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@DBUTL-PG:/var/pgsql/data/test> cat /etc/SuSE-release
SUSE LINUX Enterprise Server 9 (i586)
VERSION = 9
PATCHLEVEL = 3
new:
kgrittn@SAWYER-PG:~> cat /proc/version
Linux version 2.6.16.60-0.27-smp (geeko@buildhost) (gcc version 4.1.2
20070115 (SUSE Linux)) #1 SMP Mon Jul 28 12:55:32 UTC 2008
kgrittn@SAWYER-PG:~> uname -a
Linux SAWYER-PG 2.6.16.60-0.27-smp #1 SMP Mon Jul 28 12:55:32 UTC 2008x86_64 x86_64 x86_64 GNU/Linuxkgrittn@SAWYER-PG:~> cat /etc/SuSE-releaseSUSE Linux Enterprise Server 10 (x86_64)To be clear, file create and unlink speeds are almost the same between
VERSION = 10
PATCHLEVEL = 2
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
pgsql-performance by date: