Re: Proposal: Adding compression of temporary files

I've added a regression test for lz4 compression if the server is compiled
with the "--with-lz4" option.

    -Filip-


ne 24. 11. 2024 v 15:53 odesílatel Filip Janus <fjanus@redhat.com> napsal:

>
>
>     -Filip-
>
>
> st 20. 11. 2024 v 1:35 odesílatel Tomas Vondra <tomas@vondra.me> napsal:
>
>> Hi,
>>
>> On 11/18/24 22:58, Filip Janus wrote:
>> > ...
>> >     Hi all,
>> >     Postgresql supports data compression nowadays, but the compression
>> of
>> >     temporary files has not been implemented yet. The huge queries can
>> >     produce a significant amount of temporary data that needs to
>> >     be stored on disk
>> >     and cause many expensive I/O operations.
>> >     I am attaching a proposal of the patch to enable temporary files
>> >     compression for
>> >     hashjoins for now. Initially, I've chosen the LZ4 compression
>> >     algorithm. It would
>> >     probably make better sense to start with pglz, but I realized it
>> late.
>> >
>>
>> Thanks for the idea & patch. I agree this might be quite useful for
>> workloads generating a lot of temporary files for stuff like sorts etc.
>> I think it will be interesting to think about the trade offs, i.e. how
>> to pick the compression level - at some point the compression ratio
>> stops improving while paying more and more CPU time. Not sure what the
>> right choice is, so using default seems fine.
>>
>> I agree it'd be better to start with pglz, and only then add lz4 etc.
>> Firstly, pglz is simply the built-in compression, supported everywhere.
>> And it's also simpler to implement, I think.
>>
>> >     # Future possible improvements
>> >     Reducing the number of memory allocations within the dumping and
>> >     loading of
>> >     the buffer. I have two ideas for solving this problem. I would
>> >     either add a buffer into
>> >     struct BufFile or provide the buffer as an argument from the caller.
>> >     For the sequential
>> >     execution, I would prefer the second option.
>> >
>>
>> Yes, this would be good. Doing a palloc+pfree for each compression is
>> going to be expensive, especially because these buffers are going to be
>> large - likely larger than 8kB. Which means it's not cached in the
>> memory context, etc.
>>
>> Adding it to the BufFile is not going to fly, because that doubles the
>> amount of memory per file. And we already have major issues with hash
>> joins consuming massive amounts of memory. But at the same time the
>> buffer is only needed during compression, and there's only one at a
>> time. So I agree with passing a single buffer as an argument.
>>
>> >     # Future plan/open questions
>> >     In the future, I would like to add support for pglz and zstd.
>> >     Further, I plan to
>> >     extend the support of the temporary file compression also for
>> >     sorting, gist index creation, etc.
>> >
>> >     Experimenting with the stream mode of compression algorithms. The
>> >     compression
>> >     ratio of LZ4 in block mode seems to be satisfying, but the stream
>> >     mode could
>> >     produce a better ratio, but it would consume more memory due to the
>> >     requirement to store
>> >     context for LZ4 stream compression.
>> >
>>
>> One thing I realized is that this only enables temp file compression for
>> a single place - hash join spill files. AFAIK this is because compressed
>> files don't support random access, and the other places might need that.
>>
>> Is that correct? The patch does not explain this anywhere. If that's
>> correct, the patch probably should mention this in a comment for the
>> 'compress' argument added to BufFileCreateTemp(), so that it's clear
>> when it's legal to set compress=true.
>>
>
> I will add the description there.
>
>
>> Which other places might compress temp files? Surely hash joins are not
>> the only place that could benefit from this, right?
>>
>
> Yes, you are definitely right. I have chosen the hash joins as a POC
> because
> there are no seeks besides seeks at the beginning of the buffer.
> I have focused on hashjoins, but there are definitely also other places
> where
> the compression could be used. I want to add support in other places
> in the feature.
>
>
>> Another thing is testing. If I run regression tests, it won't use
>> compression at all, because the GUC has "none" by default, right? But we
>> need some testing, so how would we do that? One option would be to add a
>> regression test that explicitly sets the GUC and does a hash join, but
>> that won't work with lz4 (because that may not be enabled).
>
>
> Right, it's "none" by default. My opinion is that we would like to test
> every supported compression method, so I will try to add environment
> variable as
> you recommended.
>
>
>>
>> Another option might be to add a PG_TEST_xxx environment variable that
>> determines compression to use. Something like PG_TEST_USE_UNIX_SOCKETS.
>> But perhaps there's a simpler way.
>>
>> >     # Benchmark
>> >     I prepared three different databases to check expectations. Each
>> >     dataset is described below. My testing demonstrates that my patch
>> >     improves the execution time of huge hash joins.
>> >     Also, my implementation should not
>> >     negatively affect performance within smaller queries.
>> >     The usage of memory needed for temporary files was reduced in every
>> >      execution without a significant impact on execution time.
>> >
>> >     *## Dataset A:*
>> >     Tables*
>> >     *
>> >     table_a(bigint id,text data_text,integer data_number) - 10000000
>> rows
>> >     table_b(bigint id, integer ref_id, numeric data_value, bytea
>> >     data_blob) - 10000000 rows
>> >     Query:  SELECT *  FROM table_a a JOIN table_b b ON a.id <http://
>> >     a.id> = b.id <http://b.id>;
>> >
>> >     The tables contain highly compressible data.
>> >     The query demonstrated a reduction in the usage of the temporary
>> >     files ~20GB -> 3GB, based on this reduction also caused the
>> execution
>> >     time of the query to be reduced by about ~10s.
>> >
>> >
>> >     *## Dataset B:*
>> >     Tables:*
>> >     *
>> >     table_a(integer id, text data_blob) - 1110000 rows
>> >     table_b(integer id, text data_blob) - 10000000 rows
>> >     Query:  SELECT *  FROM table_a a JOIN table_b b ON a.id <http://
>> >     a.id> = b.id <http://b.id>;
>> >
>> >     The tables contain less compressible data. data_blob was generated
>> >     by a pseudo-random generator.
>> >     In this case, the data reduction was only ~50%. Also, the execution
>> >     time was reduced
>> >     only slightly with the enabled compression.
>> >
>> >     The second scenario demonstrates no overhead in the case of enabled
>> >     compression and extended work_mem to avoid temp file usage.
>> >
>> >     *## Dataset C:*
>> >     Tables
>> >     customers (integer,text,text,text,text)
>> >     order_items(integer,integer,integer,integer,numeric(10,2))
>> >     orders(integer,integer,timestamp,numeric(10,2))
>> >     products(integer,text,text,numeric(10,2),integer)
>> >
>> >     Query: SELECT p.product_id, p.name <http://p.name>, p.price,
>> >     SUM(oi.quantity) AS total_quantity, AVG(oi.price) AS avg_item_price
>> >     FROM eshop.products p JOIN eshop.order_items oi ON p.product_id =
>> >     oi.product_id JOIN
>> >     eshop.orders o ON oi.order_id = o.order_id WHERE o.order_date >
>> >     '2020-01-01' AND p.price > 50
>> >     GROUP BY p.product_id, p.name <http://p.name>, p.price HAVING
>> >     SUM(oi.quantity) > 1000
>> >     ORDER BY total_quantity DESC LIMIT 100;
>> >
>> >     This scenario should demonstrate a more realistic usage of the
>> database.
>> >     Enabled compression slightly reduced the temporary memory usage, but
>> >     the execution
>> >     time wasn't affected by compression.
>> >
>> >
>> >     +------------+-------------------------+-----------------------
>> >     +------------------------------+
>> >     |  Dataset   | Compression.       | temp_bytes         | Execution
>> >     Time (ms)   |
>> >     +------------+-------------------------+-----------------------
>> >     +----------------------------- +
>> >     | A             | Yes                        |  3.09 GiB
>> >     | 22s586ms           | work_mem  = 4MB
>> >     |                | No                         |  21.89 GiB
>> >     | 35s                       | work_mem  = 4MB
>> >     +------------+-------------------------+-----------------------
>> >     +----------------------------------------
>> >     | B             | Yes                        |  333 MB
>> >     | 1815.545 ms       | work_mem = 4MB
>> >     |                 | No                        |  146  MB
>> >       | 1500.460 ms        | work_mem = 4MB
>> >     |                 | Yes                       |  0 MB
>> >         | 3262.305 ms        | work_mem = 80MB
>> >     |                 | No                        |  0 MB
>> >        | 3174.725 ms         | work_mem = 80MB
>> >     +-------------+------------------------+------------------------
>> >     +-------------------------------------
>> >     | C             | Yes                       | 40 MB
>> >     | 1011.020 ms        | work_mem = 1MB
>> >     |                | No                        |  53
>> >     MB                 |  1034.142 ms        | work_mem = 1MB
>> >     +------------+------------------------+------------------------
>> >     +--------------------------------------
>> >
>> >
>>
>> Thanks. I'll try to do some benchmarks on my own.
>>
>> Are these results fro ma single run, or an average of multiple runs?
>
>
> It is average from multiple runs.
>
> Do
>> you maybe have a script to reproduce this, including the data generation?
>
>
> I am attaching my SQL file for database preparation. I also did further
> testing
> with two other machines( see attachment huge_tables.rtf ).
>
>>
>> Also, can you share some information about the machine used for this? I
>> expect the impact to strongly depends on memory pressure - if the temp
>> file fits into page cache (and stays there), it may not benefit from the
>> compression, right?
>>
>
> If it fits into the page cache due to compression, I would consider it as
> a benefit from compression.
> I performed further testing on machines with different memory sizes.
> Both experiments showed that compression was beneficial for execution
> time.
> The execution time reduction was more significant in the case of the
> machine that had
> less memory available.
>
> Tests were performed on:
> MacBook PRO M3 36GB - MacOs
> Virtual machine ARM64 10GB/ 6CPU - Fedora 39
>
>
>>
>> regards
>>
>> --
>> Tomas Vondra
>>
>>