Re: pgsql: Create a GUC parameter temp_tablespacesthat allows selection of

Log Message:
-----------
Create a GUC parameter temp_tablespaces that allows selection of the
tablespace(s) in which to store temp tables and temporary files. This is a
list to allow spreading the load across multiple tablespaces (a random list
element is chosen each time a temp object is to be created). Temp files are
not stored in per-database pgsql_tmp/ directories anymore, but per-tablespace
directories.

Jaime Casanova and Albert Cervera, with review by Bernd Helmle and Tom Lane.

Modified Files:
--------------
pgsql/doc/src/sgml:
config.sgml (r1.124 -> r1.125)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/config.sgml.diff?r1=1.124&r2=1.125)
manage-ag.sgml (r2.53 -> r2.54)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/manage-ag.sgml.diff?r1=2.53&r2=2.54)
storage.sgml (r1.17 -> r1.18)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/storage.sgml.diff?r1=1.17&r2=1.18)
pgsql/doc/src/sgml/ref:
create_index.sgml (r1.62 -> r1.63)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/create_index.sgml.diff?r1=1.62&r2=1.63)
create_table.sgml (r1.107 -> r1.108)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/create_table.sgml.diff?r1=1.107&r2=1.108)
create_table_as.sgml (r1.36 -> r1.37)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/create_table_as.sgml.diff?r1=1.36&r2=1.37)
drop_tablespace.sgml (r1.6 -> r1.7)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/drop_tablespace.sgml.diff?r1=1.6&r2=1.7)
grant.sgml (r1.65 -> r1.66)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/grant.sgml.diff?r1=1.65&r2=1.66)
pgsql/src/backend/commands:
indexcmds.c (r1.158 -> r1.159)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/indexcmds.c.diff?r1=1.158&r2=1.159)
tablecmds.c (r1.225 -> r1.226)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/tablecmds.c.diff?r1=1.225&r2=1.226)
tablespace.c (r1.46 -> r1.47)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/tablespace.c.diff?r1=1.46&r2=1.47)
pgsql/src/backend/executor:
execMain.c (r1.293 -> r1.294)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/execMain.c.diff?r1=1.293&r2=1.294)
nodeHash.c (r1.112 -> r1.113)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/nodeHash.c.diff?r1=1.112&r2=1.113)
nodeHashjoin.c (r1.89 -> r1.90)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/nodeHashjoin.c.diff?r1=1.89&r2=1.90)
pgsql/src/backend/storage/file:
buffile.c (r1.26 -> r1.27)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/storage/file/buffile.c.diff?r1=1.26&r2=1.27)
fd.c (r1.137 -> r1.138)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/storage/file/fd.c.diff?r1=1.137&r2=1.138)
pgsql/src/backend/utils/misc:
guc.c (r1.392 -> r1.393)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/guc.c.diff?r1=1.392&r2=1.393)
postgresql.conf.sample (r1.215 -> r1.216)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/postgresql.conf.sample.diff?r1=1.215&r2=1.216)
pgsql/src/backend/utils/sort:
logtape.c (r1.23 -> r1.24)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/sort/logtape.c.diff?r1=1.23&r2=1.24)
tuplestore.c (r1.31 -> r1.32)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/sort/tuplestore.c.diff?r1=1.31&r2=1.32)
pgsql/src/include/commands:
tablespace.h (r1.16 -> r1.17)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/commands/tablespace.h.diff?r1=1.16&r2=1.17)
pgsql/src/include/executor:
hashjoin.h (r1.45 -> r1.46)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/executor/hashjoin.h.diff?r1=1.45&r2=1.46)
nodeHashjoin.h (r1.34 -> r1.35)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/executor/nodeHashjoin.h.diff?r1=1.34&r2=1.35)
pgsql/src/include/storage:
buffile.h (r1.20 -> r1.21)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/storage/buffile.h.diff?r1=1.20&r2=1.21)
fd.h (r1.57 -> r1.58)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/storage/fd.h.diff?r1=1.57&r2=1.58)
pgsql/src/include/utils:
guc.h (r1.81 -> r1.82)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/utils/guc.h.diff?r1=1.81&r2=1.82)

On Sun, 2007-06-03 at 14:08 -0300, Tom Lane wrote:
> a random list element is chosen each time a temp object is to be
> created

I don't seem to be able to find discussion on this point on -hackers.

One of the main reasons for the implementation was to allow larger
queries to work faster by utilising multiple temp tablespaces for the
same query.

The original ideal implementation was to use round-robin/cyclic
selection, which allows much better usage in the above case. Random
selection works only with larger numbers of users.

Did I miss the discussion, or was this detail not discussed? The
original TODO description mentioned this idea.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> One of the main reasons for the implementation was to allow larger
> queries to work faster by utilising multiple temp tablespaces for the
> same query.

> The original ideal implementation was to use round-robin/cyclic
> selection, which allows much better usage in the above case.

Really? What if multiple backends are all hitting the same tablespaces
in the same order? A random selection seems much less likely to risk
having any self-synchronizing behavior.

regards, tom lane

On Mon, 2007-06-04 at 14:41 -0400, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > One of the main reasons for the implementation was to allow larger
> > queries to work faster by utilising multiple temp tablespaces for the
> > same query.
>
> > The original ideal implementation was to use round-robin/cyclic
> > selection, which allows much better usage in the above case.
>
> Really? What if multiple backends are all hitting the same tablespaces
> in the same order? A random selection seems much less likely to risk
> having any self-synchronizing behavior.

I'd like a single backend to never reuse a temp tablespace that is
actively being used so that large queries won't randomly conflict with
themselves. That's pretty certain to draw complaints, IMHO.

We can do this two ways
- cycle thru temp tablespaces, as originally suggested (not by me...)
- pick a random tablespace **other than ones already in active use**

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> On Mon, 2007-06-04 at 14:41 -0400, Tom Lane wrote:
>> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
>>> The original ideal implementation was to use round-robin/cyclic
>>> selection, which allows much better usage in the above case.
>>
>> Really? What if multiple backends are all hitting the same tablespaces
>> in the same order? A random selection seems much less likely to risk
>> having any self-synchronizing behavior.

> I'd like a single backend to never reuse a temp tablespace that is
> actively being used so that large queries won't randomly conflict with
> themselves. That's pretty certain to draw complaints, IMHO.

> We can do this two ways
> - cycle thru temp tablespaces, as originally suggested (not by me...)
> - pick a random tablespace **other than ones already in active use**

Idea 2 fails as soon as you have more temp files than tablespaces, and
also requires tracking which tablespaces are currently in use, a bit of
complexity we do not have in there.

Perhaps a reasonable compromise could work like this: at the first point
in a transaction where a temp file is created, choose a random list
element, and thereafter advance cyclically for the duration of that
transaction. This ensures within-transaction spread-out while still
having some randomness across backends.

The reason I'm thinking per-transaction is that we could tie this to
setting up a cached list of tablespace OIDs, which would avoid the
overhead of repeat parsing and tablespace validity checking. We had
rejected using a long-lived cache because of the problem of tablespaces
getting dropped, but I think one that lasts only across a transaction
would be OK.

And the reason I'm thinking a cache is important is that if you really
want to get any win from this idea, you need to spread the temp files
across tablespaces *per file*, which is not the way it works now.
As committed, the code selects one temp tablespace per sort or hashjoin.
The submitted patch already did it that way for sorts, and I forced the
same for hashjoins, because I wanted to be sure to minimize the number
of executions of aforesaid parsing/checking. So really that patch is
entirely wrong, and selection of the tablespace for a temp file needs
to be pushed much further down.

Assuming, that is, that you think this point is important enough to
drive the whole design; which I find rather questionable in view of the
fact that the submitted patch contained no mention whatever of any such
consideration. Or is this just another way in which its documentation
was not up to snuff?

regards, tom lane

On Mon, 2007-06-04 at 15:34 -0400, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > On Mon, 2007-06-04 at 14:41 -0400, Tom Lane wrote:
> >> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> >>> The original ideal implementation was to use round-robin/cyclic
> >>> selection, which allows much better usage in the above case.
> >>
> >> Really? What if multiple backends are all hitting the same tablespaces
> >> in the same order? A random selection seems much less likely to risk
> >> having any self-synchronizing behavior.
>
> > I'd like a single backend to never reuse a temp tablespace that is
> > actively being used so that large queries won't randomly conflict with
> > themselves. That's pretty certain to draw complaints, IMHO.
>
> > We can do this two ways
> > - cycle thru temp tablespaces, as originally suggested (not by me...)
> > - pick a random tablespace **other than ones already in active use**
>
> Idea 2 fails as soon as you have more temp files than tablespaces, and
> also requires tracking which tablespaces are currently in use, a bit of
> complexity we do not have in there.
>
> Perhaps a reasonable compromise could work like this: at the first point
> in a transaction where a temp file is created, choose a random list
> element, and thereafter advance cyclically for the duration of that
> transaction. This ensures within-transaction spread-out while still
> having some randomness across backends.

Works for me.

> The reason I'm thinking per-transaction is that we could tie this to
> setting up a cached list of tablespace OIDs, which would avoid the
> overhead of repeat parsing and tablespace validity checking. We had
> rejected using a long-lived cache because of the problem of tablespaces
> getting dropped, but I think one that lasts only across a transaction
> would be OK.

No problem with that.

> And the reason I'm thinking a cache is important is that if you really
> want to get any win from this idea, you need to spread the temp files
> across tablespaces *per file*, which is not the way it works now.
> As committed, the code selects one temp tablespace per sort or hashjoin.
> The submitted patch already did it that way for sorts, and I forced the
> same for hashjoins, because I wanted to be sure to minimize the number
> of executions of aforesaid parsing/checking. So really that patch is
> entirely wrong, and selection of the tablespace for a temp file needs
> to be pushed much further down.

Well, I was looking to achieve poor man's parallelism. If you have a
query with two or more temp files active then you will be reading from
one while writing to another. That could then allow you to rely on OS
file writers to give you asynch I/O like behaviour.

I can see what you're thinking though and it sounds even better, but I'm
guessing that's a much larger change anyway.

> Assuming, that is, that you think this point is important enough to
> drive the whole design; which I find rather questionable in view of the
> fact that the submitted patch contained no mention whatever of any such
> consideration. Or is this just another way in which its documentation
> was not up to snuff?

Well, it was listed in the TODO, but I guess that was lost somewhere
along the line. Oh well.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > One of the main reasons for the implementation was to allow larger
> > queries to work faster by utilising multiple temp tablespaces for the
> > same query.
>
> > The original ideal implementation was to use round-robin/cyclic
> > selection, which allows much better usage in the above case.
>
> Really? What if multiple backends are all hitting the same tablespaces
> in the same order? A random selection seems much less likely to risk
> having any self-synchronizing behavior.

I thought it was going to be random selection for the first tablespace,
and round-robin after that.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Simon Riggs wrote:
> > Assuming, that is, that you think this point is important enough to
> > drive the whole design; which I find rather questionable in view of the
> > fact that the submitted patch contained no mention whatever of any such
> > consideration. Or is this just another way in which its documentation
> > was not up to snuff?
>
> Well, it was listed in the TODO, but I guess that was lost somewhere
> along the line. Oh well.

The TODO description was removed once the item was complete because
sometimes the description doesn't match the implementation. The
description was:

It could start with a random tablespace from a supplied list and
cycle through the list.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On 6/5/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Simon Riggs wrote:
> > > Assuming, that is, that you think this point is important enough to
> > > drive the whole design; which I find rather questionable in view of the
> > > fact that the submitted patch contained no mention whatever of any such
> > > consideration. Or is this just another way in which its documentation
> > > was not up to snuff?
> >
> > Well, it was listed in the TODO, but I guess that was lost somewhere
> > along the line. Oh well.
>
> The TODO description was removed once the item was complete because
> sometimes the description doesn't match the implementation. The
> description was:
>
> It could start with a random tablespace from a supplied list and
> cycle through the list.
>

that is what the patch did but it did it one tablespace per BufFile
(not per file)

of course, it parses the GUC on every GetTempTablespaces() call :(

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

--On Montag, Juni 04, 2007 15:34:14 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> The reason I'm thinking per-transaction is that we could tie this to
> setting up a cached list of tablespace OIDs, which would avoid the
> overhead of repeat parsing and tablespace validity checking. We had
> rejected using a long-lived cache because of the problem of tablespaces
> getting dropped, but I think one that lasts only across a transaction
> would be OK.

Hmm i tried an allocated oid list in TopMemoryContext per backend, but i
didn't find any issue with that... What's the reason we cannot work with a
long-living cache during backend lifetime?

Dropping a tablespace caused get_tablespace_name() to return an InvalidOid
and the tablespace selection code to switch to $PGDATA/pgsql_tmp...

--
Thanks

Bernd

--On Montag, Juni 04, 2007 15:34:14 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Perhaps a reasonable compromise could work like this: at the first point
> in a transaction where a temp file is created, choose a random list
> element, and thereafter advance cyclically for the duration of that
> transaction. This ensures within-transaction spread-out while still
> having some randomness across backends.

Doing this on transaction-level looks pretty nice; The original code choose
the random element on backend startup (or every time you call SET).

Btw. i saw you've removed the random selection implemented by MyProcId %
num_temp_tablespaces. I liked this idea, because PID should be pretty
random on many OS?

--
Thanks

Bernd

On 6/4/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Perhaps a reasonable compromise could work like this: at the first point
> in a transaction where a temp file is created, choose a random list
> element, and thereafter advance cyclically for the duration of that
> transaction. This ensures within-transaction spread-out while still
> having some randomness across backends.
>
> The reason I'm thinking per-transaction is that we could tie this to
> setting up a cached list of tablespace OIDs, which would avoid the
> overhead of repeat parsing and tablespace validity checking. We had
> rejected using a long-lived cache because of the problem of tablespaces
> getting dropped, but I think one that lasts only across a transaction
> would be OK.
>
> And the reason I'm thinking a cache is important is that if you really
> want to get any win from this idea, you need to spread the temp files
> across tablespaces *per file*, which is not the way it works now.

ok. are you doing this? or can i prepare a patch that implements this?
i guess we can allocate the memory for the list in TopTransactionContext.

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

"Jaime Casanova" <systemguards(at)gmail(dot)com> writes:
> On 6/4/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Perhaps a reasonable compromise could work like this: at the first point
>> in a transaction where a temp file is created, choose a random list
>> element, and thereafter advance cyclically for the duration of that
>> transaction.

> ok. are you doing this? or can i prepare a patch that implements this?
> i guess we can allocate the memory for the list in TopTransactionContext.

I'll work on it ... I want to rejigger the API between fd.c and
tablespace.c anyway. (fd.c still shouldn't be calling tablespace.c ...)

regards, tom lane