Hervé already did some tests with zheap and documented his results yesterday. After some more discussions with Amit who did the session about zHeap at the conference here in Lisbon (you can find the slides here). I thought it might be a good idea to do some more testing on that probably upcoming feature. Lets go.
If you want to test it for your own, here is a simple script that clones the repository, compiles and installs from source and then start the PostgreSQL instance:
postgres@pgbox:/home/postgres/ [ZHEAP] cat refresh_zheap.sh
#!/bin/bash
rm -rf zheap
git clone https://github.com/EnterpriseDB/zheap
cd zheap
PGHOME=/u01/app/postgres/product/zheap/db_1/
SEGSIZE=2
BLOCKSIZE=8
./configure --prefix=${PGHOME}
--exec-prefix=${PGHOME}
--bindir=${PGHOME}/bin
--libdir=${PGHOME}/lib
--sysconfdir=${PGHOME}/etc
--includedir=${PGHOME}/include
--datarootdir=${PGHOME}/share
--datadir=${PGHOME}/share
--with-pgport=5432
--with-perl
--with-python
--with-openssl
--with-pam
--with-ldap
--with-libxml
--with-libxslt
--with-segsize=${SEGSIZE}
--with-blocksize=${BLOCKSIZE}
--with-systemd
make all
make install
cd contrib
make install
rm -rf /u02/pgdata/zheap
/u01/app/postgres/product/zheap/db_1/bin/initdb -D /u02/pgdata/zheap
pg_ctl -D /u02/pgdata/zheap start
psql -c "alter system set logging_collector='on'" postgres
psql -c "alter system set log_truncate_on_rotation='on'" postgres
psql -c "alter system set log_filename='postgresql-%a.log'" postgres
psql -c "alter system set log_line_prefix='%m - %l - %p - %h - %u@%d '" postgres
psql -c "alter system set log_directory='pg_log'" postgres
pg_ctl -D /u02/pgdata/zheap restart -m fast
First of all, when you startup PostgreSQL you’ll get two new background worker processes:
postgres@pgbox:/home/postgres/ [ZHEAP] ps -ef | egrep "discard|undo" postgres 1483 1475 0 14:40 ? 00:00:00 postgres: discard worker postgres 1484 1475 0 14:40 ? 00:00:01 postgres: undo worker launcher postgres 1566 1070 0 14:51 pts/0 00:00:00 grep -E --color=auto discard|undo
The “discard worker” is responsible for getting rid of all the undo segments that are not required anymore and the “undo worker launcher” obviously is responsible for launching undo worker processes for doing the rollbacks.
There is a new parameter which controls the default storage engine (at least the parameter is there as of now, maybe that will change in the future), so lets change that to zheap before we populate a sample database (“heap” is the default value):
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "alter system set storage_engine='zheap'" postgres ALTER SYSTEM Time: 12.722 ms postgres@pgbox:/home/postgres/ [ZHEAP] pg_ctl -D $PGDATA restart -m fast postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "show storage_engine" postgres storage_engine ---------------- zheap (1 row)
Lets use pgbench to create the sample data:
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "create database zheap" postgres CREATE DATABASE Time: 763.284 ms postgres@pgbox:/home/postgres/ [ZHEAP] time pgbench -i -s 100 zheap ... done. real 0m23.375s user 0m2.293s sys 0m0.772s
That should have created the tables using the zheap storage engine:
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "d+ pgbench_accounts" zheap
Table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+----------+--------------+-------------
aid | integer | | not null | | plain | |
bid | integer | | | | plain | |
abalance | integer | | | | plain | |
filler | character(84) | | | | extended | |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Options: storage_engine=zheap, fillfactor=100
When we do the same using the “heap” storage format how long does that take?:
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "alter system set storage_engine='heap'" postgres
ALTER SYSTEM
Time: 8.790 ms
postgres@pgbox:/home/postgres/ [ZHEAP] pg_ctl -D $PGDATA restart -m fast
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "create database heap" postgres
CREATE DATABASE
Time: 889.847 ms
postgres@pgbox:/home/postgres/ [ZHEAP] time pgbench -i -s 100 heap
...
real 0m30.471s
user 0m2.355s
sys 0m0.419s
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "d+ pgbench_accounts" heap
Table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+----------+--------------+-------------
aid | integer | | not null | | plain | |
bid | integer | | | | plain | |
abalance | integer | | | | plain | |
filler | character(84) | | | | extended | |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Options: fillfactor=100
postgres@pgbox:/home/postgres/ [ZHEAP]
I ran that test several times but the difference of about 5 to 6 seconds is consistent. zheap is faster here, but that is coming from vacuum. When you run the same test again but skip the vacuum ( the “-n” option of pgbench) at the end, heap is faster:
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "create database heap" postgres CREATE DATABASE Time: 562.155 ms postgres@pgbox:/home/postgres/ [ZHEAP] time pgbench -i -n -s 100 heap done. real 0m21.650s user 0m2.316s sys 0m0.225s
But anyway: As zheap has to create undo segments more needs to go to disk initially. heap needs to run vacuum, not immediately but for sure some time later. When you compare a pure insert only workload, without vacuum, heap is faster. The great thing is, that you can decide what you want to use on the table level. Some tables might be better created with the zheap storage engine, others may be better created with heap. The important bit is that you have full control.
Hervé already compared the size of his tables in the last post. Do we see the same here when we compare the size of the entire databases?
postgres@pgbox:/home/postgres/ [ZHEAP] vacuumdb heap
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "l+" postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+------------+------------+-----------------------+---------+------------+------------------------------------
heap | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | 1503 MB | pg_default |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | 7867 kB | pg_default | default administrative connection d
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 7721 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 7721 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
zheap | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | 1250 MB | pg_default |
(5 rows)
Yes, heap is 253MB larger. That difference should even get bigger once we populate the “filler” column of the pgbench_accounts table, which is currently NULL:
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "update pgbench_accounts set filler = 'aaaaaa'" zheap
UPDATE 10000000
Time: 55768.488 ms (00:55.768)
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "update pgbench_accounts set filler = 'aaaaaa'" heap
UPDATE 10000000
Time: 52598.782 ms (00:52.599)
postgres@pgbox:/home/postgres/ [ZHEAP] vacuumdb heap
vacuumdb: vacuuming database "heap"
postgres@pgbox:/home/postgres/ [ZHEAP] psql -c "l+" postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+------------+------------+-----------------------+---------+------------+-----------------------------------------
heap | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | 3213 MB | pg_default |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | 7867 kB | pg_default | default administrative connection databa
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 7721 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 7721 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
zheap | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | 1250 MB | pg_default |
As expected and consistent with what Herve has seen in his tests. The update against the heap table was a bit faster (around 3 seconds) but again: zheap hast to create undo segments and that causes additional writes on disk. Three seconds against a 10 million row table is not that huge, by the way, and how often do you update the complete table?
Now lets run a standard pgbench workload against these database and check what we can see there. For the zheap database with 1 connection for 60 seconds this is the best result I got after ten runs:
postgres@pgbox:/home/postgres/ [ZHEAP] pgbench -c 1 -T 60 zheap starting vacuum...end. transaction type: scaling factor: 100 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 29265 latency average = 2.050 ms tps = 487.726916 (including connections establishing) tps = 487.786025 (excluding connections establishing)
The same against the heap:
postgres@pgbox:/home/postgres/ [ZHEAP] pgbench -c 1 -T 60 heap starting vacuum...end. transaction type: scaling factor: 100 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 24992 latency average = 2.401 ms tps = 416.485499 (including connections establishing) tps = 416.516805 (excluding connections establishing)
The numbers changed a bit for every execution but always zheap was better than heap (Be aware that I am on little VM here), so at least there is no regression in performance but rather an improvement for this workload.
For the select only workload (the “-S” option) this is the best result for heap:
postgres@pgbox:/home/postgres/ [ZHEAP] for i in {1..10}; do pgbench -c 1 -S -T 60 heap; done
...
starting vacuum...end.
transaction type:
scaling factor: 100
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 64954
latency average = 0.924 ms
tps = 1082.514439 (including connections establishing)
tps = 1082.578288 (excluding connections establishing)
...
And this is the best result for zheap:
postgres@pgbox:/home/postgres/ [ZHEAP] for i in {1..10}; do pgbench -c 1 -S -T 60 zheap; done
...
starting vacuum...end.
transaction type:
scaling factor: 100
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 109023
latency average = 0.550 ms
tps = 1816.787280 (including connections establishing)
tps = 1817.485717 (excluding connections establishing)
...
With this workload the difference is even more clear: zheap clearly wins.
As noted before: all these test have been done locally on a little VM, so be careful with these number. We should have access to a great storage system with some good servers soon and once we have that I’ll do some more tests and publish the results.
For now it is somehow clear that zheap is an improvement for several types of workloads while heap still is better for others. In the next post I’ll try to do some tests to help the developers, meaning: Can we break it?
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)