Thread: Recommended RAID for Postgres

Recommended RAID for Postgres

From
Thomas Bräutigam
Date:
Hello all,
 
I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra. What do you guys recommend on RAID Levels for this Database. Which does Postgres recommend, and with which do Postgres run very good or in the best way?
 
What Backup Strategy do you think would be the best. Dump the DB once a week or work with the WAL`s? For your info, the data which is feeded to the database is available and could be feeded again but it would maybe take a couple of days. So what would be a solution to bring up the huge DB in about one day after a crash. This would be the target.
 
Thanks for a feedback and Cheers Thomas

Re: Recommended RAID for Postgres

From
Tino Schwarze
Date:
On Mon, Jun 30, 2008 at 02:30:23PM +0200, Thomas Bräutigam wrote:

> I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra.

Thats plenty of data.

> What do you guys recommend on RAID Levels for this Database. Which
> does Postgres recommend, and with which do Postgres run very good or
> in the best way?

Avoid RAID5. Go for RAID10.

BTW: I wonder how much the used filesystem affects Postgres' performance...

> What Backup Strategy do you think would be the best. Dump the DB once
> a week or work with the WAL`s? For your info, the data which is feeded
> to the database is available and could be feeded again but it would
> maybe take a couple of days. So what would be a solution to bring up
> the huge DB in about one day after a crash. This would be the target.

How much writing activity is on the DB? WALs are probably the way to go.
How often you backup the whole DB space (as a starting point for
replaying WALs) depends on how much changes the DB gets. And how long it
will take to restore the DB space and the WALs afterwards.

HTH,

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

Re: Recommended RAID for Postgres

From
Thomas Bräutigam
Date:
Hi Tino,

Why avoid RAID5? I though that would be good? Can you explain this?

Writing in the DB, I add about 5-10 GB of data a day. There is a lot of writing activity going on in the database every
day.
What do you recommend how often I should backup the complete database?

Cheers Thomas


-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tino Schwarze
Sent: Montag, 30. Juni 2008 14:53
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Recommended RAID for Postgres

On Mon, Jun 30, 2008 at 02:30:23PM +0200, Thomas Bräutigam wrote:

> I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra.

Thats plenty of data.

> What do you guys recommend on RAID Levels for this Database. Which
> does Postgres recommend, and with which do Postgres run very good or
> in the best way?

Avoid RAID5. Go for RAID10.

BTW: I wonder how much the used filesystem affects Postgres' performance...

> What Backup Strategy do you think would be the best. Dump the DB once
> a week or work with the WAL`s? For your info, the data which is feeded
> to the database is available and could be feeded again but it would
> maybe take a couple of days. So what would be a solution to bring up
> the huge DB in about one day after a crash. This would be the target.

How much writing activity is on the DB? WALs are probably the way to go.
How often you backup the whole DB space (as a starting point for replaying WALs) depends on how much changes the DB
gets.And how long it will take to restore the DB space and the WALs afterwards. 

HTH,

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



Re: Recommended RAID for Postgres

From
Tino Schwarze
Date:
On Mon, Jun 30, 2008 at 03:15:34PM +0200, Thomas Bräutigam wrote:

> Why avoid RAID5? I though that would be good? Can you explain this?

RAID5 has a lot of disadvantages, especially in terms of performance.
.oO(There was a link posted recently...)

> Writing in the DB, I add about 5-10 GB of data a day. There is a lot of writing activity going on in the database
everyday. 
> What do you recommend how often I should backup the complete database?

What storage subsystem do you use? This sounds like you want something
like snapshots or similar to do backup. How long do you expect a 1-1.5
TB backup to run? I don't know. My biggest DBs are like 110 GB
(compressed dump), 300-400 GB on disk and they are difficult to handle
already.

Maybe other Postgres admins have more experience with such big DBs.

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

Re: Recommended RAID for Postgres

From
Michael Monnerie
Date:
On Montag, 30. Juni 2008 Thomas Bräutigam wrote:
> Why avoid RAID5?

RAID5 has very poor WRITE performance compared to RAID10, while READ is
nearly the same speed.

If you do not have a "branded" server like HP or IBM, I can recommend
the Areca RAID controllers http://www.areca.com.tw/ their driver is
included in Linux kernel, and they are pretty damn fast. Change the
onboard RAM to 2GB, with the SAS 16 or 24 port controller this is the
way to have fun.

On their website is also benchmarks about different RAID levels, so you
can compare them.

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net                   Key-ID: 1C1209B4

Attachment

Re: Recommended RAID for Postgres

From
Thomas Bräutigam
Date:
Hi Tino and Michael,

What storage subsystem do you use? This sounds like you want something like snapshots or similar to do backup. How long
doyou expect a 1-1.5 TB backup to run? I don't know. My biggest DBs are like 110 GB (compressed dump), 300-400 GB on
diskand they are difficult to handle already. 


I would like that a backup runs during the system is live, a snapshot would be okay. How long would a 1-1.5 TB backup
runfor this size of a Postgres DB? To bring the backup back after a crash again live, I would want a solution which
takesme one or two days but not longer. Do you think I can reach the target with a snapshot and also keep in mind the
sizeof the DB? 

Cheers Thomas


-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tino Schwarze
Sent: Montag, 30. Juni 2008 15:36
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Recommended RAID for Postgres

On Mon, Jun 30, 2008 at 03:15:34PM +0200, Thomas Bräutigam wrote:

> Why avoid RAID5? I though that would be good? Can you explain this?

RAID5 has a lot of disadvantages, especially in terms of performance.
.oO(There was a link posted recently...)

> Writing in the DB, I add about 5-10 GB of data a day. There is a lot of writing activity going on in the database
everyday. 
> What do you recommend how often I should backup the complete database?

What storage subsystem do you use? This sounds like you want something like snapshots or similar to do backup. How long
doyou expect a 1-1.5 TB backup to run? I don't know. My biggest DBs are like 110 GB (compressed dump), 300-400 GB on
diskand they are difficult to handle already. 

Maybe other Postgres admins have more experience with such big DBs.

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



Re: Recommended RAID for Postgres

From
"Mikko Partio"
Date:


On Mon, Jun 30, 2008 at 3:30 PM, Thomas Bräutigam <thomas.braeutigam@nexustelecom.com> wrote:
Hello all,
 
I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra. What do you guys recommend on RAID Levels for this Database. Which does Postgres recommend, and with which do Postgres run very good or in the best way?

We are running one ~2 TB database, mainly read-only stuff except for at batch import process every night. We are using RAID5 (RAID10 would require too many hard disk drives).
 
What Backup Strategy do you think would be the best. Dump the DB once a week or work with the WAL`s? For your info, the data which is feeded to the database is available and could be feeded again but it would maybe take a couple of days. So what would be a solution to bring up the huge DB in about one day after a crash. This would be the target.

I'd say that backup (using filesystem tools) the database once a week or fortnight and archive wals everyday. That's what we are doing and it's been working just fine.

Regards

Mikko

Re: Recommended RAID for Postgres

From
Montaseri
Date:
I'd also vote for RAID-5, best disk utilization, good on read, bad on writes, performance is good due to stripping

If you can get some hardware raid to do the job (vs software raid) than performance gets better...an HBA board as low as $150 will (at least) double your speed

Medi

On Mon, Jul 7, 2008 at 1:28 AM, Mikko Partio <mpartio@gmail.com> wrote:


On Mon, Jun 30, 2008 at 3:30 PM, Thomas Bräutigam <thomas.braeutigam@nexustelecom.com> wrote:
Hello all,
 
I have a pretty huge Postgres DB, about 1,3 to 1,5 Terra. What do you guys recommend on RAID Levels for this Database. Which does Postgres recommend, and with which do Postgres run very good or in the best way?

We are running one ~2 TB database, mainly read-only stuff except for at batch import process every night. We are using RAID5 (RAID10 would require too many hard disk drives).
 
What Backup Strategy do you think would be the best. Dump the DB once a week or work with the WAL`s? For your info, the data which is feeded to the database is available and could be feeded again but it would maybe take a couple of days. So what would be a solution to bring up the huge DB in about one day after a crash. This would be the target.

I'd say that backup (using filesystem tools) the database once a week or fortnight and archive wals everyday. That's what we are doing and it's been working just fine.

Regards

Mikko