Higher TOAST compression.

Laurent Laborde <kerdezixe@gmail.com> wrote:

What about trying to change the TOAST_TUPLE_TARGET to get a higher
compression (by having more toasted record) ?

I'd like to change the TOAST_TUPLES_PER_PAGE. Maybe from 4 to 8 ?
Is that correct ? Did i missed something ?

I did some statistics and i will have much more TOASTed record as
most of them are between 1KB and 2KB.

It seems like it might be reasonable to have a separate threshold for
compression from that for out-of-line storage. Since I've been in
that code recently, I would be pretty comfortable doing something
about that; but, as is so often the case, the problem will probably be
getting agreement on what would be a good change.

Ignoring for a moment the fact that "low hanging fruit" in the form of
*very* large values can be handled first, the options would seem to
be:

(1) Just hard-code a lower default threshold for compression than for
out-of-line storage.

(2) Add a GUC or two to control thresholds.

(3) Allow override of the thresholds for individual columns.

Are any of these non-controversial? What do people like there? What
did I miss?

-Kevin

On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote:

Laurent Laborde <kerdezixe@gmail.com> wrote:

(3) Allow override of the thresholds for individual columns.

Are any of these non-controversial? What do people like there? What
did I miss?

I would skip 1 and 2 and have (3).

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

"Joshua D. Drake" <jd@commandprompt.com> wrote:

On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote:

(3) Allow override of the thresholds for individual columns.

I would skip 1 and 2 and have (3).

Sure, pick the one which requires new syntax! ;-)

How about two new ALTER TABLE actions:

ALTER [ COLUMN ] column SET COMPRESSION_THRESHOLD integer
ALTER [ COLUMN ] column SET EXTERNAL_THRESHOLD integer

Or can you suggest something better?

Laurent, would something like this address your needs? I was assuming
that if the point is to reduce I/O, you were interested in doing more
compression, not in storing more values out-of-line (in the separate
TOAST table)? Would it be manageable to tune this on a column-by-
column basis?

-Kevin

On Fri, 2009-07-17 at 14:21 -0500, Kevin Grittner wrote:

"Joshua D. Drake" <jd@commandprompt.com> wrote:

On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote:

(3) Allow override of the thresholds for individual columns.

I would skip 1 and 2 and have (3).

Sure, pick the one which requires new syntax! ;-)

Better than the argument for a new GUC :).

How about two new ALTER TABLE actions:

ALTER [ COLUMN ] column SET COMPRESSION_THRESHOLD integer
ALTER [ COLUMN ] column SET EXTERNAL_THRESHOLD integer

Or can you suggest something better?

Seems reasonable to me.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Fri, Jul 17, 2009 at 9:21 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:

"Joshua D. Drake" <jd@commandprompt.com> wrote:

On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote:

(3)  Allow override of the thresholds for individual columns.

I would skip 1 and 2 and have (3).

Sure, pick the one which requires new syntax!  ;-)

How about two new ALTER TABLE actions:

  ALTER [ COLUMN ] column SET COMPRESSION_THRESHOLD integer
  ALTER [ COLUMN ] column SET EXTERNAL_THRESHOLD integer

Or can you suggest something better?

Laurent, would something like this address your needs?  I was assuming
that if the point is to reduce I/O, you were interested in doing more
compression, not in storing more values out-of-line (in the separate
TOAST table)?  Would it be manageable to tune this on a column-by-
column basis?

Certainly !
We already alter storage type on some column, adding some more won't
be a problem. :)

But... on which version are you planning to do that ?
We're still using Postgresql 8.3, because we use Slony-1 1.2.15 and
upgrading to 8.4 is a *major* pain (discussed on slony mailling list).
Slony-1 1.2.15 won't compile on 8.4, and upgrading to Slony-1 2.0.x
require to rebuild the whole cluster (and upgrading to Pg 8.4 require
a rebuild too).
So we'd need to upgrade both slony and postgresql with an impossible downtime :)
We stay on Pg 8.3 until the slony developpers find a better upgrade solution.

The proposed solution sound really good to me.
But, for now, if i could have a simple patch for 8.3 (eg: changing a
#define in the source code), i'd be very happy :)

Is it ok to just change TOAST_TUPLES_PER_PAGE ?

Thank you for all your replies and proposed solutions :)

PS : i'm not a C coder, but if you know some perl to be
patched/cleaned, i'm here :)

--
Laurent Laborde
Sysadmin at http://www.over-blog.com/

Laurent Laborde <kerdezixe@gmail.com> wrote:

Kevin Grittner<Kevin.Grittner@wicourts.gov> wrote:

How about two new ALTER TABLE actions:

ALTER [ COLUMN ] column SET COMPRESSION_THRESHOLD integer
ALTER [ COLUMN ] column SET EXTERNAL_THRESHOLD integer

Laurent, would something like this address your needs?

Certainly !
We already alter storage type on some column, adding some more won't
be a problem. :)

But... on which version are you planning to do that ?

The patch, if there's consensus that it's a good idea, would be for
8.5. Since it is new functionality, there wouldn't be a back-port to
prior releases. Of course, I wouldn't be starting to work on such a
patch until after our current code commit phase, which ends August
15th.

We stay on Pg 8.3 until the slony developpers find a better upgrade
solution.

The proposed solution sound really good to me.
But, for now, if i could have a simple patch for 8.3 (eg: changing a
#define in the source code), i'd be very happy :)

Is it ok to just change TOAST_TUPLES_PER_PAGE ?

The thing that worries me about that is that it would tend to force
more data to be stored out-of-line, which might *increase* your I/O;
since the whole point of this exercise is to try to *decrease* it,
that seems pretty iffy. However, once we get to the end of code
commit, I might be able to give you a little one-off patch that would
be more aggressive about compression without affecting out-of-line
storage. Hard-coded, like what you're talking about, but with a
little more finesse.

-Kevin

On Fri, Jul 17, 2009 at 10:40 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:

Laurent Laborde <kerdezixe@gmail.com> wrote:

But... on which version are you planning to do that ?

The patch, if there's consensus that it's a good idea, would be for
8.5.  Since it is new functionality, there wouldn't be a back-port to
prior releases.  Of course, I wouldn't be starting to work on such a
patch until after our current code commit phase, which ends August
15th.

Sure, no problem.

We stay on Pg 8.3 until the slony developpers find a better upgrade
solution.

The proposed solution sound really good to me.
But, for now, if i could have a simple patch for 8.3 (eg: changing a
#define in the source code), i'd be very happy :)

Is it ok to just change TOAST_TUPLES_PER_PAGE ?

The thing that worries me about that is that it would tend to force
more data to be stored out-of-line, which might *increase* your I/O;
since the whole point of this exercise is to try to *decrease* it,
that seems pretty iffy.  However, once we get to the end of code
commit, I might be able to give you a little one-off patch that would
be more aggressive about compression without affecting out-of-line
storage.  Hard-coded, like what you're talking about, but with a
little more finesse.

Awesome !
Yes, i understand the problem.

What about SET STORAGE MAIN then ? To prevent out-of-line storage ?
We use PLAIN on some specific column (i don't know why, it was here
before i join overblog)
And the default extended storage for all other columns.

Thank you :)

--
Laurent Laborde
Sysadmin @ http://www.over-blog.com/

Laurent Laborde <kerdezixe@gmail.com> wrote:

What about SET STORAGE MAIN then ? To prevent out-of-line storage ?

Well, that doesn't try as hard as you might think to keep from storing
data out-of-line. It uses the same threshold as the default EXTENDED
storage, but saves the out-of-line option for such columns as the last
thing to try to get it within the threshold. It is because I wrote a
very small patch to address that issue that I jumped in on your issue.

If you wanted to try my patch here:

http://archives.postgresql.org/message-id/4A3638530200002500027A95@gw.wicourts.gov

you could adjust both TOAST_TUPLES_PER_PAGE_MAIN and
TOAST_TUPLES_PER_PAGE to suit your needs and set storage for columns
to MAIN as needed. Be very cautious if you try this, because this
patch has not yet been reviewed or accepted.

-Kevin

On Fri, Jul 17, 2009 at 11:10 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:

Laurent Laborde <kerdezixe@gmail.com> wrote:

What about SET STORAGE MAIN then ? To prevent out-of-line storage ?

Well, that doesn't try as hard as you might think to keep from storing
data out-of-line.  It uses the same threshold as the default EXTENDED
storage, but saves the out-of-line option for such columns as the last
thing to try to get it within the threshold.  It is because I wrote a
very small patch to address that issue that I jumped in on your issue.

If you wanted to try my patch here:

http://archives.postgresql.org/message-id/4A3638530200002500027A95@gw.wicourts.gov

you could adjust both TOAST_TUPLES_PER_PAGE_MAIN and
TOAST_TUPLES_PER_PAGE to suit your needs and set storage for columns
to MAIN as needed.  Be very cautious if you try this, because this
patch has not yet been reviewed or accepted.

I'll take a look at it, compile, and try that. (on a test server).
Thank you :)

--
Laurent Laborde
Sysadmin @ http://www.over-blog.com/

Hi again !

I also take a look at another possibility to improve compression.

There is two compression strategy :
static const PGLZ_Strategy strategy_default_data = {
256, /* Data chunks less than 256 bytes are not
* compressed */
6144, /* Data chunks >= 6K force compression, unless
* compressed output is larger than input */
20, /* Below 6K, compression rates below 20% mean
* fallback to uncompressed */
128, /* Stop history lookup if a match of 128 bytes
* is found */
10 /* Lower good match size by 10% at every
* lookup loop iteration */
};
const PGLZ_Strategy *const PGLZ_strategy_default = &strategy_default_data;

static const PGLZ_Strategy strategy_always_data = {
0, /* Chunks of any size are compressed */
0,
0, /* It's enough to save one single byte */
128, /* Stop history lookup if a match of 128 bytes
* is found */
6 /* Look harder for a good match */
};
const PGLZ_Strategy *const PGLZ_strategy_always = &strategy_always_data;

1) "strategy_always_data" seems to never be used.
2) the default strategy could be more aggressive (with a higher cpu cost)

Additionally, we use a patched version that modify the default strategy.
If i understand correctly, instead of being more aggresive on
compression, it is *LESS* aggresive :

static const PGLZ_Strategy strategy_default_data = {
32, /* Data chunks less than 32
bytes are not compressed */
1024 * 1024, /* Data chunks over 1MB are not compressed either */
25, /* Require 25% compression
rate, or not worth it */
1024, /* Give up if no compression in the first 1KB */
128, /* Stop history lookup if a match of
128 bytes is found */
10 /* Lower good match size by
10% at every loop iteration */
};
const PGLZ_Strategy *const PGLZ_strategy_default = &strategy_default_data;

Isn't it ?

What about setting "PGLZ_strategy_always" as the default strategy
(insane cpu cost ?) ?
Or something in-between ?

Thank you.

--
Laurent Laborde
Sysadmin @ http://www.over-blog.com/

Laurent Laborde <kerdezixe@gmail.com> wrote:

There is two compression strategy :
static const PGLZ_Strategy strategy_default_data = {

static const PGLZ_Strategy strategy_always_data = {

1) "strategy_always_data" seems to never be used.

A quick grep sure makes it look that way. I will look closer later.

2) the default strategy could be more aggressive (with a higher cpu
cost)

What about setting "PGLZ_strategy_always" as the default strategy
(insane cpu cost ?) ?
Or something in-between ?

That goes beyond what I was trying to do with my recent patch. What
you propose may be useful, but there would need to be much discussion
and benchmarking and it would be a new patch.

If you have any benchmark information on relative speed and space
savings, please post them.

-Kevin

On Mon, Jul 20, 2009 at 6:04 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:

What about setting "PGLZ_strategy_always" as the default strategy
(insane cpu cost ?) ?
Or something in-between ?

That goes beyond what I was trying to do with my recent patch.  What
you propose may be useful, but there would need to be much discussion
and benchmarking and it would be a new patch.

If you have any benchmark information on relative speed and space
savings, please post them.

I will try that as soon as my spare production server (2x4core Xeon,
32GB RAM, 8 SAS Disk) is back to life.

I wasn't thinking about the very aggressive (strategy_always)
compression strategy for a default postgresql release.
Not everyone is IObound :)

But just as a default setting here at over-blog. (definitively IOBound
with large articles and comment).
Anyway, i will try and report different strategy here.

Thank you again for your feedback.

--
Laurent Laborde
Sysadmin @ http://www.over-blog.com/

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

It seems like it might be reasonable to have a separate threshold for
compression from that for out-of-line storage. Since I've been in
that code recently, I would be pretty comfortable doing something
about that; but, as is so often the case, the problem will probably be
getting agreement on what would be a good change.

Ignoring for a moment the fact that "low hanging fruit" in the form of
*very* large values can be handled first, the options would seem to
be:

(1) Just hard-code a lower default threshold for compression than for
out-of-line storage.

(2) Add a GUC or two to control thresholds.

(3) Allow override of the thresholds for individual columns.

I'm not clear how this would work. The toast code is designed around
hitting a target for the overall tuple size; how would it make sense
to treat compression and out-of-lining differently? And especially,
how could you have per-column targets?

I could see having a reloption that allowed per-table adjustment of the
target tuple width...

regards, tom lane

My 1st applied patch is the safest and simpliest :
in pg_lzcompress.c :

static const PGLZ_Strategy strategy_default_data = {
256, /* Data chunks less than 256 are not compressed */
256, /* force compression on data chunks on record >= 256bytes */
1, /* compression rate below 1% fall back to uncompressed */
256, /* Stop history lookup if a match of 256 bytes is found */
6 /* lower good match size b 6% at every lookup iteration */
};
const PGLZ_Strategy *const PGLZ_strategy_default = &strategy_default_data;

I need to test for a few days. But the firsts tests show that we're
still IObound :)
The most obvious effect is reduction by a factor 2~10 of the size of
some TOAST table.
It seems that a lot of record are now kept in-line instead of being
stored in TOAST.

I will come back later with some numbers :)

Next patch will be a modified kevin's patch. (it doesn't directly
apply to our source code as i'm using postgresql 8.3 and his patch is
for 8.4) and a change in TOAST thresold and target.

What do you think about the parameters i used in the compression strategy ?
PS : biggest records are french text and html. (blog data : articles,
comments, ...)
Thank you.

--
Laurent Laborde
Sysadmin @ http://www.over-blog.com/

Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

It seems like it might be reasonable to have a separate threshold
for compression from that for out-of-line storage. Since I've been
in that code recently, I would be pretty comfortable doing
something about that; but, as is so often the case, the problem
will probably be getting agreement on what would be a good change.

I'm not clear how this would work. The toast code is designed
around hitting a target for the overall tuple size; how would it
make sense to treat compression and out-of-lining differently?

The current steps are:

1: Inline compress attributes with attstorage 'x', and store very
large attributes with attstorage 'x' or 'e' external immediately
2: Store attributes with attstorage 'x' or 'e' external
3: Inline compress attributes with attstorage 'm'
4: Store attributes with attstorage 'm' external

If we had separate compression and external storage tuple targets:

Is there any reason not to include 'm' in the first inline compression
phase (step 1)? It does seem reasonable to store "very large
attributes" externally in the first pass, but it would be pretty easy
to include 'm' in the compression but skip it for the external storage
during step 1. In this phase we would use the compression target.

Step 2 would use the target tuple size for external storage, which
would probably usually be >= the compression target. If we want to
allow a compression target > external storage target, I guess we would
have to allow the smaller target to go first; however, I'm not really
sure if there is a sane use case for a larger compression target than
external storage target.

Step 3 would go away, since its work could be moved to step 1.

Step 4 would maintain the behavior created by the recent patch.

And especially, how could you have per-column targets?

I could see having a reloption that allowed per-table adjustment of
the target tuple width...

Yeah, this would have to be done by table, not by column.

The compression configuration mentioned by Laurent, if we want to make
that tunable, which could make sense by column; but the toast tuple
sizes targets would clearly need to be by table.

-Kevin

I wrote:

If we want to allow a compression target > external storage target,
I guess we would have to allow the smaller target to go first

Scratch that part -- even with a compression target > the external
storage target, it would make sense use the same sequence of steps.

-Kevin

Sorry I responded that quickly this early. I keep having additional
thoughts....

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

And especially, how could you have per-column targets?

Yeah, this would have to be done by table, not by column.

If we had an optional two targets by column, we could pass any columns
with such targets as a "step 0", before starting the tuple size
checks. I think that makes sense, so I'm flip-flopping on that as a
possibility.

Now, whether that's overkill is another question.

-Kevin

On Wed, Jul 22, 2009 at 10:54 AM, Laurent Laborde<kerdezixe@gmail.com> wrote:

My 1st applied patch is the safest and simpliest :
in pg_lzcompress.c :

static const PGLZ_Strategy strategy_default_data = {
       256,    /* Data chunks less than 256 are not compressed */
       256,    /* force compression on data chunks on record >= 256bytes */
       1,      /* compression rate below 1% fall back to uncompressed    */
       256,    /* Stop history lookup if a match of 256 bytes is found   */
       6       /* lower good match size b 6% at every lookup iteration   */
};
const PGLZ_Strategy *const PGLZ_strategy_default = &strategy_default_data;

I'm testing in production since yesterday.
It greatly improved %IOwait.

My 1st guess is that postgresql keep more data inline instead of
moving it in extern to toast table, reducing massively the IOseek and
resulting in a higher IO througput.
(iostat show a 5~25MB/s bandwidth at 100%util instead of 2~5MB/s at 100%util).

So... now i'm not sure anymore about lowering the tuple per page from 4 to 8.
Doing that would mean more data in TOAST table ...

--
Laurent "ker2x" Laborde
Sysadmin @ http://www.over-blog.com/

Laurent Laborde <kerdezixe@gmail.com> wrote:

(iostat show a 5~25MB/s bandwidth at 100%util instead of 2~5MB/s at
100%util).

Any numbers for overall benefit at the application level?

So... now i'm not sure anymore about lowering the tuple per page
om 4 to 8.
Doing that would mean more data in TOAST table ...

Yeah, I've been skeptical that it would be a good thing for your
situation unless the lower target only applied to more aggressive
compression, not out-of-line storage.

If you can wait for a week or two, I can give you a "proof of concept"
patch to use separate targets for compression and out-of-line storage.
It would be interesting to see how much that helps when combined with
your more aggressive compression configuration.

-Kevin