Recomended FS

[Mark Kirkwood]

Re: Recomended FS

I have found this as well -

I have a nice simple example of a program that loops and occasionally
writes a block to a file.
On a 2 cpu machine, running 2 of these processes in parallel takes twice
as long as running just 1 process!
However if I comment out the IO, then 2 processes takes the same elapsed
time as 1.

My conclusion is there exists some sort of "big" lock on access to the
ATA array.

I believe that 3ware have a non blocking implementation of ATA RAID -
I intend to sell the Promise and obtain a 3ware in the next month of so
and test this out.

regards

Mark

Andrew Sullivan wrote:
[color=blue]
>That said, even the 2-way RAID became almost uselessly slow when
>multiple queries were running -- indeed, dramatically slower than a
>plain single IDE drive.
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Mark Kirkwood]

Re: Recomended FS


scott.marlowe wrote:
[color=blue]
>
>OK, but here's the real test. As the postgres user, run 'pgbench -i',
>then after that runs, run 'pgbench -c 50 -t 1000000'. While it's running
>and settled (pg aux|grep postgres|wc -l should show a number of ~54 or
>so.) pull the plug. Wait for the hard drives to spin down, then plug it
>back in and power it one. With SCSI you will still have a coherent
>database.
>
>[/color]
Agreed in principle - pgbench is the most interesting test... for this
mailing list anyway :-).
However s = 1 makes a tiny database that fits into the file buffer cache
on most machines, which is not a very realistic situation.

e.g. the Dell gets tps = 250 for s = 1 c = 5 t = 1000. This number
looks great but its not too much to do with IO....

I am happier about s = 10 - 50 for machines with 512+ Mb of RAM.

From memory the Dell gets tps = 36 for s = 10 c = 5 t = 100000. This
result seems more believable!

[color=blue]
>If you want a coherent database on IDE drives under postgresql you will
>need to issue this command: 'hdparm -W0 /dev/hdx' where x is the letter of
>the drives under the RAID array to turn off write caching. This will slow
>them to a crawl on writes.
>
>[/color]
I should have said that I was using Freebsd 4.8 with write caching off.
The question of whether the disk *actually* turned it off is the
significant issue, so yes, "use with care" should preface any comments
about IDE usage!

best wishes

Mark


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Joshua D. Drake]

Re: Recomended FS

[color=blue]
> I believe that 3ware have a non blocking implementation of ATA RAID -
> I intend to sell the Promise and obtain a 3ware in the next month of
> so and test this out.[/color]


I use 3Ware exclusively for my ATA-RAID solutions. The nice thing about
them is that
they are REAL hardware RAID and the use the SCSI layer within Linux so
you address
them as a standard SCSI device.

Also their support is in the kernel... no wierd, experimental patching.

On a Dual 2000 Athlon MP I was able to sustain 50MB/sec over large
copys (4+ gigs). Very, Very happy with them.

Sincerely,

Joshua Drake



[color=blue]
> regards
>
> Mark
>
> Andrew Sullivan wrote:
>[color=green]
>> That said, even the 2-way RAID became almost uselessly slow when
>> multiple queries were running -- indeed, dramatically slower than a
>> plain single IDE drive.
>>
>>[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)[/color]



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandpromp t.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Mark Kirkwood]

Re: Recomended FS (correction)



Mark Kirkwood wrote:
[color=blue]
> I should have said that I was using Freebsd 4.8 with write caching off.[/color]

write caching *on* - I got myself confused about what the value "1"
means....


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[scott.marlowe]

Re: Recomended FS

On Thu, 23 Oct 2003, Mark Kirkwood wrote:
[color=blue]
>
> scott.marlowe wrote:
>[color=green]
> >
> >OK, but here's the real test. As the postgres user, run 'pgbench -i',
> >then after that runs, run 'pgbench -c 50 -t 1000000'. While it's running
> >and settled (pg aux|grep postgres|wc -l should show a number of ~54 or
> >so.) pull the plug. Wait for the hard drives to spin down, then plug it
> >back in and power it one. With SCSI you will still have a coherent
> >database.
> >
> >[/color]
> Agreed in principle - pgbench is the most interesting test... for this
> mailing list anyway :-).
> However s = 1 makes a tiny database that fits into the file buffer cache
> on most machines, which is not a very realistic situation.
>
> e.g. the Dell gets tps = 250 for s = 1 c = 5 t = 1000. This number
> looks great but its not too much to do with IO....
>
> I am happier about s = 10 - 50 for machines with 512+ Mb of RAM.
>
> From memory the Dell gets tps = 36 for s = 10 c = 5 t = 100000. This
> result seems more believable![/color]

You missed my point there. I wasn't CARING what kind of numbers you get
back at all. My point was that if you place the database under fairly
high transactional load, and pull the plug, is the database still coherent
when it comes back up.

I generally test with -s10 through -s50, but for this test it makes no
difference I can see, i.e. if the thing is gonna get scrammed at -s50,
it'll get scrammed at -s1 as well, and take less time to test.
[color=blue][color=green]
> >If you want a coherent database on IDE drives under postgresql you will
> >need to issue this command: 'hdparm -W0 /dev/hdx' where x is the letter of
> >the drives under the RAID array to turn off write caching. This will slow
> >them to a crawl on writes.
> >
> >[/color]
> I should have said that I was using Freebsd 4.8 with write caching off.
> The question of whether the disk *actually* turned it off is the
> significant issue, so yes, "use with care" should preface any comments
> about IDE usage![/color]

-- NOTE in a correction Mark stated that caching was on, not off --

Assuming that the caching was on, I'm betting your database won't survive
a power plug pull in the middle of transactions like the test I put up
above.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[scott.marlowe]

Re: Recomended FS

On Wed, 22 Oct 2003, Joshua D. Drake wrote:
[color=blue]
>[color=green]
> > I believe that 3ware have a non blocking implementation of ATA RAID -
> > I intend to sell the Promise and obtain a 3ware in the next month of
> > so and test this out.[/color]
>
>
> I use 3Ware exclusively for my ATA-RAID solutions. The nice thing about
> them is that
> they are REAL hardware RAID and the use the SCSI layer within Linux so
> you address
> them as a standard SCSI device.
>
> Also their support is in the kernel... no wierd, experimental patching.
>
> On a Dual 2000 Athlon MP I was able to sustain 50MB/sec over large
> copys (4+ gigs). Very, Very happy with them.[/color]

Do they survive the power plug pulling test I was talking about elsewhere
in this thread?


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Mark Kirkwood]

Re: Recomended FS

Its worth checking - isn't it ?

I appeciate that you may have performed such tests previously - but as
hardware and software evolve its often worth repeating such tests (goes
away to do the suggested one tonight).

Note that I am not trying to argue away the issue about write caching -
it *has* to increase the risk of database corruption following a power
failure, however if your backups are regular and reliable this may be a
risk worth taking to achieve acceptable performance at a low price.

regards

Mark


scott.marlowe wrote:
[color=blue]
>
>Assuming that the caching was on, I'm betting your database won't survive
>a power plug pull in the middle of transactions like the test I put up
>above.
>
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bruce Momjian]

Re: Recomended FS

Mark Kirkwood wrote:[color=blue]
> Its worth checking - isn't it ?
>
> I appeciate that you may have performed such tests previously - but as
> hardware and software evolve its often worth repeating such tests (goes
> away to do the suggested one tonight).
>
> Note that I am not trying to argue away the issue about write caching -
> it *has* to increase the risk of database corruption following a power
> failure, however if your backups are regular and reliable this may be a
> risk worth taking to achieve acceptable performance at a low price.[/color]

Sure, but how many people are taking that risk and not knowing it!

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.ph a.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Mark Kirkwood]

Re: Recomended FS

I suspect almost everyone using IDE drives -

We the "consumers" of this technology need to demand that the vendors:

1. Be honest about these limitations / bugs
2. Work to fix obvious bugs - e.g. drives lying about write cache status
need to have their behaviour changed as soon as possible.

In the meantime I guess all we can do is try to understand the issue and
raise awareness

regards

Mark

Bruce Momjian wrote:
[color=blue]
>Mark Kirkwood wrote:
>
>[color=green]
>>Its worth checking - isn't it ?
>>
>>I appeciate that you may have performed such tests previously - but as
>>hardware and software evolve its often worth repeating such tests (goes
>>away to do the suggested one tonight).
>>
>>Note that I am not trying to argue away the issue about write caching -
>>it *has* to increase the risk of database corruption following a power
>>failure, however if your backups are regular and reliable this may be a
>>risk worth taking to achieve acceptable performance at a low price.
>>
>>[/color]
>
>Sure, but how many people are taking that risk and not knowing it!
>
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[scott.marlowe]

Re: Recomended FS

On Mon, 20 Oct 2003, Ben-Nes Michael wrote:
[color=blue]
> ----- Original Message -----
> From: "Nick Burrett" <nick@dsvr.ne t>
> To: "Ben-Nes Michael" <miki@canaan.co .il>
> Cc: "postgresql " <pgsql-general@postgre sql.org>
> Sent: Monday, October 20, 2003 2:54 PM
> Subject: Re: [GENERAL] Recomended FS
>[color=green][color=darkred]
> > >>>But still the greatest question is what FS to put on ?
> > >>>
> > >>>I heard Reiesref can handle small files very quickly.
> > >>
> > >>Switching from ext3 to reiserfs for our name servers reduced the time
> > >>taken to load 110,000 zones from 45 minutes to 5 minutes.
> > >>
> > >>However for a database, I don't think you can really factor this type of
> > >>stuff into the equation. The performance benefits you get from
> > >>different filesystem types are going to be small compared to the
> > >>modificatio ns that you can make to your database structure, queries and
> > >>application s. The actual algorithms used in processing the data will be
> > >>much slower than the time taken to fetch the data off disk.
> > >
> > >
> > > So you say the FS has no real speed impact on the SB ?
> > >
> > > In my pg data folder i have 2367 files, some big some small.[/color]
> >
> > I'm saying: don't expect your DB performance to come on leaps and bounds
> > just because you changed to a different filesystem format. If you've
> > got speed problems then it might help to look elsewhere first.
> >[/color]
> I dont expect miracles :)
> but still i have to choose one,so why shouldnt i choose the one which best
> fit ?[/color]

I agree. I also think that the top of that logic develoment tree you
should ask yourself the first question of

"Is it ok that if the machine should suffer sudden catastrophic shutdown
due to any reason that I would have a corrupted database and would be
willing to reinitdb/restore from scratch?"

While I agree that in many instances this is acceptable, in
many it is not. If you may need it one day, SCSI is so much faster than
IDE when you turn off IDE's write cache that you now have a machine 1/2
as fast when you're on the IDE machine.

I pitted two systems against each other.

Machine A: < Clone of our current production box
Dual PIII-750MHz
1.5 Gig PC133 memory
dual 18 gig 10Krpm USCSI 160 drives

Maching B: < New machines intended to replace production box
Dual PIV Xeons-2.4GHz
2 Gig 400MHz memory
dual 80 gig 7200 RPM UDMA 133 drives

With two configs (all fresh 'initdb --locale=C'):
and postgresql.conf : wal_sync_method = open_sync, buffers = 4000.

Config 1:
/db on one partition (on IDE this always had write cache on.)
/pg_xlog on another (write cache on or off (W0/W1))

Config 2:
everything on /db/ which is a RAID-1 (both with write cache on or off on
W0/W1 on IDE) Allowed the software RAID-1 to replicate on both machines
before starting the tests.

With two possible IDE settings:

W0: Write cache off
W1: Write cache on

Note that W1 does not guarantee data integrity if power is lost while a
transaction is in progress (i.e. it's like running with fsync=false all
the time)

I ran pgbench -i -s 5 then pgbench -c 5 -t 1000 several times to
settle the machine, then ran pgbench -c 5 -t 1000 three times and chose
the median result of those three.

MachineA Config1:
141 tps

MachineB Config1 W0:
60 tps

MachineB Config1 W1:
112 tps

MachineA Config2:
101 tps

MachineB Config2 W0:
44 tps

MachineB Config2 W1:
135 tps

Just some numbers someone might find useful. I'll try to test both setups
in the same box later on if I get a chance. But it would seem that RAID
is performing better. I've tested all these configurations with the "pull
the plug" test. The SCSI survives in both configurations, while the IDE
will only survive uncorrupted when Write cache is off (W0).


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Michael Teter]

Re: Recomended FS

Here are some recent benchmarks on different Linux filesystems. As with
any benchmarks, take what you will from the numbers.

Note the Summary section, and then the detailed benchmark numbers (if
you have a stomach for huge tables of pure numbers :)

http://fsbench.netnation.com/

scott.marlowe wrote:
[color=blue]
> On Mon, 20 Oct 2003, Ben-Nes Michael wrote:
>
>[color=green]
>>----- Original Message -----
>>From: "Nick Burrett" <nick@dsvr.ne t>
>>To: "Ben-Nes Michael" <miki@canaan.co .il>
>>Cc: "postgresql " <pgsql-general@postgre sql.org>
>>Sent: Monday, October 20, 2003 2:54 PM
>>Subject: Re: [GENERAL] Recomended FS
>>
>>[color=darkred]
>>>>>>But still the greatest question is what FS to put on ?
>>>>>>
>>>>>>I heard Reiesref can handle small files very quickly.
>>>>>
>>>>>Switchin g from ext3 to reiserfs for our name servers reduced the time
>>>>>taken to load 110,000 zones from 45 minutes to 5 minutes.
>>>>>
>>>>>However for a database, I don't think you can really factor this type of
>>>>>stuff into the equation. The performance benefits you get from
>>>>>differen t filesystem types are going to be small compared to the
>>>>>modificati ons that you can make to your database structure, queries and
>>>>>applicatio ns. The actual algorithms used in processing the data will be
>>>>>much slower than the time taken to fetch the data off disk.
>>>>
>>>>
>>>>So you say the FS has no real speed impact on the SB ?
>>>>
>>>>In my pg data folder i have 2367 files, some big some small.
>>>
>>>I'm saying: don't expect your DB performance to come on leaps and bounds
>>>just because you changed to a different filesystem format. If you've
>>>got speed problems then it might help to look elsewhere first.
>>>[/color]
>>
>>I dont expect miracles :)
>>but still i have to choose one,so why shouldnt i choose the one which best
>>fit ?[/color]
>
>
> I agree. I also think that the top of that logic develoment tree you
> should ask yourself the first question of
>
> "Is it ok that if the machine should suffer sudden catastrophic shutdown
> due to any reason that I would have a corrupted database and would be
> willing to reinitdb/restore from scratch?"
>
> While I agree that in many instances this is acceptable, in
> many it is not. If you may need it one day, SCSI is so much faster than
> IDE when you turn off IDE's write cache that you now have a machine 1/2
> as fast when you're on the IDE machine.
>
> I pitted two systems against each other.
>
> Machine A: < Clone of our current production box
> Dual PIII-750MHz
> 1.5 Gig PC133 memory
> dual 18 gig 10Krpm USCSI 160 drives
>
> Maching B: < New machines intended to replace production box
> Dual PIV Xeons-2.4GHz
> 2 Gig 400MHz memory
> dual 80 gig 7200 RPM UDMA 133 drives
>
> With two configs (all fresh 'initdb --locale=C'):
> and postgresql.conf : wal_sync_method = open_sync, buffers = 4000.
>
> Config 1:
> /db on one partition (on IDE this always had write cache on.)
> /pg_xlog on another (write cache on or off (W0/W1))
>
> Config 2:
> everything on /db/ which is a RAID-1 (both with write cache on or off on
> W0/W1 on IDE) Allowed the software RAID-1 to replicate on both machines
> before starting the tests.
>
> With two possible IDE settings:
>
> W0: Write cache off
> W1: Write cache on
>
> Note that W1 does not guarantee data integrity if power is lost while a
> transaction is in progress (i.e. it's like running with fsync=false all
> the time)
>
> I ran pgbench -i -s 5 then pgbench -c 5 -t 1000 several times to
> settle the machine, then ran pgbench -c 5 -t 1000 three times and chose
> the median result of those three.
>
> MachineA Config1:
> 141 tps
>
> MachineB Config1 W0:
> 60 tps
>
> MachineB Config1 W1:
> 112 tps
>
> MachineA Config2:
> 101 tps
>
> MachineB Config2 W0:
> 44 tps
>
> MachineB Config2 W1:
> 135 tps
>
> Just some numbers someone might find useful. I'll try to test both setups
> in the same box later on if I get a chance. But it would seem that RAID
> is performing better. I've tested all these configurations with the "pull
> the plug" test. The SCSI survives in both configurations, while the IDE
> will only survive uncorrupted when Write cache is off (W0).
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[scott.marlowe]

Re: Recomended FS

On Fri, 24 Oct 2003, Michael Teter wrote:
[color=blue]
> Here are some recent benchmarks on different Linux filesystems. As with
> any benchmarks, take what you will from the numbers.
>
> Note the Summary section, and then the detailed benchmark numbers (if
> you have a stomach for huge tables of pure numbers :)
>
> http://fsbench.netnation.com/[/color]

Right, but NONE of the benchmarks I've seen have been with IDE drives with
their cache disabled, which is the only way to make them reliable under
postgresql should something bad happen. but thanks for the benchmarks,
I'll look them over.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Scott Chapman]

Re: Recomended FS

On Friday 24 October 2003 16:23, scott.marlowe wrote:[color=blue]
> Right, but NONE of the benchmarks I've seen have been with IDE drives with
> their cache disabled, which is the only way to make them reliable under
> postgresql should something bad happen. but thanks for the benchmarks,
> I'll look them over.[/color]

I don't recall seeing anyone explain how to disable caching on a drive in this
thread. Did I miss that? 'Would be useful. I'm running a 3Ware mirror of 2
IDE drives.

Scott

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Mark Kirkwood]

Re: Recomended FS

Got to going this today, after a small delay due to the arrival of new
disks,

So the system is 2x700Mhz PIII, 512 Mb, Promise TX2000, 2x40G ATA-133
Maxtor Diamond+8 .
The relevent software is Freebsd 4.8 and Postgresql 7.4 Beta 2.

Two runs of 'pgbench -c 50 -t 1000000 -s 10 bench' with a power cord
removal after about 2 minutes were performed, one with hw.ata.wc = 1
(write cache enabled) and other with hw.ata.wc = 0 (disabled).

In *both* cases the Pg server survived - i.e it came up, performed
automatic recovery. Subsequent 'vacuum full' and further runs of pgbench
completed with no issues.

I would conclude that it not *always* the case that power failure
renders the database unuseable.

I have just noticed a similar posting from Scott were he finds the cache
enabled case has an dead database after power failure. It seems that
it's a question of how *likely* is it that the database will survive/not
survive a power failure...

The other interesting possibility is that Freebsd with soft updates
helped things remain salvageable in the cache enabled case (as some
writes *must* be lost at power off in this case)....

regards

Mark

scott.marlowe wrote:
[color=blue]
>
>OK, but here's the real test. As the postgres user, run 'pgbench -i',
>then after that runs, run 'pgbench -c 50 -t 1000000'. While it's running
>and settled (pg aux|grep postgres|wc -l should show a number of ~54 or
>so.) pull the plug. Wait for the hard drives to spin down, then plug it
>back in and power it one. With SCSI you will still have a coherent
>database.
>
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[James Moe]

Re: Recomended FS

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sun, 26 Oct 2003 16:24:17 +1300, Mark Kirkwood wrote:
[color=blue]
>I would conclude that it not *always* the case that power failure
>renders the database unuseable.
>
>I have just noticed a similar posting from Scott were he finds the cache
>enabled case has a dead database after power failure.
>[/color]
Other posts have noted that SCSI never fails under this condition. Apparently SCSI
drives sense an impending power loss and flush the cache before power completely
disappears. Speed *and* reliability. Hm.
Of course, anyone serious about a server would have it backed up with a UPS and
appropriate software to shut the system down during an extended power outage. This just
leaves people tripping over the power cords or maliciously pulling the plugs.


- --
jimoe at sohnen-moe dot com
pgp/gpg public key: http://www.keyserver.net/en/
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 5.0 OS/2 for non-commercial use
Comment: PGP 5.0 for OS/2
Charset: cp850

wj8DBQE/m2PQsxxMki0foKo RAjsOAJ0ed1MV8F cWcALoxIJk66wn4 0EEvwCfVTPB
n/rxejkV2upgeZmoy 3yipes=
=fDes
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html