Hi Aditya,

Is it possible to install a Gigabit adapter in the *BSD box and filer? That alone will probably give you a good performance boost... Switching to udp probably won't improve things, but using Gbe and jumbo frames will do wonders.

Regards, max

-----Original Message----- From: Aditya To: toasters@mathworks.com Sent: 9/15/2003 2:57 PM Subject: Running Postgresql with db on Netapp

Hi,

We've been running Postgresql (7.2.x) on FreeBSD with the database files on a Netapp F85. The mounts are NFSv3/TCP with no special tuning on the filer or client. This is a single db server accessing the database on a rw mount so there are no traditional filesystem locking problems (AFAIK).

We seem to have hit a performance wall (vs. a linux box with local IDE drive) and I've been reading the various whitepapers on the NetApp website about Oracle/Informix and filers and started to follow the recommendations:

- checked that all interfaces on nfs client/switch/filer are 100baseTX-FD

- turned minra on, which made the filer slightly more responsive but nothing spectacular

- changed the mount options to include -w=32768,-r=32768 with no perceptible difference (also increased it to 65536 for both r and w with no change)

- turned fsync off in Postgres (yes, I know it's risky, but I'm trying to figure out why this is so borked)

- unlike solaris there is no "direct access" mode in FreeBSD to prevent the NFS client from caching which is 2x as wasteful since the db is also caching

You can see the general performance stats (which are matched by "human perception" testing) for this filer ever since we turned on a heavy-duty application last Thursday:

http://dev.zapatec.com/website/internal/stats/netapp/zilch.pr.zapatec.co m/

and the various tweaking described above hasn't made much of a difference as you can see from the graphs (5 minute averages).

current sysstat snapshot is pretty consistent and looks like this:

zilch> sysstat 1 CPU NFS CIFS HTTP Net kB/s Disk kB/s Tape kB/s Cache in out read write read write age 9% 1450 0 0 1324 3726 3392 0 0 0 0 8% 1744 0 0 1323 3863 3916 0 0 0 0 10% 1588 0 0 1539 3617 3048 0 0 0 0 9% 1940 0 0 2251 3149 3140 4 0 0 0 8% 1718 0 0 401 3750 3288 2816 0 0 0 10% 1440 0 0 2327 3253 3504 0 0 0 0 9% 1648 0 0 2052 3504 3308 0 0 0 0 10% 1425 0 0 1625 3459 3172 0 0 0 0 8% 1709 0 0 1925 3167 3184 0 0 0 0

Any suggestions or hints of what to try other than don't run a db over NFS if you want performance:

http://www.sunmanagers.org/pipermail/summaries/2000-December/000072.html

After all, I presume that Oracle and Informix using a filer are doing well enough that people stick with the filer?

Thanks, Adi This electronic message, along with any attachments, is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any proprietary information or intellectual property contained in this transmission remains the sole and exclusive property of Marshall & Swift or Marshall & Swift/Boeckh. No right to use any products or services described in this transmission is valid without an executed and paid license agreement. If you received this in error, please reply to advise the sender and delete the material from any computer.