Re: Modifying TOAST thresholds

tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> "Simon Riggs" <simon(at)2ndquadrant(dot)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(at)[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.