On 04/30/15 02:42, Robert Haas wrote:
On Wed, Apr 29, 2015 at 6:55 PM, Tomas Vondra
<tomas.von...@2ndquadrant.com> wrote:
I'm not convinced not compressing the data is a good idea - it suspect it
would only move the time to TOAST, increase memory pressure (in general and
in shared buffers). But I think that using a more efficient compression
algorithm would help a lot.
For example, when profiling the multivariate stats patch (with multiple
quite large histograms), the pglz_decompress is #1 in the profile, occupying
more than 30% of the time. After replacing it with the lz4, the data are bit
larger, but it drops to ~0.25% in the profile and planning the drops
proportionally.
That seems to imply a >100x improvement in decompression speed. Really???
Sorry, that was a bit misleading over-statement. The profiles (same
dataset, same workload) look like this:
pglz_decompress
---------------
44.51% postgres [.] pglz_decompress
13.60% postgres [.] update_match_bitmap_histogram
8.40% postgres [.] float8_cmp_internal
7.43% postgres [.] float8lt
6.49% postgres [.] deserialize_mv_histogram
6.23% postgres [.] FunctionCall2Coll
4.06% postgres [.] DatumGetFloat8
3.48% libc-2.18.so [.] __isnan
1.26% postgres [.] clauselist_mv_selectivity
1.09% libc-2.18.so [.] __memcpy_sse2_unaligned
lz4
---
18.05% postgres [.] update_match_bitmap_histogram
11.67% postgres [.] float8_cmp_internal
10.53% postgres [.] float8lt
8.67% postgres [.] FunctionCall2Coll
8.52% postgres [.] deserialize_mv_histogram
5.52% postgres [.] DatumGetFloat8
4.90% libc-2.18.so [.] __isnan
3.92% liblz4.so.1.6.0 [.] 0x0000000000002603
2.08% liblz4.so.1.6.0 [.] 0x0000000000002847
1.81% postgres [.] clauselist_mv_selectivity
1.47% libc-2.18.so [.] __memcpy_sse2_unaligned
1.33% liblz4.so.1.6.0 [.] 0x000000000000260f
1.16% liblz4.so.1.6.0 [.] 0x00000000000025e3
(and then a long tail of other lz4 calls)
The difference used to more significant, but I've done a lot of
improvements in the update_match_bitmap method (so the lz4 methods are
more significant).
The whole script (doing a lot of estimates) takes 1:50 with pglz and
only 1:25 with lz4. That's ~25-30% improvement.
The results are slightly unreliable because collected in a Xen VM, and
the overhead is non-negligible (but the same in both cases). I wouldn't
be surprised if the difference was more significant without the VM.
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
