Re: Higher TOAST compression.

On Thu, Jul 23, 2009 at 4:45 PM, Kevin
Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Laurent Laborde <kerdezixe(at)gmail(dot)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.

I tested to change the TUPLES PER PAGE (EXTERNAL) to 8.
As expected, it very badly impact the IO performance as many tuple
(always read) are now stored external.

With some extremly high IOwait peak because of the additional disk
seek required to get the toasted data (the average IO bandwidth
dropped) :
Cpu0 : 5.3%us, 3.0%sy, 0.0%ni, 7.0%id, 83.4%wa, 0.7%hi, 0.7%si, 0.0%st
Cpu1 : 4.3%us, 1.3%sy, 0.0%ni, 5.7%id, 88.7%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 3.3%us, 0.7%sy, 0.0%ni, 8.0%id, 88.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 3.7%us, 0.7%sy, 0.0%ni, 4.7%id, 91.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 4.0%us, 1.3%sy, 0.0%ni, 8.0%id, 86.7%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 3.7%us, 0.3%sy, 0.0%ni, 5.7%id, 90.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 3.0%us, 0.7%sy, 0.0%ni, 6.7%id, 89.7%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 2.7%us, 0.7%sy, 0.0%ni, 7.7%id, 89.0%wa, 0.0%hi, 0.0%si, 0.0%st

> 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.

Of course, of course, i can wait.
All my patchs and testing are released on a public github.com :
http://github.com/ker2x/AkaneSQL/tree/master

I'll continue to patch postgresql/AkaneSQL, for fun and learning purpose :)
Hoping to be good enough, someday, to submit patch here.

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