Re: SAN vs Internal Disks - Mailing list pgsql-performance

On Thu, 2007-09-06 at 18:05 +0530, Harsh Azad wrote:
> Hi,
>
> We are currently running our DB on a DualCore, Dual Proc 3.Ghz Xeon,
> 8GB RAM, 4x SAS 146 GB 15K RPM on RAID 5.
>
> The current data size is about 50GB, but we want to purchase the
> hardware to scale to about 1TB as we think our business will need to
> support that much soon.
> - Currently we have a 80% read and 20% write perecntages.
> - Currently with this configuration the Database is showing signs of
> over-loading.
> - Auto-vaccum, etc run on this database, vaccum full runs nightly.
> - Currently CPU loads are about 20%, memory utilization is full (but
> this is also due to linux caching disk blocks) and IO waits are
> frequent.
> - We have a load of about 400 queries per second
>
> Now we are considering to purchase our own servers and in the process
> are facing the usual dilemmas. First I'll list out what machine we
> have decided to use:
> 2x Quad Xeon 2.4 Ghz (4-way only 2 populated right now)
> 32 GB RAM
> OS Only storage - 2x SCSI 146 GB 15k RPM on RAID-1
> (Data Storage mentioned below)
>
> We have already decided to split our database into 3 machines on the
> basis on disjoint sets of data. So we will be purchasing three of
> these boxes.
>
> HELP 1: Does something look wrong with above configuration, I know
> there will be small differences b/w opetron/xeon. But do you think
> there is something against going for 2.4Ghz Quad Xeons (clovertown i
> think)?
>
> HELP 2: The main confusion is with regards to Data Storage. We have
> the option of going for:
>
> A: IBM N-3700 SAN Box, having 12x FC 300GB disks,  Partitioned into 3
> disks into RAID-4 for WAL/backup, and 9 disks on RAID-DP for data, 2
> hot spare. We are also considering similar solution from EMC -
> CX310C.
>
> B: Go for Internal of DAS based storage. Here for each server we
> should be able to have: 2x disks on RAID-1 for logs, 6x disks on
> RAID-10 for tablespace1 and 6x disks on RAID-10 for tablespace2. Or
> maybe 12x disks on RAID-10 single table-space.
>
> What do I think? Well..
> SAN wins on manageability, replication (say to a DR site), backup,
> etc...
> DAS wins on cost
>
> But for a moment keeping these aside, i wanted to discuss, purely on
> performance side which one is a winner? It feels like internal-disks
> will perform better, but need to understand a rough magnitude of
> difference in performance to see if its worth loosing the
> manageability features.
>
> Also if we choose to go with DAS, what would be the best tool to do
> async replication to DR site and maybe even as a extra plus a second
> read-only DB server to distribute select loads.

Sounds like a good candidate for Slony replication for backups /
read-only slaves.

I haven't seen a SAN yet whose DR / replication facilities are on par
with a good database replication solution.  My impression is that those
facilities are mostly for file servers, mail servers, etc.  It would be
difficult for a SAN to properly replicate a database given the strict
ordering, size and consistency requirements for the data files.  Not
impossible, but in my limited experience I haven't found one that I
trust to do it reliably either, vendor boastings to the contrary
notwithstanding.  (Hint: make sure you know exactly what your vendor's
definition of the term 'snapshot' really means).

So before you invest in a SAN, make sure that you're actually going to
be able to (and want to) use all the nice management features you're
paying for.  We have some SAN's that are basically acting just as
expensive external RAID arrays because we do the database
replication/backup in software anyway.

-- Mark Lewis