PostgreSQL undo多版本存储引擎 zheap测试

5 minute read

背景

undo 存储引擎,由于大量使用inplace update,所以大幅度降低了表膨胀的概率。并且减少了垃圾回收引入的IO。

预计会在PG 12的版本中release。

目前可以下源码进行测试。

部署zheap引擎

1、源码使用

https://github.com/EnterpriseDB/zheap

2、其他参考

《PostgreSQL on Linux 最佳部署手册 - 珍藏级》

简单步骤如下

git clone https://github.com/EnterpriseDB/zheap  
cd zheap  
  
export USE_NAMED_POSIX_SEMAPHORES=1  
LIBS=-lpthread CFLAGS="-O3 " ./configure --with-trans_slots_per_zheap_page=8 --prefix=/home/digoal/pgsql_zheap  
LIBS=-lpthread CFLAGS="-O3 " make world -j 64  
LIBS=-lpthread CFLAGS="-O3 " make install-world  

或调试编译

export USE_NAMED_POSIX_SEMAPHORES=1  
LIBS=-lpthread CFLAGS="-O0 -g -ggdb -fno-omit-frame-pointer" ./configure --prefix=/home/digoal/pgsql_zheap --enable-cassert  
LIBS=-lpthread CFLAGS="-O0 -g -ggdb -fno-omit-frame-pointer" make world -j 64  
LIBS=-lpthread CFLAGS="-O0 -g -ggdb -fno-omit-frame-pointer" make install-world  

环境变量

export PS1="$USER@`/bin/hostname -s`-> "      
export PGPORT=4001      
export PGDATA=/data01/digoal/pg_root$PGPORT      
export LANG=en_US.utf8      
export PGHOME=/home/digoal/pgsql_zheap  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH      
export DATE=`date +"%Y%m%d%H%M"`    
export PATH=$PGHOME/bin:$PATH:.      
export MANPATH=$PGHOME/share/man:$MANPATH      
export PGHOST=$PGDATA      
export PGUSER=postgres      
export PGDATABASE=postgres      
alias rm='rm -i'      
alias ll='ls -lh'      
unalias vi   

初始化数据库

initdb -D $PGDATA -U postgres --lc-collate=C --lc-ctype=en_US.utf8 -E UTF8  

修改配置文件

cat postgresql.auto.conf   
listen_addresses = '0.0.0.0'    
port = 4001  
max_connections = 2000    
superuser_reserved_connections = 3    
unix_socket_directories = '., /tmp'    
tcp_keepalives_idle = 60    
tcp_keepalives_interval = 10    
tcp_keepalives_count = 10    
shared_buffers = 300GB    
huge_pages=on  
max_prepared_transactions = 2000    
work_mem = 8MB    
maintenance_work_mem = 2GB    
dynamic_shared_memory_type = posix    
vacuum_cost_delay = 0    
bgwriter_delay = 10ms    
bgwriter_lru_maxpages = 1000    
bgwriter_lru_multiplier = 10.0    
effective_io_concurrency = 0    
wal_level = minimal      
# full_page_writes=off  
synchronous_commit = off    
wal_writer_delay = 10ms    
checkpoint_timeout = 15min    
max_wal_size = 600GB    
min_wal_size = 150GB    
checkpoint_completion_target = 0.1    
max_wal_senders = 0    
effective_cache_size = 200GB    
log_destination = 'csvlog'    
logging_collector = on    
log_directory = 'log'    
log_filename = 'postgresql-%a.log'    
log_truncate_on_rotation = on    
log_rotation_age = 1d    
log_rotation_size = 0    
log_checkpoints = on     
log_connections = on    
log_disconnections = on    
log_error_verbosity = verbose     
log_line_prefix = '%m [%p] '    
log_timezone = 'PRC'    
log_autovacuum_min_duration = 0    
autovacuum_max_workers = 16    
autovacuum_freeze_max_age = 1200000000    
autovacuum_multixact_freeze_max_age = 1400000000    
autovacuum_vacuum_cost_delay = 0ms    
vacuum_freeze_table_age = 1150000000    
vacuum_multixact_freeze_table_age = 1150000000    
datestyle = 'iso, mdy'    
timezone = 'PRC'    
lc_messages = 'C'    
lc_monetary = 'C'    
lc_numeric = 'C'    
lc_time = 'C'    
default_text_search_config = 'pg_catalog.english'    
jit = off    
cpu_tuple_cost=0.00018884145574257426      
cpu_index_tuple_cost = 0.00433497085216479990      
cpu_operator_cost = 0.00216748542608239995      
seq_page_cost=0.014329      
random_page_cost = 0.016   
  
max_worker_processes = 128      
max_parallel_workers = 32      
max_parallel_maintenance_workers = 24      
max_parallel_workers_per_gather = 0      
parallel_leader_participation = on      
min_parallel_table_scan_size=0    
min_parallel_index_scan_size=0    
parallel_setup_cost=0    
parallel_tuple_cost=0  

启动数据库

pg_ctl start  

创建表空间

mkdir /data02/digoal/zheap_tbs1  
mkdir /data03/digoal/zheap_tbs2  
  
postgres=# create tablespace tbs1 location '/data02/digoal/zheap_tbs1';  
CREATE TABLESPACE  
postgres=# create tablespace tbs2 location '/data03/digoal/zheap_tbs2';  
CREATE TABLESPACE  

创建zheap引擎的表

create table t_zheap(c1 int, c2 varchar)   
with (storage_engine='zheap')   -- 使用zheap引擎,默认为heap引擎。  
tablespace tbs1;  

使用sysbench-tpcc进行测试

https://github.com/digoal/sysbench-tpcc

准备数据

export pgsql_table_options="with (storage_engine='zheap') tablespace tbs1"  
export pgsql_index_options="tablespace tbs2"  
  
./tpcc.lua --pgsql-host=/tmp --pgsql-port=4001 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=20 --scale=100 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --use_fk=0 prepare  
Total DISK READ :       7.80 K/s | Total DISK WRITE :     503.30 M/s  
Actual DISK READ:       7.80 K/s | Actual DISK WRITE:     777.18 M/s  
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                
50651 be/4 digoal      0.00 B/s  279.37 M/s  0.00 %  6.78 % postgres: walwriter      
50654 be/4 digoal      3.90 K/s   96.00 M/s  0.00 %  2.18 % postgres: undo launcher  
53662 be/4 digoal      0.00 B/s 1248.69 K/s  0.00 %  0.14 % postgres: postgres postgres [local] INSERT  
53396 be/4 digoal      3.90 K/s    2.39 M/s  0.00 %  0.01 % postgres: postgres postgres [local] INSERT          
53637 be/4 digoal      0.00 B/s 1521.84 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53670 be/4 digoal      0.00 B/s  476.06 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53527 be/4 digoal      0.00 B/s 1771.58 K/s  0.00 %  0.00 % postgres: postgres postgres [local] idle            
53542 be/4 digoal      0.00 B/s    3.06 M/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53554 be/4 digoal      0.00 B/s 1787.19 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53702 be/4 digoal      0.00 B/s  842.87 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53660 be/4 digoal      0.00 B/s 1475.02 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT  
53625 be/4 digoal      0.00 B/s    4.36 M/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53533 be/4 digoal      0.00 B/s 1225.28 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53655 be/4 digoal      0.00 B/s 1396.97 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53658 be/4 digoal      0.00 B/s    2.50 M/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53688 be/4 digoal      0.00 B/s 1077.00 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53649 be/4 digoal      0.00 B/s    2.46 M/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53671 be/4 digoal      0.00 B/s    6.58 M/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53410 be/4 digoal      0.00 B/s 1592.08 K/s  0.00 %  0.00 % postgres: postgres postgres [local] idle            
53620 be/4 digoal      0.00 B/s    2.52 M/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53700 be/4 digoal      0.00 B/s 1373.56 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53529 be/4 digoal      0.00 B/s    2.71 M/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53526 be/4 digoal      0.00 B/s 1365.76 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53643 be/4 digoal      0.00 B/s    5.40 M/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT          
53505 be/4 digoal      0.00 B/s 1272.10 K/s  0.00 %  0.00 % postgres: postgres postgres [local] INSERT    
  
       
  
postgres=# \l+  
                                                                 List of databases  
   Name    |  Owner   | Encoding | Collate |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description                   
-----------+----------+----------+---------+------------+-----------------------+---------+------------+--------------------------------------------  
 postgres  | postgres | UTF8     | C       | en_US.utf8 |                       | 223 GB  | pg_default | default administrative connection database  
 template0 | postgres | UTF8     | C       | en_US.utf8 | =c/postgres          +| 7769 kB | pg_default | unmodifiable empty database  
           |          |          |         |            | postgres=CTc/postgres |         |            |   
 template1 | postgres | UTF8     | C       | en_US.utf8 | =c/postgres          +| 7769 kB | pg_default | default template for new databases  
           |          |          |         |            | postgres=CTc/postgres |         |            |   
(3 rows)  

压测

nohup ./tpcc.lua --pgsql-host=/tmp --pgsql-port=4001 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=20 --scale=100 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --use_fk=0 --time=604800 --report-interval=1 run > ./zheap.tpcc.log 2>&1 &  

目前ZHEAP还在开发阶段,有些问题,提交gdb bt.

2018-09-22 10:31:09.927 CST,"postgres","postgres",3224,"[local]",5ba5a360.c98,173,"EXECUTE",2018-09-22 10:05:20 CST,66/449744,26393362,LOG,00000,"created undo segment ""base/undo/000002.0096800000""",,,,,,"execute p_new_order8_6(66,62176,27)",,"allocate_empty_undo_segment, undolog.c:593",""  
2018-09-22 10:31:09.931 CST,"postgres","postgres",3211,"[local]",5ba5a360.c8b,174,"EXECUTE",2018-09-22 10:05:20 CST,53/450654,26393510,LOG,00000,"created undo segment ""base/undo/000017.0096200000""",,,,,,"execute p_new_order7_19_02(30886,62)",,"allocate_empty_undo_segment, undolog.c:593",""  
2018-09-22 10:31:09.944 CST,,,3452,,5ba5a966.d7c,7,,2018-09-22 10:31:02 CST,69/5044,26314469,LOG,00000,"created undo segment ""base/undo/000041.0000900000""",,,,,,,,"allocate_empty_undo_segment, undolog.c:593",""  
2018-09-22 10:31:09.949 CST,"postgres","postgres",3221,"[local]",5ba5a360.c95,197,"EXECUTE",2018-09-22 10:05:20 CST,63/450578,26393718,LOG,00000,"created undo segment ""base/undo/000034.007C100000""",,,,,,"execute p_new_order8_9(60,19799,91)",,"allocate_empty_undo_segment, undolog.c:593",""  
2018-09-22 10:31:09.965 CST,"postgres","postgres",3172,"[local]",5ba5a360.c64,185,"EXECUTE",2018-09-22 10:05:20 CST,14/454551,26393944,LOG,00000,"created undo segment ""base/undo/00001D.0096A00000""",,,,,,"execute p_new_order8_2(87,32171,25)",,"allocate_empty_undo_segment, undolog.c:593",""  
2018-09-22 10:31:10.009 CST,"postgres","postgres",3217,"[local]",5ba5a360.c91,176,"EXECUTE",2018-09-22 10:05:20 CST,59/450987,26394408,LOG,00000,"created undo segment ""base/undo/000018.007EF00000""",,,,,,"execute p_delivery7_10(43829,2730,7,32)",,"allocate_empty_undo_segment, undolog.c:593",""  
2018-09-22 10:31:10.019 CST,"postgres","postgres",3165,"[local]",5ba5a360.c5d,179,"EXECUTE",2018-09-22 10:05:20 CST,7/452940,26394603,LOG,00000,"created undo segment ""base/undo/000025.0096400000""",,,,,,"execute p_new_order8_11(63,53488,32)",,"allocate_empty_undo_segment, undolog.c:593",""  
2018-09-22 10:31:10.050 CST,"postgres","postgres",3166,"[local]",5ba5a360.c5e,172,"EXECUTE",2018-09-22 10:05:20 CST,8/451340,26395133,ERROR,23505,"duplicate key value violates unique constraint ""orders7_pkey""","Key (o_w_id, o_d_id, o_id)=(97, 8, 3241) already exists.",,,,,"execute p_new_order4_7(3241,8,97,1617,6,1)",,"_bt_check_unique, nbtinsert.c:548",""  
2018-09-22 10:31:10.050 CST,"postgres","postgres",3166,"[local]",5ba5a360.c5e,173,"ROLLBACK",2018-09-22 10:05:20 CST,8/451342,0,WARNING,25P01,"there is no transaction in progress",,,,,,,,"UserAbortTransactionBlock, xact.c:4001",""  
2018-09-22 10:31:10.076 CST,"postgres","postgres",3166,"[local]",5ba5a360.c5e,174,"EXECUTE",2018-09-22 10:05:20 CST,8/451344,26395382,LOG,00000,"created undo segment ""base/undo/000008.0096600000""",,,,,,"execute p_new_order7_17_08(7969,1)",,"allocate_empty_undo_segment, undolog.c:593",""  
2018-09-22 10:31:10.095 CST,,,50646,,5ba59719.c5d6,3,,2018-09-22 09:12:57 CST,,0,LOG,00000,"server process (PID 3190) was terminated by signal 11: Segmentation fault","Failed process was running: execute p_payment3_2(3383,22,10)",,,,,,,"LogChildExit, postmaster.c:3586",""  

参考

src/backend/access/zheap/README

src/backend/access/undo/README

Flag Counter

digoal’s 大量PostgreSQL文章入口