Gregory Stark wrote:
> "Bruce Momjian" <bruce(at)momjian(dot)us> writes:
> > I tested TOAST using a method similar to the above method against CVS
> > HEAD, with default shared_buffers = 32MB and no assert()s. I created
> > backends with power-of-2 seetings for TOAST_TUPLES_PER_PAGE (4(default),
> > 8, 16, 32, 64) which gives TOAST/non-TOAST breakpoints of 2k(default),
> > 1k, 512, 256, and 128, roughly.
> > The results are here:
> > http://momjian.us/expire/TOAST/
> > Strangely, 128 bytes seems to be the break-even point for TOAST and
> > non-TOAST, even for sequential scans of the entire heap touching all
> > long row values. I am somewhat confused why TOAST has faster access
> > than inline heap data.
> Did your test also imply setting the MAX_TOAST_CHUNK_SIZE (or however that's
> spelled)? And what size long values were you actually storing? How did you
> generate them?
Please look at the script sqltest.sh at that URL. I did not modify
TOAST_MAX_CHUNK_SIZE, but it changes based on TOAST_TUPLES_PER_PAGE,
which I did change.
> I wonder if what's happening is that you have large chunks which when stored
> inline are leaving lots of dead space in the table. Ie, if you're generating
> values with size near 2k and the default chunk size you would expect to find
> an average of 1k dead space per page, or a 12.5% drain on performance. As you
> lower the chunk size you decrease that margin.
Well, that could be it, but effectively that is what would happen in the
real world too.
> However I agree that it's hard to believe that the costs of random access
> wouldn't swamp that 12.5% overhead pretty quickly.
> One query I used when measuring the impact of the variable varlena stuff was
> this which gives the distribution of tuples/page over a table:
> SELECT count(*),n
> FROM (SELECT count(*) AS n
> FROM foo
> GROUP BY (point_in(tidout(ctid)))
> ) as x
> GROUP BY n;
> Which might help you peek at what's going on. You could also combine
> pg_column_size(foo.*) to measure the size of the tuple. I think that will
> measure the size of the tuple as is before the columns are detoasted.
Please use my test script and see what you find.
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
+ If your life is a hard drive, Christ can be your backup. +
In response to
pgsql-hackers by date
|Next:||From: Zdenek Kotala||Date: 2007-05-29 14:14:34|
|Subject: Re: Users specific to a Database|
|Previous:||From: Tom Lane||Date: 2007-05-29 14:00:06|
|Subject: Re: What is the maximum encoding-conversion growth rate, anyway? |