[EMAIL PROTECTED] (Tom Lane) writes: > "Simon Riggs" <[EMAIL PROTECTED]> 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'); [EMAIL PROTECTED]: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