Thread: Modifying TOAST thresholds
In another thread I wrote:
> ... One thing I was just thinking about is that it's silly to have
> the threshold constrained so strongly by a desire that tuples in toast
> tables not be toastable. It would be trivial to tweak the heapam.c
> routines so that they simply don't invoke the toaster when relkind is
> 't', and then we could have independent choices of toast-tuple size and
> main-tuple size. This would be particularly good because in the current
> scheme you can't modify toast-tuple size without an initdb, but if that
> were decoupled there'd be no reason not to allow changes in the
> main-tuple thresholds.
After thinking about this more I'm convinced that the above is a good
idea, eg in heap_insert change
if (HeapTupleHasExternal(tup) || tup->t_len > TOAST_TUPLE_THRESHOLD) heaptup =
toast_insert_or_update(relation,tup, NULL, use_wal); else heaptup = tup;
to
if (relation->rd_rel->relkind == RELKIND_TOASTVALUE) { /* toast table entries should never be recursively
toasted*/ Assert(!HeapTupleHasExternal(tup)); heaptup = tup; } else if (HeapTupleHasExternal(tup) ||
tup->t_len> TOAST_TUPLE_THRESHOLD) heaptup = toast_insert_or_update(relation, tup, NULL, use_wal); else
heaptup= tup;
I also think that we ought to add TOAST_MAX_CHUNK_SIZE to the set of
compiled-in parameters that are recorded in pg_control and checked for
compatibility at startup (like BLCKSZ) --- this will prevent anyone from
shooting themselves in the foot while experimenting.
Any objections?
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > I also think that we ought to add TOAST_MAX_CHUNK_SIZE to the set of > compiled-in parameters that are recorded in pg_control and checked for > compatibility at startup (like BLCKSZ) --- this will prevent anyone from > shooting themselves in the foot while experimenting. Is there any reason to experiment with this? I would have thought we would divorce TOAST_MAX_CHUNK_SIZE from TOAST_THRESHOLD and hard code it as the same expression that's there now. Ie, the largest size that can fit in a page. That doesn't mean it shouldn't go in pg_control of course but it would just be a function of BLCKSIZE and the architecture alignment and not dependent on any user configurable value. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> I also think that we ought to add TOAST_MAX_CHUNK_SIZE to the set of
>> compiled-in parameters that are recorded in pg_control and checked for
>> compatibility at startup (like BLCKSZ) --- this will prevent anyone from
>> shooting themselves in the foot while experimenting.
> Is there any reason to experiment with this? I would have thought we would
> divorce TOAST_MAX_CHUNK_SIZE from TOAST_THRESHOLD and hard code it as the same
> expression that's there now. Ie, the largest size that can fit in a page.
No, right now it's the largest size that you can fit 4 on a page. It's
not obvious to me that 4 is optimal once it's divorced from TOAST_THRESHOLD.
It seems possible that the correct number is 1, and even if it's useful
to keep the tuples smaller than that, there's no reason to assume 4 is
the best number per page.
regards, tom lane
On Wed, 2007-03-28 at 14:08 -0400, Tom Lane wrote: > Gregory Stark <stark@enterprisedb.com> writes: > > "Tom Lane" <tgl@sss.pgh.pa.us> writes: > >> I also think that we ought to add TOAST_MAX_CHUNK_SIZE to the set of > >> compiled-in parameters that are recorded in pg_control and checked for > >> compatibility at startup (like BLCKSZ) --- this will prevent anyone from > >> shooting themselves in the foot while experimenting. > > > Is there any reason to experiment with this? I would have thought we would > > divorce TOAST_MAX_CHUNK_SIZE from TOAST_THRESHOLD and hard code it as the same > > expression that's there now. Ie, the largest size that can fit in a page. > > No, right now it's the largest size that you can fit 4 on a page. It's > not obvious to me that 4 is optimal once it's divorced from TOAST_THRESHOLD. > It seems possible that the correct number is 1, and even if it's useful > to keep the tuples smaller than that, there's no reason to assume 4 is > the best number per page. Well it certainly seems worth separating them. It does seem possible that recursive toasting effected some of the earlier results we looked at. Would you like me to do this, or will you? I'll look again at the possibility for setting TOAST_THRESHOLD and re-cast the test patch I have for production use. But either way it's going to be a couple of days after freeze now. I'd like to get some mechanism for reducing WAL volume into 8.3, whether its configurable toast or WAL reduction for UPDATEs. If for no other reason than making backup and availability solutions more manageable. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
"Simon Riggs" <simon@2ndquadrant.com> writes:
> Well it certainly seems worth separating them. It does seem possible
> that recursive toasting effected some of the earlier results we looked
> at.
> Would you like me to do this, or will you?
I'm willing to do the code changes to separate TOAST_THRESHOLD from
the toast chunk size, but I do not have the time or facilities to do
any performance testing for different parameter choices. Anyone want
to work on that?
> I'd like to get some mechanism for reducing WAL volume into 8.3, whether
> its configurable toast or WAL reduction for UPDATEs. If for no other
> reason than making backup and availability solutions more manageable.
I think the WAL-reduction proposal needs more time and thought than is
feasible before 8.3. OTOH, tuning the TOAST parameters seems like
something we understand well enough already, we just need to put some
cycles into testing different alternatives. I would have no objection
to someone working on that during April and delivering a final patch
sometime before beta.
regards, tom lane
On Thu, 2007-03-29 at 12:05 -0400, Tom Lane wrote: > I think the WAL-reduction proposal needs more time and thought than is > feasible before 8.3. Agreed. We really need to focus on the major features. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
tgl@sss.pgh.pa.us (Tom Lane) wrote:
> I'm willing to do the code changes to separate TOAST_THRESHOLD from
> the toast chunk size, but I do not have the time or facilities to do
> any performance testing for different parameter choices. Anyone
> want to work on that?
What have you got in mind there? I might be able to do something,
assuming that it doesn't cause heartburn that I'll be "offline" April
6-14th.
Are we simply talking about having the option of #defining a different
threshold at which items get thrown out to TOAST? Or one of the more
sophisticated options? Or is the idea more simply that we might
consider having the default set somewhat lower than it is at present?
And I guess a good question is, what's going to get regarded as a
meaningful test? I've got a couple local test cases I could draw
from, unfortunately, the interaction with TOAST will more than likely
be pretty trivial, showing off "Yeah, cutting the threshold was a good
idea." And that may not be fair to everyone's case.
[The good news is, of course, that if the end "deliverable" is a
single #define parameter that's used as the denominator to the
fraction, delivery during "beta" time is, indeed, quite trivial...]
--
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/slony.html
Outside of a dog, a book is man's best friend. Inside of a dog, it's
too dark to read. -Groucho Marx
tgl@sss.pgh.pa.us (Tom Lane) writes:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
>> Well it certainly seems worth separating them. It does seem possible
>> that recursive toasting effected some of the earlier results we looked
>> at.
>
>> Would you like me to do this, or will you?
>
> I'm willing to do the code changes to separate TOAST_THRESHOLD from
> the toast chunk size, but I do not have the time or facilities to do
> any performance testing for different parameter choices. Anyone want
> to work on that?
>
>> I'd like to get some mechanism for reducing WAL volume into 8.3, whether
>> its configurable toast or WAL reduction for UPDATEs. If for no other
>> reason than making backup and availability solutions more manageable.
>
> I think the WAL-reduction proposal needs more time and thought than is
> feasible before 8.3. OTOH, tuning the TOAST parameters seems like
> something we understand well enough already, we just need to put some
> cycles into testing different alternatives. I would have no objection
> to someone working on that during April and delivering a final patch
> sometime before beta.
Here's a "drafty" patch that *tries* to do this using a GUC variable;
it passes some interactive testing. It probably needs an
assign_hook() function to do further validation (probably to make sure
that sizes are rightly aligned on both 32 and 64 bit platforms); feel
free to consider me incompetent at this stage at generating such...
I would *very* much like to see something of this sort in 8.3; that
would be of definite value to some of our applications which store
data that is a bit too small to meet the present
TOAST_TUPLE_THRESHOLD. (E.g. - it's worth noting that common
SOAP-like XML requests are in the 700-1000 byte range; such values are
generally nicely compressible and are often not likely to be used in
summary-oriented queries on mainline tables...)
I don't think I can come up with a performance "test suite" this week,
and will be unavailable from April 6-14th; if others were to find this
valuable, and volunteer to set up some sort of test in the interim,
that would be super. Absent that, I should be able to do some work on
this in the latter half of April.
set toast_default_threshold TO 128;
create table sample (id serial primary key, txt text);
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
create table sample (id serial primary key, txt text);
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
set toast_default_threshold TO 250;
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
test@[local]:5883=# vacuum verbose sample;
INFO: vacuuming "public.sample"
INFO: index "sample_pkey" now contains 52 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sample": found 0 removable, 52 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_16387"
INFO: index "pg_toast_16387_index" now contains 25 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_16387": found 0 removable, 25 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
------------------------------------
? Makefile.global
? backend/postgres
? backend/catalog/postgres.bki
? backend/catalog/postgres.description
? backend/catalog/postgres.shdescription
? backend/utils/mb/conversion_procs/conversion_create.sql
? backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0.0
? backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0.0
? backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0.0
? backend/utils/mb/conversion_procs/euc_jis_2004_and_shift_jis_2004/libeuc_jis_2004_and_shift_jis_2004.so.0.0
? backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0.0
? backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0.0
? backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0.0
? backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0.0
? backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004/libutf8_and_euc_jis_2004.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_shift_jis_2004/libutf8_and_shift_jis_2004.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0.0
? backend/utils/mb/conversion_procs/utf8_and_win/libutf8_and_win.so.0.0
? bin/initdb/initdb
? bin/ipcclean/ipcclean
? bin/pg_config/pg_config
? bin/pg_controldata/pg_controldata
? bin/pg_ctl/pg_ctl
? bin/pg_dump/pg_dump
? bin/pg_dump/pg_dumpall
? bin/pg_dump/pg_restore
? bin/pg_resetxlog/pg_resetxlog
? bin/psql/psql
? bin/scripts/clusterdb
? bin/scripts/createdb
? bin/scripts/createlang
? bin/scripts/createuser
? bin/scripts/dropdb
? bin/scripts/droplang
? bin/scripts/dropuser
? bin/scripts/reindexdb
? bin/scripts/vacuumdb
? include/pg_config.h
? include/stamp-h
? interfaces/ecpg/compatlib/libecpg_compat.so.2.2
? interfaces/ecpg/compatlib/libecpg_compat.so.2.3
? interfaces/ecpg/ecpglib/libecpg.so.5.2
? interfaces/ecpg/ecpglib/libecpg.so.5.3
? interfaces/ecpg/include/ecpg_config.h
? interfaces/ecpg/pgtypeslib/libpgtypes.so.2.2
? interfaces/ecpg/pgtypeslib/libpgtypes.so.2.3
? interfaces/ecpg/preproc/ecpg
? interfaces/ecpg/test/sql/dyntest2
? interfaces/ecpg/test/sql/dyntest2.c
? interfaces/libpq/exports.list
? interfaces/libpq/libpq.so.5.0
? interfaces/libpq/libpq.so.5.1
? pl/plpgsql/src/libplpgsql.so.1.0
? port/pg_config_paths.h
? test/regress/libregress.so.0.0
? test/regress/log
? test/regress/pg_regress
? test/regress/results
? test/regress/testtablespace
? test/regress/tmp_check
? test/regress/expected/constraints.out
? test/regress/expected/copy.out
? test/regress/expected/create_function_1.out
? test/regress/expected/create_function_2.out
? test/regress/expected/largeobject.out
? test/regress/expected/largeobject_1.out
? test/regress/expected/misc.out
? test/regress/expected/tablespace.out
? test/regress/sql/constraints.sql
? test/regress/sql/copy.sql
? test/regress/sql/create_function_1.sql
? test/regress/sql/create_function_2.sql
? test/regress/sql/largeobject.sql
? test/regress/sql/misc.sql
? test/regress/sql/tablespace.sql
? timezone/zic
Index: backend/access/heap/heapam.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/heapam.c,v
retrieving revision 1.230
diff -c -u -r1.230 heapam.c
--- backend/access/heap/heapam.c 29 Mar 2007 00:15:37 -0000 1.230
+++ backend/access/heap/heapam.c 2 Apr 2007 22:41:05 -0000
@@ -57,6 +57,7 @@#include "utils/relcache.h"#include "utils/syscache.h"
+extern int toast_default_threshold;static XLogRecPtr log_heap_update(Relation reln, Buffer oldbuf,
ItemPointerDatafrom, Buffer newbuf, HeapTuple newtup, bool move);
Index: backend/access/heap/tuptoaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/tuptoaster.c,v
retrieving revision 1.72
diff -c -u -r1.72 tuptoaster.c
--- backend/access/heap/tuptoaster.c 29 Mar 2007 00:15:37 -0000 1.72
+++ backend/access/heap/tuptoaster.c 2 Apr 2007 22:41:05 -0000
@@ -42,6 +42,7 @@#undef TOAST_DEBUG
+extern int toast_default_threshold;static void toast_delete_datum(Relation rel, Datum value);static Datum
toast_save_datum(Relationrel, Datum value, bool use_wal, bool use_fsm);
Index: backend/catalog/toasting.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/toasting.c,v
retrieving revision 1.5
diff -c -u -r1.5 toasting.c
--- backend/catalog/toasting.c 9 Jan 2007 02:14:11 -0000 1.5
+++ backend/catalog/toasting.c 2 Apr 2007 22:41:05 -0000
@@ -33,7 +33,7 @@static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid);static bool
needs_toast_table(Relationrel);
-
+extern int toast_default_threshold;/* * AlterTableCreateToastTable
Index: backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.383
diff -c -u -r1.383 guc.c
--- backend/utils/misc/guc.c 19 Mar 2007 23:38:30 -0000 1.383
+++ backend/utils/misc/guc.c 2 Apr 2007 22:41:06 -0000
@@ -195,6 +195,7 @@int client_min_messages = NOTICE;int log_min_duration_statement = -1;int
log_temp_files = -1;
+int toast_default_threshold = MAXALIGN_DOWN((BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) /
4);int num_temp_buffers = 1000;
@@ -1695,6 +1696,17 @@ -1, -1, INT_MAX, NULL, NULL },
+ {
+ {"toast_default_threshold", PGC_SUSET, CUSTOM_OPTIONS,
+ gettext_noop("Tuples larger than this size will be considered for TOASTing"),
+ gettext_noop("Default is ~ BLKSIZE / 4"),
+ NULL
+ },
+ &toast_default_threshold,
+ MAXALIGN_DOWN((BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) / 16),
+ 128, BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData)), NULL, NULL
+ },
+ /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL
Index: include/access/tuptoaster.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/tuptoaster.h,v
retrieving revision 1.33
diff -c -u -r1.33 tuptoaster.h
--- include/access/tuptoaster.h 29 Mar 2007 00:15:39 -0000 1.33
+++ include/access/tuptoaster.h 2 Apr 2007 22:41:06 -0000
@@ -38,12 +38,17 @@ * Note: sizeof(PageHeaderData) includes the first ItemId, but we have * to allow for 3 more, if we
wantto fit 4 tuples on a page. */
-#define TOAST_TUPLE_THRESHOLD \
- MAXALIGN_DOWN((BLCKSZ - \
- MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \
- / 4)
-#define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD
+/* #define TOAST_TUPLE_THRESHOLD \ */
+/* MAXALIGN_DOWN((BLCKSZ - \ */
+/* MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \ */
+/* / TOAST_DENOMINATOR) */
+
+/* #define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD */
+
+#define TOAST_TUPLE_THRESHOLD toast_default_threshold
+
+#define TOAST_TUPLE_TARGET toast_default_threshold/* * If an index value is larger than TOAST_INDEX_TARGET, we
willtry to
@@ -65,11 +70,13 @@ * NB: you cannot change this value without forcing initdb, at least not * if your DB contains any
multi-chunktoasted values. */
-#define TOAST_MAX_CHUNK_SIZE (TOAST_TUPLE_THRESHOLD - \
+#define TOAST_MAX_CHUNK_SIZE ((MAXALIGN_DOWN((BLCKSZ - \
+ MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \
+ / 4)) - \ MAXALIGN(offsetof(HeapTupleHeaderData, t_bits)) - \
sizeof(Oid) - \ sizeof(int32) -
\
- VARHDRSZ)
+ VARHDRSZ)/* ----------
--
output = ("cbbrowne" "@" "linuxdatabases.info")
http://cbbrowne.com/info/linuxxian.html
When a man talks dirty to a woman, its sexual harassment. When a woman
talks dirty to a man, it's 3.95 per minute.
Tom, are you going to do this for 8.3?
---------------------------------------------------------------------------
Tom Lane wrote:
> In another thread I wrote:
> > ... One thing I was just thinking about is that it's silly to have
> > the threshold constrained so strongly by a desire that tuples in toast
> > tables not be toastable. It would be trivial to tweak the heapam.c
> > routines so that they simply don't invoke the toaster when relkind is
> > 't', and then we could have independent choices of toast-tuple size and
> > main-tuple size. This would be particularly good because in the current
> > scheme you can't modify toast-tuple size without an initdb, but if that
> > were decoupled there'd be no reason not to allow changes in the
> > main-tuple thresholds.
>
> After thinking about this more I'm convinced that the above is a good
> idea, eg in heap_insert change
>
> if (HeapTupleHasExternal(tup) || tup->t_len > TOAST_TUPLE_THRESHOLD)
> heaptup = toast_insert_or_update(relation, tup, NULL, use_wal);
> else
> heaptup = tup;
>
> to
>
> if (relation->rd_rel->relkind == RELKIND_TOASTVALUE)
> {
> /* toast table entries should never be recursively toasted */
> Assert(!HeapTupleHasExternal(tup));
> heaptup = tup;
> }
> else if (HeapTupleHasExternal(tup) || tup->t_len > TOAST_TUPLE_THRESHOLD)
> heaptup = toast_insert_or_update(relation, tup, NULL, use_wal);
> else
> heaptup = tup;
>
> I also think that we ought to add TOAST_MAX_CHUNK_SIZE to the set of
> compiled-in parameters that are recorded in pg_control and checked for
> compatibility at startup (like BLCKSZ) --- this will prevent anyone from
> shooting themselves in the foot while experimenting.
>
> Any objections?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
-- 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. +
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --------------------------------------------------------------------------- Chris Browne wrote: > tgl@sss.pgh.pa.us (Tom Lane) writes: > > "Simon Riggs" <simon@2ndquadrant.com> writes: > >> Well it certainly seems worth separating them. It does seem possible > >> that recursive toasting effected some of the earlier results we looked > >> at. > > > >> Would you like me to do this, or will you? > > > > I'm willing to do the code changes to separate TOAST_THRESHOLD from > > the toast chunk size, but I do not have the time or facilities to do > > any performance testing for different parameter choices. Anyone want > > to work on that? > > > >> I'd like to get some mechanism for reducing WAL volume into 8.3, whether > >> its configurable toast or WAL reduction for UPDATEs. If for no other > >> reason than making backup and availability solutions more manageable. > > > > I think the WAL-reduction proposal needs more time and thought than is > > feasible before 8.3. OTOH, tuning the TOAST parameters seems like > > something we understand well enough already, we just need to put some > > cycles into testing different alternatives. I would have no objection > > to someone working on that during April and delivering a final patch > > sometime before beta. > > Here's a "drafty" patch that *tries* to do this using a GUC variable; > it passes some interactive testing. It probably needs an > assign_hook() function to do further validation (probably to make sure > that sizes are rightly aligned on both 32 and 64 bit platforms); feel > free to consider me incompetent at this stage at generating such... > > I would *very* much like to see something of this sort in 8.3; that > would be of definite value to some of our applications which store > data that is a bit too small to meet the present > TOAST_TUPLE_THRESHOLD. (E.g. - it's worth noting that common > SOAP-like XML requests are in the 700-1000 byte range; such values are > generally nicely compressible and are often not likely to be used in > summary-oriented queries on mainline tables...) > > I don't think I can come up with a performance "test suite" this week, > and will be unavailable from April 6-14th; if others were to find this > valuable, and volunteer to set up some sort of test in the interim, > that would be super. Absent that, I should be able to do some work on > this in the latter half of April. > > set toast_default_threshold TO 128; > create table sample (id serial primary key, txt text); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > create table sample (id serial primary key, txt text); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > set toast_default_threshold TO 250; > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > insert into sample (txt) values ('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423'); > > test@[local]:5883=# vacuum verbose sample; > INFO: vacuuming "public.sample" > INFO: index "sample_pkey" now contains 52 row versions in 2 pages > DETAIL: 0 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO: "sample": found 0 removable, 52 nonremovable row versions in 1 pages > DETAIL: 0 dead row versions cannot be removed yet. > There were 0 unused item pointers. > 1 pages contain useful free space. > 0 pages are entirely empty. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO: vacuuming "pg_toast.pg_toast_16387" > INFO: index "pg_toast_16387_index" now contains 25 row versions in 2 pages > DETAIL: 0 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO: "pg_toast_16387": found 0 removable, 25 nonremovable row versions in 1 pages > DETAIL: 0 dead row versions cannot be removed yet. > There were 0 unused item pointers. > 1 pages contain useful free space. > 0 pages are entirely empty. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > VACUUM > ------------------------------------ > ? Makefile.global > ? backend/postgres > ? backend/catalog/postgres.bki > ? backend/catalog/postgres.description > ? backend/catalog/postgres.shdescription > ? backend/utils/mb/conversion_procs/conversion_create.sql > ? backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0.0 > ? backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0.0 > ? backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0.0 > ? backend/utils/mb/conversion_procs/euc_jis_2004_and_shift_jis_2004/libeuc_jis_2004_and_shift_jis_2004.so.0.0 > ? backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0.0 > ? backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0.0 > ? backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0.0 > ? backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0.0 > ? backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004/libutf8_and_euc_jis_2004.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_shift_jis_2004/libutf8_and_shift_jis_2004.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0.0 > ? backend/utils/mb/conversion_procs/utf8_and_win/libutf8_and_win.so.0.0 > ? bin/initdb/initdb > ? bin/ipcclean/ipcclean > ? bin/pg_config/pg_config > ? bin/pg_controldata/pg_controldata > ? bin/pg_ctl/pg_ctl > ? bin/pg_dump/pg_dump > ? bin/pg_dump/pg_dumpall > ? bin/pg_dump/pg_restore > ? bin/pg_resetxlog/pg_resetxlog > ? bin/psql/psql > ? bin/scripts/clusterdb > ? bin/scripts/createdb > ? bin/scripts/createlang > ? bin/scripts/createuser > ? bin/scripts/dropdb > ? bin/scripts/droplang > ? bin/scripts/dropuser > ? bin/scripts/reindexdb > ? bin/scripts/vacuumdb > ? include/pg_config.h > ? include/stamp-h > ? interfaces/ecpg/compatlib/libecpg_compat.so.2.2 > ? interfaces/ecpg/compatlib/libecpg_compat.so.2.3 > ? interfaces/ecpg/ecpglib/libecpg.so.5.2 > ? interfaces/ecpg/ecpglib/libecpg.so.5.3 > ? interfaces/ecpg/include/ecpg_config.h > ? interfaces/ecpg/pgtypeslib/libpgtypes.so.2.2 > ? interfaces/ecpg/pgtypeslib/libpgtypes.so.2.3 > ? interfaces/ecpg/preproc/ecpg > ? interfaces/ecpg/test/sql/dyntest2 > ? interfaces/ecpg/test/sql/dyntest2.c > ? interfaces/libpq/exports.list > ? interfaces/libpq/libpq.so.5.0 > ? interfaces/libpq/libpq.so.5.1 > ? pl/plpgsql/src/libplpgsql.so.1.0 > ? port/pg_config_paths.h > ? test/regress/libregress.so.0.0 > ? test/regress/log > ? test/regress/pg_regress > ? test/regress/results > ? test/regress/testtablespace > ? test/regress/tmp_check > ? test/regress/expected/constraints.out > ? test/regress/expected/copy.out > ? test/regress/expected/create_function_1.out > ? test/regress/expected/create_function_2.out > ? test/regress/expected/largeobject.out > ? test/regress/expected/largeobject_1.out > ? test/regress/expected/misc.out > ? test/regress/expected/tablespace.out > ? test/regress/sql/constraints.sql > ? test/regress/sql/copy.sql > ? test/regress/sql/create_function_1.sql > ? test/regress/sql/create_function_2.sql > ? test/regress/sql/largeobject.sql > ? test/regress/sql/misc.sql > ? test/regress/sql/tablespace.sql > ? timezone/zic > Index: backend/access/heap/heapam.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/heapam.c,v > retrieving revision 1.230 > diff -c -u -r1.230 heapam.c > --- backend/access/heap/heapam.c 29 Mar 2007 00:15:37 -0000 1.230 > +++ backend/access/heap/heapam.c 2 Apr 2007 22:41:05 -0000 > @@ -57,6 +57,7 @@ > #include "utils/relcache.h" > #include "utils/syscache.h" > > +extern int toast_default_threshold; > > static XLogRecPtr log_heap_update(Relation reln, Buffer oldbuf, > ItemPointerData from, Buffer newbuf, HeapTuple newtup, bool move); > Index: backend/access/heap/tuptoaster.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/tuptoaster.c,v > retrieving revision 1.72 > diff -c -u -r1.72 tuptoaster.c > --- backend/access/heap/tuptoaster.c 29 Mar 2007 00:15:37 -0000 1.72 > +++ backend/access/heap/tuptoaster.c 2 Apr 2007 22:41:05 -0000 > @@ -42,6 +42,7 @@ > > #undef TOAST_DEBUG > > +extern int toast_default_threshold; > static void toast_delete_datum(Relation rel, Datum value); > static Datum toast_save_datum(Relation rel, Datum value, > bool use_wal, bool use_fsm); > Index: backend/catalog/toasting.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/backend/catalog/toasting.c,v > retrieving revision 1.5 > diff -c -u -r1.5 toasting.c > --- backend/catalog/toasting.c 9 Jan 2007 02:14:11 -0000 1.5 > +++ backend/catalog/toasting.c 2 Apr 2007 22:41:05 -0000 > @@ -33,7 +33,7 @@ > > static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid); > static bool needs_toast_table(Relation rel); > - > +extern int toast_default_threshold; > > /* > * AlterTableCreateToastTable > Index: backend/utils/misc/guc.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v > retrieving revision 1.383 > diff -c -u -r1.383 guc.c > --- backend/utils/misc/guc.c 19 Mar 2007 23:38:30 -0000 1.383 > +++ backend/utils/misc/guc.c 2 Apr 2007 22:41:06 -0000 > @@ -195,6 +195,7 @@ > int client_min_messages = NOTICE; > int log_min_duration_statement = -1; > int log_temp_files = -1; > +int toast_default_threshold = MAXALIGN_DOWN((BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) / 4); > > int num_temp_buffers = 1000; > > @@ -1695,6 +1696,17 @@ > -1, -1, INT_MAX, NULL, NULL > }, > > + { > + {"toast_default_threshold", PGC_SUSET, CUSTOM_OPTIONS, > + gettext_noop("Tuples larger than this size will be considered for TOASTing"), > + gettext_noop("Default is ~ BLKSIZE / 4"), > + NULL > + }, > + &toast_default_threshold, > + MAXALIGN_DOWN((BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) / 16), > + 128, BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData)), NULL, NULL > + }, > + > /* End-of-list marker */ > { > {NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL > Index: include/access/tuptoaster.h > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/include/access/tuptoaster.h,v > retrieving revision 1.33 > diff -c -u -r1.33 tuptoaster.h > --- include/access/tuptoaster.h 29 Mar 2007 00:15:39 -0000 1.33 > +++ include/access/tuptoaster.h 2 Apr 2007 22:41:06 -0000 > @@ -38,12 +38,17 @@ > * Note: sizeof(PageHeaderData) includes the first ItemId, but we have > * to allow for 3 more, if we want to fit 4 tuples on a page. > */ > -#define TOAST_TUPLE_THRESHOLD \ > - MAXALIGN_DOWN((BLCKSZ - \ > - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \ > - / 4) > > -#define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD > +/* #define TOAST_TUPLE_THRESHOLD \ */ > +/* MAXALIGN_DOWN((BLCKSZ - \ */ > +/* MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \ */ > +/* / TOAST_DENOMINATOR) */ > + > +/* #define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD */ > + > +#define TOAST_TUPLE_THRESHOLD toast_default_threshold > + > +#define TOAST_TUPLE_TARGET toast_default_threshold > > /* > * If an index value is larger than TOAST_INDEX_TARGET, we will try to > @@ -65,11 +70,13 @@ > * NB: you cannot change this value without forcing initdb, at least not > * if your DB contains any multi-chunk toasted values. > */ > -#define TOAST_MAX_CHUNK_SIZE (TOAST_TUPLE_THRESHOLD - \ > +#define TOAST_MAX_CHUNK_SIZE ((MAXALIGN_DOWN((BLCKSZ - \ > + MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \ > + / 4)) - \ > MAXALIGN(offsetof(HeapTupleHeaderData, t_bits)) - \ > sizeof(Oid) - \ > sizeof(int32) - \ > - VARHDRSZ) > + VARHDRSZ) > > > /* ---------- > > -- > output = ("cbbrowne" "@" "linuxdatabases.info") > http://cbbrowne.com/info/linuxxian.html > When a man talks dirty to a woman, its sexual harassment. When a woman > talks dirty to a man, it's 3.95 per minute. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- 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. +
Bruce Momjian <bruce@momjian.us> writes:
> Tom, are you going to do this for 8.3?
Right, I promised to do that --- will work on it now.
regards, tom lane
Chris Browne <cbbrowne@acm.org> writes:
> tgl@sss.pgh.pa.us (Tom Lane) writes:
>> ... tuning the TOAST parameters seems like
>> something we understand well enough already, we just need to put some
>> cycles into testing different alternatives. I would have no objection
>> to someone working on that during April and delivering a final patch
>> sometime before beta.
> Here's a "drafty" patch that *tries* to do this using a GUC variable;
> it passes some interactive testing.
I came across a couple of issues while fooling with decoupling
TOAST_TUPLE_THRESHOLD from TOAST_MAX_CHUNK_SIZE:
* Should TOAST_TUPLE_TARGET be configurable separately from
TOAST_TUPLE_THRESHOLD? It certainly doesn't make sense for the target
to be larger, but perhaps it is sane to want it to be smaller.
* There's a hardwired assumption in the system that
TOAST_TUPLE_THRESHOLD is invariant: we do not create a toast table at
all when we can prove that the maximum tuple width is less than
TOAST_TUPLE_THRESHOLD (see needs_toast_table() in toasting.c).
Clearly this will not do if TOAST_TUPLE_THRESHOLD can be changed.
Should we abandon the notion altogether, and create a toast table
anytime the table contains any toastable types? Or should we revel
in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary
depending on the current threshold setting? We'd have to fix the
toaster routines to not try to push stuff out-of-line when there is no
out-of-line to push to ... but I think we probably had better do that
anyway for robustness, if we're allowing any variability at all in these
numbers.
Comments?
regards, tom lane
I wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
>> Is there any reason to experiment with this? I would have thought we would
>> divorce TOAST_MAX_CHUNK_SIZE from TOAST_THRESHOLD and hard code it as the same
>> expression that's there now. Ie, the largest size that can fit in a page.
> No, right now it's the largest size that you can fit 4 on a page. It's
> not obvious to me that 4 is optimal once it's divorced from TOAST_THRESHOLD.
> It seems possible that the correct number is 1, and even if it's useful
> to keep the tuples smaller than that, there's no reason to assume 4 is
> the best number per page.
I've just committed changes that make it trivial to experiment with the
number of toast tuples per page:
#define EXTERN_TUPLES_PER_PAGE 4 /* tweak only this */
/* Note: sizeof(PageHeaderData) includes the first ItemId on the page */
#define EXTERN_TUPLE_MAX_SIZE \ MAXALIGN_DOWN((BLCKSZ - \ MAXALIGN(sizeof(PageHeaderData) +
(EXTERN_TUPLES_PER_PAGE-1)* sizeof(ItemIdData))) \ / EXTERN_TUPLES_PER_PAGE)
#define TOAST_MAX_CHUNK_SIZE \ (EXTERN_TUPLE_MAX_SIZE - \
MAXALIGN(offsetof(HeapTupleHeaderData,t_bits)) - \ sizeof(Oid) - \
sizeof(int32)- \ VARHDRSZ)
Anyone who's got time to run performance experiments, have at it ...
regards, tom lane
I wrote:
> ... should we revel
> in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary
> depending on the current threshold setting? We'd have to fix the
> toaster routines to not try to push stuff out-of-line when there is no
> out-of-line to push to ... but I think we probably had better do that
> anyway for robustness, if we're allowing any variability at all in these
> numbers.
Actually, upon looking closely at the toast code, it already does the
right thing when there's no toast table. Good on someone for getting
that right. But we still need to think about whether it's sane for
CREATE/ALTER TABLE to condition the create-a-toast-table decision on
a parameter that may now be changeable.
regards, tom lane
tgl@sss.pgh.pa.us (Tom Lane) writes: > Chris Browne <cbbrowne@acm.org> writes: >> tgl@sss.pgh.pa.us (Tom Lane) writes: >>> ... tuning the TOAST parameters seems like >>> something we understand well enough already, we just need to put some >>> cycles into testing different alternatives. I would have no objection >>> to someone working on that during April and delivering a final patch >>> sometime before beta. > >> Here's a "drafty" patch that *tries* to do this using a GUC variable; >> it passes some interactive testing. > > I came across a couple of issues while fooling with decoupling > TOAST_TUPLE_THRESHOLD from TOAST_MAX_CHUNK_SIZE: > > * Should TOAST_TUPLE_TARGET be configurable separately from > TOAST_TUPLE_THRESHOLD? It certainly doesn't make sense for the target > to be larger, but perhaps it is sane to want it to be smaller. In the longer run, it would be desirable for there to be by-table configurability. Ergo my use of the word "default" in the variable name; that default can remain relevant even in a future 8.4 enhancement. I'm not sure what to prefer with regards to TOAST_TUPLE_TARGET; as you say, it oughtn't be larger than the THRESHOLD value, but I'm not sure how to rationally set it to a specific lower value. > * There's a hardwired assumption in the system that > TOAST_TUPLE_THRESHOLD is invariant: we do not create a toast table at > all when we can prove that the maximum tuple width is less than > TOAST_TUPLE_THRESHOLD (see needs_toast_table() in toasting.c). > Clearly this will not do if TOAST_TUPLE_THRESHOLD can be changed. > Should we abandon the notion altogether, and create a toast table > anytime the table contains any toastable types? Or should we revel > in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary > depending on the current threshold setting? We'd have to fix the > toaster routines to not try to push stuff out-of-line when there is no > out-of-line to push to ... but I think we probably had better do that > anyway for robustness, if we're allowing any variability at all in these > numbers. > > Comments? In the 8.3 context, it seems to me that "simplicity rulez." In some future version, it would be attractive to have this all variable on a table by table basis; it would at present seem preferable for the default behaviour to be as little divergent from past behaviour as possible. I think I'd be willing to live with the logic that there's no toast table defined if it was "proven" at create time that we couldn't need TOAST. That would conform with present behaviour, and remains simple. The other logical option would be to always create the TOAST table if there exist extendible columns. Those two seem to be the options that are most rational to choose between. I'm happy to defer to well-argued opinions on the matter... -- (reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc")) http://cbbrowne.com/info/lsf.html "Please, Captain. Not in front of the Klingons." -- Leonard Nimoy as Spock in Star Trek V, The Final Frontier
> > ... should we revel > > in configurability, and allow CREATE TABLE/ALTER TABLE behavior to > > vary depending on the current threshold setting? We'd have to fix the > > toaster routines to not try to push stuff out-of-line when there is no > > out-of-line to push to ... but I think we probably had better do that > > anyway for robustness, if we're allowing any variability at all in > > these numbers. > > Actually, upon looking closely at the toast code, it already > does the right thing when there's no toast table. Good on > someone for getting that right. But we still need to think > about whether it's sane for CREATE/ALTER TABLE to condition > the create-a-toast-table decision on a parameter that may now > be changeable. I think it is ok to decide during creation with current settings. When a user wants a toast table that has not been created we can direct them to use some dummy "alter table ... set storage ..." and create a toast table if it does not exist (and the new settings opt for one). And a new threshold has immediate consequences for inline compression, so a change is not ignored. Andreas
Chris Browne <cbbrowne@acm.org> writes:
> Here's a "drafty" patch that *tries* to do this using a GUC variable;
> it passes some interactive testing.
BTW, it strikes me that a GUC variable is quite the wrong way to go
about this. The right way is a table storage parameter, a la FILLFACTOR,
so that it can be set on a per-table basis. That would also give us a
chance to fix my concern about needs_toast_table: the case where we
might need a toast table that we didn't need before is where the toast
threshold is lowered via ALTER TABLE SET, and we could reasonably make
that command recheck the situation.
regards, tom lane
Tom, On 4/3/07 7:15 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > BTW, it strikes me that a GUC variable is quite the wrong way to go > about this. The right way is a table storage parameter, a la FILLFACTOR, > so that it can be set on a per-table basis. That would also give us a > chance to fix my concern about needs_toast_table: the case where we > might need a toast table that we didn't need before is where the toast > threshold is lowered via ALTER TABLE SET, and we could reasonably make > that command recheck the situation. This also seems to also support further development along the vertical partitioning path. Any thought on support of per-column TOAST tables? - Luke
On Mon, 2007-04-02 at 22:23 -0400, Tom Lane wrote: > Chris Browne <cbbrowne@acm.org> writes: > > tgl@sss.pgh.pa.us (Tom Lane) writes: > >> ... tuning the TOAST parameters seems like > >> something we understand well enough already, we just need to put some > >> cycles into testing different alternatives. I would have no objection > >> to someone working on that during April and delivering a final patch > >> sometime before beta. > > > Here's a "drafty" patch that *tries* to do this using a GUC variable; > > it passes some interactive testing. Having both default GUC and individual table-level WITH parameters seems like the best way to me. > I came across a couple of issues while fooling with decoupling > TOAST_TUPLE_THRESHOLD from TOAST_MAX_CHUNK_SIZE: > > * Should TOAST_TUPLE_TARGET be configurable separately from > TOAST_TUPLE_THRESHOLD? It certainly doesn't make sense for the target > to be larger, but perhaps it is sane to want it to be smaller. I can't see I'd ever set them differently in practice. Sounds like too many people would get confused and set them wrong anyhow. > * There's a hardwired assumption in the system that > TOAST_TUPLE_THRESHOLD is invariant: we do not create a toast table at > all when we can prove that the maximum tuple width is less than > TOAST_TUPLE_THRESHOLD (see needs_toast_table() in toasting.c). > Clearly this will not do if TOAST_TUPLE_THRESHOLD can be changed. > Should we abandon the notion altogether, and create a toast table > anytime the table contains any toastable types? That will create many more catalog entries than we have now, which seems not that great a side-effect. > Or should we revel > in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary > depending on the current threshold setting? We'd have to fix the > toaster routines to not try to push stuff out-of-line when there is no > out-of-line to push to ... but I think we probably had better do that > anyway for robustness, if we're allowing any variability at all in these > numbers. Sounds like the best plan. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > On Mon, 2007-04-02 at 22:23 -0400, Tom Lane wrote: > > Chris Browne <cbbrowne@acm.org> writes: > > > tgl@sss.pgh.pa.us (Tom Lane) writes: > > >> ... tuning the TOAST parameters seems like > > >> something we understand well enough already, we just need to put some > > >> cycles into testing different alternatives. I would have no objection > > >> to someone working on that during April and delivering a final patch > > >> sometime before beta. > > > > > Here's a "drafty" patch that *tries* to do this using a GUC variable; > > > it passes some interactive testing. > > Having both default GUC and individual table-level WITH parameters seems > like the best way to me. Agreed. > > I came across a couple of issues while fooling with decoupling > > TOAST_TUPLE_THRESHOLD from TOAST_MAX_CHUNK_SIZE: > > > > * Should TOAST_TUPLE_TARGET be configurable separately from > > TOAST_TUPLE_THRESHOLD? It certainly doesn't make sense for the target > > to be larger, but perhaps it is sane to want it to be smaller. > > I can't see I'd ever set them differently in practice. Sounds like too > many people would get confused and set them wrong anyhow. OK. > > * There's a hardwired assumption in the system that > > TOAST_TUPLE_THRESHOLD is invariant: we do not create a toast table at > > all when we can prove that the maximum tuple width is less than > > TOAST_TUPLE_THRESHOLD (see needs_toast_table() in toasting.c). > > Clearly this will not do if TOAST_TUPLE_THRESHOLD can be changed. > > Should we abandon the notion altogether, and create a toast table > > anytime the table contains any toastable types? > > That will create many more catalog entries than we have now, which seems > not that great a side-effect. OK, but we need to throw a clear message when the TOAST table needs to be created by the administrator. > > Or should we revel > > in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary > > depending on the current threshold setting? We'd have to fix the > > toaster routines to not try to push stuff out-of-line when there is no > > out-of-line to push to ... but I think we probably had better do that > > anyway for robustness, if we're allowing any variability at all in these > > numbers. > > Sounds like the best plan. Agreed. If you add a GUC without pg_class storage of the value at CREATE TABLE/ALTER TABLE time, the GUC has to be checked at INSERT time, meaning if the GUC changes, you might need a TOAST table during an INSERT, which is going to fail. The big question is whether this is for 8.3 or 8.4. I think adding a GUC just for 8.3, without pg_class storage, will be a problem because the GUC behavior will change once pg_class storage exists, i.e. GUC will control at CREATE TABLE/ALTER TABLE rather than at INSERT time. -- 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. +
Bruce Momjian <bruce@momjian.us> writes:
> Simon Riggs wrote:
>> Having both default GUC and individual table-level WITH parameters seems
>> like the best way to me.
> Agreed.
There's an extremely good reason not to have a GUC variable, which is
that changes in it would fail to reflect into decisions about whether to
create TOAST tables. When I first brought up the point I didn't see a
way around it, but now that I do, I don't think we should expose a
failure mode just to have a GUC.
> OK, but we need to throw a clear message when the TOAST table needs to
> be created by the administrator.
No, we just need to not have a GUC for this. There's no GUC for default
fill factor; have you seen anyone complain about that?
> The big question is whether this is for 8.3 or 8.4.
What I would definitely like to see for 8.3 is some performance testing
done to determine whether we ought to change the current defaults.
(Both TOAST_TUPLES_PER_PAGE and EXTERN_TUPLES_PER_PAGE ought to be looked
at.)
Whether it's possible to get the storage parameter in there depends on
how soon someone produces a patch. Given that we understand this area
fairly well, I personally would be willing to give it a pass on the
"feature freeze" rule, as long as we have the patch by say mid-April.
regards, tom lane
On Wed, 2007-04-04 at 16:26 -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Simon Riggs wrote: > >> Having both default GUC and individual table-level WITH parameters seems > >> like the best way to me. > > > Agreed. > > There's an extremely good reason not to have a GUC variable, which is > that changes in it would fail to reflect into decisions about whether to > create TOAST tables. When I first brought up the point I didn't see a > way around it, but now that I do, I don't think we should expose a > failure mode just to have a GUC. It depends how it works. If the GUC was a default that was applied only at CREATE TABLE time, then it would be safe. Changing default_with_oids didn't cause all tables to stop/start using oids. Why would it? > > OK, but we need to throw a clear message when the TOAST table needs to > > be created by the administrator. > > No, we just need to not have a GUC for this. There's no GUC for default > fill factor; have you seen anyone complain about that? I'd rather set it once than many times, thats all. I certainly care more about temp_tablespaces than I do about this GUC... that is something I'll be moaning about if that gets deferred. > > The big question is whether this is for 8.3 or 8.4. > > What I would definitely like to see for 8.3 is some performance testing > done to determine whether we ought to change the current defaults. > (Both TOAST_TUPLES_PER_PAGE and EXTERN_TUPLES_PER_PAGE ought to be looked > at.) > > Whether it's possible to get the storage parameter in there depends on > how soon someone produces a patch. Given that we understand this area > fairly well, I personally would be willing to give it a pass on the > "feature freeze" rule, as long as we have the patch by say mid-April. I meant to say a clear "yes" to that, but I've other business stuff for two weeks in mid-April so I'll need to rely on colleagues to take up the challenge. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > On Wed, 2007-04-04 at 16:26 -0400, Tom Lane wrote: > > Bruce Momjian <bruce@momjian.us> writes: > > > Simon Riggs wrote: > > >> Having both default GUC and individual table-level WITH parameters seems > > >> like the best way to me. > > > > > Agreed. > > > > There's an extremely good reason not to have a GUC variable, which is > > that changes in it would fail to reflect into decisions about whether to > > create TOAST tables. When I first brought up the point I didn't see a > > way around it, but now that I do, I don't think we should expose a > > failure mode just to have a GUC. > > It depends how it works. If the GUC was a default that was applied only > at CREATE TABLE time, then it would be safe. Well, if the GUC applies at CREATE TABLE, it is storing the GUC in pg_class, which was my point. > Changing default_with_oids didn't cause all tables to stop/start using > oids. Why would it? oid status is stored in pg_class. > > > OK, but we need to throw a clear message when the TOAST table needs to > > > be created by the administrator. > > > > No, we just need to not have a GUC for this. There's no GUC for default > > fill factor; have you seen anyone complain about that? > > I'd rather set it once than many times, thats all. Let's find the optimal value for the default, and then you use that all the time and just change it when you want to when you create the column. > I certainly care more about temp_tablespaces than I do about this GUC... > that is something I'll be moaning about if that gets deferred. I don't see how they are related. -- 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. +
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > What I would definitely like to see for 8.3 is some performance testing > done to determine whether we ought to change the current defaults. > (Both TOAST_TUPLES_PER_PAGE and EXTERN_TUPLES_PER_PAGE ought to be looked > at.) It will take some thinking before it's even clear what we should be measuring. The "optimal" value will depend heavily on the usage pattern so the best value for the default will be something hand-wavy like "the smallest tuple size where the cost of a select including the column is greater than the time saved on a select not including the column" or something like that. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
tgl@sss.pgh.pa.us (Tom Lane) writes: > Bruce Momjian <bruce@momjian.us> writes: >> The big question is whether this is for 8.3 or 8.4. > > What I would definitely like to see for 8.3 is some performance testing > done to determine whether we ought to change the current defaults. > (Both TOAST_TUPLES_PER_PAGE and EXTERN_TUPLES_PER_PAGE ought to be looked > at.) > > Whether it's possible to get the storage parameter in there depends on > how soon someone produces a patch. Given that we understand this area > fairly well, I personally would be willing to give it a pass on the > "feature freeze" rule, as long as we have the patch by say mid-April. If I had to end of April, I'd volunteer. Alas, I will be unavailable between tomorrow and mid-April. It seems to me that changing the denominator from 4 to something somewhat higher (16 comes to mind) would probably be "the patch," and would likely be useful for a fair number of cases. I'd really like to see something like this (e.g. - something that would start TOASTing for, say, tuples > 500 bytes) in 8.3; this seems to me to have the potential to be a significant optimization that would be VERY cheap to implement. "Low hanging fruit," so to speak. EDB might like to use this as a route into a tunable "column store" some time later; I don't think that should stop us from an improvement today... -- "cbbrowne","@","linuxfinances.info" http://www3.sympatico.ca/cbbrowne/rdbms.html "MSDOS didn't get as bad as it is overnight -- it took over ten years of careful development." -- <dmeggins@aix1.uottawa.ca>
Patch rejected, since we have decided we need to have this as part of
CREATE/ALTER table, rather than a GUC.
---------------------------------------------------------------------------
Chris Browne wrote:
> tgl@sss.pgh.pa.us (Tom Lane) writes:
> > "Simon Riggs" <simon@2ndquadrant.com> writes:
> >> Well it certainly seems worth separating them. It does seem possible
> >> that recursive toasting effected some of the earlier results we looked
> >> at.
> >
> >> Would you like me to do this, or will you?
> >
> > I'm willing to do the code changes to separate TOAST_THRESHOLD from
> > the toast chunk size, but I do not have the time or facilities to do
> > any performance testing for different parameter choices. Anyone want
> > to work on that?
> >
> >> I'd like to get some mechanism for reducing WAL volume into 8.3, whether
> >> its configurable toast or WAL reduction for UPDATEs. If for no other
> >> reason than making backup and availability solutions more manageable.
> >
> > I think the WAL-reduction proposal needs more time and thought than is
> > feasible before 8.3. OTOH, tuning the TOAST parameters seems like
> > something we understand well enough already, we just need to put some
> > cycles into testing different alternatives. I would have no objection
> > to someone working on that during April and delivering a final patch
> > sometime before beta.
>
> Here's a "drafty" patch that *tries* to do this using a GUC variable;
> it passes some interactive testing. It probably needs an
> assign_hook() function to do further validation (probably to make sure
> that sizes are rightly aligned on both 32 and 64 bit platforms); feel
> free to consider me incompetent at this stage at generating such...
>
> I would *very* much like to see something of this sort in 8.3; that
> would be of definite value to some of our applications which store
> data that is a bit too small to meet the present
> TOAST_TUPLE_THRESHOLD. (E.g. - it's worth noting that common
> SOAP-like XML requests are in the 700-1000 byte range; such values are
> generally nicely compressible and are often not likely to be used in
> summary-oriented queries on mainline tables...)
>
> I don't think I can come up with a performance "test suite" this week,
> and will be unavailable from April 6-14th; if others were to find this
> valuable, and volunteer to set up some sort of test in the interim,
> that would be super. Absent that, I should be able to do some work on
> this in the latter half of April.
>
> set toast_default_threshold TO 128;
> create table sample (id serial primary key, txt text);
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> create table sample (id serial primary key, txt text);
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> set toast_default_threshold TO 250;
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
> insert into sample (txt) values
('12347890347902317849018732417094739027847891230978041237890341278904123789012347980123479081234789041378901234789041237980123478901423789014237980123478901342789014237890142378901423');
>
> test@[local]:5883=# vacuum verbose sample;
> INFO: vacuuming "public.sample"
> INFO: index "sample_pkey" now contains 52 row versions in 2 pages
> DETAIL: 0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "sample": found 0 removable, 52 nonremovable row versions in 1 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 1 pages contain useful free space.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: vacuuming "pg_toast.pg_toast_16387"
> INFO: index "pg_toast_16387_index" now contains 25 row versions in 2 pages
> DETAIL: 0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "pg_toast_16387": found 0 removable, 25 nonremovable row versions in 1 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 1 pages contain useful free space.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> VACUUM
> ------------------------------------
> ? Makefile.global
> ? backend/postgres
> ? backend/catalog/postgres.bki
> ? backend/catalog/postgres.description
> ? backend/catalog/postgres.shdescription
> ? backend/utils/mb/conversion_procs/conversion_create.sql
> ? backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0.0
> ? backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0.0
> ? backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0.0
> ? backend/utils/mb/conversion_procs/euc_jis_2004_and_shift_jis_2004/libeuc_jis_2004_and_shift_jis_2004.so.0.0
> ? backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0.0
> ? backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0.0
> ? backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0.0
> ? backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0.0
> ? backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004/libutf8_and_euc_jis_2004.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_shift_jis_2004/libutf8_and_shift_jis_2004.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0.0
> ? backend/utils/mb/conversion_procs/utf8_and_win/libutf8_and_win.so.0.0
> ? bin/initdb/initdb
> ? bin/ipcclean/ipcclean
> ? bin/pg_config/pg_config
> ? bin/pg_controldata/pg_controldata
> ? bin/pg_ctl/pg_ctl
> ? bin/pg_dump/pg_dump
> ? bin/pg_dump/pg_dumpall
> ? bin/pg_dump/pg_restore
> ? bin/pg_resetxlog/pg_resetxlog
> ? bin/psql/psql
> ? bin/scripts/clusterdb
> ? bin/scripts/createdb
> ? bin/scripts/createlang
> ? bin/scripts/createuser
> ? bin/scripts/dropdb
> ? bin/scripts/droplang
> ? bin/scripts/dropuser
> ? bin/scripts/reindexdb
> ? bin/scripts/vacuumdb
> ? include/pg_config.h
> ? include/stamp-h
> ? interfaces/ecpg/compatlib/libecpg_compat.so.2.2
> ? interfaces/ecpg/compatlib/libecpg_compat.so.2.3
> ? interfaces/ecpg/ecpglib/libecpg.so.5.2
> ? interfaces/ecpg/ecpglib/libecpg.so.5.3
> ? interfaces/ecpg/include/ecpg_config.h
> ? interfaces/ecpg/pgtypeslib/libpgtypes.so.2.2
> ? interfaces/ecpg/pgtypeslib/libpgtypes.so.2.3
> ? interfaces/ecpg/preproc/ecpg
> ? interfaces/ecpg/test/sql/dyntest2
> ? interfaces/ecpg/test/sql/dyntest2.c
> ? interfaces/libpq/exports.list
> ? interfaces/libpq/libpq.so.5.0
> ? interfaces/libpq/libpq.so.5.1
> ? pl/plpgsql/src/libplpgsql.so.1.0
> ? port/pg_config_paths.h
> ? test/regress/libregress.so.0.0
> ? test/regress/log
> ? test/regress/pg_regress
> ? test/regress/results
> ? test/regress/testtablespace
> ? test/regress/tmp_check
> ? test/regress/expected/constraints.out
> ? test/regress/expected/copy.out
> ? test/regress/expected/create_function_1.out
> ? test/regress/expected/create_function_2.out
> ? test/regress/expected/largeobject.out
> ? test/regress/expected/largeobject_1.out
> ? test/regress/expected/misc.out
> ? test/regress/expected/tablespace.out
> ? test/regress/sql/constraints.sql
> ? test/regress/sql/copy.sql
> ? test/regress/sql/create_function_1.sql
> ? test/regress/sql/create_function_2.sql
> ? test/regress/sql/largeobject.sql
> ? test/regress/sql/misc.sql
> ? test/regress/sql/tablespace.sql
> ? timezone/zic
> Index: backend/access/heap/heapam.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/heapam.c,v
> retrieving revision 1.230
> diff -c -u -r1.230 heapam.c
> --- backend/access/heap/heapam.c 29 Mar 2007 00:15:37 -0000 1.230
> +++ backend/access/heap/heapam.c 2 Apr 2007 22:41:05 -0000
> @@ -57,6 +57,7 @@
> #include "utils/relcache.h"
> #include "utils/syscache.h"
>
> +extern int toast_default_threshold;
>
> static XLogRecPtr log_heap_update(Relation reln, Buffer oldbuf,
> ItemPointerData from, Buffer newbuf, HeapTuple newtup, bool move);
> Index: backend/access/heap/tuptoaster.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/tuptoaster.c,v
> retrieving revision 1.72
> diff -c -u -r1.72 tuptoaster.c
> --- backend/access/heap/tuptoaster.c 29 Mar 2007 00:15:37 -0000 1.72
> +++ backend/access/heap/tuptoaster.c 2 Apr 2007 22:41:05 -0000
> @@ -42,6 +42,7 @@
>
> #undef TOAST_DEBUG
>
> +extern int toast_default_threshold;
> static void toast_delete_datum(Relation rel, Datum value);
> static Datum toast_save_datum(Relation rel, Datum value,
> bool use_wal, bool use_fsm);
> Index: backend/catalog/toasting.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/catalog/toasting.c,v
> retrieving revision 1.5
> diff -c -u -r1.5 toasting.c
> --- backend/catalog/toasting.c 9 Jan 2007 02:14:11 -0000 1.5
> +++ backend/catalog/toasting.c 2 Apr 2007 22:41:05 -0000
> @@ -33,7 +33,7 @@
>
> static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid);
> static bool needs_toast_table(Relation rel);
> -
> +extern int toast_default_threshold;
>
> /*
> * AlterTableCreateToastTable
> Index: backend/utils/misc/guc.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
> retrieving revision 1.383
> diff -c -u -r1.383 guc.c
> --- backend/utils/misc/guc.c 19 Mar 2007 23:38:30 -0000 1.383
> +++ backend/utils/misc/guc.c 2 Apr 2007 22:41:06 -0000
> @@ -195,6 +195,7 @@
> int client_min_messages = NOTICE;
> int log_min_duration_statement = -1;
> int log_temp_files = -1;
> +int toast_default_threshold = MAXALIGN_DOWN((BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) /
4);
>
> int num_temp_buffers = 1000;
>
> @@ -1695,6 +1696,17 @@
> -1, -1, INT_MAX, NULL, NULL
> },
>
> + {
> + {"toast_default_threshold", PGC_SUSET, CUSTOM_OPTIONS,
> + gettext_noop("Tuples larger than this size will be considered for TOASTing"),
> + gettext_noop("Default is ~ BLKSIZE / 4"),
> + NULL
> + },
> + &toast_default_threshold,
> + MAXALIGN_DOWN((BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) / 16),
> + 128, BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData)), NULL, NULL
> + },
> +
> /* End-of-list marker */
> {
> {NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL
> Index: include/access/tuptoaster.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/access/tuptoaster.h,v
> retrieving revision 1.33
> diff -c -u -r1.33 tuptoaster.h
> --- include/access/tuptoaster.h 29 Mar 2007 00:15:39 -0000 1.33
> +++ include/access/tuptoaster.h 2 Apr 2007 22:41:06 -0000
> @@ -38,12 +38,17 @@
> * Note: sizeof(PageHeaderData) includes the first ItemId, but we have
> * to allow for 3 more, if we want to fit 4 tuples on a page.
> */
> -#define TOAST_TUPLE_THRESHOLD \
> - MAXALIGN_DOWN((BLCKSZ - \
> - MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \
> - / 4)
>
> -#define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD
> +/* #define TOAST_TUPLE_THRESHOLD \ */
> +/* MAXALIGN_DOWN((BLCKSZ - \ */
> +/* MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \ */
> +/* / TOAST_DENOMINATOR) */
> +
> +/* #define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD */
> +
> +#define TOAST_TUPLE_THRESHOLD toast_default_threshold
> +
> +#define TOAST_TUPLE_TARGET toast_default_threshold
>
> /*
> * If an index value is larger than TOAST_INDEX_TARGET, we will try to
> @@ -65,11 +70,13 @@
> * NB: you cannot change this value without forcing initdb, at least not
> * if your DB contains any multi-chunk toasted values.
> */
> -#define TOAST_MAX_CHUNK_SIZE (TOAST_TUPLE_THRESHOLD - \
> +#define TOAST_MAX_CHUNK_SIZE ((MAXALIGN_DOWN((BLCKSZ - \
> + MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \
> + / 4)) - \
> MAXALIGN(offsetof(HeapTupleHeaderData, t_bits)) - \
> sizeof(Oid) - \
> sizeof(int32) - \
> - VARHDRSZ)
> + VARHDRSZ)
>
>
> /* ----------
>
> --
> output = ("cbbrowne" "@" "linuxdatabases.info")
> http://cbbrowne.com/info/linuxxian.html
> When a man talks dirty to a woman, its sexual harassment. When a woman
> talks dirty to a man, it's 3.95 per minute.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
-- 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. +
<p><font size="2">Not just EDB :-)<br /><br /> - Luke<br /><br /> Msg is shrt cuz m on ma treo<br /><br /> -----OriginalMessage-----<br /> From: Chris Browne [<a href="mailto:cbbrowne@acm.org">mailto:cbbrowne@acm.org</a>]<br/> Sent: Wednesday, April 04, 2007 10:07 PM Eastern StandardTime<br /> To: pgsql-hackers@postgresql.org<br /> Subject: Re: [HACKERS] Modifying TOAST thresholds<br/><br /> tgl@sss.pgh.pa.us (Tom Lane) writes:<br /> > Bruce Momjian <bruce@momjian.us> writes:<br />>> The big question is whether this is for 8.3 or 8.4.<br /> ><br /> > What I would definitely like to seefor 8.3 is some performance testing<br /> > done to determine whether we ought to change the current defaults.<br />> (Both TOAST_TUPLES_PER_PAGE and EXTERN_TUPLES_PER_PAGE ought to be looked<br /> > at.)<br /> ><br /> > Whetherit's possible to get the storage parameter in there depends on<br /> > how soon someone produces a patch. Giventhat we understand this area<br /> > fairly well, I personally would be willing to give it a pass on the<br /> >"feature freeze" rule, as long as we have the patch by say mid-April.<br /><br /> If I had to end of April, I'd volunteer. Alas, I will be unavailable<br /> between tomorrow and mid-April.<br /><br /> It seems to me that changing thedenominator from 4 to something<br /> somewhat higher (16 comes to mind) would probably be "the patch," and<br /> wouldlikely be useful for a fair number of cases. I'd really like to<br /> see something like this (e.g. - something thatwould start TOASTing<br /> for, say, tuples > 500 bytes) in 8.3; this seems to me to have the<br /> potential to bea significant optimization that would be VERY cheap to<br /> implement. "Low hanging fruit," so to speak.<br /><br />EDB might like to use this as a route into a tunable "column store"<br /> some time later; I don't think that should stopus from an improvement<br /> today...<br /> --<br /> "cbbrowne","@","linuxfinances.info"<br /><a href="http://www3.sympatico.ca/cbbrowne/rdbms.html">http://www3.sympatico.ca/cbbrowne/rdbms.html</a><br/> "MSDOS didn't getas bad as it is overnight -- it took over ten years<br /> of careful development." -- <dmeggins@aix1.uottawa.ca><br/><br /> ---------------------------(end of broadcast)---------------------------<br />TIP 4: Have you searched our list archives?<br /><br /> <a href="http://archives.postgresql.org">http://archives.postgresql.org</a><br/><br /></font>
Chris Browne <cbbrowne@acm.org> writes:
> tgl@sss.pgh.pa.us (Tom Lane) writes:
>> What I would definitely like to see for 8.3 is some performance testing
>> done to determine whether we ought to change the current defaults.
>> (Both TOAST_TUPLES_PER_PAGE and EXTERN_TUPLES_PER_PAGE ought to be looked
>> at.)
> It seems to me that changing the denominator from 4 to something
> somewhat higher (16 comes to mind) would probably be "the patch," and
> would likely be useful for a fair number of cases. I'd really like to
> see something like this (e.g. - something that would start TOASTing
> for, say, tuples > 500 bytes) in 8.3; this seems to me to have the
> potential to be a significant optimization that would be VERY cheap to
> implement. "Low hanging fruit," so to speak.
So let's see some performance measurements to back up that intuition.
regards, tom lane
I have seen no one do peroformance testing of this, so it seems it will have to wait for 8.4. --------------------------------------------------------------------------- Gregory Stark wrote: > "Tom Lane" <tgl@sss.pgh.pa.us> writes: > > > What I would definitely like to see for 8.3 is some performance testing > > done to determine whether we ought to change the current defaults. > > (Both TOAST_TUPLES_PER_PAGE and EXTERN_TUPLES_PER_PAGE ought to be looked > > at.) > > It will take some thinking before it's even clear what we should be measuring. > The "optimal" value will depend heavily on the usage pattern so the best value > for the default will be something hand-wavy like "the smallest tuple size > where the cost of a select including the column is greater than the time saved > on a select not including the column" or something like that. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- 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. +
bruce@momjian.us (Bruce Momjian) writes: > I have seen no one do peroformance testing of this, so it seems it > will have to wait for 8.4. I didn't have time... I'll see if I can find a decent place to document how to tweak the threshold, as that seems like it could be worth doing in cases where it is known that there is benefit to TOASTing smaller tuples for one's well-understood workload. (e.g. - we've got a case where dropping the threshold to ~900 bytes would give us a big win for certain databases and tables.) -- let name="cbbrowne" and tld="linuxfinances.info" in name ^ "@" ^ tld;; http://linuxfinances.info/info/unix.html recursion, n: See recursion.
Chris Browne <cbbrowne@acm.org> writes:
> bruce@momjian.us (Bruce Momjian) writes:
>> I have seen no one do peroformance testing of this, so it seems it
>> will have to wait for 8.4.
> I didn't have time...
> (e.g. - we've got a case where dropping the threshold to ~900 bytes
> would give us a big win for certain databases and tables.)
How do you know? Seems like you've got a readymade test case there.
regards, tom lane
tgl@sss.pgh.pa.us (Tom Lane) writes: > Chris Browne <cbbrowne@acm.org> writes: >> bruce@momjian.us (Bruce Momjian) writes: >>> I have seen no one do peroformance testing of this, so it seems it >>> will have to wait for 8.4. > >> I didn't have time... > >> (e.g. - we've got a case where dropping the threshold to ~900 bytes >> would give us a big win for certain databases and tables.) > > How do you know? Seems like you've got a readymade test case there. I did some testing with Known Scenario, and found, indeed, that there was a significant gain to be had. I documented it at least partially on March 21... <http://www.nabble.com/Re%3A-TOASTing-smaller-things-p9602766.html> Unfortunately, the sample query that I used to validate usefulness isn't one I can share :-(. More importantly, it's only one test case, and is strongly influenced by some *very* strong regularity to the patterns of updates that take place to the table that I looked at. It's not nearly good enough to treat as a generalizable case. -- output = ("cbbrowne" "@" "linuxfinances.info") http://linuxdatabases.info/info/spiritual.html MICROS~1 is to quality software what MacDonalds is to gourmet cooking