Re: file system and raid performance - Mailing list pgsql-performance

From Jean-David Beyer
Subject Re: file system and raid performance
Date
Msg-id 493D0FDA.5030403@verizon.net
Whole thread Raw
In response to Re: file system and raid performance  ("M. Edward (Ed) Borasky" <znmeb@cesmail.net>)
List pgsql-performance
M. Edward (Ed) Borasky wrote:

> Ah, but shouldn't a PostgreSQL (or any other database, for that matter)
> have its own set of filesystems tuned to the application's I/O patterns?
> Sure, there are some people who need to have all of their eggs in one
> basket because they can't afford multiple baskets. For them, maybe the
> OS defaults are the right choice. But if you're building a
> database-specific server, you can optimize the I/O for that.
>
I used to run IBM's DB2 database management system. It can use a normal
Linux file system (e.g., ext2 or ext3), but it prefers to run a partition
(or more, preferably more than one) itself in raw mode. This eliminates
core-to-core copies of in put and output, organizing the disk space as it
prefers, allows multiple writer processes (typically one per disk drive),
and multiple reader processes (also, typically one per drive), and
potentially increasing the concurrency of reading, writing, and processing.

My dbms needs are extremely modest (only one database, usually only one
user, all on one machine), so I saw only a little benefit to using DB2, and
there were administrative problems. I use Red Hat Enterprise Linux, and the
latest version of that (RHEL 5) does not offer raw file systems anymore, but
provides the same thing by other means. Trouble is, I would have to buy the
latest version of DB2 to be compatible with my version of Linux. Instead, I
just converted everything to postgreSQL instead, it it works very well.

When I started with this in 1986, I first tried Microsoft Access, but could
not get it to accept the database description I was using. So I switched to
Linux (for many reasons -- that was just one of them) and tried postgreSQL.
At the time, it was useless. One version would not do views (it accepted the
construct, IIRC, but they did not work), and the other version would do
views, but would not do something else (I forget what), so I got Informix
that worked pretty well with Red Hat Linux 5.0. When I upgraded to RHL 5.2
or 6.0 (I forget which), Informix would not work (could not even install
it), I could get no support from them, so that is why I went to DB2. When I
got tired of trying to keep DB2 working with RHEL 5, I switched to
postgreSQL, and the dbms itself worked right out of the box. I had to diddle
my programs very slightly (I used embedded SQL), but there were superficial
changes here and there.

The advantage of using one of the OS's file systems (I use ext2 for the dbms
and ext3 for everything else) are that the dbms developers have to be ready
for only about one file system. That is a really big advantage, I imagine. I
also have atime turned off. The main database is on 4 small hard drive
(about 18 GBytes each) each of which has just one partition taking the
entire drive. They are all on a single SCSI controller that also has my
backup tape drive on it. The machine has two other hard drives (around 80
GBytes each) on another SCSI controller and nothing else on that controller.
One of the drives has a partition on it where mainly the WAL is placed, and
another with little stuff. Those two drives have other partitions for the
Linus stuff, /tmp, /var, and /home as the main partitions on them, but the
one with the WAL on it is just about unused (contains /usr/source and stuff
like that) when postgres is running. That is good enough for me. If I were
in a serious production environment, I would take everything except the dbms
off that machine and run it on another one.

I cannot make any speed comparisons between postgreSQL and DB2, because the
machine I ran DB2 on has two 550 MHz processors and 512 Megabytes RAM
running RHL 7.3, and the new machine for postgres has two 3.06 GBYte
hyperthreaded Xeon processors and 8 GBytes RAM running RHEL 5, so a
comparison would be kind of meaningless.

--
   .~.  Jean-David Beyer          Registered Linux User 85642.
   /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
  /( )\ Shrewsbury, New Jersey    http://counter.li.org
  ^^-^^ 06:50:01 up 4 days, 17:08, 4 users, load average: 4.18, 4.15, 4.07

pgsql-performance by date:

Previous
From: Derek Lewis
Date:
Subject: Re: Need help with 8.4 Performance Testing
Next
From: "Scott Marlowe"
Date:
Subject: Re: file system and raid performance