Thread
-
Separate volumes
Ed Behn <ed.behn@collins.com> — 2020-04-06T11:51:15Z
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. -
Re: Separate volumes
Erik Brandsberg <erik@heimdalldata.com> — 2020-04-06T16:41:48Z
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>
-
Re: Separate volumes
Steve Midgley <science@misuse.org> — 2020-04-06T17:11:39Z
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
-
Re: Separate volumes
MichaelDBA <michaeldba@sqlexec.com> — 2020-04-06T19:30:57Z
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
-
Re: [External] Re: Separate volumes
Ed Behn <ed.behn@collins.com> — 2020-04-06T19:36:41Z
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 > > > -
Re: [External] Re: Separate volumes
Iuri Sampaio <iuri.sampaio@gmail.com> — 2020-04-06T21:40:24Z
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 >
-
回复: [External] Re: Separate volumes
Lu Dillon <ludi_1981@hotmail.com> — 2020-04-07T16:31:51Z
Hi All, This is a very intersting question. I believe this is not just a best practice to PG. We can apply to all RDBMS. In my opinion, I agree with the others: with SSD, you don’t separate tables and indexs to different disks. I think the IOPS is enough. If you still have a problem of IOPS, you can try NVME device or U2 device. Thanks, Dillon 发送自 Windows 10 版邮件<https://go.microsoft.com/fwlink/?LinkId=550986>应用 发件人: Iuri Sampaio<mailto:iuri.sampaio@gmail.com> 发送时间: 2020年4月7日 5:40 收件人: Ed Behn<mailto:ed.behn@collins.com> 抄送: MichaelDBA<mailto:MichaelDBA@sqlexec.com>; Steve Midgley<mailto:science@misuse.org>; Erik Brandsberg<mailto:erik@heimdalldata.com>; pgsql-sql@lists.postgresql.org<mailto:pgsql-sql@lists.postgresql.org> 主题: Re: [External] Re: Separate volumes 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<mailto: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 -
Re: 回复: [External] Re: Separate volumes
Bruce Momjian <bruce@momjian.us> — 2020-04-10T20:04:20Z
On Tue, Apr 7, 2020 at 04:31:51PM +0000, Lu Dillon wrote: > Hi All, > > This is a very intersting question. I believe this is not just a best practice > to PG. We can apply to all RDBMS. In my opinion, I agree with the others: with > SSD, you don’t separate tables and indexs to different disks. I think the IOPS > is enough. If you still have a problem of IOPS, you can try NVME device or U2 > device. If you are mixing magnetic and SSDs for the same database, having indexes on SSDs can really help, compared to table files on SSDs, where the benefit is more limited. Also, having current data on SSDs and archive data on magnetic is also useful, and you usually use time-based partitioning for such cases. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
-
Re: 回复: [External] Re: Separate volumes
Erik Brandsberg <erik@heimdalldata.com> — 2020-04-10T20:52:06Z
A modern filesystem can help avoid even this complexity. As an example, I am managing one PG setup that is self-hosted on an AWS EC2 instance, with 16TB of raw storage. The bulk of that storage is in ST1, or the cheapest rotating disk capacity available in EBS, but is using ZFS as the filesystem (with compression, so realistically about 35TB of raw data). The instance type is a Z1d.metal, which has two 900GB NVME drives, which have been divided to provide swap space, as well as ZFS read and write caching. This setup has largely offset the slow performance of the st1 disks, and kept the performance usable (most of the data is legacy, and rarely used). I'm a big fan of keeping the DB configuration simple, as it is way too easy to overlook tuning of a filespace for an index, causing performance problems, while if you keep it auto-tuning at the filesystem level, it "just works". Must my $.02 On Fri, Apr 10, 2020 at 4:04 PM Bruce Momjian <bruce@momjian.us> wrote: > On Tue, Apr 7, 2020 at 04:31:51PM +0000, Lu Dillon wrote: > > Hi All, > > > > This is a very intersting question. I believe this is not just a best > practice > > to PG. We can apply to all RDBMS. In my opinion, I agree with the > others: with > > SSD, you don’t separate tables and indexs to different disks. I think > the IOPS > > is enough. If you still have a problem of IOPS, you can try NVME device > or U2 > > device. > > If you are mixing magnetic and SSDs for the same database, having > indexes on SSDs can really help, compared to table files on SSDs, where > the benefit is more limited. Also, having current data on SSDs and > archive data on magnetic is also useful, and you usually use > time-based partitioning for such cases. > > -- > Bruce Momjian <bruce@momjian.us> https://momjian.us > EnterpriseDB https://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > -- *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>
-
Re: 回复: [External] Re: Separate volumes
Bruce Momjian <bruce@momjian.us> — 2020-04-10T21:01:54Z
On Fri, Apr 10, 2020 at 04:52:06PM -0400, Erik Brandsberg wrote: > A modern filesystem can help avoid even this complexity. As an example, I am > managing one PG setup that is self-hosted on an AWS EC2 instance, with 16TB of > raw storage. The bulk of that storage is in ST1, or the cheapest rotating disk > capacity available in EBS, but is using ZFS as the filesystem (with > compression, so realistically about 35TB of raw data). The instance type is a > Z1d.metal, which has two 900GB NVME drives, which have been divided to provide > swap space, as well as ZFS read and write caching. This setup has largely > offset the slow performance of the st1 disks, and kept the performance usable > (most of the data is legacy, and rarely used). I'm a big fan of keeping the > DB configuration simple, as it is way too easy to overlook tuning of a > filespace for an index, causing performance problems, while if you keep it > auto-tuning at the filesystem level, it "just works". You are saying the cloud automatically moves data between the fast and slow storage? I know many NAS systems do this, but I have also seen problems when NAS systems guess wrong. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
-
Re: 回复: [External] Re: Separate volumes
Erik Brandsberg <erik@heimdalldata.com> — 2020-04-10T21:26:07Z
It isn't exactly two-tiered storage, all data gets written to the underlying disk as quickly as it can, but the random IO that indexes use will end up in the local cache, so to provide faster access. Writes will take a temporary stop in the local nvme as well, so that even if it takes a long time to write to the disk, the upstream DB can move on to the next task. This provides the effective benefits of splitting the index on local ssd, while simplifying the management and backup. On Fri, Apr 10, 2020 at 5:01 PM Bruce Momjian <bruce@momjian.us> wrote: > On Fri, Apr 10, 2020 at 04:52:06PM -0400, Erik Brandsberg wrote: > > A modern filesystem can help avoid even this complexity. As an example, > I am > > managing one PG setup that is self-hosted on an AWS EC2 instance, with > 16TB of > > raw storage. The bulk of that storage is in ST1, or the cheapest > rotating disk > > capacity available in EBS, but is using ZFS as the filesystem (with > > compression, so realistically about 35TB of raw data). The instance > type is a > > Z1d.metal, which has two 900GB NVME drives, which have been divided to > provide > > swap space, as well as ZFS read and write caching. This setup has > largely > > offset the slow performance of the st1 disks, and kept the performance > usable > > (most of the data is legacy, and rarely used). I'm a big fan of > keeping the > > DB configuration simple, as it is way too easy to overlook tuning of a > > filespace for an index, causing performance problems, while if you keep > it > > auto-tuning at the filesystem level, it "just works". > > You are saying the cloud automatically moves data between the fast and > slow storage? I know many NAS systems do this, but I have also seen > problems when NAS systems guess wrong. > > -- > Bruce Momjian <bruce@momjian.us> https://momjian.us > EnterpriseDB https://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > -- *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>