作者
digoal
日期
2018-09-25
标签
PostgreSQL , zheap , undo , mvcc
背景
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
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
```
或调试编译
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
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
