MIME-Version: 1.0
References: <73954ab7-44d3-b37b-81a3-69bdcbb446f7@postgrespro.ru>
 <CAMsr+YGnJg9OBwG8RU6k1wm64QuPwwtyZkG2D0vsME=r8krCAQ@mail.gmail.com>
 <0baf9e02-23b3-f3c1-6fe9-fd35aee6fee1@postgrespro.ru>
 <8e0b539b-4b64-d2b2-08a1-ddaa3c7e7747@postgrespro.ru>
 <CAMsr+YFahmjG0PaFPL-J7Zxa4cgt_PFtgSNV7nRNgCE=29cvcw@mail.gmail.com>
 <8b4bdfa6-7a90-95e2-1fd0-243767e5b357@postgrespro.ru>
 <CAMsr+YER2y7mYb82wNvL6LECvJ_vL3Bv67eToKUuDwL_aLmJaQ@mail.gmail.com>
 <94a19e7b-d99a-110c-8e5b-f5068682b474@postgrespro.ru>
In-Reply-To: <94a19e7b-d99a-110c-8e5b-f5068682b474@postgrespro.ru>
From: Craig Ringer <craig@2ndquadrant.com>
Date: Sat, 10 Aug 2019 10:12:03 +0800
Message-ID: 
 <CAMsr+YEM0kHskTucOwH6d4YPfeTSPXj6bXNH3VcG5jsF3d=-sQ@mail.gmail.com>
Subject: Re: Global temporary tables
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d6e214058fb9d180"
Precedence: bulk

--000000000000d6e214058fb9d180
Content-Type: text/plain; charset="UTF-8"

On Fri, 9 Aug 2019 at 22:07, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
wrote:

>
>
> Ok, here it is: global_private_temp-1.patch
>

Fantastic.

I'll put that high on my queue.

I'd love to see something like this get in.

Doubly so if it brings us closer to being able to use temp tables on
physical read replicas, though I know there are plenty of other barriers
there (not least of which being temp tables using persistent txns not
vtxids)

Does it have a CF entry?


> Also I have attached updated version of the global temp tables with shared
> buffers - global_shared_temp-1.patch
>

Nice to see that split out. In addition to giving the first patch more hope
of being committed this time around, it'll help with readability and
testability too.

To be clear, I have long wanted to see PostgreSQL have the "session" state
abstraction you have implemented. I think it's really important for high
client count OLTP workloads, working with the endless collection of ORMs
out there, etc. So I'm all in favour of it in principle so long as it can
be made to work reliably with limited performance impact on existing
workloads and without making life lots harder when adding new core
functionality, for extension authors etc. The same goes for built-in
pooling. I think PostgreSQL has needed some sort of separation of
"connection", "backend", "session" and "executor" for a long time and I'm
glad to see you working on it.

With that said: How do you intend to address the likelihood that this will
cause performance regressions for existing workloads that use temp tables
*without* relying on your session state and connection pooler? Consider
workloads that use temp tables for mid-long txns where txn pooling is
unimportant, where they also do plenty of read and write activity on
persistent tables. Classic OLAP/DW stuff. e.g.:

* four clients, four backends, four connections, session-level connections
that stay busy with minimal client sleeps
* All sessions run the same bench code
* transactions all read plenty of data from a medium to large persistent
table (think fact tables, etc)
* transactions store a filtered, joined dataset with some pre-computed
window results or something in temp tables
* benchmark workload makes big-ish temp tables to store intermediate data
for its medium-length transactions
* transactions also write to some persistent relations, say to record their
summarised results

How does it perform with and without your patch? I'm concerned that:

* the extra buffer locking and various IPC may degrade performance of temp
tables
* the temp table data in shared_buffers may put pressure on shared_buffers
space, cached pages for persistent tables all sessions are sharing;
* the temp table data in shared_buffers may put pressure on shared_buffers
space for dirty buffers, forcing writes of persistent tables out earlier
therefore reducing write-combining opportunities;


-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise

--000000000000d6e214058fb9d180
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, 9 Aug 2019 at 22:07, Konstantin K=
nizhnik &lt;<a href=3D"mailto:k.knizhnik@postgrespro.ru">k.knizhnik@postgre=
spro.ru</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">
 =20
   =20
 =20
  <div bgcolor=3D"#FFFFFF">
    <br><br>
    Ok, here it is: global_private_temp-1.patch<br></div></blockquote><div>=
<br></div><div>Fantastic.</div><div><br></div><div>I&#39;ll put that high o=
n my queue.</div><div><br></div><div>I&#39;d love to see something like thi=
s get in.</div><div><br></div><div>Doubly so if it brings us closer to bein=
g able to use temp tables on physical read replicas, though I know there ar=
e plenty of other barriers there (not least of which being temp tables usin=
g persistent txns not vtxids)</div><div><br></div><div>Does it have a CF en=
try?</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
"><div bgcolor=3D"#FFFFFF">
    Also I have attached updated version of the global temp tables with
    shared buffers - global_shared_temp-1.patch <br></div></blockquote><div=
><br></div><div>Nice to see that split out. In addition to giving the first=
 patch more hope of being committed this time around, it&#39;ll help with r=
eadability and testability too.</div><div><br></div><div>To be clear, I hav=
e long wanted to see PostgreSQL have the &quot;session&quot; state abstract=
ion you have implemented. I think it&#39;s really important for high client=
 count OLTP workloads, working with the endless collection of ORMs out ther=
e, etc. So I&#39;m all in favour of it in principle so long as it can be ma=
de to work reliably with limited performance impact on existing workloads a=
nd without making life lots harder when adding new core functionality, for =
extension authors etc. The same goes for built-in pooling. I think PostgreS=
QL has needed some sort of separation of &quot;connection&quot;, &quot;back=
end&quot;, &quot;session&quot; and &quot;executor&quot; for a long time and=
 I&#39;m glad to see you working on it.</div><div><br></div><div>With that =
said: How do you intend to address the likelihood that this will cause perf=
ormance regressions for existing workloads that use temp tables *without* r=
elying on your session state and connection pooler? Consider workloads that=
 use temp tables for mid-long txns where txn pooling is unimportant, where =
they also do plenty of read and write activity on persistent tables. Classi=
c OLAP/DW stuff. e.g.:</div><div><br></div><div>* four clients, four backen=
ds, four connections, session-level connections that stay busy with minimal=
 client sleeps</div><div>* All sessions run the same bench code</div><div>*=
 transactions all read plenty of data from a medium to large persistent tab=
le (think fact tables, etc)<br></div><div>* transactions store a filtered, =
joined dataset with some pre-computed window results or something in temp t=
ables</div><div>* benchmark workload makes big-ish temp tables to store int=
ermediate data for its medium-length transactions</div><div>* transactions =
also write to some persistent relations, say to record their summarised res=
ults=C2=A0=C2=A0</div><div><br></div><div>How does it perform with and with=
out your patch? I&#39;m concerned that:</div><div><br></div><div>* the extr=
a buffer locking and various IPC may degrade performance of temp tables</di=
v><div>* the temp table data in shared_buffers may put pressure on shared_b=
uffers space, cached pages for persistent tables all sessions are sharing;<=
br></div><div>* the temp table data in shared_buffers may put pressure on s=
hared_buffers space for dirty buffers, forcing writes of persistent tables =
out earlier therefore reducing write-combining opportunities;</div><div><br=
></div><div><br></div></div>-- <br><div dir=3D"ltr" class=3D"gmail_signatur=
e"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr">=C2=A0Craig=
 Ringer=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <a href=3D"http://www.2ndQuadran=
t.com/" target=3D"_blank">http://www.2ndQuadrant.com/</a><br>=C2=A02ndQuadr=
ant - PostgreSQL Solutions for the Enterprise<br></div></div></div></div></=
div></div></div>

--000000000000d6e214058fb9d180--