Re: [HACKERS] Higher TOAST compression.

2009-07-29 Thread decibel
On Jul 23, 2009, at 6:22 AM, Laurent Laborde wrote: On Wed, Jul 22, 2009 at 10:54 AM, Laurent Laborde 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 compress

Re: [HACKERS] Higher TOAST compression.

2009-07-28 Thread Robert Haas
On Tue, Jul 28, 2009 at 12:44 PM, Kevin Grittner wrote: > Finally, you should probably consider volunteering to review a patch > or two for the next commitfest.  :-)  To ensure timely review of > submitted patches, while still allowing the reviewers some guarantee > of unencumbered time to write th

Re: [HACKERS] Higher TOAST compression.

2009-07-28 Thread Kevin Grittner
Laurent Laborde wrote: > If it works, and if you're interested, i may try to write a patch > for 8.5. Cool! I can help with it if you wish. If you haven't already done so, be sure to read this carefully: http://wiki.postgresql.org/wiki/Developer_FAQ Also, be sure you are taking into ac

Re: [HACKERS] Higher TOAST compression.

2009-07-28 Thread Laurent Laborde
On Tue, Jul 28, 2009 at 2:36 PM, Laurent Laborde wrote: > I'm currently rewriting the whole toaster stuff to simply define : > - a compression threshold (size limit to compress, in Nth of page) > - an external threshold (size limit to externalize compressed data, in > Nth of page) > > i keep the TO

Re: [HACKERS] Higher TOAST compression.

2009-07-28 Thread Laurent Laborde
I'm currently rewriting the whole toaster stuff to simply define : - a compression threshold (size limit to compress, in Nth of page) - an external threshold (size limit to externalize compressed data, in Nth of page) i keep the TOAST_INDEX_TARGET and EXTERN_TUPLES_PER_PAGE. I expect a lot of tri

Re: [HACKERS] Higher TOAST compression.

2009-07-28 Thread Laurent Laborde
On Thu, Jul 23, 2009 at 4:45 PM, Kevin Grittner wrote: > Laurent Laborde 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

Re: [HACKERS] Higher TOAST compression.

2009-07-23 Thread Kevin Grittner
Laurent Laborde 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 tab

Re: [HACKERS] Higher TOAST compression.

2009-07-23 Thread Laurent Laborde
On Wed, Jul 22, 2009 at 10:54 AM, Laurent Laborde 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

Re: [HACKERS] Higher TOAST compression.

2009-07-22 Thread Kevin Grittner
Sorry I responded that quickly this early. I keep having additional thoughts "Kevin Grittner" wrote: > Tom Lane 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, w

Re: [HACKERS] Higher TOAST compression.

2009-07-22 Thread Kevin Grittner
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 -- Sent

Re: [HACKERS] Higher TOAST compression.

2009-07-22 Thread Kevin Grittner
Tom Lane wrote: > "Kevin Grittner" 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 cas

Re: [HACKERS] Higher TOAST compression.

2009-07-22 Thread Laurent Laborde
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, /* compressio

Re: [HACKERS] Higher TOAST compression.

2009-07-21 Thread Tom Lane
"Kevin Grittner" 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 pro

Re: [HACKERS] Higher TOAST compression.

2009-07-20 Thread Laurent Laborde
On Mon, Jul 20, 2009 at 6:04 PM, Kevin Grittner 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 n

Re: [HACKERS] Higher TOAST compression.

2009-07-20 Thread Kevin Grittner
Laurent Laborde 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

Re: [HACKERS] Higher TOAST compression.

2009-07-20 Thread Laurent Laborde
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 */ 614

Re: [HACKERS] Higher TOAST compression.

2009-07-20 Thread Laurent Laborde
On Fri, Jul 17, 2009 at 11:10 PM, Kevin Grittner wrote: > Laurent Laborde 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 EXTEND

Re: [HACKERS] Higher TOAST compression.

2009-07-17 Thread Kevin Grittner
Laurent Laborde 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

Re: [HACKERS] Higher TOAST compression.

2009-07-17 Thread Laurent Laborde
On Fri, Jul 17, 2009 at 10:40 PM, Kevin Grittner wrote: > Laurent Laborde 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 releas

Re: [HACKERS] Higher TOAST compression.

2009-07-17 Thread Kevin Grittner
Laurent Laborde wrote: > Kevin Grittner 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

Re: [HACKERS] Higher TOAST compression.

2009-07-17 Thread Laurent Laborde
On Fri, Jul 17, 2009 at 9:21 PM, Kevin Grittner wrote: > "Joshua D. Drake" 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

Re: [HACKERS] Higher TOAST compression.

2009-07-17 Thread Joshua D. Drake
On Fri, 2009-07-17 at 14:21 -0500, Kevin Grittner wrote: > "Joshua D. Drake" 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 require

Re: [HACKERS] Higher TOAST compression.

2009-07-17 Thread Kevin Grittner
"Joshua D. Drake" 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 [

Re: [HACKERS] Higher TOAST compression.

2009-07-17 Thread Joshua D. Drake
On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote: > Laurent Laborde 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 -- Po

Re: [HACKERS] Higher TOAST compression.

2009-07-17 Thread Kevin Grittner
Laurent Laborde 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 h

[HACKERS] Higher TOAST compression.

2009-07-17 Thread Laurent Laborde
Friendly greetings ! I'd like to have a higher compression ratio on our base. >From pg 8.3 documentation : The TOAST code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The TOAST code will compress and/or move field values out