pgsql: Parameter toast_tuple_target controls TOAST for new rows

Поиск
Список
Период
Сортировка
Искать

pgsql: Parameter toast_tuple_target controls TOAST for new rows

От:
Simon Riggs <simon@2ndQuadrant.com>
Дата:
Parameter toast_tuple_target controls TOAST for new rows

Specifies the point at which we try to move long column values
into TOAST tables.

No effect on existing rows.

Discussion: https://postgr.es/m/CANP8+jKsVmw6CX6YP9z7zqkTzcKV1+Uzr3XjKcZW=2Ya00OyQQ@mail.gmail.com

Author: Simon Riggs 
Reviewed-by: Andrew Dunstan 

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/c2513365a0a85e77d3c21adb92fe12cfbe0d1897

Modified Files
--------------
doc/src/sgml/ref/alter_table.sgml      |  2 +-
doc/src/sgml/ref/create_table.sgml     | 21 +++++++++++++++
src/backend/access/common/reloptions.c | 12 +++++++++
src/backend/access/heap/tuptoaster.c   |  2 +-
src/include/utils/rel.h                |  9 +++++++
src/test/regress/expected/strings.out  | 47 ++++++++++++++++++++++++++++++++++
src/test/regress/sql/strings.sql       | 19 ++++++++++++++
7 files changed, 110 insertions(+), 2 deletions(-)

Re: pgsql: Parameter toast_tuple_target controls TOAST for new rows

От:
Simon Riggs <simon@2ndquadrant.com>
Дата:
On 19 November 2017 at 17:52, Simon Riggs  wrote:
> Parameter toast_tuple_target controls TOAST for new rows
>
> Specifies the point at which we try to move long column values
> into TOAST tables.
>
> No effect on existing rows.
>
> Discussion: https://postgr.es/m/CANP8+jKsVmw6CX6YP9z7zqkTzcKV1+Uzr3XjKcZW=2Ya00OyQQ@mail.gmail.com
>
> Author: Simon Riggs 
> Reviewed-by: Andrew Dunstan 
>
> Branch
> ------
> master
>
> Details
> -------
> https://git.postgresql.org/pg/commitdiff/c2513365a0a85e77d3c21adb92fe12cfbe0d1897
>
> Modified Files
> --------------
> doc/src/sgml/ref/alter_table.sgml      |  2 +-
> doc/src/sgml/ref/create_table.sgml     | 21 +++++++++++++++
> src/backend/access/common/reloptions.c | 12 +++++++++
> src/backend/access/heap/tuptoaster.c   |  2 +-
> src/include/utils/rel.h                |  9 +++++++
> src/test/regress/expected/strings.out  | 47 ++++++++++++++++++++++++++++++++++
> src/test/regress/sql/strings.sql       | 19 ++++++++++++++
> 7 files changed, 110 insertions(+), 2 deletions(-)

Am investigating the few buildfarm failures

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pgsql: Parameter toast_tuple_target controls TOAST for new rows

От:
Andres Freund <andres@anarazel.de>
Дата:
On 2017-11-19 19:08:48 -0500, Simon Riggs wrote:
> On 19 November 2017 at 17:52, Simon Riggs  wrote:
> > Parameter toast_tuple_target controls TOAST for new rows
> >
> > Specifies the point at which we try to move long column values
> > into TOAST tables.
> >
> > No effect on existing rows.
> >
> > Discussion: https://postgr.es/m/CANP8+jKsVmw6CX6YP9z7zqkTzcKV1+Uzr3XjKcZW=2Ya00OyQQ@mail.gmail.com
> >
> > Author: Simon Riggs 
> > Reviewed-by: Andrew Dunstan 
> >
> > Branch
> > ------
> > master
> >
> > Details
> > -------
> > https://git.postgresql.org/pg/commitdiff/c2513365a0a85e77d3c21adb92fe12cfbe0d1897
> >
> > Modified Files
> > --------------
> > doc/src/sgml/ref/alter_table.sgml      |  2 +-
> > doc/src/sgml/ref/create_table.sgml     | 21 +++++++++++++++
> > src/backend/access/common/reloptions.c | 12 +++++++++
> > src/backend/access/heap/tuptoaster.c   |  2 +-
> > src/include/utils/rel.h                |  9 +++++++
> > src/test/regress/expected/strings.out  | 47 ++++++++++++++++++++++++++++++++++
> > src/test/regress/sql/strings.sql       | 19 ++++++++++++++
> > 7 files changed, 110 insertions(+), 2 deletions(-)
> 
> Am investigating the few buildfarm failures

The tests look very sensitive to differences in tuple size due to
different alignment requirements. Dependant on what MAXALIGN (and some
others) is the number of tuples fitting on a page will differ.

Greetings,

Andres Freund

Re: pgsql: Parameter toast_tuple_target controls TOAST for new rows

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Andres Freund  writes:
> On 2017-11-19 19:08:48 -0500, Simon Riggs wrote:
>> Am investigating the few buildfarm failures

> The tests look very sensitive to differences in tuple size due to
> different alignment requirements. Dependant on what MAXALIGN (and some
> others) is the number of tuples fitting on a page will differ.

"Few" buildfarm failures?  It's probably going to fail on every 32-bit host.

TBH, I would just remove those test cases.  Even if they were stable
across platforms, they don't directly prove anything at all about
whether the feature does what it's supposed to.

(Also, scaling the results to blocksize seems unlikely to help in
passing on different BLCKSZ configurations...)
		regards, tom lane

Re: pgsql: Parameter toast_tuple_target controls TOAST for new rows

От:
Andres Freund <andres@anarazel.de>
Дата:
On 2017-11-19 19:49:01 -0500, Tom Lane wrote:
> Andres Freund  writes:
> > On 2017-11-19 19:08:48 -0500, Simon Riggs wrote:
> >> Am investigating the few buildfarm failures
> 
> > The tests look very sensitive to differences in tuple size due to
> > different alignment requirements. Dependant on what MAXALIGN (and some
> > others) is the number of tuples fitting on a page will differ.
> 
> "Few" buildfarm failures?  It's probably going to fail on every 32-bit host.
> 
> TBH, I would just remove those test cases.  Even if they were stable
> across platforms, they don't directly prove anything at all about
> whether the feature does what it's supposed to.
> 
> (Also, scaling the results to blocksize seems unlikely to help in
> passing on different BLCKSZ configurations...)

I think it might make sense to rewrite the tests so it doesn't output
any of the sizes, but instead just compares the size of tables with
different thresholds. That should be fairly reliable.

Greetings,

Andres Freund

Re: pgsql: Parameter toast_tuple_target controls TOAST for new rows

От:
Simon Riggs <simon@2ndquadrant.com>
Дата:
On 19 November 2017 at 19:49, Tom Lane  wrote:
> Andres Freund  writes:
>> On 2017-11-19 19:08:48 -0500, Simon Riggs wrote:
>>> Am investigating the few buildfarm failures
>
>> The tests look very sensitive to differences in tuple size due to
>> different alignment requirements. Dependant on what MAXALIGN (and some
>> others) is the number of tuples fitting on a page will differ.
>
> "Few" buildfarm failures?  It's probably going to fail on every 32-bit host.
>
> TBH, I would just remove those test cases.  Even if they were stable
> across platforms, they don't directly prove anything at all about
> whether the feature does what it's supposed to.
>
> (Also, scaling the results to blocksize seems unlikely to help in
> passing on different BLCKSZ configurations...)

I just committed a change while this email arrived. I think what I've
done is essentially this.

The original test was more about demonstrating effectiveness.

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pgsql: Parameter toast_tuple_target controls TOAST for new rows

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Andres Freund  writes:
> On 2017-11-19 19:49:01 -0500, Tom Lane wrote:
>> TBH, I would just remove those test cases.  Even if they were stable
>> across platforms, they don't directly prove anything at all about
>> whether the feature does what it's supposed to.

> I think it might make sense to rewrite the tests so it doesn't output
> any of the sizes, but instead just compares the size of tables with
> different thresholds. That should be fairly reliable.

Hm, what I'd try after a bit of thought is to stuff the same data
into two different tables with different settings chosen to make it
TOAST or not, and then just test the toast tables for zero or nonzero
size.  Setting STORAGE = EXTERNAL on the data column to disable
compression would help make things more stable, too.
		regards, tom lane

Re: pgsql: Parameter toast_tuple_target controls TOAST for new rows

От:
Simon Riggs <simon@2ndquadrant.com>
Дата:
On 19 November 2017 at 19:52, Andres Freund  wrote:
> On 2017-11-19 19:49:01 -0500, Tom Lane wrote:
>> Andres Freund  writes:
>> > On 2017-11-19 19:08:48 -0500, Simon Riggs wrote:
>> >> Am investigating the few buildfarm failures
>>
>> > The tests look very sensitive to differences in tuple size due to
>> > different alignment requirements. Dependant on what MAXALIGN (and some
>> > others) is the number of tuples fitting on a page will differ.
>>
>> "Few" buildfarm failures?  It's probably going to fail on every 32-bit host.
>>
>> TBH, I would just remove those test cases.  Even if they were stable
>> across platforms, they don't directly prove anything at all about
>> whether the feature does what it's supposed to.
>>
>> (Also, scaling the results to blocksize seems unlikely to help in
>> passing on different BLCKSZ configurations...)
>
> I think it might make sense to rewrite the tests so it doesn't output
> any of the sizes, but instead just compares the size of tables with
> different thresholds. That should be fairly reliable.

Thanks for your input.

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pgsql: Parameter toast_tuple_target controls TOAST for new rows

От:
Simon Riggs <simon@2ndquadrant.com>
Дата:
On 19 November 2017 at 20:30, Tom Lane  wrote:
> Andres Freund  writes:
>> On 2017-11-19 19:49:01 -0500, Tom Lane wrote:
>>> TBH, I would just remove those test cases.  Even if they were stable
>>> across platforms, they don't directly prove anything at all about
>>> whether the feature does what it's supposed to.
>
>> I think it might make sense to rewrite the tests so it doesn't output
>> any of the sizes, but instead just compares the size of tables with
>> different thresholds. That should be fairly reliable.
>
> Hm, what I'd try after a bit of thought is to stuff the same data
> into two different tables with different settings chosen to make it
> TOAST or not, and then just test the toast tables for zero or nonzero
> size.

Roughly that.

> Setting STORAGE = EXTERNAL on the data column to disable
> compression would help make things more stable, too.

That was already set at line 350

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

FAQ