Re: Compression and on-disk sorting

Jim C. Nasby wrote:
> Finally completed testing of a dataset that doesn't fit in memory with
> compression enabled. Results are at
> http://jim.nasby.net/misc/pgsqlcompression .
> 
> Summary:
>                     work_mem    compressed  not compressed  gain
> in-memory           20000       400.1       797.7           49.8%
> in-memory           2000        371.4       805.7           53.9%
> not in-memory       20000       8537        17436           51.0%
> not in-memory       2000        8152        17820           54.3%
> 
> I find it very interesting that the gains are identical even when the
> tapes should fit in memory. My guess is that for some reason the OS is
> flushing those to disk anyway. In fact, watching gstat during a run, I
> do see write activity hitting the drives. So if there was some way to
> tune that behavior, the in-memory case would probably be much, much
> faster. Anyone know FreeBSD well enough to suggest how to change this?
> Anyone want to test on linux and see if the results are the same? This
> could indicate that it might be advantageous to attempt an in-memory
> sort with compressed data before spilling that compressed data to
> disk...
> 

I can test it on linux just let me know what you need.

J


> As for CPU utilization, it was ~33% with compression and ~13% without.
> That tells me that CPU could become a factor if everything was truely in
> memory (including the table we were reading from), but if that's the
> case there's a good chance that we wouldn't even be switching to an
> on-disk sort. If everything isn't in memory then you're likely to be IO
> bound anyway...


-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240   Providing the most comprehensive  PostgreSQL
solutionssince 1997             http://www.commandprompt.com/