Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jLZTx-0004aw-86 for pgsql-sql@arkaria.postgresql.org; Mon, 06 Apr 2020 21:40:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jLZTw-0004lX-6J for pgsql-sql@arkaria.postgresql.org; Mon, 06 Apr 2020 21:40:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jLZTv-0004lQ-KG for pgsql-sql@lists.postgresql.org; Mon, 06 Apr 2020 21:40:35 +0000 Received: from mail-qv1-xf2c.google.com ([2607:f8b0:4864:20::f2c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jLZTr-0003g6-0B for pgsql-sql@lists.postgresql.org; Mon, 06 Apr 2020 21:40:34 +0000 Received: by mail-qv1-xf2c.google.com with SMTP id q73so845314qvq.2 for ; Mon, 06 Apr 2020 14:40:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=X4Pul8UaQI7eVPLZ9T6bCXDmLvBkpXDkfF2cHKU5QdA=; b=l/0xIIl2mp9m9ACLBfoMgamzHb/Vm0WID+Nl/oek39YQ41i15XpjZpCvbfEyHnHvJT hHcTwRBUzLR27SiDSfBBwuqmMIBcnZ1ZU6qyAUbq2qgzmJ36VeBeXgFTOBZjiD6K7d9V pJF3fxzp2MNqAGv78l6P0OgjHeUNqS+Xu9nt86ao30R3PiamPRVhyD41vtDumojiy6qA e7xR5vvLhcpvv1WEdT3S8k7i8mDl+lgk6RHnGrY2DMg2pJJS5I9sJ5Q8xK5RHTJbXgnz Q5OljCyLO7U2vcbgBm8X8ofRC4DJT88ljj2JG/6FPmhh9rzPVHQS+sfFtQywPJ2f9Ir6 FzUg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=X4Pul8UaQI7eVPLZ9T6bCXDmLvBkpXDkfF2cHKU5QdA=; b=EkMOxX+MMoADvowRojf53CHxhZsmFIzkae/KNabYJybNZRW2Mm7L1UeKpUMlNZ3feL QxBZlcHLCptnK0B6UHRF0aVWMmVXaiTEq3hc+n4Dbdf7Co5lLLd1rOu9TtLZ3ScV2oq+ LgT8eAXcAehyQnAp3SUTJVw+0e8ehs5axCIxSbFkazPtIcJHKZbWzDT69DrkVqx/f0rq dp6cn+ZVrPitBrPWjdgqOkWdtwZMloWuy+fifh0NDC6qEo+NW2eCvkI6vBmmK5nt9wyZ b/RQfzYjPvY9K/WgxanmucoqN5LVyG57QqLZdEZiy3HMROWSFaw049vxkndwXwTuS0+L ovug== X-Gm-Message-State: AGi0PubyRwcHK4GLtYn1+/vaEmny9Scv3D/BRac0XoznN7pwjRTgYv0b Llz4YwDgYfjvvHdVaUl/KXI= X-Google-Smtp-Source: APiQypKQX01/ZEO5xGLslBvqH8xKxCvNAMQcdauV27avR6zx3ns00WTiv1HC2B6NJC62sK1UpNEtJA== X-Received: by 2002:a05:6214:9cb:: with SMTP id dp11mr1839213qvb.60.1586209229684; Mon, 06 Apr 2020 14:40:29 -0700 (PDT) Received: from ?IPv6:2804:d47:4f98:300:ed32:dc96:7432:a63f? ([2804:d47:4f98:300:ed32:dc96:7432:a63f]) by smtp.gmail.com with ESMTPSA id f13sm15750105qti.47.2020.04.06.14.40.26 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 06 Apr 2020 14:40:29 -0700 (PDT) From: Iuri Sampaio Message-Id: <399454FC-D175-421C-AC3A-31B1082E7881@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_E2BF9FFF-ED25-4FEF-A0B2-FB347D78852E" Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\)) Subject: Re: [External] Re: Separate volumes Date: Mon, 6 Apr 2020 18:40:24 -0300 In-Reply-To: Cc: MichaelDBA , Steve Midgley , Erik Brandsberg , pgsql-sql@lists.postgresql.org To: Ed Behn References: <5b60ae44-33e0-d9ea-d6e9-c6db9b4c7ef1@sqlexec.com> X-Mailer: Apple Mail (2.3445.9.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_E2BF9FFF-ED25-4FEF-A0B2-FB347D78852E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi Ed, We=E2=80=99d 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=E2=80=99t 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=E2=80=99t a yes/no question indeed. Hope that helps Best wishes, I > On Apr 6, 2020, at 16:36, Ed Behn wrote: >=20 >=20 > That makes sense. The person who told me this was very experienced = with Oracle but was a PG novice.=20 > -Ed >=20 > Ed Behn | Senior Systems Engineer | Avionics > COLLINS =C3=86ROSPACE > 2551 Riva Road, Annapolis, MD 21401 USA > Tel: +1 410 266 4426 | Mobile: +1 240 696 7443 > ed.behn@collins.com | = collinsaerospace.com > =20 > 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. >=20 >=20 >=20 > On Mon, Apr 6, 2020 at 3:33 PM MichaelDBA > wrote: > Hi Steve, >=20 > 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: >=20 > alter tablespace mytablespace set ( seq_page_cost=3D0.5, = random_page_cost=3D0.5 ); > But they also can be a headache in managing stuff. For instance, all = replicas must have the same directory structure and symlinks. >=20 > Regards, > Michael Vitale >=20 >=20 >=20 > Steve Midgley wrote on 4/6/2020 1:11 PM: >>=20 >>=20 >> On Mon, Apr 6, 2020 at 9:42 AM Erik Brandsberg > 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. >>=20 >> On Mon, Apr 6, 2020 at 10:40 AM Ed Behn > 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.=20 >>=20 >> However, I can't seem to find this advice anywhere online. Is it = indeed best practice? Is it worth the hassle? >>=20 >> =20 >>=20 >> 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.. >>=20 >> Steve >=20 --Apple-Mail=_E2BF9FFF-ED25-4FEF-A0B2-FB347D78852E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi = Ed,
We=E2=80=99d 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=E2=80=99t = 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=E2=80=99t 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 =C3=86ROSPACE
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=20= PG land.  In PG land, they can control the mapping of = tables/indexes to=20 faster or slower devices. By separating a table's tablespace from its=20 index tablespace, you may get more parallel I/O.  They also allow = for=20 flexibility in setting pg config parameters per tablespace:
= 
alter =
tablespace mytablespace set ( seq_page_cost=3D0.5, random_page_cost=3D0.5 =
);
But they also can be a headache in managing stuff.   For = instance, all=20 replicas must have the same directory structure and symlinks.

Regards,
Michael Vitale



Steve Midgley wrote on 4/6/2020 1:11 PM:
=20


On Mon, Apr 6, 2020 at 9:42 AM Erik=20 Brandsberg <erik@heimdalldata.com> wrote:
With SSD = and=20 it's random IO performance, I doubt that this advice would apply as=20 much, and adds complexity to your configuration and management.  In=20= particular if you use any filesystem level snapshotting (like with ZFS), splitting the filespaces will make it harder to do restores and using=20= 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=20 and indexes in separate tablespaces located on = separate physical drives. It seemed logical that this should improve performance because the=20= read-head wouldn't need to jump back and forth between a table and = its=20 index. 

However, I can't seem to find this=20 advice anywhere online. Is it indeed best practice? Is it worth the=20 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=20 difference. However for extremely high, sustained workloads, you might=20= find splitting tables, indices, and transaction logs onto separate disk=20= _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=20= strong metrics to show this is worth it. At that point, you'd probably=20= be better off getting commercial disk array solutions into the mix=20 rather than rolling your own anyway..

Steve


= --Apple-Mail=_E2BF9FFF-ED25-4FEF-A0B2-FB347D78852E--