Re: Recomended FS - Mailing list pgsql-general

On Mon, 20 Oct 2003, Ben-Nes Michael wrote:

> ----- Original Message -----
> From: "Nick Burrett" <nick@dsvr.net>
> To: "Ben-Nes Michael" <miki@canaan.co.il>
> Cc: "postgresql" <pgsql-general@postgresql.org>
> Sent: Monday, October 20, 2003 2:54 PM
> Subject: Re: [GENERAL] Recomended FS
>
> > >>>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
> > >>modifications that you can make to your database structure, queries and
> > >>applications.  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.
> >
> I dont expect miracles :)
> but still i have to choose one,so why shouldnt i choose the one which best
> fit ?

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).