Thread: pgsql: Create a GUC parameter temp_tablespaces that allows selection of
pgsql: Create a GUC parameter temp_tablespaces that allows selection of
From
tgl@postgresql.org (Tom Lane)
Date:
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@2ndquadrant.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@2ndquadrant.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
Tom Lane wrote: > "Simon Riggs" <simon@2ndquadrant.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@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +