Re: Filesystem benchmarking for pg 8.3.3 server

On Wed, 13 Aug 2008, Ron Mayer wrote:

> I assume test_fsync in the postgres source distribution is
> a decent way to see?

Not really. It takes too long (runs too many tests you don't care about)
and doesn't spit out the results the way you want them--TPS, not average
time.

You can do it with pgbench (scale here really doesn't matter):

$ cat insert.sql
\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
BEGIN;
INSERT INTO history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid,
:aid, :delta, CURRENT_TIMESTAMP);
END;
$ createdb pgbench
$ pgbench -i -s 20 pgbench
$ pgbench -f insert.sql -s 20 -c 1 -t 10000 pgbench

Don't really need to ever rebuild that just to run more tests if all you
care about is the fsync speed (no indexes in the history table to bloat or
anything).

Or you can measure with sysbench;
http://www.mysqlperformanceblog.com/2006/05/03/group-commit-and-real-fsync/
goes over that but they don't have the syntax exacty right. Here's an
example that works:

:~/sysbench-0.4.8/bin/bin$ ./sysbench run --test=fileio
--file-fsync-freq=1 --file-num=1 --file-total-size=16384
--file-test-mode=rndwr

> But I still am looking for any evidence that there were any widely
> shipped SATA (or even IDE drives) that were at fault, as opposed to
> filesystem bugs and poor settings of defaults.

Alan Cox claims that until circa 2001, the ATA standard didn't require
implementing the cache flush call at all. See
http://www.kerneltraffic.org/kernel-traffic/kt20011015_137.html Since
firmware is expensive to write and manufacturers are generally lazy here,
I'd bet a lot of disks from that era were missing support for the call.
Next time I'd digging through my disk graveyard I'll try and find such a
disk. If he's correct that the standard changed around you wouldn't
expect any recent drive to not support the call.

I feel it's largely irrelevant that most drives handle things just fine
nowadays if you send them the correct flush commands, because there are so
manh other things that can make that system as a whole not work right.
Even if the flush call works most of the time, disk firmware is turning
increasibly into buggy software, and attempts to reduce how much of that
firmware you're actually using can be viewed as helpful.

This is why I usually suggest just turning the individual drive caches
off; the caveats for when they might work fine in this context are just
too numerous.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD