Subject: Re: SAN performance mystery
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Tim Allen <tim@proximity.com.au>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <4491D61B.9040507@proximity.com.au>
References: <4491D61B.9040507@proximity.com.au>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1150408614.26538.95.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Thu, 15 Jun 2006 16:56:54 -0500

On Thu, 2006-06-15 at 16:50, Tim Allen wrote:
> We have a customer who are having performance problems. They have a 
> large (36G+) postgres 8.1.3 database installed on an 8-way opteron with 
> 8G RAM, attached to an EMC SAN via fibre-channel (I don't have details 
> of the EMC SAN model, or the type of fibre-channel card at the moment). 
> They're running RedHat ES3 (which means a 2.4.something Linux kernel).
> 
> They are unhappy about their query performance. We've been doing various 
> things to try to work out what we can do. One thing that has been 
> apparent is that autovacuum has not been able to keep the database 
> sufficiently tamed. A pg_dump/pg_restore cycle reduced the total 
> database size from 81G to 36G. Performing the restore took about 23 hours.

Do you have the ability to do any simple IO performance testing, like
with bonnie++ (the old bonnie is not really capable of properly testing
modern equipment, but bonnie++ will give you some idea of the throughput
of the SAN)  Or even just timing a dd write to the SAN?

> We tried restoring the pg_dump output to one of our machines, a 
> dual-core pentium D with a single SATA disk, no raid, I forget how much 
> RAM but definitely much less than 8G. The restore took five hours. So it 
> would seem that our machine, which on paper should be far less 
> impressive than the customer's box, does more than four times the I/O 
> performance.
> 
> To simplify greatly - single local SATA disk beats EMC SAN by factor of 
> four.
> 
> Is that expected performance, anyone? It doesn't sound right to me. Does 
> anyone have any clues about what might be going on? Buggy kernel 
> drivers? Buggy kernel, come to think of it? Does a SAN just not provide 
> adequate performance for a large database?

Yes, this is not uncommon.  It is very likely that your SATA disk is
lying about fsync.

What kind of backup are you using?  insert statements or copy
statements?  If insert statements, then the difference is quite
believable.  If copy statements, less so.

Next time, on their big server, see if you can try a restore with fsync
turned off and see if that makes the restore faster.  Note you should
turn fsync back on after the restore, as running without it is quite
dangerous should you suffer a power outage.

How are you mounting to the EMC SAN?  NFS, iSCSI? Other?