Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com>
napsal:

> > On Thu, Mar 1, 2018 at 7:39 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
>
> I did some testing for performance of COPY command for zheap against heap,
> here are my results,
> Machine : cthulhu, (is a 8 node numa machine with 500GB of RAM)
> server non default settings: shared buffers 32GB, max_wal_size = 20GB,
> min_wal_size = 15GB
>
> Test tables and data:
> ----------------------------
> I have used pgbench_accounts table of pgbench tool as data source with 3
> different scale factors 100, 1000, 2000. Both heap and zheap table is
> lookalike of pgbench_accounts
>
> CREATE TABLE pgbench_zheap (LIKE pgbench_accounts) WITH
> (storage_engine='zheap');
> CREATE TABLE pgbench_heap (LIKE pgbench_accounts) WITH
> (storage_engine='heap');
>
> Test Commands:
> Command to generate datafile: COPY pgbench_accounts TO '/mnt/data-mag/
> mithun.cy/zheapperfbin/bin/pgbench.data';
>
> Command to load from datafile:
> COPY pgbench_heap FROM '/mnt/data-mag/
> mithun.cy/zheapperfbin/bin/pgbench.data'; -- heap table
> COPY pgbench_zheap FROM '/mnt/data-mag/
> mithun.cy/zheapperfbin/bin/pgbench.data'; -- zheap table
>
> Results
> ======
>
> Scale factor : 100
> ------------------------
> zheap table size : 1028 MB
> heap table size: 1281 MB
> -- table size reduction: 19% size reduction.
> zheap wal size: 1007 MB
> heap wal size: 1024 MB
> -- wal size difference: 1.6% size reduction.
> zheap COPY execution time: 24869.451 ms
> heap COPY execution time: 25858.773 ms
> -- % of improvement -- 3.8% reduction in execution time for zheap
>
> Scale factor : 1000
> -------------------------
> zheap table size : 10 GB
> heap table size: 13 GB
> -- table size reduction: 23% size reduction.
> zheap wal size: 10071 MB
> heap wal size: 10243 MB
> -- wal size difference: 1.67% size reduction.
> zheap COPY execution time: 270790.235 ms
> heap COPY execution time: 280325.632 ms
> -- % of improvement -- 3.4% reduction in execution time for zheap
>
> Scale factor : 2000
> -------------------------
> zheap table size : 20GB
> heap table size: 25GB
> -- table size reduction: 20% size reduction.
> zheap wal size: 20142 MB
> heap wal size: 20499 MB
> -- wal size difference: 1.7% size reduction.
> zheap COPY execution time: 523702.904 ms
> heap COPY execution time: 537537.720 ms
> -- % of improvement -- 2.5 % reduction in execution time for zheap
>
>
> COPY command seems to have improved very slightly with zheap in both with
> size of wal and execution time. I also did some tests with insert statement
> where I could see some regression in zheap when compared to heap with
> respect to execution time. With further more investigation I will reply
> here.
>
>
20% of size reduction looks like effect of fill factor.

Regards

Pavel

--
> Thanks and Regards
> Mithun Chicklore Yogendra
> EnterpriseDB: http://www.enterprisedb.com
>