Infrastructure at your Service

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 smaller. 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?

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure