From: Justin Pryzby <pryzby@telsasoft.com>
To: Alexandra Wang <lewang@pivotal.io>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Ashwin Agrawal <aagrawal@pivotal.io>
Cc: Ashutosh Sharma <ashu.coek88@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Subject: Re: Zedstore - compressed in-core columnar storage
Date: Mon, 19 Aug 2019 21:04:25 -0500
Message-ID: <20190820020425.GX11185@telsasoft.com> (raw)
In-Reply-To: <CACiyaSr3EEMR=wjdhf9XZiBuOgB0bqdwjPyS5Yh63d-fpACBPQ@mail.gmail.com>
References: <CAH2-WzkTUkLfWAxYZZLeoUxiJkHnnrnWBZVcJdfnSsRxWK0YVA@mail.gmail.com>
<c4f3f315-bee3-3d7f-1284-5db2c528fbc6@iki.fi>
<CALfoeiuuLe12PuQ=zvM_L7B5qegBqGHYENHUGbLOsjAnG=mi4A@mail.gmail.com>
<5ceff1bd.1c69fb81.704ee.a5b1SMTPIN_ADDED_BROKEN@mx.google.com>
<CALfoeivDkvyyw68GsUo5NX67P+i5WUvpbbJ14BOFB0M=4RWAzQ@mail.gmail.com>
<CAE9k0P=1MhCSMeBJytPe34Zm-xUbRJupHrk2jqkT=9VFYM-BiQ@mail.gmail.com>
<CALfoeivAGExe37Q1yVJyYPm+7k1AUS2_QW_cFCNamDA5vU_Few@mail.gmail.com>
<ffee5017-90e9-238a-6e9e-ac1697d007a0@iki.fi>
<20190818193533.GL11185@telsasoft.com>
<CACiyaSr3EEMR=wjdhf9XZiBuOgB0bqdwjPyS5Yh63d-fpACBPQ@mail.gmail.com>
On Mon, Aug 19, 2019 at 04:15:30PM -0700, Alexandra Wang wrote:
> On Sun, Aug 18, 2019 at 12:35 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> > . I was missing a way to check for compression ratio;
>
> Here are the ways to check compression ratio for zedstore:
>
> Table level:
> SELECT sum(uncompressedsz::numeric)/sum(totalsz) AS compratio FROM pg_zs_btree_pages(<tablename>);
postgres=# SELECT sum(uncompressedsz::numeric)/sum(totalsz) AS compratio FROM pg_zs_btree_pages('child.cdrs_huawei_pgwrecord_2019_07_01');
compratio | 4.2730304163521529
For a fair test, I created a separate ZFS tablspace for storing just a copy of
that table.
ts=# CREATE TABLE test TABLESPACE testcomp AS SELECT * FROM child.cdrs_huawei_pgwrecord_2019_07_01;
SELECT 39933381
Time: 882417.775 ms (14:42.418)
zfs/testJTP20190819 compressratio 6.01x -
zfs/testJTP20190819 compression gzip-1 inherited from zfs
> Per column level:
> select attno, count(*), sum(uncompressedsz::numeric)/sum(totalsz) as compratio from pg_zs_btree_pages(<tablename>) group by attno order by attno;
Order by 3; I see we have SOME highly compressed columns.
It's still surprising to me that's as low as it is, given their content: phone
numbers and IPv4 addresses in text form, using characters limited to
[[:digit:].]
(I realize we can probably save space using inet type.)
0 | 4743 | 1.00000000000000000000
32 | 21912 | 1.05953637381493823513
80 | 36441 | 1.2416446300175039
4 | 45059 | 1.3184106811322728
83 | 45059 | 1.3184106811322728
52 | 39208 | 1.3900788061770992
...
74 | 3464 | 10.8258665101057364
17 | 3535 | 10.8776086243096534
3 | 7092 | 11.0388009154683678
11 | 3518 | 11.4396055611832109
65 | 3333 | 14.6594723104237634
35 | 14077 | 15.1642131499381887
...
43 | 1601 | 21.4200106784573211
79 | 1599 | 21.4487670806076829
89 | 1934 | 23.6292134031933401
33 | 1934 | 23.6292134031933401
It seems clear the columns with high n_distinct have low compress ratio, and
columns with high compress ratio are those with n_distinct=1...
CREATE TEMP TABLE zs AS SELECT zs.*, n_distinct, avg_width, a.attname FROM (SELECT 'child.cdrs_huawei_pgwrecord_2019_07_01'::regclass t)t , LATERAL (SELECT attno, count(*), sum(uncompressedsz::numeric)/sum(totalsz) AS compratio FROM pg_zs_btree_pages(t) GROUP BY attno)zs , pg_attribute a, pg_class c, pg_stats s WHERE a.attrelid=t AND a.attnum=zs.attno AND c.oid=a.attrelid AND c.relname=s.tablename AND s.attname=a.attname;
n_distinct | compratio
------------+------------------------
217141 | 1.2416446300175039
154829 | 1.5306062496764190
144486 | 1.3900788061770992
128334 | 1.5395022739568842
121324 | 1.4005533187886683
86341 | 1.6262709389296389
84073 | 4.4379336418590519
65413 | 5.1890181028038757
63703 | 5.5029855093836425
63637 | 5.3648468796642262
46450 | 1.3184106811322728
46450 | 1.3184106811322728
43029 | 1.8003513772661308
39363 | 1.5845730687475706
36720 | 1.4751147557399539
36445 | 1.8403087513759131
36445 | 1.5453935268318613
11455 | 1.05953637381493823513
2862 | 9.8649823666870671
2625 | 2.3573614181847621
1376 | 1.7895024285340428
1335 | 2.2812551964262787
807 | 7.1192324141359373
610 | 7.9373623460089360
16 | 11.4396055611832109
10 | 5.5429763442365557
7 | 5.0440578041440675
7 | 5.2000132813261135
4 | 6.9741514753325536
4 | 4.2872818036896340
3 | 1.9080838412634827
3 | 2.9915954457453485
3 | 2.3056387009407882
2 | 10.8776086243096534
2 | 5.5950929307378287
2 | 18.5796576388128741
2 | 10.8258665101057364
2 | 9.1112820658021406
2 | 3.4986057630739795
2 | 4.6250999234025238
2 | 11.0388009154683678
1 | 15.1642131499381887
1 | 2.8855860118178798
1 | 23.6292134031933401
1 | 21.4200106784573211
[...]
> > it looks like zedstore
> > with lz4 gets ~4.6x for our largest customer's largest table. zfs using
> > compress=gzip-1 gives 6x compression across all their partitioned
> > tables,
> > and I'm surprised it beats zedstore .
> >
>
> What kind of tables did you use? Is it possible to give us the schema
> of the table? Did you perform 'INSERT INTO ... SELECT' or COPY?
I did this:
|time ~/src/postgresql.bin/bin/pg_restore /srv/cdrperfbackup/ts/final/child.cdrs_huawei_pgwrecord_2019_07_01 -f- |PGOPTIONS='-cdefault_table_access_method=zedstore' psql --port 5678 postgres --host /tmp
...
COPY 39933381
...
real 100m25.764s
child | cdrs_huawei_pgwrecord_2019_07_01 | table | pryzbyj | permanent | 8277 MB |
postgres=# SELECT array_to_string(array_agg(format_type(atttypid, atttypmod) ||CASE WHEN attnotnull THEN ' not null' ELSE '' END ORDER BY attnum),',') FROM pg_attribute WHERE attrelid='child.cdrs_huawei_pgwrecord_2019_07_01'::regclass AND attnum>0;
array_to_string | text not null,text,text not null,text not null,text not null,text,text,text,boolean,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,timestamp without time zone not null,bigint not null,text not null,text,text,text,text,text,text,text,text,text,text not null,text,boolean,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,timestamp with time zone,timestamp with time zone,text,text,boolean,text,text,boolean,boolean,text not null,text not null
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, lewang@pivotal.io, hlinnaka@iki.fi, aagrawal@pivotal.io, ashu.coek88@gmail.com
Subject: Re: Zedstore - compressed in-core columnar storage
In-Reply-To: <20190820020425.GX11185@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox