pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed12 disks raid setup
15+ messages / 10 participants
[nested] [flat]
* 12 disks raid setup
@ 2008-02-29 11:51 Franck Routier <franck.routier@axege.com>
0 siblings, 2 replies; 15+ messages in thread
From: Franck Routier @ 2008-02-29 11:51 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am in the process of setting up a postgresql server with 12 SAS disks.
I am considering two options:
1) set up a 12 disks raid 10 array to get maximum raw performance from
the system and put everything on it (it the whole pg cluster, including
WAL, and every tablespcace)
2) set up 3 raid 10 arrays of 4 disks, and dispatch my data on these
disks via tablespaces :
data1 = pg cluster + references data (dimensions) tablespace
data2 = fact data tablespace
data3 = indices tablespace
Typical workload is either massive insert/update via ETL or complex
queries on big (10 millions tuples) tables with several joins (including
Mondrian ROLAP).
Does anyone have an opinion of what could give best results ?
Thanks,
Franck
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-02-29 14:13 Scott Marlowe <scott.marlowe@gmail.com>
parent: Franck Routier <franck.routier@axege.com>
1 sibling, 1 reply; 15+ messages in thread
From: Scott Marlowe @ 2008-02-29 14:13 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
On Fri, Feb 29, 2008 at 5:51 AM, Franck Routier
<franck.routier@axege.com> wrote:
> Hi,
>
> I am in the process of setting up a postgresql server with 12 SAS disks.
>
> I am considering two options:
>
> 1) set up a 12 disks raid 10 array to get maximum raw performance from
> the system and put everything on it (it the whole pg cluster, including
> WAL, and every tablespcace)
>
> 2) set up 3 raid 10 arrays of 4 disks, and dispatch my data on these
> disks via tablespaces :
How you set it up depends on your RAID controller as much as anything.
Good battery backed RAID controllers seem to work better with one big
RAID-10 array. But as with anything, unless you benchmark it, you're
really just guessing which is best.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-02-29 14:51 Franck Routier <franck.routier@axege.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 15+ messages in thread
From: Franck Routier @ 2008-02-29 14:51 UTC (permalink / raw)
To: pgsql-performance
Hi,
my Raid controller is an Adaptec 31205 SAS/RAID controller. The battery
was an option, but I didn't know it at purchase time. So I have no
battery, but the whole system is on an UPS.
I have done quite a few tests using bonnie++, focusing on 'random seek'
results, and found out that:
1) linux md raid 10 performs better than Adaptec hardware raid in this
field (random seek) by 15%, quite consistently
2) hardware raid is better on sequential output
3) md outperforms it again when coming to sequential read, especially
with far layout option.
So in fact I think I will use md raid, but still don't know with which
layout (3x4 or 1x12).
What would you suggest as a benchmarking method ? Simply issue a few big
queries that I expect to be usual and see how long it last, or is there
a more convinient and or "scientific" method ?
Thanks,
Franck
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-02-29 15:41 Greg Smith <gsmith@gregsmith.com>
parent: Franck Routier <franck.routier@axege.com>
1 sibling, 2 replies; 15+ messages in thread
From: Greg Smith @ 2008-02-29 15:41 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
On Fri, 29 Feb 2008, Franck Routier wrote:
> my Raid controller is an Adaptec 31205 SAS/RAID controller. The battery
> was an option, but I didn't know it at purchase time. So I have no
> battery, but the whole system is on an UPS.
The UPS is of no help here. The problem is that PostgreSQL forces the
disk controller to commit WAL writes to disk after every transaction. If
you have a controller with a battery-backed cache, you can use that cache
to buffer those writes and dramatically increase write performance. The
USP doesn't give you the same write guarantees. Let's say someone trips
over the server power cord (simplest example of a whole class of
failures). With the BBC controller, the cached writes will get committed
when you plug the server back in. If all you've got is a UPS, writes that
didn't make it to disk before the outage are lost. That means you can't
buffer those writes without risking database corruption.
The general guideline here is that if you don't have a battery-backed
cache on your controller, based on disk rotation speed you'll be limited
to around 100 (7200 RPM) to 200 (15K RPM) commits/second per single
client, with each commit facing around a 2-4ms delay. That rises to
perhaps 500/s total with lots of clients. BBC configurations can easily
clear 3000/s total and individual commits don't have that couple of ms
delay.
> So in fact I think I will use md raid, but still don't know with which
> layout (3x4 or 1x12).
The only real downside of md RAID is that if you lose the boot device it
can be tricky to get the system to start again; hardware RAID hides that
little detail from the BIOS. Make sure you simulate a failure of the
primary boot drive and are comfortable with recovering from that situation
before you go into production with md.
The only way to know which layout will work better is to have a lot of
knowledge of this application and how it bottlenecks under load. If you
know, for example, that there's a particular set of tables/indexes that
are critical to real-time users, whereas others are only used by batch
operations, things like that can be used to figure out how to optimize
disk layout. If you don't know your database to that level, put
everything into one big array and forget about it; you won't do any better
than that.
> What would you suggest as a benchmarking method ? Simply issue a few big
> queries that I expect to be usual and see how long it last, or is there
> a more convinient and or "scientific" method ?
Benchmarking is hard and you have to use a whole array of tests if you
want to quantify the many aspects of performance. You're doing the right
thing using bonnie++ to quantify disk speed. If you've got some typical
queries, using those to fine-tune postgresql.conf parameters is a good
idea; just make sure to set shared_buffers, estimated_cache_size, and run
ANALYZE on your tables. Be careful to note performance differences when
the cache is already filled with data from previous runs. Measuring
write/commit performance is probably easiest using pgbench.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-02-29 17:17 Jignesh K. Shah <J.K.Shah@sun.com>
parent: Franck Routier <franck.routier@axege.com>
1 sibling, 1 reply; 15+ messages in thread
From: Jignesh K. Shah @ 2008-02-29 17:17 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
Franck Routier wrote:
> Hi,
>
> I am in the process of setting up a postgresql server with 12 SAS disks.
>
> I am considering two options:
>
> 1) set up a 12 disks raid 10 array to get maximum raw performance from
> the system and put everything on it (it the whole pg cluster, including
> WAL, and every tablespcace)
>
> 2) set up 3 raid 10 arrays of 4 disks, and dispatch my data on these
> disks via tablespaces :
>
> data1 = pg cluster + references data (dimensions) tablespace
> data2 = fact data tablespace
> data3 = indices tablespace
>
>
>
Option 2: Infact I would also say within one of the RAID1 use another
softpartition and separate out pg_xlog also.
My 2 cents based on my benchmarks.
-Jignesh
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-02-29 17:50 Joshua D. Drake <jd@commandprompt.com>
parent: Jignesh K. Shah <J.K.Shah@sun.com>
0 siblings, 1 reply; 15+ messages in thread
From: Joshua D. Drake @ 2008-02-29 17:50 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@sun.com>; +Cc: Franck Routier <franck.routier@axege.com>; pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 29 Feb 2008 12:17:29 -0500
"Jignesh K. Shah" <J.K.Shah@sun.com> wrote:
>
>
> Franck Routier wrote:
> > Hi,
> >
> > I am in the process of setting up a postgresql server with 12 SAS
> > disks.
> >
> > I am considering two options:
> >
> > 1) set up a 12 disks raid 10 array to get maximum raw performance
> > from the system and put everything on it (it the whole pg cluster,
> > including WAL, and every tablespcace)
I would do this (assuming you have other spindles for the OS):
/data1 - RAID 10 journalled filesystem + 1 (so 9 disks)
/xlogs - RAID 1 non journalled filesystem + 1 (so 3 disks)
You can create any number of tablespaces for further growth you see fit
and move them as more IO becomes available.
Sincerely,
Joshua D. Drake
- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHyEX6ATb/zqfZUUQRAu1XAKCpszYwF4dbI0hidg71JhmcrPqbmACcDhdc
E0qVOtKrUBpEEerGUjTMF9I=
=LzZS
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-02-29 18:24 Matthew <matthew@flymine.org>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 15+ messages in thread
From: Matthew @ 2008-02-29 18:24 UTC (permalink / raw)
To: pgsql-performance
On Fri, 29 Feb 2008, Joshua D. Drake wrote:
> /data1 - RAID 10 journalled filesystem + 1 (so 9 disks)
> /xlogs - RAID 1 non journalled filesystem + 1 (so 3 disks)
Sounds good. Can't they share the hot spare, rather than having two?
However, I would recommend splashing out on the battery for the cache, and
then just putting then all in one RAID 10 lump.
Matthew
--
To be or not to be -- Shakespeare
To do is to be -- Nietzsche
To be is to do -- Sartre
Do be do be do -- Sinatra
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-02-29 18:58 Vivek Khera <vivek@khera.org>
parent: Franck Routier <franck.routier@axege.com>
1 sibling, 0 replies; 15+ messages in thread
From: Vivek Khera @ 2008-02-29 18:58 UTC (permalink / raw)
To: pgsql-performance
On Feb 29, 2008, at 9:51 AM, Franck Routier wrote:
> my Raid controller is an Adaptec 31205 SAS/RAID controller. The
> battery
> was an option, but I didn't know it at purchase time. So I have no
> battery, but the whole system is on an UPS.
Go find one on ebay or google search, and plug it in. Adaptec
batteries just snap in and sometimes have a bracket to clip them in
place.
Your performance will be awful without one since you can't safely
write cache. Also, if your card has upgradable RAM (but I've never
seen an adaptec card which could) max it out.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-02-29 22:33 Mark Kirkwood <markir@paradise.net.nz>
parent: Greg Smith <gsmith@gregsmith.com>
1 sibling, 0 replies; 15+ messages in thread
From: Mark Kirkwood @ 2008-02-29 22:33 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: Franck Routier <franck.routier@axege.com>; pgsql-performance
Greg Smith wrote:
>
> The only real downside of md RAID is that if you lose the boot device
> it can be tricky to get the system to start again; hardware RAID hides
> that little detail from the BIOS. Make sure you simulate a failure of
> the primary boot drive and are comfortable with recovering from that
> situation before you go into production with md.
+1
I usually ensure there is a separate /boot that is setup RAID1 (with md
using all the disks for the RAID1 - so the I can keep the partition map
the same for all the disks, otherwise it is fiddly!)
Cheers
Mark
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-03-01 00:44 Shane Ambler <pgsql@Sheeky.Biz>
parent: Greg Smith <gsmith@gregsmith.com>
1 sibling, 1 reply; 15+ messages in thread
From: Shane Ambler @ 2008-03-01 00:44 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
Greg Smith wrote:
> On Fri, 29 Feb 2008, Franck Routier wrote:
>
>> my Raid controller is an Adaptec 31205 SAS/RAID controller. The battery
>> was an option, but I didn't know it at purchase time. So I have no
>> battery, but the whole system is on an UPS.
>
> The UPS is of no help here. The problem is that PostgreSQL forces the
> disk controller to commit WAL writes to disk after every transaction.
> If you have a controller with a battery-backed cache, you can use that
> cache to buffer those writes and dramatically increase write
> performance. The USP doesn't give you the same write guarantees. Let's
> say someone trips over the server power cord (simplest example of a
> whole class of failures). With the BBC controller, the cached writes
> will get committed when you plug the server back in. If all you've got
> is a UPS, writes that didn't make it to disk before the outage are
> lost. That means you can't buffer those writes without risking database
> corruption.
>
> The general guideline here is that if you don't have a battery-backed
> cache on your controller, based on disk rotation speed you'll be limited
> to around 100 (7200 RPM) to 200 (15K RPM) commits/second per single
> client, with each commit facing around a 2-4ms delay. That rises to
> perhaps 500/s total with lots of clients. BBC configurations can easily
> clear 3000/s total and individual commits don't have that couple of ms
> delay.
>
It may be the way you have worded this but it makes it sound like the
cache and the battery backup are as one (or that the cache doesn't work
unless you have the battery) The cache may be optional (or plug-in) in
some cards, even of varied size. The battery is normally optional. You
can normally add/remove the battery without changing the cache options.
If the raid card has the cache without the battery you would get the
performance figures you mentioned, you just wouldn't have the
reliability of finishing writes after a power off situation.
correct me if I am wrong here.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-03-01 04:56 Greg Smith <gsmith@gregsmith.com>
parent: Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 1 reply; 15+ messages in thread
From: Greg Smith @ 2008-03-01 04:56 UTC (permalink / raw)
To: pgsql-performance
On Sat, 1 Mar 2008, Shane Ambler wrote:
> It may be the way you have worded this but it makes it sound like the
> cache and the battery backup are as one (or that the cache doesn't work
> unless you have the battery)...If the raid card has the cache without
> the battery you would get the performance figures you mentioned, you
> just wouldn't have the reliability of finishing writes after a power off
> situation.
Wording is intentional--if you don't have a battery for it, the cache has
to be turned off (or set to write-through so it's only being used on
reads) in order for the database to be reliable. If you can't finish
writes after a power off, you can't cache writes and expect your database
to survive for too long.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-03-01 10:27 Franck Routier <franck.routier@axege.com>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 2 replies; 15+ messages in thread
From: Franck Routier @ 2008-03-01 10:27 UTC (permalink / raw)
To: pgsql-performance
Hi,
Le vendredi 29 février 2008 à 23:56 -0500, Greg Smith a écrit :
> Wording is intentional--if you don't have a battery for it, the cache has
> to be turned off (or set to write-through so it's only being used on
> reads) in order for the database to be reliable. If you can't finish
> writes after a power off, you can't cache writes and expect your database
> to survive for too long.
Well, am I just wrong, or the file system might also heavily rely on
cache, especially as I use XFS ?
So anyway Postgresql has no way to know if the data is really on the
disk, and in case of a brutal outage, the system may definitely lose
data, wether there is another level of caching (Raid controller) or
not...
Right ?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-03-01 12:44 Scott Marlowe <scott.marlowe@gmail.com>
parent: Franck Routier <franck.routier@axege.com>
1 sibling, 1 reply; 15+ messages in thread
From: Scott Marlowe @ 2008-03-01 12:44 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
On Sat, Mar 1, 2008 at 4:27 AM, Franck Routier <franck.routier@axege.com> wrote:
> Hi,
>
> Le vendredi 29 février 2008 à 23:56 -0500, Greg Smith a écrit :
> > Wording is intentional--if you don't have a battery for it, the cache has
> > to be turned off (or set to write-through so it's only being used on
> > reads) in order for the database to be reliable. If you can't finish
> > writes after a power off, you can't cache writes and expect your database
> > to survive for too long.
>
> Well, am I just wrong, or the file system might also heavily rely on
> cache, especially as I use XFS ?
>
> So anyway Postgresql has no way to know if the data is really on the
> disk, and in case of a brutal outage, the system may definitely lose
> data, wether there is another level of caching (Raid controller) or
> not...
>
> Right ?
nope. assuming your disk subsystem doesn't lie about write
completion, then postgresql can recover from complete and sudden loss
of power without any data loss.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-03-01 18:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2008-03-01 18:11 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Franck Routier <franck.routier@axege.com>; pgsql-performance
"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On Sat, Mar 1, 2008 at 4:27 AM, Franck Routier <franck.routier@axege.com> wrote:
>> Well, am I just wrong, or the file system might also heavily rely on
>> cache, especially as I use XFS ?
>>
>> So anyway Postgresql has no way to know if the data is really on the
>> disk, and in case of a brutal outage, the system may definitely lose
>> data, wether there is another level of caching (Raid controller) or
>> not...
> nope. assuming your disk subsystem doesn't lie about write
> completion, then postgresql can recover from complete and sudden loss
> of power without any data loss.
Franck does have a point here: we are expecting the filesystem to tend
to its own knitting. If a power failure corrupts the filesystem so
badly that we can't find the WAL files, or their contents are badly
scrambled, then we're screwed. Most modern filesystems defend
themselves against that using journaling, which is exactly the same
idea as WAL but applied to filesystem metadata.
We do expect that when we fsync a file, by the time the OS reports that
that's done both the file contents and its metadata are safely on disk.
This is part of the specification for fsync, so the OS is clearly broken
if it doesn't get that right. Whether the OS *can* guarantee it if the
disk drive lies about write completion is something you'd have to ask
the filesystem hackers about.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 12 disks raid setup
@ 2008-03-02 04:17 Greg Smith <gsmith@gregsmith.com>
parent: Franck Routier <franck.routier@axege.com>
1 sibling, 0 replies; 15+ messages in thread
From: Greg Smith @ 2008-03-02 04:17 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
On Sat, 1 Mar 2008, Franck Routier wrote:
> Well, am I just wrong, or the file system might also heavily rely on
> cache, especially as I use XFS ? So anyway Postgresql has no way to know
> if the data is really on the disk, and in case of a brutal outage, the
> system may definitely lose data, wether there is another level of
> caching (Raid controller) or not...
After PostgreSQL writes to the WAL, it calls fsync. If your filesystem
doesn't then force a real write to disk at that point and clear whatever
cache it might have, it's broken and unsuitable for database use. XFS is
smart enough to understand that.
The only thing people typically run into that will hear fsync and lie
about the data actually being written to disk are a) caching controllers
with the write cache turned on and b) cheap hard drives. In case (a),
having a battery backup for the cache is sufficient to survive most
classes of outage without damage--if the system is without power for
longer than the battery lasts you're in trouble, otherwise is shouldn't be
a problem. In case (b), you have to turn the disk cache off to get
reliable database operation.
I've put all the interesting trivia on this topic I've ever come across at
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm if
you're looking for some really exciting reading.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2008-03-02 04:17 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-29 11:51 12 disks raid setup Franck Routier <franck.routier@axege.com>
2008-02-29 14:13 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-29 14:51 ` Franck Routier <franck.routier@axege.com>
2008-02-29 15:41 ` Greg Smith <gsmith@gregsmith.com>
2008-02-29 22:33 ` Mark Kirkwood <markir@paradise.net.nz>
2008-03-01 00:44 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-03-01 04:56 ` Greg Smith <gsmith@gregsmith.com>
2008-03-01 10:27 ` Franck Routier <franck.routier@axege.com>
2008-03-01 12:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-01 18:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-02 04:17 ` Greg Smith <gsmith@gregsmith.com>
2008-02-29 18:58 ` Vivek Khera <vivek@khera.org>
2008-02-29 17:17 ` Jignesh K. Shah <J.K.Shah@sun.com>
2008-02-29 17:50 ` Joshua D. Drake <jd@commandprompt.com>
2008-02-29 18:24 ` Matthew <matthew@flymine.org>
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