pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTablespaces on a raid configuration
8+ messages / 8 participants
[nested] [flat]
* Tablespaces on a raid configuration
@ 2012-03-30 14:45 Campbell, Lance <lance@illinois.edu>
2012-03-30 14:53 ` Re: Tablespaces on a raid configuration ktm@rice.edu <ktm@rice.edu>
2012-03-30 15:02 ` Re: Tablespaces on a raid configuration Greg Spiegelberg <gspiegelberg@gmail.com>
2012-03-30 15:02 ` Re: Tablespaces on a raid configuration Andrew Dunstan <andrew@dunslane.net>
0 siblings, 3 replies; 8+ messages in thread
From: Campbell, Lance @ 2012-03-30 14:45 UTC (permalink / raw)
To: pgsql-performance
PostgreSQL 9.0.x
When PostgreSQL storage is using a relatively large raid 5 or 6 array is there any value in having your tables distributed across multiple tablespaces if those tablespaces will exists on the same raid array? I understand the value if you were to have the tablespaces on different raid arrays. But what about on the same one?
Thanks,
Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Tablespaces on a raid configuration
2012-03-30 14:45 Tablespaces on a raid configuration Campbell, Lance <lance@illinois.edu>
@ 2012-03-30 14:53 ` ktm@rice.edu <ktm@rice.edu>
2012-03-30 17:30 ` Re: Tablespaces on a raid configuration Craig James <cjames@emolecules.com>
2012-03-31 01:32 ` Re: Tablespaces on a raid configuration Tomas Vondra <tv@fuzzy.cz>
2 siblings, 2 replies; 8+ messages in thread
From: ktm@rice.edu @ 2012-03-30 14:53 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-performance
On Fri, Mar 30, 2012 at 02:45:36PM +0000, Campbell, Lance wrote:
> PostgreSQL 9.0.x
> When PostgreSQL storage is using a relatively large raid 5 or 6 array is there any value in having your tables distributed across multiple tablespaces if those tablespaces will exists on the same raid array? I understand the value if you were to have the tablespaces on different raid arrays. But what about on the same one?
>
>
> Thanks,
>
> Lance Campbell
> Software Architect
> Web Services at Public Affairs
> 217-333-0382
>
I have seen previous discussions about using different filesystems versus
a single filesystem and one advantage that multiple tablespaces have is
that an fsync on one table/tablespace would not block or be blocked by
an fsync on a different table/tablespace at the OS level.
Regards,
Ken
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Tablespaces on a raid configuration
2012-03-30 14:45 Tablespaces on a raid configuration Campbell, Lance <lance@illinois.edu>
2012-03-30 14:53 ` Re: Tablespaces on a raid configuration ktm@rice.edu <ktm@rice.edu>
@ 2012-03-30 17:30 ` Craig James <cjames@emolecules.com>
1 sibling, 0 replies; 8+ messages in thread
From: Craig James @ 2012-03-30 17:30 UTC (permalink / raw)
To: ktm@rice.edu <ktm@rice.edu>; +Cc: Campbell, Lance <lance@illinois.edu>; pgsql-performance
On Fri, Mar 30, 2012 at 7:53 AM, ktm@rice.edu <ktm@rice.edu> wrote:
> On Fri, Mar 30, 2012 at 02:45:36PM +0000, Campbell, Lance wrote:
>> PostgreSQL 9.0.x
>> When PostgreSQL storage is using a relatively large raid 5 or 6 array is there any value in having your tables distributed across multiple tablespaces if those tablespaces will exists on the same raid array? I understand the value if you were to have the tablespaces on different raid arrays. But what about on the same one?
>>
>>
>> Thanks,
>>
>> Lance Campbell
>> Software Architect
>> Web Services at Public Affairs
>> 217-333-0382
>>
>
> I have seen previous discussions about using different filesystems versus
> a single filesystem and one advantage that multiple tablespaces have is
> that an fsync on one table/tablespace would not block or be blocked by
> an fsync on a different table/tablespace at the OS level.
Another advantage is that you can use a non-journaling FS for the WAL
(ext2) and a journaling FS for the data (ext4 etc.). I was told that
there's no reason to use a journaling fs for the WAL since the WAL is
a journal.
Craig
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Tablespaces on a raid configuration
2012-03-30 14:45 Tablespaces on a raid configuration Campbell, Lance <lance@illinois.edu>
2012-03-30 14:53 ` Re: Tablespaces on a raid configuration ktm@rice.edu <ktm@rice.edu>
@ 2012-03-31 01:32 ` Tomas Vondra <tv@fuzzy.cz>
2012-03-31 13:42 ` Re: Tablespaces on a raid configuration Aidan Van Dyk <aidan@highrise.ca>
1 sibling, 1 reply; 8+ messages in thread
From: Tomas Vondra @ 2012-03-31 01:32 UTC (permalink / raw)
To: pgsql-performance
On 30.3.2012 16:53, ktm@rice.edu wrote:
> On Fri, Mar 30, 2012 at 02:45:36PM +0000, Campbell, Lance wrote:
>> PostgreSQL 9.0.x
>> When PostgreSQL storage is using a relatively large raid 5 or 6 array is there any value in having your tables distributed across multiple tablespaces if those tablespaces will exists on the same raid array? I understand the value if you were to have the tablespaces on different raid arrays. But what about on the same one?
>>
>>
>> Thanks,
>>
>> Lance Campbell
>> Software Architect
>> Web Services at Public Affairs
>> 217-333-0382
>>
>
> I have seen previous discussions about using different filesystems versus
> a single filesystem and one advantage that multiple tablespaces have is
> that an fsync on one table/tablespace would not block or be blocked by
> an fsync on a different table/tablespace at the OS level.
No. What matters is a physical device. If you have a drive that can do
just 120 seeks/fsyncs per second (or more, depends on the speed), then
even if you divide that into multiple filesystems you're still stuck
with the total of 120 seeks. I.e. splitting that into 10 partitions
won't give you 1200 seeks ...
OP mentions he's using RAID-5 or 6 - that's pretty bad because it
effectively creates one huge device. Splitting this into filesystem will
behave rather bad, because all the drives are rather tightly coupled
because of to the parity.
If you can create the filesystems on different devices, then you're
golden and this can really help.
And it's not just about fsync operations - WAL is written in sequential
manner. By placing it on the same device as data files you're
effectively forcing it to be written randomly, because the the database
has to write a WAL record, seeks somewhere else to read something, etc.
Tomas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Tablespaces on a raid configuration
2012-03-30 14:45 Tablespaces on a raid configuration Campbell, Lance <lance@illinois.edu>
2012-03-30 14:53 ` Re: Tablespaces on a raid configuration ktm@rice.edu <ktm@rice.edu>
2012-03-31 01:32 ` Re: Tablespaces on a raid configuration Tomas Vondra <tv@fuzzy.cz>
@ 2012-03-31 13:42 ` Aidan Van Dyk <aidan@highrise.ca>
0 siblings, 0 replies; 8+ messages in thread
From: Aidan Van Dyk @ 2012-03-31 13:42 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
On Fri, Mar 30, 2012 at 9:32 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> And it's not just about fsync operations - WAL is written in sequential
> manner. By placing it on the same device as data files you're
> effectively forcing it to be written randomly, because the the database
> has to write a WAL record, seeks somewhere else to read something, etc.
Or, if you put WAL on a journalled FS, even if it's on dedicated spindles ;-)
a.
--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Tablespaces on a raid configuration
2012-03-30 14:45 Tablespaces on a raid configuration Campbell, Lance <lance@illinois.edu>
@ 2012-03-30 15:02 ` Greg Spiegelberg <gspiegelberg@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Greg Spiegelberg @ 2012-03-30 15:02 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-performance
On Fri, Mar 30, 2012 at 8:45 AM, Campbell, Lance <lance@illinois.edu> wrote:
> PostgreSQL 9.0.x****
>
> When PostgreSQL storage is using a relatively large raid 5 or 6 array is
> there any value in having your tables distributed across multiple
> tablespaces if those tablespaces will exists on the same raid array? I
> understand the value if you were to have the tablespaces on different raid
> arrays. But what about on the same one?****
>
>
>
Our application is a combination of OLTP and OLAP. We've successfully
split the database into 3 different tablespaces.
1. RAID Group A is RAID 10 contains /var/lib/pgsql/data and the OLTP
database (default tablespace)
2. RAID Group B is a RAID 10 for the indexes on the data warehouse (index
tablespace)
3. RAID Group C is a RAID 5 containing the actual data warehouse (data
tablespace)
A more optimum configuration would include another RAID 10 for the indexes
for the OLTP but we ran out of drives to create a RAID Group D and the
above configuration works well enough.
Before going with RAID 5, please review http://www.baarf.com/.
-Greg
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Tablespaces on a raid configuration
2012-03-30 14:45 Tablespaces on a raid configuration Campbell, Lance <lance@illinois.edu>
@ 2012-03-30 15:02 ` Andrew Dunstan <andrew@dunslane.net>
2012-03-30 16:11 ` Re: Tablespaces on a raid configuration Merlin Moncure <mmoncure@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Andrew Dunstan @ 2012-03-30 15:02 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-performance
On 03/30/2012 10:45 AM, Campbell, Lance wrote:
>
> PostgreSQL 9.0.x
>
> When PostgreSQL storage is using a relatively large raid 5 or 6
> array is there any value in having your tables distributed across
> multiple tablespaces if those tablespaces will exists on the same raid
> array? I understand the value if you were to have the tablespaces on
> different raid arrays. But what about on the same one?
>
>
Not answering your question, but standard advice is not to use RAID 5 or
6, but RAID 10 for databases. Not sure if that still hold if you're
using SSDs.
cheers
andrew
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Tablespaces on a raid configuration
2012-03-30 14:45 Tablespaces on a raid configuration Campbell, Lance <lance@illinois.edu>
2012-03-30 15:02 ` Re: Tablespaces on a raid configuration Andrew Dunstan <andrew@dunslane.net>
@ 2012-03-30 16:11 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Merlin Moncure @ 2012-03-30 16:11 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Campbell, Lance <lance@illinois.edu>; pgsql-performance
On Fri, Mar 30, 2012 at 10:02 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> Not answering your question, but standard advice is not to use RAID 5 or 6,
> but RAID 10 for databases. Not sure if that still hold if you're using SSDs.
Yeah, for SSD the equations may change. Parity based RAID has two
problems: performance due to writes having to do a read before writing
in order to calculate parity and safety (especially for raid 5) since
you are at greater risk of having a second drive pop while you're
rebuilding your volume. In both things the SSD might significantly
reduce the negative impacts: read and write performance are highly
asymmetric greatly reducing or even eliminating observed cost of the
'write hole'. Also, huge sequential speeds and generally smaller
device sizes mean very rapid rebuild time. Also, higher cost/gb can
play in. Food for thought.
merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2012-03-31 13:42 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-30 14:45 Tablespaces on a raid configuration Campbell, Lance <lance@illinois.edu>
2012-03-30 14:53 ` ktm@rice.edu <ktm@rice.edu>
2012-03-30 17:30 ` Craig James <cjames@emolecules.com>
2012-03-31 01:32 ` Tomas Vondra <tv@fuzzy.cz>
2012-03-31 13:42 ` Aidan Van Dyk <aidan@highrise.ca>
2012-03-30 15:02 ` Greg Spiegelberg <gspiegelberg@gmail.com>
2012-03-30 15:02 ` Andrew Dunstan <andrew@dunslane.net>
2012-03-30 16:11 ` Merlin Moncure <mmoncure@gmail.com>
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