Monday, November 28

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.

2 comments:

Unknown said...

I would love to see this same benchmark against Linux and FreeBSD.

But thanks for the numbers..

I'm actually quite surprised at the lack of info of Solaris10+Postgres vs BSD+Postgres vs Linux+Postgres. It seems that ZFS is the perfect mate to a DB. If I gain ZFS but don't give up speed, concurrency, and stability as compared to BSD and Linux, then that'd be the obvious choice..

Unknown said...

Have you ever thought about adding a little bit more than just your articles? I mean, what you say is important and all. However think about if you added some great visuals or videos to give your posts more, “pop”! Your content is excellent but with pics and videos, this blog could undeniably be one of the greatest in its field. Excellent blog!advertising | advertisement | production houses in pakistan | pakistani matrimony