agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: MichaelDBA <MichaelDBA@sqlexec.com>
To: Steve Midgley <science@misuse.org>
Cc: Erik Brandsberg <erik@heimdalldata.com>
Cc: Ed Behn <ed.behn@collins.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Separate volumes
Date: Mon, 6 Apr 2020 15:30:57 -0400
Message-ID: <5b60ae44-33e0-d9ea-d6e9-c6db9b4c7ef1@sqlexec.com> (raw)
In-Reply-To: <CAJexoSLSHRVOKzVGD-pDHE=KY9un7bPsxGO6fSKZVgeGsj2t6g@mail.gmail.com>
References: <CAE1kc7VfQVz0VGf4cyjaY5KYSZxrPQjPWMoEsqFTJ41-LkMOHg@mail.gmail.com>
<CAFcck8FF4T3_PL7Fa+iW5YBkA4iZ3aR2V_jka=GpV9taPph9NQ@mail.gmail.com>
<CAJexoSLSHRVOKzVGD-pDHE=KY9un7bPsxGO6fSKZVgeGsj2t6g@mail.gmail.com>
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
view thread (6+ messages) latest in thread
Message-ID: <5b60ae44-33e0-d9ea-d6e9-c6db9b4c7ef1@sqlexec.com>
Permalink: ../5b60ae44-33e0-d9ea-d6e9-c6db9b4c7ef1@sqlexec.com/
Also on: postgresql.org/message-id/5b60ae44-33e0-d9ea-d6e9-c6db9b4c7ef1@sqlexec.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: MichaelDBA@sqlexec.com, science@misuse.org, erik@heimdalldata.com, ed.behn@collins.com, pgsql-sql@lists.postgresql.org
Subject: Re: Separate volumes
In-Reply-To: <5b60ae44-33e0-d9ea-d6e9-c6db9b4c7ef1@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox