Re: Proposal: Adding compression of temporary files

Even though i started with lz4, I added also pglz support and enhanced
memory management based on provided review.



    -Filip-


čt 28. 11. 2024 v 12:32 odesílatel Filip Janus <fjanus@redhat.com> napsal:

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