DB over NFS (was Re: Postgres over SAN/iSCSI)

Does anyone have experience running Postgres and using an iSCSI SAN as storage? I am looking for options that let me grow storage capacity without downtime while maintaining a reasonable level of performance. iSCSI seems like it might work but I have 0 experience with the technology. Any comments or suggestions are greatly appreciated.

---
Bill Pennington
VP Services
WhiteHat Security Inc.
http://www.whitehatsec.com

Bill Pennington wrote:
> Does anyone have experience running Postgres and using an iSCSI SAN as storage? I am looking for options that let me grow storage capacity without downtime while maintaining a reasonable level of performance. iSCSI seems like it might work but I have 0 experience with the technology. Any comments or suggestions are greatly appreciated.

I've run Postgres over DRBD ( http://www.drbd.org )before. It worked,
but the main bottleneck became the I/O limitations in DRBD.

I haven't used iSCSI specifically for Postgres, but have been on the
downtime end of iSCSI SANs quite a bit :) That particular situation was
using NFS over iSCSI, so I can't say assuredly what part of the stack
was the trouble spot.

LVM and data partitioning might be a good option to look at if you are
running Linux.

> ---
> Bill Pennington
> VP Services
> WhiteHat Security Inc.
> http://www.whitehatsec.com

--
Red Hot Penguin Consulting LLC
mod_perl/PostgreSQL consulting and implementation
http://www.redhotpenguin.com/

On Thu, Jun 05, 2008 at 09:33:58AM -0700, Bill Pennington wrote:
> Does anyone have experience running Postgres and using an iSCSI SAN as
> storage? I am looking for options that let me grow storage capacity without
> downtime while maintaining a reasonable level of performance. iSCSI seems like
> it might work but I have 0 experience with the technology. Any comments or
> suggestions are greatly appreciated.

though not exactly answering your question, we (Zapatec) ran Postgres over NFS
(on a Netapp) for one of our products a few years ago. It worked well, though
GigE is the minimum network bw you'd want to consider (much less expensive now
than when we were doing it)

the Netapp allowed volumes to grow without downtime and both FreeBSD and
Postgres had no problems with that

there is a vast amount of literature (esp. from Netapp and Oracle) about
running databases over NFS and *much* of it is applicable to Postgres too
AFAICT -- I now run Oracle (and have run 10g RAC) over NFS to Netapp at a
sizable installation (Oracle was already here and there is a site license
hence not switching to Postgres :-) and have no problems to report about the
combination or running a db over NFS (other than flow control network
interface issues with Dell hardware!) and everything to recommend about it!

Adi

Thanks Adi, I am probably just being paranoid but running *anything* of NFS gives me the creeps, but I had bad experiences with a goofball NFS server in my past that have probably scarred me too much.

How much data where you storing? We are at close to 3TB today and estimating 10-50TB by the end of 2009, assuming we continue with our current data retention policies.

---
Bill Pennington
VP Services
WhiteHat Security Inc.
http://www.whitehatsec.com

----- Original Message -----
From: "R.P. Aditya" <aditya(at)grot(dot)org>
To: "Bill Pennington" <bill(at)whitehatsec(dot)com>
Cc: sfpug(at)postgresql(dot)org
Sent: Thursday, June 5, 2008 10:27:01 AM GMT -08:00 US/Canada Pacific
Subject: Re: [sfpug] Postgres over SAN/iSCSI

On Thu, Jun 05, 2008 at 09:33:58AM -0700, Bill Pennington wrote:
> Does anyone have experience running Postgres and using an iSCSI SAN as
> storage? I am looking for options that let me grow storage capacity without
> downtime while maintaining a reasonable level of performance. iSCSI seems like
> it might work but I have 0 experience with the technology. Any comments or
> suggestions are greatly appreciated.

though not exactly answering your question, we (Zapatec) ran Postgres over NFS
(on a Netapp) for one of our products a few years ago. It worked well, though
GigE is the minimum network bw you'd want to consider (much less expensive now
than when we were doing it)

the Netapp allowed volumes to grow without downtime and both FreeBSD and
Postgres had no problems with that

there is a vast amount of literature (esp. from Netapp and Oracle) about
running databases over NFS and *much* of it is applicable to Postgres too
AFAICT -- I now run Oracle (and have run 10g RAC) over NFS to Netapp at a
sizable installation (Oracle was already here and there is a site license
hence not switching to Postgres :-) and have no problems to report about the
combination or running a db over NFS (other than flow control network
interface issues with Dell hardware!) and everything to recommend about it!

Adi

On Thu, Jun 05, 2008 at 10:33:26AM -0700, Bill Pennington wrote:
> Thanks Adi, I am probably just being paranoid but running *anything* of NFS
> gives me the creeps, but I had bad experiences with a goofball NFS server in
> my past that have probably scarred me too much.

Our Netapp was/is certainly not "goofball" and we were prepared to pay enough
to make sure it wasn't, so maybe that was/is the saving grace :-)

> How much data where you storing? We are at close to 3TB today and estimating
> 10-50TB by the end of 2009, assuming we continue with our current data
> retention policies.

In 2002 we had about 100GB in our Postgresql db and a single table with 14
million rows IIRC and growing about 2 million rows a month.

In Oracle now I have 1+ TB in data alone (3+ TB in tablespaces) and growing
about 100GB per month. I have over 20TB in total Netapp storage for Oracle
including backups, exports and replication at a remote site (all for just one
project currently).

I don't think the space usage should be your concern, but rather IO esp. if
you are doing analytics/data warehousing esp. involving full table scans. In
many ways running it over NFS is just like DAS except with advantages (easily
bring up a new server, expand the volume size and in the case of Netapp use
snapshots to make RO copies in <1 second, and flexclone to make rw copies from
those snapshots without using twice the amount of space -- basically a clone
only uses as much room as the changed blocks)

and no, I don't get a cut from Netapp (though I should) :-)

Adi