agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedSeparate volumes
6+ messages / 5 participants
[nested] [flat]
* Separate volumes
@ 2020-04-06 11:51 Ed Behn <ed.behn@collins.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ed Behn @ 2020-04-06 11:51 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
I was once told that it's best practice to store tables and indexes in
separate tablespaces located on separate physical drives. It seemed logical
that this should improve performance because the read-head wouldn't need to
jump back and forth between a table and its index.
However, I can't seem to find this advice anywhere online. Is it indeed
best practice? Is it worth the hassle?
-Ed
Ed Behn | Senior Systems Engineer | Avionics
COLLINS ÆROSPACE
2551 Riva Road, Annapolis, MD 21401 USA
Tel: +1 410 266 4426 | Mobile: +1 240 696 7443
ed.behn@collins.com | collinsaerospace.com
CONFIDENTIALITY WARNING: This message may contain proprietary and/or
privileged information of Collins Aerospace and its affiliated companies.
If you are not the intended recipient, please 1) Do not disclose, copy,
distribute or use this message or its contents. 2) Advise the sender by
return email. 3) Delete all copies (including all attachments) from your
computer. Your cooperation is greatly appreciated.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Separate volumes
@ 2020-04-06 16:41 Erik Brandsberg <erik@heimdalldata.com>
parent: Ed Behn <ed.behn@collins.com>
0 siblings, 1 reply; 6+ messages in thread
From: Erik Brandsberg @ 2020-04-06 16:41 UTC (permalink / raw)
To: Ed Behn <ed.behn@collins.com>; +Cc: pgsql-sql@lists.postgresql.org
With SSD and it's random IO performance, I doubt that this advice would
apply as much, and adds complexity to your configuration and management.
In particular if you use any filesystem level snapshotting (like with ZFS),
splitting the filespaces will make it harder to do restores and using
snapshots.
On Mon, Apr 6, 2020 at 10:40 AM Ed Behn <ed.behn@collins.com> wrote:
> I was once told that it's best practice to store tables and indexes in
> separate tablespaces located on separate physical drives. It seemed logical
> that this should improve performance because the read-head wouldn't need to
> jump back and forth between a table and its index.
>
> However, I can't seem to find this advice anywhere online. Is it indeed
> best practice? Is it worth the hassle?
>
> -Ed
>
> Ed Behn | Senior Systems Engineer | Avionics
>
> COLLINS ÆROSPACE
>
> 2551 Riva Road, Annapolis, MD 21401 USA
>
> Tel: +1 410 266 4426 | Mobile: +1 240 696 7443
>
> ed.behn@collins.com | collinsaerospace.com
>
> CONFIDENTIALITY WARNING: This message may contain proprietary and/or
> privileged information of Collins Aerospace and its affiliated companies.
> If you are not the intended recipient, please 1) Do not disclose, copy,
> distribute or use this message or its contents. 2) Advise the sender by
> return email. 3) Delete all copies (including all attachments) from your
> computer. Your cooperation is greatly appreciated.
>
>
--
*Erik Brandsberg*
erik@heimdalldata.com
www.heimdalldata.com
+1 (866) 433-2824 x 700
[image: AWS Competency Program]
<https://aws.amazon.com/partners/find/partnerdetails/?n=Heimdall%20Data&id=001E000001d9pndIAA;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Separate volumes
@ 2020-04-06 17:11 Steve Midgley <science@misuse.org>
parent: Erik Brandsberg <erik@heimdalldata.com>
0 siblings, 1 reply; 6+ messages in thread
From: Steve Midgley @ 2020-04-06 17:11 UTC (permalink / raw)
To: Erik Brandsberg <erik@heimdalldata.com>; +Cc: Ed Behn <ed.behn@collins.com>; pgsql-sql@lists.postgresql.org
On Mon, Apr 6, 2020 at 9:42 AM Erik Brandsberg <erik@heimdalldata.com>
wrote:
> With SSD and it's random IO performance, I doubt that this advice would
> apply as much, and adds complexity to your configuration and management.
> In particular if you use any filesystem level snapshotting (like with ZFS),
> splitting the filespaces will make it harder to do restores and using
> snapshots.
>
> On Mon, Apr 6, 2020 at 10:40 AM Ed Behn <ed.behn@collins.com> wrote:
>
>> I was once told that it's best practice to store tables and indexes in
>> separate tablespaces located on separate physical drives. It seemed logical
>> that this should improve performance because the read-head wouldn't need to
>> jump back and forth between a table and its index.
>>
>> However, I can't seem to find this advice anywhere online. Is it indeed
>> best practice? Is it worth the hassle?
>>
>>
>>
>
> As a general and practical matter I 100% agree with Erik -- the advice is
a bit out of date, and for SSDs it probably makes no meaningful difference.
However for extremely high, sustained workloads, you might find splitting
tables, indices, and transaction logs onto separate disk _disk arrays and
controllers_ could yield improvements, particularly for certain RAID
setups. But maxing out a disk controller is pretty hard to do (impossible
afaik with a single drive), so you'd want to have some strong metrics to
show this is worth it. At that point, you'd probably be better off getting
commercial disk array solutions into the mix rather than rolling your own
anyway..
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Separate volumes
@ 2020-04-06 19:30 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Steve Midgley <science@misuse.org>
0 siblings, 1 reply; 6+ messages in thread
From: MichaelDBA @ 2020-04-06 19:30 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: Erik Brandsberg <erik@heimdalldata.com>; Ed Behn <ed.behn@collins.com>; pgsql-sql@lists.postgresql.org
Hi Steve,
Coming from oracle land, tablespaces play a bigger role than they do in
PG land. In PG land, they can control the mapping of tables/indexes to
faster or slower devices. By separating a table's tablespace from its
index tablespace, you may get more parallel I/O. They also allow for
flexibility in setting pg config parameters per tablespace:
|alter tablespace mytablespace set ( seq_page_cost=0.5,
random_page_cost=0.5 ); |
But they also can be a headache in managing stuff. For instance, all
replicas must have the same directory structure and symlinks.
Regards,
Michael Vitale
Steve Midgley wrote on 4/6/2020 1:11 PM:
>
>
> On Mon, Apr 6, 2020 at 9:42 AM Erik Brandsberg <erik@heimdalldata.com
> <mailto:erik@heimdalldata.com>> wrote:
>
> With SSD and it's random IO performance, I doubt that this advice
> would apply as much, and adds complexity to your configuration and
> management. In particular if you use any filesystem level
> snapshotting (like with ZFS), splitting the filespaces will make
> it harder to do restores and using snapshots.
>
> On Mon, Apr 6, 2020 at 10:40 AM Ed Behn <ed.behn@collins.com
> <mailto:ed.behn@collins.com>> wrote:
>
> I was once told that it's best practice to store tables and
> indexes in separate tablespaces located on separate physical
> drives. It seemed logical that this should improve
> performance because the read-head wouldn't need to jump back
> and forth between a table and its index.
>
> However, I can't seem to find this advice anywhere online. Is
> it indeed best practice? Is it worth the hassle?
>
>
>
> As a general and practical matter I 100% agree with Erik -- the advice
> is a bit out of date, and for SSDs it probably makes no meaningful
> difference. However for extremely high, sustained workloads, you might
> find splitting tables, indices, and transaction logs onto separate
> disk _disk arrays and controllers_ could yield improvements,
> particularly for certain RAID setups. But maxing out a disk controller
> is pretty hard to do (impossible afaik with a single drive), so you'd
> want to have some strong metrics to show this is worth it. At that
> point, you'd probably be better off getting commercial disk array
> solutions into the mix rather than rolling your own anyway..
>
> Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [External] Re: Separate volumes
@ 2020-04-06 19:36 Ed Behn <ed.behn@collins.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ed Behn @ 2020-04-06 19:36 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Steve Midgley <science@misuse.org>; Erik Brandsberg <erik@heimdalldata.com>; pgsql-sql@lists.postgresql.org
That makes sense. The person who told me this was very experienced with
Oracle but was a PG novice.
-Ed
Ed Behn | Senior Systems Engineer | Avionics
COLLINS ÆROSPACE
2551 Riva Road, Annapolis, MD 21401 USA
Tel: +1 410 266 4426 | Mobile: +1 240 696 7443
ed.behn@collins.com | collinsaerospace.com
CONFIDENTIALITY WARNING: This message may contain proprietary and/or
privileged information of Collins Aerospace and its affiliated companies.
If you are not the intended recipient, please 1) Do not disclose, copy,
distribute or use this message or its contents. 2) Advise the sender by
return email. 3) Delete all copies (including all attachments) from your
computer. Your cooperation is greatly appreciated.
On Mon, Apr 6, 2020 at 3:33 PM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> Hi Steve,
>
> Coming from oracle land, tablespaces play a bigger role than they do in PG
> land. In PG land, they can control the mapping of tables/indexes to faster
> or slower devices. By separating a table's tablespace from its index
> tablespace, you may get more parallel I/O. They also allow for flexibility
> in setting pg config parameters per tablespace:
>
> alter tablespace mytablespace set ( seq_page_cost=0.5, random_page_cost=0.5 );
>
> But they also can be a headache in managing stuff. For instance, all
> replicas must have the same directory structure and symlinks.
>
> Regards,
> Michael Vitale
>
>
>
> Steve Midgley wrote on 4/6/2020 1:11 PM:
>
>
>
> On Mon, Apr 6, 2020 at 9:42 AM Erik Brandsberg <erik@heimdalldata.com>
> wrote:
>
>> With SSD and it's random IO performance, I doubt that this advice would
>> apply as much, and adds complexity to your configuration and management.
>> In particular if you use any filesystem level snapshotting (like with ZFS),
>> splitting the filespaces will make it harder to do restores and using
>> snapshots.
>>
>> On Mon, Apr 6, 2020 at 10:40 AM Ed Behn <ed.behn@collins.com> wrote:
>>
>>> I was once told that it's best practice to store tables and indexes in
>>> separate tablespaces located on separate physical drives. It seemed logical
>>> that this should improve performance because the read-head wouldn't need to
>>> jump back and forth between a table and its index.
>>>
>>> However, I can't seem to find this advice anywhere online. Is it indeed
>>> best practice? Is it worth the hassle?
>>>
>>>
>>>
>>
>> As a general and practical matter I 100% agree with Erik -- the advice is
> a bit out of date, and for SSDs it probably makes no meaningful difference.
> However for extremely high, sustained workloads, you might find splitting
> tables, indices, and transaction logs onto separate disk _disk arrays and
> controllers_ could yield improvements, particularly for certain RAID
> setups. But maxing out a disk controller is pretty hard to do (impossible
> afaik with a single drive), so you'd want to have some strong metrics to
> show this is worth it. At that point, you'd probably be better off getting
> commercial disk array solutions into the mix rather than rolling your own
> anyway..
>
> Steve
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [External] Re: Separate volumes
@ 2020-04-06 21:40 Iuri Sampaio <iuri.sampaio@gmail.com>
parent: Ed Behn <ed.behn@collins.com>
0 siblings, 0 replies; 6+ messages in thread
From: Iuri Sampaio @ 2020-04-06 21:40 UTC (permalink / raw)
To: Ed Behn <ed.behn@collins.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Steve Midgley <science@misuse.org>; Erik Brandsberg <erik@heimdalldata.com>; pgsql-sql@lists.postgresql.org
Hi Ed,
We’d need more information (numbers, characteristics, statistics, workflow, payload, etc), about your environment, in order to give you a better answer.
However, a simple rule for better performance is: one must alway look for the balance (i.e. equilibrium) between those two setups. Meaning, you can choose to storage tables and index, that are more accessed, in the same tablespace, and the other datamodel (tables and indexes), which are less accessed in different tablespaces.
That would increase complexity, however, it will give you better performance. But again, we don’t know your need and numbers in details, to give you the best metrics.
Furthermore, you can always create plsql procedures (weather in Oracle or PGSQL) to keep the complexity in a separate layer, avoiding the overload of work to you server side programmers.
Anyway, that isn’t a yes/no question indeed.
Hope that helps
Best wishes,
I
> On Apr 6, 2020, at 16:36, Ed Behn <ed.behn@collins.com> wrote:
>
>
> That makes sense. The person who told me this was very experienced with Oracle but was a PG novice.
> -Ed
>
> Ed Behn | Senior Systems Engineer | Avionics
> COLLINS ÆROSPACE
> 2551 Riva Road, Annapolis, MD 21401 USA
> Tel: +1 410 266 4426 | Mobile: +1 240 696 7443
> ed.behn@collins.com <mailto:ed.behn@collins.com> | collinsaerospace.com <https://collinsaerospace.com/;
>
> CONFIDENTIALITY WARNING: This message may contain proprietary and/or privileged information of Collins Aerospace and its affiliated companies. If you are not the intended recipient, please 1) Do not disclose, copy, distribute or use this message or its contents. 2) Advise the sender by return email. 3) Delete all copies (including all attachments) from your computer. Your cooperation is greatly appreciated.
>
>
>
> On Mon, Apr 6, 2020 at 3:33 PM MichaelDBA <MichaelDBA@sqlexec.com <mailto:MichaelDBA@sqlexec.com>> wrote:
> Hi Steve,
>
> Coming from oracle land, tablespaces play a bigger role than they do in PG land. In PG land, they can control the mapping of tables/indexes to faster or slower devices. By separating a table's tablespace from its index tablespace, you may get more parallel I/O. They also allow for flexibility in setting pg config parameters per tablespace:
>
> alter tablespace mytablespace set ( seq_page_cost=0.5, random_page_cost=0.5 );
> But they also can be a headache in managing stuff. For instance, all replicas must have the same directory structure and symlinks.
>
> Regards,
> Michael Vitale
>
>
>
> Steve Midgley wrote on 4/6/2020 1:11 PM:
>>
>>
>> On Mon, Apr 6, 2020 at 9:42 AM Erik Brandsberg <erik@heimdalldata.com <mailto:erik@heimdalldata.com>> wrote:
>> With SSD and it's random IO performance, I doubt that this advice would apply as much, and adds complexity to your configuration and management. In particular if you use any filesystem level snapshotting (like with ZFS), splitting the filespaces will make it harder to do restores and using snapshots.
>>
>> On Mon, Apr 6, 2020 at 10:40 AM Ed Behn <ed.behn@collins.com <mailto:ed.behn@collins.com>> wrote:
>> I was once told that it's best practice to store tables and indexes in separate tablespaces located on separate physical drives. It seemed logical that this should improve performance because the read-head wouldn't need to jump back and forth between a table and its index.
>>
>> However, I can't seem to find this advice anywhere online. Is it indeed best practice? Is it worth the hassle?
>>
>>
>>
>> As a general and practical matter I 100% agree with Erik -- the advice is a bit out of date, and for SSDs it probably makes no meaningful difference. However for extremely high, sustained workloads, you might find splitting tables, indices, and transaction logs onto separate disk _disk arrays and controllers_ could yield improvements, particularly for certain RAID setups. But maxing out a disk controller is pretty hard to do (impossible afaik with a single drive), so you'd want to have some strong metrics to show this is worth it. At that point, you'd probably be better off getting commercial disk array solutions into the mix rather than rolling your own anyway..
>>
>> Steve
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-04-06 21:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-06 11:51 Separate volumes Ed Behn <ed.behn@collins.com>
2020-04-06 16:41 ` Erik Brandsberg <erik@heimdalldata.com>
2020-04-06 17:11 ` Steve Midgley <science@misuse.org>
2020-04-06 19:30 ` MichaelDBA <MichaelDBA@sqlexec.com>
2020-04-06 19:36 ` Ed Behn <ed.behn@collins.com>
2020-04-06 21:40 ` Iuri Sampaio <iuri.sampaio@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox