Re: Modifying TOAST thresholds

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.