Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hEVLe-0000yw-R2 for pgsql-hackers@arkaria.postgresql.org; Thu, 11 Apr 2019 08:46:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hEVLd-0001ZJ-HZ for pgsql-hackers@arkaria.postgresql.org; Thu, 11 Apr 2019 08:46:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hEVLc-0001Yl-UI for pgsql-hackers@lists.postgresql.org; Thu, 11 Apr 2019 08:46:17 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hEVLY-00042y-UD for pgsql-hackers@postgresql.org; Thu, 11 Apr 2019 08:46:15 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 17CEB21C3FC6; Thu, 11 Apr 2019 11:46:10 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.27.200] (gw.postgrespro.ru [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id DD1A621C3FB3; Thu, 11 Apr 2019 11:46:09 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1554972369; bh=v+qP9gbQSyvrBa+emWG3LxyOHkCgDP24pwVJNkUD1GE=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=Fe2/iFwA/RSimw5obgDQTh83G6OH1XzGEOlspqId1tovydIcVd/GTsWRdLUj9AAJ5 naZQfT5wfWRG/APtvVz9B0edHtsQZffFaQccuWZ1Y0RQenCZ6p7CJBBlmZt/tHik1G Fp9DOYKr27IAAvIRq577jgn6FcISTnFuKcGwuHP4= Subject: Re: Zedstore - compressed in-core columnar storage To: Ashwin Agrawal , Mark Kirkwood Cc: PostgreSQL mailing lists References: <6A2B85A8-1ABD-4F85-981E-C94172D662FD@pivotal.io> From: Konstantin Knizhnik Message-ID: <44cafbdf-0a04-64ef-3cf8-6d5b14b68643@postgrespro.ru> Date: Thu, 11 Apr 2019 11:46:09 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 In-Reply-To: <6A2B85A8-1ABD-4F85-981E-C94172D662FD@pivotal.io> Content-Type: multipart/alternative; boundary="------------14C48D43F818D4DE31601B6A" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------14C48D43F818D4DE31601B6A Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 11.04.2019 8:03, Ashwin Agrawal wrote: >> On Apr 10, 2019, at 9:08 PM, Mark Kirkwood wrote: >> >> >>> On 11/04/19 4:01 PM, Mark Kirkwood wrote: >>>> On 9/04/19 12:27 PM, Ashwin Agrawal wrote: >>>> >>>> Heikki and I have been hacking recently for few weeks to implement >>>> in-core columnar storage for PostgreSQL. Here's the design and initial >>>> implementation of Zedstore, compressed in-core columnar storage (table >>>> access method). Attaching the patch and link to github branch [1] to >>>> follow along. >>>> >>>> >>> Very nice. I realize that it is very early days, but applying this patch I've managed to stumble over some compression bugs doing some COPY's: >>> >>> benchz=# COPY dim1 FROM '/data0/dump/dim1.dat' >>> USING DELIMITERS ','; >>> psql: ERROR: compression failed. what now? >>> CONTEXT: COPY dim1, line 458 >>> >>> The log has: >>> >>> 2019-04-11 15:48:43.976 NZST [2006] ERROR: XX000: compression failed. what now? >>> 2019-04-11 15:48:43.976 NZST [2006] CONTEXT: COPY dim1, line 458 >>> 2019-04-11 15:48:43.976 NZST [2006] LOCATION: zs_compress_finish, zedstore_compression.c:287 >>> 2019-04-11 15:48:43.976 NZST [2006] STATEMENT: COPY dim1 FROM '/data0/dump/dim1.dat' >>> USING DELIMITERS ','; >>> >>> The dataset is generated from and old DW benchmark I wrote (https://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_projects_benchw_&d=DwIDaQ&c=lnl9vOaLMzsy2niBC8-h_K-7QJuNJEsFrzdndhuJ3Sw&r=gxIaqms7ncm0pvqXLI_xjkgwSStxAET2rnZQpzba2KM&m=BgmTkDoY6SKOgODe8v6fpH4hs-wM0H91cLfrAfEL6C0&s=lLcXp_8h2bRb_OR4FT8kxD-FG9MaLBPU7M5aV9nQ7JY&e=). The row concerned looks like: >>> >>> 457,457th interesting measure,1th measure type,aqwycdevcmybxcnpwqgrdsmfelaxfpbhfxghamfezdiwfvneltvqlivstwralshsppcpchvdkdbraoxnkvexdbpyzgamajfp >>> 458,458th interesting measure,2th measure type,bjgdsciehjvkxvxjqbhtdwtcftpfewxfhfkzjsdrdabbvymlctghsblxucezydghjrgsjjjnmmqhncvpwbwodhnzmtakxhsg >>> >>> >>> I'll see if changing to LZ4 makes any different. >>> >>> >> The COPY works with LZ4 configured. > Thank you for trying it out. Yes, noticed for certain patterns pg_lzcompress() actually requires much larger output buffers. Like for one 86 len source it required 2296 len output buffer. Current zedstore code doesn’t handle this case and errors out. LZ4 for same patterns works fine, would highly recommend using LZ4 only, as anyways speed is very fast as well with it. > Internal Postgres lz compressor is really very inefficient comparing with other compression algorithms. But in any case you should never assume that size of compressed data will be smaller than size of plain data. Moreover, if you are trying to compress already compressed data, then result almost always will be larger. If size of compressed data is larger (or even not significantly smaller) than size of raw data, then you should store original data. lz4 is actually very fast. But it doesn't provide good compression ratio. This my results of compressing pbench data using different compressors: Configuration Size (Gb) Time (sec) no compression 15.31 92 zlib (default level) 2.37 284 zlib (best speed) 2.43 191 postgres internal lz 3.89 214 lz4 4.12 95 snappy 5.18 99 lzfse 2.80 1099 (apple) 2.80 1099 1.69 125 You see that zstd provides almost 2 times better compression ration and almost at the same speed. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------14C48D43F818D4DE31601B6A Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 11.04.2019 8:03, Ashwin Agrawal wrote:

      

        
On Apr 10, 2019, at 9:08 PM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote:




        

          
On 11/04/19 4:01 PM, Mark Kirkwood wrote:


          

            
On 9/04/19 12:27 PM, Ashwin Agrawal wrote:

Heikki and I have been hacking recently for few weeks to implement
in-core columnar storage for PostgreSQL. Here's the design and initial
implementation of Zedstore, compressed in-core columnar storage (table
access method). Attaching the patch and link to github branch [1] to
follow along.




          

          
Very nice. I realize that it is very early days, but applying this patch I've managed to stumble over some compression bugs doing some COPY's:

benchz=# COPY dim1 FROM '/data0/dump/dim1.dat'
USING DELIMITERS ',';
psql: ERROR:  compression failed. what now?
CONTEXT:  COPY dim1, line 458

The log has:

2019-04-11 15:48:43.976 NZST [2006] ERROR:  XX000: compression failed. what now?
2019-04-11 15:48:43.976 NZST [2006] CONTEXT:  COPY dim1, line 458
2019-04-11 15:48:43.976 NZST [2006] LOCATION: zs_compress_finish, zedstore_compression.c:287
2019-04-11 15:48:43.976 NZST [2006] STATEMENT:  COPY dim1 FROM '/data0/dump/dim1.dat'
    USING DELIMITERS ',';

The dataset is generated from and old DW benchmark I wrote (https://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_projects_benchw_&d=DwIDaQ&c=lnl9vOaLMzsy2niBC8-h_K-7QJuNJEsFrzdndhuJ3Sw&r=gxIaqms7ncm0pvqXLI_xjkgwSStxAET2rnZQpzba2KM&m=BgmTkDoY6SKOgODe8v6fpH4hs-wM0H91cLfrAfEL6C0&s=lLcXp_8h2bRb_OR4FT8kxD-FG9MaLBPU7M5aV9nQ7JY&e=). The row concerned looks like:

457,457th interesting measure,1th measure type,aqwycdevcmybxcnpwqgrdsmfelaxfpbhfxghamfezdiwfvneltvqlivstwralshsppcpchvdkdbraoxnkvexdbpyzgamajfp
458,458th interesting measure,2th measure type,bjgdsciehjvkxvxjqbhtdwtcftpfewxfhfkzjsdrdabbvymlctghsblxucezydghjrgsjjjnmmqhncvpwbwodhnzmtakxhsg


I'll see if changing to LZ4 makes any different.




        

        
The COPY works with LZ4 configured.


      

      
Thank you for trying it out. Yes, noticed for certain patterns pg_lzcompress() actually requires much larger output buffers. Like for one 86 len source it required 2296 len output buffer. Current zedstore code doesn’t handle this case and errors out. LZ4 for same patterns works fine, would highly recommend using LZ4 only, as anyways speed is very fast as well with it.



    

    

    

    Internal Postgres lz compressor is really very inefficient comparing
    with other compression algorithms.

    But in any case you should never assume that size of compressed data
    will be smaller than size of plain data.

    Moreover, if you are trying to compress already compressed data,
    then result almost always will be larger.

    If size of compressed data is larger (or even not significantly
    smaller) than size of raw data, then you should store original data.

    

    lz4 is actually very fast. But it doesn't provide good compression
    ratio.

    This my results of compressing pbench data using different
    compressors:

    

    

      
        

          Configuration
          Size (Gb)
          Time (sec)
        

        

          no compression

          		
          15.31
          92
        

        

          zlib (default level) 
          2.37 
          284
        

        

          zlib (best speed) 
          2.43
          191
        

        

          postgres internal lz 
          3.89 
          214
        

        

          lz4
          4.12 

          		
          95
        

        

          snappy
          5.18
          99
        

        

          lzfse
          2.80
          1099
        

        

           (apple) 2.80 1099

          		
          1.69
          125
        

      
    

    

    

    You see that zstd provides almost 2 times better compression ration
    and almost at the same speed.

    

    
-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 

  


--------------14C48D43F818D4DE31601B6A--