čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy <mithun...@enterprisedb.com> napsal:
> > On Thu, Mar 1, 2018 at 7:39 PM Amit Kapila <amit.kapil...@gmail.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 >