PostgreSQL 8.1 on ZFS in Solaris

Benchmark time!

Got some free time with my laptop so I decided to do some basic (very unscientific) benchmarks with Postgres to compare Sun's new filesystem ZFS and with the older UFS filesystem.

Test system
Dell Inspiron 8600
Intel Centrino 1.4GHz/1Mb.
768Mb RAM
30Gb 4200RPM 2.5" HDD (Hehe, did you say bottleneck?)

Postgres version is 8.1 and I used more or less the default config. Increased shared_buffers and the fsm parameters, the fsync parameter was toggled on and off during the test as indicated below in the chart.

SQL schema

create table i1 (
        id      serial,
        test    char(50)
);

-- Insert 1 million rows with the letter x 50 times in each row.
insert into i1(test) select lpad('', 50,'x') from 
  generate_series(1,1000000);

I also did a test using the pgbench tool (20 simultaneous clients) that comes in the postgres contrib tree.

Here are the results

FS      FSYNC   Compre. Ins time. Pgbench TPS
------- ------- ------- --------- -----------
UFS     On      -       25718 ms  312
UFS     Off     -       26347 ms  380
ZFS     On      Off     98544 ms  52
ZFS     Off     Off     31850 ms  380
ZFS     On      On      69753 ms  62
ZFS     Off     On      12114 ms  375

Some results are quite intersting.
First, the automatic fsync in postgres kills zfs write performance.
Second, compression gives higher performance on my system.

Conclusion, this benchmark would have been more intersting if it had been done on a Sun Opteron server hooked up to a proper storage system. But the basic IDE drive benchmark may give some pointers. ZFS would probably have performed much better on a more suitable setup and probably proved faster than UFS due to its more direct approach to disk striping.