Re: Performance Problem - pgsql on MD3000 DAS

On 2008-05-25 15:19, Marc wrote:

> I ran pgbench against two database instances - one using the disks from
> the MD3000 and the other using local internal SATA storage.  The results
> I got showed roughly twice the throughput on local storage vs the
> external direct-attached-storage array.

A local storage often is lying to OS about what did it save to physical
media, if write cache is enabled.

Check this:
    hdparm -I /dev/sda
If before "Write Cache" line in "Commands/features" list is a star (*)
then it is enabled and a disk can lie. It then can not be compared to
anything that does not lie.

If it lies then it is not safe for Postgres to store data on it, as a
power failure or a hang can corrupt this data, because a server can not
know what is written to disk and what is not.

Try this:
$ download postgresql-8.3.1.tar.bz2 and save to /tmp
$ cd /tmp
$ tar xjf postgresql-8.3.1.tar.bz2
$ cd postgresql-8.3.1
$ ./configure
$ cd src/tools/fsync/
$ make
$ ./test_fsync -f [filename]

On my 7200 SATA drive with write cache enabled I get times (in seconds)
like this:
    Simple write timing:
            write                    0.009288

    Compare fsync times on write() and non-write() descriptor:
    (If the times are similar, fsync() can sync data written
     on a different descriptor.)
            write, fsync, close      0.247575
            write, close, fsync      0.236484

    Compare one o_sync write to two:
            (o_sync unavailable)
            (o_dsync unavailable)
            write, fdatasync         0.249111
            write, fsync,            0.254667

    Compare file sync methods with 2 8k writes:
            (o_dsync unavailable)
            write, fdatasync         0.356484
            write, fsync,            0.372457

But when I disable write cache with hdparm -W 0 /dev/sda:
    Simple write timing:
            write                    0.009002

    Compare fsync times on write() and non-write() descriptor:
    (If the times are similar, fsync() can sync data written
     on a different descriptor.)
            write, fsync, close      9.027829
            write, close, fsync      8.879246

    Compare one o_sync write to two:
            (o_sync unavailable)
            (o_dsync unavailable)
            write, fdatasync         8.807287
            write, fsync,            9.345699

    Compare file sync methods with 2 8k writes:
            (o_dsync unavailable)
            write, fdatasync         9.071120
            write, fsync,            9.237469

With write cache enabled it is clearly lying, as my 7200rpm drive can
not save 1000 times in the same place in less than 8.33s.
8.33s = 1000writes / (7200rpm / 60s)


I assume that you do not have an expensive SATA drive controller with
battery backed cache.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh