[PERFORM] Restoration of datas

[dforums]

Hello

Regarding the advice from all, and the performance of postgresql 8.3.3

I'm trying to change the server and to upgrade to 8.3.3

I install postgresql 8.3.3 on a new server for testing. All well!!!

And I run a \i mybackup.sql since yesterday 7pm. This morning the datas 
are not insert yet.


COuld you advice me on which restoration method is the faster. To 
upgrade from postgresql 8.1.11 to 8.3.3.


Regards

David



--


*David Bigand
*Président Directeur Générale*
*51 chemin des moulins
73000 CHAMBERY - FRANCE

Web : htttp://www.1st-affiliation.fr
Email : [EMAIL PROTECTED]
Tel. : +33 479 696 685
Mob. : +33 666 583 836
Skype : firstaffiliation_support


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Restoration of datas

[Richard Huxton]

dforums wrote:
COuld you advice me on which restoration method is the faster. To 
upgrade from postgresql 8.1.11 to 8.3.3.


Using the pg_dump from your 8.3 package, dump the database using -Fc to 
get a nicely compressed dump. Then use pg_restore to restore it. If you 
add a --verbose flag then you will be able to track it.


You might want to set fsync=off while doing the restore. This is safe 
since if the machine crashes during restore you just start again. Oh, 
and increase work_mem too - there's only going to be one process.


What will take the most time is the creating of indexes etc.

It will take a long time to do a full restore though - you've got 64GB 
of data and slow disks.


--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Filesystem benchmarking for pg 8.3.3 server

[Henrik]

Hello list,

I have a server with a direct attached storage containing 4 15k SAS  
drives and 6 standard SATA drives.

The server is a quad core xeon with 16GB ram.
Both server and DAS has dual PERC/6E raid controllers with 512 MB BBU

There is 2 raid set configured.
One RAID 10 containing 4 SAS disks
One RAID 5 containing 6 SATA disks

There is one partition per RAID set with ext2 filesystem.

I ran the following iozone test which I stole from Joshua Drake's test  
at

http://www.commandprompt.com/blogs/joshua_drake/2008/04/is_that_performance_i_smell_ext2_vs_ext3_on_50_spindles_testing_for_postgresql/

I ran this test against the RAID 5 SATA partition

#iozone -e -i0 -i1 -i2 -i8 -t1 -s 1000m -r 8k -+u

With these random write results

Children see throughput for 1 random writers=  168647.33 KB/sec
Parent sees throughput for 1 random writers =  168413.61 KB/sec
Min throughput per process  =  168647.33 KB/sec
Max throughput per process  =  168647.33 KB/sec
Avg throughput per process  =  168647.33 KB/sec
Min xfer= 1024000.00 KB
	CPU utilization: Wall time6.072CPU time0.540CPU  
utilization   8.89 %


Almost 170 MB/sek. Not bad for 6 standard SATA drives.

Then I ran the same thing against the RAID 10 SAS partition

Children see throughput for 1 random writers=   68816.25 KB/sec
Parent sees throughput for 1 random writers =   68767.90 KB/sec
Min throughput per process  =   68816.25 KB/sec
Max throughput per process  =   68816.25 KB/sec
Avg throughput per process  =   68816.25 KB/sec
Min xfer= 1024000.00 KB
	CPU utilization: Wall time   14.880CPU time0.520CPU  
utilization   3.49 %


What only 70 MB/sek?

Is it possible that the 2 more spindles for the SATA drives makes that  
partition s much faster? Even though the disks and the RAID  
configuration should be slower?
It feels like there is something fishy going on. Maybe the RAID 10  
implementation on the PERC/6e is crap?


Any pointers, suggestion, ideas?

I'm going to change the RAID 10 to a RAID 5 and test again and see  
what happens.


Cheers,
Henke


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] query planner not using the correct index

[Joshua Shanks]

Just for closure I ended up doing

ALTER TABLE bars ALTER COLUMN bars_id SET STATISTICS 500;

On Thu, Aug 7, 2008 at 7:11 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Joshua Shanks" <[EMAIL PROTECTED]> writes:
>> How do I increase the stats target for just one column?
>
> Look under ALTER TABLE.
>
>regards, tom lane
>

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

[Luke Lonergan]

Your expected write speed on a 4 drive RAID10 is two drives worth, probably 160 
MB/s, depending on the generation of drives.

The expect write speed for a 6 drive RAID5 is 5 drives worth, or about 400 
MB/s, sans the RAID5 parity overhead.

- Luke

- Original Message -
From: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
To: pgsql-performance@postgresql.org 
Sent: Fri Aug 08 10:23:55 2008
Subject: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

Hello list,

I have a server with a direct attached storage containing 4 15k SAS  
drives and 6 standard SATA drives.
The server is a quad core xeon with 16GB ram.
Both server and DAS has dual PERC/6E raid controllers with 512 MB BBU

There is 2 raid set configured.
One RAID 10 containing 4 SAS disks
One RAID 5 containing 6 SATA disks

There is one partition per RAID set with ext2 filesystem.

I ran the following iozone test which I stole from Joshua Drake's test  
at
http://www.commandprompt.com/blogs/joshua_drake/2008/04/is_that_performance_i_smell_ext2_vs_ext3_on_50_spindles_testing_for_postgresql/

I ran this test against the RAID 5 SATA partition

#iozone -e -i0 -i1 -i2 -i8 -t1 -s 1000m -r 8k -+u

With these random write results

Children see throughput for 1 random writers=  168647.33 KB/sec
Parent sees throughput for 1 random writers =  168413.61 KB/sec
Min throughput per process  =  168647.33 KB/sec
Max throughput per process  =  168647.33 KB/sec
Avg throughput per process  =  168647.33 KB/sec
Min xfer= 1024000.00 KB
CPU utilization: Wall time6.072CPU time0.540CPU  
utilization   8.89 %

Almost 170 MB/sek. Not bad for 6 standard SATA drives.

Then I ran the same thing against the RAID 10 SAS partition

Children see throughput for 1 random writers=   68816.25 KB/sec
Parent sees throughput for 1 random writers =   68767.90 KB/sec
Min throughput per process  =   68816.25 KB/sec
Max throughput per process  =   68816.25 KB/sec
Avg throughput per process  =   68816.25 KB/sec
Min xfer= 1024000.00 KB
CPU utilization: Wall time   14.880CPU time0.520CPU  
utilization   3.49 %

What only 70 MB/sek?

Is it possible that the 2 more spindles for the SATA drives makes that  
partition s much faster? Even though the disks and the RAID  
configuration should be slower?
It feels like there is something fishy going on. Maybe the RAID 10  
implementation on the PERC/6e is crap?

Any pointers, suggestion, ideas?

I'm going to change the RAID 10 to a RAID 5 and test again and see  
what happens.

Cheers,
Henke


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

[Henrik]

But random writes should be faster on a RAID10 as it doesn't need to  
calculate parity. That is why people suggest RAID 10 for datases,  
correct?


I can understand that RAID5 can be faster with sequential writes.

//Henke

8 aug 2008 kl. 16.53 skrev Luke Lonergan:

Your expected write speed on a 4 drive RAID10 is two drives worth,  
probably 160 MB/s, depending on the generation of drives.


The expect write speed for a 6 drive RAID5 is 5 drives worth, or  
about 400 MB/s, sans the RAID5 parity overhead.


- Luke

- Original Message -
From: [EMAIL PROTECTED] <[EMAIL PROTECTED] 
>
To: pgsql-performance@postgresql.org [EMAIL PROTECTED]>

Sent: Fri Aug 08 10:23:55 2008
Subject: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

Hello list,

I have a server with a direct attached storage containing 4 15k SAS
drives and 6 standard SATA drives.
The server is a quad core xeon with 16GB ram.
Both server and DAS has dual PERC/6E raid controllers with 512 MB BBU

There is 2 raid set configured.
One RAID 10 containing 4 SAS disks
One RAID 5 containing 6 SATA disks

There is one partition per RAID set with ext2 filesystem.

I ran the following iozone test which I stole from Joshua Drake's test
at
http://www.commandprompt.com/blogs/joshua_drake/2008/04/is_that_performance_i_smell_ext2_vs_ext3_on_50_spindles_testing_for_postgresql/

I ran this test against the RAID 5 SATA partition

#iozone -e -i0 -i1 -i2 -i8 -t1 -s 1000m -r 8k -+u

With these random write results

Children see throughput for 1 random writers=  168647.33  
KB/sec
Parent sees throughput for 1 random writers =  168413.61  
KB/sec
Min throughput per process  =  168647.33  
KB/sec
Max throughput per process  =  168647.33  
KB/sec
Avg throughput per process  =  168647.33  
KB/sec
Min xfer= 1024000.00  
KB
CPU utilization: Wall time6.072CPU time0.540 
CPU

utilization   8.89 %

Almost 170 MB/sek. Not bad for 6 standard SATA drives.

Then I ran the same thing against the RAID 10 SAS partition

Children see throughput for 1 random writers=   68816.25  
KB/sec
Parent sees throughput for 1 random writers =   68767.90  
KB/sec
Min throughput per process  =   68816.25  
KB/sec
Max throughput per process  =   68816.25  
KB/sec
Avg throughput per process  =   68816.25  
KB/sec
Min xfer= 1024000.00  
KB
CPU utilization: Wall time   14.880CPU time0.520 
CPU

utilization   3.49 %

What only 70 MB/sek?

Is it possible that the 2 more spindles for the SATA drives makes that
partition s much faster? Even though the disks and the RAID
configuration should be slower?
It feels like there is something fishy going on. Maybe the RAID 10
implementation on the PERC/6e is crap?

Any pointers, suggestion, ideas?

I'm going to change the RAID 10 to a RAID 5 and test again and see
what happens.

Cheers,
Henke


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org 
)

To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] file system and raid performance

[Mark Wong]

On Thu, Aug 7, 2008 at 1:24 PM, Gregory S. Youngblood <[EMAIL PROTECTED]> wrote:
>> -Original Message-
>> From: Mark Wong [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, August 07, 2008 12:37 PM
>> To: Mario Weilguni
>> Cc: Mark Kirkwood; [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-
>> [EMAIL PROTECTED]; Gabrielle Roth
>> Subject: Re: [PERFORM] file system and raid performance
>>
>
>> I have heard of one or two situations where the combination of the
>> disk controller caused bizarre behaviors with different journaling
>> file systems.  They seem so few and far between though.  I personally
>> wasn't looking forwarding to chasing Linux file system problems, but I
>> can set up an account and remote management access if anyone else
>> would like to volunteer.
>
> [Greg says]
> Tempting... if no one else takes you up on it by then, I might have some
> time in a week or two to experiment and test a couple of things.

Ok, let me know and I'll set you up with access.

Regards,
Mark

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] file system and raid performance

[Mark Wong]

On Thu, Aug 7, 2008 at 3:08 PM, Mark Mielke <[EMAIL PROTECTED]> wrote:
> Andrej Ricnik-Bay wrote:
>
> 2008/8/8 Scott Marlowe <[EMAIL PROTECTED]>:
>
>
> noatime turns off the atime write behaviour.  Or did you already know
> that and I missed some weird post where noatime somehow managed to
> slow down performance?
>
>
> Scott, I'm quite aware of what noatime does ... you didn't miss a post, but
> if you look at Mark's graphs on
> http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide
> they pretty much all indicate that (unless I completely misinterpret the
> meaning and purpose of the labels), independent of the file-system,
> using noatime slows read/writes down (on average)
>
> That doesn't make sense - if noatime slows things down, then the analysis is
> probably wrong.
>
> Now, modern Linux distributions default to "relatime" - which will only
> update access time if the access time is currently less than the update time
> or something like this. The effect is that modern Linux distributions do not
> benefit from "noatime" as much as they have in the past. In this case,
> "noatime" vs default would probably be measuring % noise.

Anyone know what to look for in kernel profiles?  There is readprofile
(profile.text) and oprofile (oprofile.kernel and oprofile.user) data
available.  Just click on the results number, then the "raw data" link
for a directory listing of files.  For example, here is one of the
links:

http://osdldbt.sourceforge.net/dl380/3disk/sraid5/ext3-journal/seq-read/fio/profiling/oprofile.kernel

Regards,
Mark

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

[Mark Wong]

On Fri, Aug 8, 2008 at 8:08 AM, Henrik <[EMAIL PROTECTED]> wrote:
> But random writes should be faster on a RAID10 as it doesn't need to
> calculate parity. That is why people suggest RAID 10 for datases, correct?
> I can understand that RAID5 can be faster with sequential writes.

There is some data here that does not support that RAID5 can be faster
than RAID10 for sequential writes:

http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide

Regards,
Mark

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] file system and raid performance

[Mark Wong]

On Thu, Aug 7, 2008 at 3:08 PM, Mark Mielke <[EMAIL PROTECTED]> wrote:
> Andrej Ricnik-Bay wrote:
>
> 2008/8/8 Scott Marlowe <[EMAIL PROTECTED]>:
>
>
> noatime turns off the atime write behaviour.  Or did you already know
> that and I missed some weird post where noatime somehow managed to
> slow down performance?
>
>
> Scott, I'm quite aware of what noatime does ... you didn't miss a post, but
> if you look at Mark's graphs on
> http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide
> they pretty much all indicate that (unless I completely misinterpret the
> meaning and purpose of the labels), independent of the file-system,
> using noatime slows read/writes down (on average)
>
> That doesn't make sense - if noatime slows things down, then the analysis is
> probably wrong.
>
> Now, modern Linux distributions default to "relatime" - which will only
> update access time if the access time is currently less than the update time
> or something like this. The effect is that modern Linux distributions do not
> benefit from "noatime" as much as they have in the past. In this case,
> "noatime" vs default would probably be measuring % noise.

Interesting, now how would we see if it is defaulting to "relatime"?

Regards,
Mark

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

[Henrik]

8 aug 2008 kl. 18.44 skrev Mark Wong:


On Fri, Aug 8, 2008 at 8:08 AM, Henrik <[EMAIL PROTECTED]> wrote:

But random writes should be faster on a RAID10 as it doesn't need to
calculate parity. That is why people suggest RAID 10 for datases,  
correct?

I can understand that RAID5 can be faster with sequential writes.


There is some data here that does not support that RAID5 can be faster
than RAID10 for sequential writes:

http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide

I'm amazed by the big difference on hardware vs software raid.

I set up e new Dell(!) system against a MD1000 DAS with singel quad  
core 2,33 Ghz, 16GB RAM and Perc/6E raid controllers with 512MB BBU.


I set up a RAID 10 on 4 15K SAS disks.

I ran IOZone against this partition with ext2 filesystem and got the  
following results.


[EMAIL PROTECTED]:/$ iozone -e -i0 -i1 -i2 -i8 -t1 -s 1000m -r 8k - 
+u -F /database/iotest

Iozone: Performance Test of File I/O
Version $Revision: 3.279 $
Compiled for 64 bit mode.
Build: linux

Children see throughput for  1 initial writers  =  254561.23 KB/sec
Parent sees throughput for  1 initial writers   =  253935.07 KB/sec
Min throughput per process  =  254561.23 KB/sec
Max throughput per process  =  254561.23 KB/sec
Avg throughput per process  =  254561.23 KB/sec
Min xfer= 1024000.00 KB
	CPU Utilization: Wall time4.023CPU time0.740CPU  
utilization  18.40 %



Children see throughput for  1 rewriters=  259640.61 KB/sec
Parent sees throughput for  1 rewriters =  259351.20 KB/sec
Min throughput per process  =  259640.61 KB/sec
Max throughput per process  =  259640.61 KB/sec
Avg throughput per process  =  259640.61 KB/sec
Min xfer= 1024000.00 KB
	CPU utilization: Wall time3.944CPU time0.460CPU  
utilization  11.66 %



Children see throughput for  1 readers  = 2931030.50 KB/sec
Parent sees throughput for  1 readers   = 2877172.20 KB/sec
Min throughput per process  = 2931030.50 KB/sec
Max throughput per process  = 2931030.50 KB/sec
Avg throughput per process  = 2931030.50 KB/sec
Min xfer= 1024000.00 KB
	CPU utilization: Wall time0.349CPU time0.340CPU  
utilization  97.32 %



Children see throughput for 1 random readers= 2534182.50 KB/sec
Parent sees throughput for 1 random readers = 2465408.13 KB/sec
Min throughput per process  = 2534182.50 KB/sec
Max throughput per process  = 2534182.50 KB/sec
Avg throughput per process  = 2534182.50 KB/sec
Min xfer= 1024000.00 KB
	CPU utilization: Wall time0.404CPU time0.400CPU  
utilization  98.99 %


Children see throughput for 1 random writers=   68816.25 KB/sec
Parent sees throughput for 1 random writers =   68767.90 KB/sec
Min throughput per process  =   68816.25 KB/sec
Max throughput per process  =   68816.25 KB/sec
Avg throughput per process  =   68816.25 KB/sec
Min xfer= 1024000.00 KB
	CPU utilization: Wall time   14.880CPU time0.520CPU  
utilization   3.49 %



So compared to the HP 8000 benchmarks this setup is even better than  
the software raid.


But I'm skeptical of iozones results as when I run the same test  
agains 6 standard SATA drives in RAID5 I got random writes of 170MB /  
sek (!). Sure 2 more spindles but still.


Cheers,
Henke


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

[Andrej Ricnik-Bay]

On 09/08/2008, Henrik <[EMAIL PROTECTED]> wrote:
> But random writes should be faster on a RAID10 as it doesn't need to
> calculate parity. That is why people suggest RAID 10 for datases, correct?

If it had 10 spindles as opposed to 4 ... with 4 drives the "split" is (because
you're striping and mirroring) like writing to two.


Cheers,
Andrej

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] file system and raid performance

[Mark Wong]

On Thu, Aug 7, 2008 at 3:08 PM, Mark Mielke <[EMAIL PROTECTED]> wrote:
> Andrej Ricnik-Bay wrote:
>
> 2008/8/8 Scott Marlowe <[EMAIL PROTECTED]>:
>
>
> noatime turns off the atime write behaviour.  Or did you already know
> that and I missed some weird post where noatime somehow managed to
> slow down performance?
>
>
> Scott, I'm quite aware of what noatime does ... you didn't miss a post, but
> if you look at Mark's graphs on
> http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide
> they pretty much all indicate that (unless I completely misinterpret the
> meaning and purpose of the labels), independent of the file-system,
> using noatime slows read/writes down (on average)
>
> That doesn't make sense - if noatime slows things down, then the analysis is
> probably wrong.
>
> Now, modern Linux distributions default to "relatime" - which will only
> update access time if the access time is currently less than the update time
> or something like this. The effect is that modern Linux distributions do not
> benefit from "noatime" as much as they have in the past. In this case,
> "noatime" vs default would probably be measuring % noise.

It appears that the default mount option on this system is "atime".
Not specifying any options, "relatime" or "noatime", results in
neither being shown in /proc/mounts.  I'm assuming if the default
behavior was to use "relatime" that it would be shown in /proc/mounts.

Regards,
Mark

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] file system and raid performance

[Greg Smith]

On Thu, 7 Aug 2008, Mark Mielke wrote:


Now, modern Linux distributions default to "relatime"


Right, but Mark's HP test system is running Gentoo.

(ducks)

According to http://brainstorm.ubuntu.com/idea/2369/ relatime is the 
default for Fedora 8, Mandriva 2008, Pardus, and Ubuntu 8.04.


Anyway, there aren't many actual files involved in this test, and I 
suspect the atime writes are just being cached until forced out to disk 
only periodically.  You need to run something that accesses more files 
and/or regularly forces sync to disk periodically to get a more 
database-like situation where the atime writes degrade performance.  Note 
how Joshua Drake's ext2 vs. ext3 comparison, which does show a large 
difference here, was run with the iozone's -e parameter that flushes the 
writes with fsync.  I don't see anything like that in the DL380 G5 fio 
tests.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filesystem setup on new system

[Greg Smith]

On Thu, 7 Aug 2008, Henrik wrote:

My first idea was to have one partition on the RAID 10 using ext3 with 
data=writeback, noatime as mount options.


But I wonder if I should have 2 partitions on the RAID 10 one for the PGDATA 
dir using ext3 and one partition for XLOGS using ext2.


Really depends on your write volume.  The write cache on your controller 
will keep having a separate xlog disk from being as important as it is 
without one.  If your write volume is really high though, it may still be 
a bottleneck, and you may discover your app runs better with a dedicated 
ext2 xlog disk instead.


The simple version is:

WAL write volume extremely high->dedicated xlog can be better

WAL volume low->more disks for the database array better even if that 
mixes the WAL on there as well


If you want a true answer for which is better, you have to measure your 
application running on this hardware.



6 SAS 15K drives in RAID 10 on one of the SAN controllers for database


With only 6 disks available, in general you won't be able to reach the WAL 
as a bottleneck before being limited by seeks on the remaining 4 database 
disks, so you might as well group all 6 together.  It's possible your 
particular application might prefer it the other way though, if you're 
doing a while lot of small writes for example.  I've seen a separate WAL 
handle low-level benchmarks better, but on more real-world loads it's 
harder to run into that situation.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

[Greg Smith]

On Fri, 8 Aug 2008, Henrik wrote:

It feels like there is something fishy going on. Maybe the RAID 10 
implementation on the PERC/6e is crap?


Normally, when a SATA implementation is running significantly faster than 
a SAS one, it's because there's some write cache in the SATA disks turned 
on (which they usually are unless you go out of your way to disable them). 
Since all non-battery backed caches need to get turned off for reliable 
database use, you might want to double-check that on the controller that's 
driving the SATA disks.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

[david]

On Fri, 8 Aug 2008, Henrik wrote:

But random writes should be faster on a RAID10 as it doesn't need to 
calculate parity. That is why people suggest RAID 10 for datases, correct?


I can understand that RAID5 can be faster with sequential writes.


the key word here is "can" be faster, it depends on the exact 
implementation, stripe size, OS caching, etc.


the ideal situation would be that the OS would flush exactly one stripe of 
data at a time (aligned with the array) and no reads would need to be 
done, mearly calculate the parity info for the new data and write it all.


the worst case is when the write size is small in relation to the stripe 
size and crosses the stripe boundry. In that case the system needs to read 
data from multiple stripes to calculate the new parity and write the 
data and parity data.


I don't know any systems (software or hardware) that meet the ideal 
situation today.


when comparing software and hardware raid, one other thing to remember is 
that CPU and I/O bandwidth that's used for software raid is not available 
to do other things.


so a system that benchmarks much faster with software raid could end up 
being significantly slower in practice if it needs that CPU and I/O 
bandwidth for other purposes.


examples could be needing the CPU/memory capacity to search through 
amounts of RAM once the data is retrieved from disk, or finding that you 
have enough network I/O that it combines with your disk I/O to saturate 
your system busses.


David Lang



//Henke

8 aug 2008 kl. 16.53 skrev Luke Lonergan:

Your expected write speed on a 4 drive RAID10 is two drives worth, probably 
160 MB/s, depending on the generation of drives.


The expect write speed for a 6 drive RAID5 is 5 drives worth, or about 400 
MB/s, sans the RAID5 parity overhead.


- Luke

- Original Message -
From: [EMAIL PROTECTED] 
<[EMAIL PROTECTED]>

To: pgsql-performance@postgresql.org 
Sent: Fri Aug 08 10:23:55 2008
Subject: [PERFORM] Filesystem benchmarking for pg 8.3.3 server

Hello list,

I have a server with a direct attached storage containing 4 15k SAS
drives and 6 standard SATA drives.
The server is a quad core xeon with 16GB ram.
Both server and DAS has dual PERC/6E raid controllers with 512 MB BBU

There is 2 raid set configured.
One RAID 10 containing 4 SAS disks
One RAID 5 containing 6 SATA disks

There is one partition per RAID set with ext2 filesystem.

I ran the following iozone test which I stole from Joshua Drake's test
at
http://www.commandprompt.com/blogs/joshua_drake/2008/04/is_that_performance_i_smell_ext2_vs_ext3_on_50_spindles_testing_for_postgresql/

I ran this test against the RAID 5 SATA partition

#iozone -e -i0 -i1 -i2 -i8 -t1 -s 1000m -r 8k -+u

With these random write results

   Children see throughput for 1 random writers=  168647.33 KB/sec
   Parent sees throughput for 1 random writers =  168413.61 KB/sec
   Min throughput per process  =  168647.33 KB/sec
   Max throughput per process  =  168647.33 KB/sec
   Avg throughput per process  =  168647.33 KB/sec
   Min xfer= 1024000.00 KB
   CPU utilization: Wall time6.072CPU time0.540CPU
utilization   8.89 %

Almost 170 MB/sek. Not bad for 6 standard SATA drives.

Then I ran the same thing against the RAID 10 SAS partition

   Children see throughput for 1 random writers=   68816.25 KB/sec
   Parent sees throughput for 1 random writers =   68767.90 KB/sec
   Min throughput per process  =   68816.25 KB/sec
   Max throughput per process  =   68816.25 KB/sec
   Avg throughput per process  =   68816.25 KB/sec
   Min xfer= 1024000.00 KB
   CPU utilization: Wall time   14.880CPU time0.520CPU
utilization   3.49 %

What only 70 MB/sek?

Is it possible that the 2 more spindles for the SATA drives makes that
partition s much faster? Even though the disks and the RAID
configuration should be slower?
It feels like there is something fishy going on. Maybe the RAID 10
implementation on the PERC/6e is crap?

Any pointers, suggestion, ideas?

I'm going to change the RAID 10 to a RAID 5 and test again and see
what happens.

Cheers,
Henke


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance





--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] index scan cost

[Jeff Frost]

Tom Lane wrote:

Jeff Frost <[EMAIL PROTECTED]> writes:
  
I have two postgresql servers.  One runs 8.3.1, the other 8.3.3.  On the 8.3.1 
machine, the index scans are being planned extremely low cost:



  
  Index Scan using ix_email_entity_thread on email_entity  (cost=0.00..4.59 
rows=1 width=1031) (actual time=0.095..0.120 rows=4 loops=1)

Index Cond: (email_thread = 375629157)



  
  Index Scan using ix_email_entity_thread on email_entity  (cost=0.00..2218.61 
rows=1151 width=931) (actual time=0.094..0.111 rows=4 loops=1)

Index Cond: (email_thread = 375629157)



This isn't a "cost" problem, this is a "stats" problem.  Why does the
second server think 1151 rows will be returned?  Try comparing the
pg_stats entries for the email_thread column on both servers ... seems
like they must be significantly different.
  
Sorry it took me a while to close the loop on this.  So, the server that 
had the less desirable plan had actually been analyzed more recently by 
autovacuum.  When I went back to compare the stats on the faster server, 
autovacuum had analyzed it and the plan was now more similar.  Adjusting 
the stats target up for that column helped on both servers though it 
never did get back as close as before.


--
Jeff Frost, Owner   <[EMAIL PROTECTED]>
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032