Thread: Recommended RAID for Postgres
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
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
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
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
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
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
On Mon, Jun 30, 2008 at 3:30 PM, Thomas Bräutigam <thomas.braeutigam@nexustelecom.com> wrote:
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
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
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
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