pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple disks: RAID 5 or PG Cluster
11+ messages / 8 participants
[nested] [flat]
* Multiple disks: RAID 5 or PG Cluster
@ 2005-06-17 19:34 Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-17 20:21 ` Re: Multiple disks: RAID 5 or PG Cluster Vivek Khera <vivek@khera.org>
2005-06-17 20:38 ` Re: Multiple disks: RAID 5 or PG Cluster mudfoot@rawbw.com
0 siblings, 2 replies; 11+ messages in thread
From: Yves Vindevogel @ 2005-06-17 19:34 UTC (permalink / raw)
To: pgsql-performance
Hi,
We are looking to build a new machine for a big PG database.
We were wondering if a machine with 5 scsi-disks would perform better
if we use a hardware raid 5 controller or if we would go for the
clustering in PG.
If we cluster in PG, do we have redundancy on the data like in a RAID 5
?
First concern is performance, not redundancy (we can do that a
different way because all data comes from upload files)
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Multiple disks: RAID 5 or PG Cluster
2005-06-17 19:34 Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-17 20:21 ` Vivek Khera <vivek@khera.org>
1 sibling, 0 replies; 11+ messages in thread
From: Vivek Khera @ 2005-06-17 20:21 UTC (permalink / raw)
To: pgsql-performance
On Jun 17, 2005, at 3:34 PM, Yves Vindevogel wrote:
> We are looking to build a new machine for a big PG database.
> We were wondering if a machine with 5 scsi-disks would perform
> better if we use a hardware raid 5 controller or if we would go for
> the clustering in PG.
> If we cluster in PG, do we have redundancy on the data like in a
> RAID 5 ?
>
I'd recommend 4 disks in a hardware RAID10 plus a hot spare, or use
the 5th disk as boot + OS if you're feeling lucky.
Vivek Khera, Ph.D.
+1-301-869-4449 x806
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Multiple disks: RAID 5 or PG Cluster
2005-06-17 19:34 Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-17 20:38 ` mudfoot@rawbw.com
2005-06-18 16:00 ` Re: Multiple disks: RAID 5 or PG Cluster PFC <lists@boutiquenumerique.com>
1 sibling, 1 reply; 11+ messages in thread
From: mudfoot@rawbw.com @ 2005-06-17 20:38 UTC (permalink / raw)
To: pgsql-performance
If you truly do not care about data protection -- either from drive loss or from
sudden power failure, or anything else -- and just want to get the fastest
possible performance, then do RAID 0 (striping). It may be faster to do that
with software RAID on the host than with a special RAID controller. And turn
off fsyncing the write ahead log in postgresql.conf (fsync = false).
But be prepared to replace your whole database from scratch (or backup or
whatever) if you lose a single hard drive. And if you have a sudden power loss
or other type of unclean system shutdown (kernel panic or something) then your
data integrity will be at risk as well.
To squeeze evena little bit more performance, put your operating system, swap
and PostgreSQL binaries on a cheap IDE or SATA drive--and only your data on the
5 striped SCSI drives.
I do not know what clustering would do for you. But striping will provide a
high level of assurance that each of your hard drives will process equivalent
amounts of IO operations.
Quoting Yves Vindevogel <yves.vindevogel@implements.be>:
> Hi,
>
> We are looking to build a new machine for a big PG database.
> We were wondering if a machine with 5 scsi-disks would perform better
> if we use a hardware raid 5 controller or if we would go for the
> clustering in PG.
> If we cluster in PG, do we have redundancy on the data like in a RAID 5
> ?
>
> First concern is performance, not redundancy (we can do that a
> different way because all data comes from upload files)
>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Multiple disks: RAID 5 or PG Cluster
2005-06-17 19:34 Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-17 20:38 ` Re: Multiple disks: RAID 5 or PG Cluster mudfoot@rawbw.com
@ 2005-06-18 16:00 ` PFC <lists@boutiquenumerique.com>
2005-06-18 16:24 ` Re: Multiple disks: RAID 5 or PG Cluster Jacques Caron <jc@directinfos.com>
0 siblings, 1 reply; 11+ messages in thread
From: PFC @ 2005-06-18 16:00 UTC (permalink / raw)
To: mudfoot@rawbw.com; pgsql-performance
> I do not know what clustering would do for you. But striping will
> provide a
> high level of assurance that each of your hard drives will process
> equivalent
> amounts of IO operations.
I don't know what I'm talking about, but wouldn't mirorring be faster
than striping for random reads like you often get on a database ? (ie. the
reads can be dispatched to any disk) ? (or course, not for writes, but if
you won't use fsync, random writes should be reduced no ?)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Multiple disks: RAID 5 or PG Cluster
2005-06-17 19:34 Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-17 20:38 ` Re: Multiple disks: RAID 5 or PG Cluster mudfoot@rawbw.com
2005-06-18 16:00 ` Re: Multiple disks: RAID 5 or PG Cluster PFC <lists@boutiquenumerique.com>
@ 2005-06-18 16:24 ` Jacques Caron <jc@directinfos.com>
2005-06-19 04:58 ` Re: Multiple disks: RAID 5 or PG Cluster Alex Turner <armtuk@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jacques Caron @ 2005-06-18 16:24 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: mudfoot@rawbw.com; pgsql-performance
Hi,
At 18:00 18/06/2005, PFC wrote:
> I don't know what I'm talking about, but wouldn't mirorring be
> faster
>than striping for random reads like you often get on a database ? (ie. the
>reads can be dispatched to any disk) ? (or course, not for writes, but if
>you won't use fsync, random writes should be reduced no ?)
Roughly, for random reads, the performance (in terms of operations/s)
compared to a single disk setup, with N being the number of drives, is:
RAID 0 (striping):
- read = N
- write = N
- capacity = N
- redundancy = 0
RAID 1 (mirroring, N=2):
- read = N
- write = 1
- capacity = 1
- redundancy = 1
RAID 5 (striping + parity, N>=3)
- read = N-1
- write = 1/2
- capacity = N-1
- redundancy = 1
RAID 10 (mirroring + striping, N=2n, N>=4)
- read = N
- write = N/2
- capacity = N/2
- redundancy < N/2
So depending on your app, i.e. your read/write ratio, how much data can be
cached, whether the data is important or not, how much data you have, etc,
one or the other option might be better.
Jacques.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Multiple disks: RAID 5 or PG Cluster
2005-06-17 19:34 Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-17 20:38 ` Re: Multiple disks: RAID 5 or PG Cluster mudfoot@rawbw.com
2005-06-18 16:00 ` Re: Multiple disks: RAID 5 or PG Cluster PFC <lists@boutiquenumerique.com>
2005-06-18 16:24 ` Re: Multiple disks: RAID 5 or PG Cluster Jacques Caron <jc@directinfos.com>
@ 2005-06-19 04:58 ` Alex Turner <armtuk@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Alex Turner @ 2005-06-19 04:58 UTC (permalink / raw)
To: Jacques Caron <jc@directinfos.com>; +Cc: PFC <lists@boutiquenumerique.com>; mudfoot@rawbw.com; pgsql-performance
Of course these numbers are not true as soon as you exceed the stripe size
for a read operation, which is often only 128k. Typically a stripe of
mirrors will not read from seperate halves of the mirrors either, so RAID 10
is only N/2 best case in my experience, Raid 0+1 is a mirror of stripes and
will read from independant halves, but gives worse redundancy.
Alex Turner
NetEconomist
On 6/18/05, Jacques Caron <jc@directinfos.com> wrote:
>
> Hi,
>
> At 18:00 18/06/2005, PFC wrote:
> > I don't know what I'm talking about, but wouldn't mirorring be
> > faster
> >than striping for random reads like you often get on a database ? (ie.
> the
> >reads can be dispatched to any disk) ? (or course, not for writes, but if
> >you won't use fsync, random writes should be reduced no ?)
>
> Roughly, for random reads, the performance (in terms of operations/s)
> compared to a single disk setup, with N being the number of drives, is:
>
> RAID 0 (striping):
> - read = N
> - write = N
> - capacity = N
> - redundancy = 0
>
> RAID 1 (mirroring, N=2):
> - read = N
> - write = 1
> - capacity = 1
> - redundancy = 1
>
> RAID 5 (striping + parity, N>=3)
> - read = N-1
> - write = 1/2
> - capacity = N-1
> - redundancy = 1
>
> RAID 10 (mirroring + striping, N=2n, N>=4)
> - read = N
> - write = N/2
> - capacity = N/2
> - redundancy < N/2
>
> So depending on your app, i.e. your read/write ratio, how much data can be
> cached, whether the data is important or not, how much data you have, etc,
> one or the other option might be better.
>
> Jacques.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Fwd: Multiple disks: RAID 5 or PG Cluster
@ 2005-06-17 21:30 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 11+ messages in thread
From: Yves Vindevogel @ 2005-06-17 21:30 UTC (permalink / raw)
To: pgsql-performance
Ok, I will hate that day, but it's only 6 months
Begin forwarded message:
> From: Vivek Khera <vivek@khera.org>
> Date: Fri 17 Jun 2005 23:26:43 CEST
> To: Yves Vindevogel <yves.vindevogel@implements.be>
> Subject: Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster
>
>
> On Jun 17, 2005, at 5:24 PM, Yves Vindevogel wrote:
>
>> That means that only 2 / 5 of my discs are actual storage. That's a
>> bit low, imho.
>>
>> Maybe I can ask my question again:
>> Would I go for RAID 5, RAID 0 or PG clustering
>>
>> On 17 Jun 2005, at 22:21, Vivek Khera wrote:
> If you're allergic to RAID10, then do RAID5. but you'll sacrifice
> performance. You'll hate life the day you blow a disk and have to
> rebuild everything, even if it is all easily restored.
>
>
>
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Fwd: Multiple disks: RAID 5 or PG Cluster
@ 2005-06-17 21:31 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 11+ messages in thread
From: Yves Vindevogel @ 2005-06-17 21:31 UTC (permalink / raw)
To: pgsql-performance
BTW, tnx for the opinion ...
I forgot to cc list ...
Begin forwarded message:
> From: Yves Vindevogel <yves.vindevogel@implements.be>
> Date: Fri 17 Jun 2005 23:29:32 CEST
> To: mudfoot@rawbw.com
> Subject: Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster
>
> Ok, striping is a good option ...
>
> I'll tell you why I don't care about dataloss
>
> 1) The database will run 6 months, no more.
> 2) The database is fed with upload files. So, if I have a backup each
> day, plus my files of that day, I can restore pretty quickly.
> 3) Power failure is out of the question: battery backup (UPS), disk
> failure is minimal change: new server, new discs, 6 months ...
>
> We do have about 500.000 new records each day in that database, so
> that's why I want performance
> Records are uploaded in one major table and then denormalised into
> several others.
>
> But, I would like to hear somebody about the clustering method. Isn't
> that much used ?
> Or isn't it used in a single machine ?
>
> On 17 Jun 2005, at 22:38, mudfoot@rawbw.com wrote:
>
>> If you truly do not care about data protection -- either from drive
>> loss or from
>> sudden power failure, or anything else -- and just want to get the
>> fastest
>> possible performance, then do RAID 0 (striping). It may be faster to
>> do that
>> with software RAID on the host than with a special RAID controller.
>> And turn
>> off fsyncing the write ahead log in postgresql.conf (fsync = false).
>>
>> But be prepared to replace your whole database from scratch (or
>> backup or
>> whatever) if you lose a single hard drive. And if you have a sudden
>> power loss
>> or other type of unclean system shutdown (kernel panic or something)
>> then your
>> data integrity will be at risk as well.
>>
>> To squeeze evena little bit more performance, put your operating
>> system, swap
>> and PostgreSQL binaries on a cheap IDE or SATA drive--and only your
>> data on the
>> 5 striped SCSI drives.
>>
>> I do not know what clustering would do for you. But striping will
>> provide a
>> high level of assurance that each of your hard drives will process
>> equivalent
>> amounts of IO operations.
>>
>> Quoting Yves Vindevogel <yves.vindevogel@implements.be>:
>>
>>> Hi,
>>>
>>> We are looking to build a new machine for a big PG database.
>>> We were wondering if a machine with 5 scsi-disks would perform better
>>> if we use a hardware raid 5 controller or if we would go for the
>>> clustering in PG.
>>> If we cluster in PG, do we have redundancy on the data like in a
>>> RAID 5
>>> ?
>>>
>>> First concern is performance, not redundancy (we can do that a
>>> different way because all data comes from upload files)
>>>
>>> Met vriendelijke groeten,
>>> Bien à vous,
>>> Kind regards,
>>>
>>> Yves Vindevogel
>>> Implements
>>>
>>>
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>
>
>
> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
>
> Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
>
> Web: http://www.implements.be
>
> First they ignore you. Then they laugh at you. Then they fight you.
> Then you win.
> Mahatma Ghandi.
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Fwd: Multiple disks: RAID 5 or PG Cluster
@ 2005-06-18 16:42 Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-18 22:57 ` Re: Fwd: Multiple disks: RAID 5 or PG Cluster Michael Stone <mstone+postgres@mathom.us>
0 siblings, 1 reply; 11+ messages in thread
From: Yves Vindevogel @ 2005-06-18 16:42 UTC (permalink / raw)
To: pgsql-performance
cc ...
Begin forwarded message:
> From: Yves Vindevogel <yves.vindevogel@implements.be>
> Date: Sat 18 Jun 2005 18:18:53 CEST
> To: PFC <lists@boutiquenumerique.com>
> Subject: Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster
>
> There's a basic difference between striping (raid 0) and mirroring
> (raid 1)
>
> With striping, each file is distributed over several disks, making the
> physical write faster because several disks can do the work. Same for
> reading, multiple disks return a part of the file.
> Striping can not be used for safety/backup, if one disk fails, your
> file is lost (if it is partly on that failing disk). With mirroring
> you do not lose any disk space.
>
> Mirroring is a technique for avoiding disasters when you have a disk
> failure. Every file is written twice, each time to a different disk,
> which is a mirror of the first one.
> You effectively lose half of your diskspace to that mirror. But when
> a disk fails, you don't lose anything, since you can rely on the other
> mirrored disk.
>
> Raid 10, which is the combination of that, has both. You have
> multiple disks that form your first part of the raid and you have an
> equal amount of disks for the mirror.
> On each part of the mirror, striping is used to spread the files like
> in a raid 0. This is a very costly operation. You need a minimum of
> 4 disks, and you lose 50% of your capacity.
>
> BTW: mirroring is always slower than striping.
>
> On 18 Jun 2005, at 18:00, PFC wrote:
>
>>
>>
>>> I do not know what clustering would do for you. But striping will
>>> provide a
>>> high level of assurance that each of your hard drives will process
>>> equivalent
>>> amounts of IO operations.
>>
>> I don't know what I'm talking about, but wouldn't mirorring be
>> faster than striping for random reads like you often get on a
>> database ? (ie. the reads can be dispatched to any disk) ? (or
>> course, not for writes, but if you won't use fsync, random writes
>> should be reduced no ?)
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>
>
>
> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
>
> Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
>
> Web: http://www.implements.be
>
> First they ignore you. Then they laugh at you. Then they fight you.
> Then you win.
> Mahatma Ghandi.
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Fwd: Multiple disks: RAID 5 or PG Cluster
2005-06-18 16:42 Fwd: Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-18 22:57 ` Michael Stone <mstone+postgres@mathom.us>
2005-06-19 05:53 ` Re: Fwd: Multiple disks: RAID 5 or PG Cluster Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Stone @ 2005-06-18 22:57 UTC (permalink / raw)
To: pgsql-performance
On Sat, Jun 18, 2005 at 06:42:27PM +0200, Yves Vindevogel wrote:
>>With striping, each file is distributed over several disks, making
>>the physical write faster because several disks can do the work.
>>Same for reading, multiple disks return a part of the file.
A mirror behaves almost exactly the same for reads, with a caveat: for a
large enough file, multiple disks *must* be accessed in a striped
configuration, while in a mirrored configuration the controller may
access either one or more disks to read any file.
>>BTW: mirroring is always slower than striping.
That's simply not true. Striping speeds up writes but has no advantage
over a simlarly sized mirror for reading. In fact, the mirror will be
faster for pathological cases in which the reads are aligned in such a
way that they would all be have to be read from the same stripe of a
striped array. The striped configuration has an advantage when more than
two disks are used, but that derives from the number of spindles, not
from the striping; it is possible to have a mirror of more than two
disks (which would have the same read advantage as the striped
configuration with the same number of disks) but this is rarely seen
because it is expensive.
Mike Stone
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Fwd: Multiple disks: RAID 5 or PG Cluster
2005-06-18 16:42 Fwd: Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-18 22:57 ` Re: Fwd: Multiple disks: RAID 5 or PG Cluster Michael Stone <mstone+postgres@mathom.us>
@ 2005-06-19 05:53 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 11+ messages in thread
From: Greg Stark @ 2005-06-19 05:53 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; +Cc: pgsql-performance
Michael Stone <mstone+postgres@mathom.us> writes:
> it is possible to have a mirror of more than two disks (which would have the
> same read advantage as the striped configuration with the same number of
> disks) but this is rarely seen because it is expensive.
Actually three-way mirrors are quite common for backup purposes. To take a
backup you break the mirror by taking one of the three copies out. Back it up
at your leisure, then just resync it in time for the next backup.
--
greg
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-06-19 05:53 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-17 19:34 Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-17 20:21 ` Vivek Khera <vivek@khera.org>
2005-06-17 20:38 ` mudfoot@rawbw.com
2005-06-18 16:00 ` PFC <lists@boutiquenumerique.com>
2005-06-18 16:24 ` Jacques Caron <jc@directinfos.com>
2005-06-19 04:58 ` Alex Turner <armtuk@gmail.com>
2005-06-17 21:30 Fwd: Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-17 21:31 Fwd: Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-18 16:42 Fwd: Multiple disks: RAID 5 or PG Cluster Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-18 22:57 ` Michael Stone <mstone+postgres@mathom.us>
2005-06-19 05:53 ` Greg Stark <gsstark@mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox