pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marty Scholes <marty@outputservices.com>
To: pgsql-performance@postgresql.org
To: pgsql@mohawksoft.com
To: pgadmin@pse-consulting.de
Cc: marty@outputservices.com
Subject: Disk performance, was Re: tablespaces and DB administration
Date: Tue, 01 Jun 2004 18:08:34 -0600
Message-ID: <40BD1A82.7010001@outputservices.com> (raw)

This was a lively debate on what was faster, single spindles or RAID.

This is important, because I keep running into people who do not 
understand the performance dynamics of a RDBMS like Oracle or Pg.

Pg and Oracle make a zillion tiny reads and writes and fsync() 
regularly.  If your drive will copy a 4GB file at a sustained rate of 72 
MB/s, that tells you nothing about how it will do with an RDBMS.

I will throw in my experience on RAID vs spindles.

With the RAID write cache disabled, a well balanced set of spindles will 
kill a RAID system any day.

Enable the cache, and the RAID starts inching ahead.  My experience is 
that no one can continuously keep I/O properly balanced across several 
spindles on a production system.  Things change and the I/O mix changes. 
  Then, the RAID is outperforming the spindles.  If you want to spend 
the rest of your career constantly balancing I/O across spindles, then 
do so.

For the rest of us, with a write cache, a hardware RAID wins hands down 
over the long haul.

It might make sense to provide some sort of benchmarking tool for 
various systems so that we can predict I/O performance.

Run the following code both on a hardware RAID and on a single spindle.

#include <stdlib.h>
#include <stdio.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <time.h>

void makefile(int fs)
{
         int i;
         char    buf[8192];
         int     ld;
         int     blocks=4096;
         int     pos;
         time_t  stim;
         time_t  etim;
         float   avg;

         unlink("dump.out");
         ld=open("dump.out", O_WRONLY | O_CREAT);

         printf("Writing %d blocks sequentially\n", blocks);
         time(&stim);
         for (i=0; i<blocks; i++) {
                 write(ld, buf, sizeof(buf));
                 if (fs) {
                         fsync(ld);
                         }
                 }
         time(&etim);
         avg = (blocks+0.0)/(etim-stim-0.0);
         printf("Took %d seconds, avg %f iops\n\n", etim-stim, avg);

         // purge the write cache
         fsync(ld);

         printf("Writing %d blocks (somewhat randomly)\n", blocks);
         time(&stim);
         for (i=0; i<blocks; i++) {
                 pos = (rand()%blocks)*sizeof(buf);
                 lseek(ld, pos, SEEK_SET);
                 write(ld, buf, sizeof(buf));
                 if (fs) {
                         fsync(ld);
                         }
                 }
         time(&etim);
         avg = (blocks+0.0)/(etim-stim-0.0);
         printf("Took %d seconds, avg %f iops\n\n", etim-stim, avg);

         close(ld);
         unlink("dump.out");
}

int main()
{
         printf("No fsync()\n");
         makefile(0);

         printf("With fsync()\n");
         makefile(1);

         return 0;
}


The first operation shows how well the OS write cache is doing.  The 
second shows how poorly everything runs with fsync(), which is what Pg 
and Oracle do.

My RAID produced the following, but was also running production when I 
ran it:

No fsync()
Writing 4096 blocks sequentially
Took 1 seconds, avg 4096.000000 iops

Writing 4096 blocks (somewhat randomly)
Took 4 seconds, avg 1024.000000 iops

With fsync()
Writing 4096 blocks sequentially
Took 40 seconds, avg 102.400002 iops

Writing 4096 blocks (somewhat randomly)
Took 66 seconds, avg 62.060608 iops

When I ran this on a decent fibre channel drive, I got:

No fsync()
Writing 4096 blocks sequentially
Took 1 seconds, avg 4096.000000 iops

Writing 4096 blocks (somewhat randomly)
Took 7 seconds, avg 585.142883 iops

With fsync()
Writing 4096 blocks sequentially
Took 106 seconds, avg 38.641510 iops

Writing 4096 blocks (somewhat randomly)
Took 115 seconds, avg 35.617390 iops


You can see that the RAID array really helps out with small writes.





Message-ID: <40BD1A82.7010001@outputservices.com>
Permalink:  ../40BD1A82.7010001@outputservices.com/
Also on:    postgresql.org/message-id/40BD1A82.7010001@outputservices.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: marty@outputservices.com, pgsql@mohawksoft.com, pgadmin@pse-consulting.de
  Subject: Re: Disk performance, was Re: tablespaces and DB administration
  In-Reply-To: <40BD1A82.7010001@outputservices.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox