Re: zheap: a new storage format for PostgreSQL
От | Pavel Stehule |
---|---|
Тема | Re: zheap: a new storage format for PostgreSQL |
Дата | |
Msg-id | CAFj8pRD1WO9vHShO++FAmRwbi4Xrbn6cAGgdhL7AqtS63KNTvw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: zheap: a new storage format for PostgreSQL (Mithun Cy <mithun.cy@enterprisedb.com>) |
Ответы |
Re: zheap: a new storage format for PostgreSQL
(Amit Kapila <amit.kapila16@gmail.com>)
|
Список | pgsql-hackers |
čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy <mithun.cy@enterprisedb.com> napsal:
> On Thu, Mar 1, 2018 at 7:39 PM Amit Kapila <amit.kapila16@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 zheapCOPY 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
--
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: error message when subscription target is a partitioned table