来自PostgreSQL中国社区发起人之一，阿里数据库内核高级技术专家德歌在云栖社区个人博客的深度分享。使用BenchmarkSQL测试Oracle 12c的TPC-C性能，并在同样的硬件测试环境下，测试了PostgreSQL 9.5.0。相关数据对比如下。
测试机：
3 * PCI-E SSD，逻辑卷条带，XFS，数据块对齐，16核开HT，256G内存。
需要准备：benchmarkSQL 配置，Oracle 12c TPC-C 测试结果、文件系统XFS优化手段等。（由于今日头条对文章字数有限定，大段代码无法全部显示，请需要查看测试细节以及深度交流的技术朋友移步云栖社区德歌个人博客）
PostgreSQL编译项
./configure --prefix=/u02/digoal/soft_bak/pgsql9.5--with-blocksize=8--with-pgport=1921--with-perl --with-python --with-tcl--with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety
gmake world -j32
gmake install-world -j32
配置postgres环境变量
$ vi env_pg.sh
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=1921
export PGDATA=/data01/pgdata/pg_root
export LANG=en_US.utf8
export PGHOME=/u02/digoal/soft_bak/pgsql9.5
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 PGDATABASE=postgres
export PGUSER=postgres
alias rm=rm -i
alias ll=ls -lh
unalias vi
配置postgresql.conf
$ vi $PGDATA/postgresql.conf
port =1921# (change requires restart)
max_connections = 300 # (change requires restart)
unix_socket_directories = . # comma-separated list of directories
shared_buffers = 32GB # min 128kB
huge_pages = try # on, off, or try
maintenance_work_mem = 2GB # min 1MB
dynamic_shared_memory_type = posix # the default is the first option
bgwriter_delay = 10ms # 10-10000ms between rounds
wal_level = minimal # minimal, archive, hot_standby, or logical
synchronous_commit = off # synchronization level;
full_page_writes = off # recover from partial page writes
wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
max_wal_size = 32GB
effective_cache_size = 240GB
log_destination = csvlog # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on # If on, an existing log file with the
log_timezone = PRC
datestyle = iso, mdy
timezone = PRC
lc_messages = C # locale for system error message
lc_monetary = C # locale for monetary formatting
lc_numeric = C # locale for number formatting
lc_time = C # locale for time formatting
default_text_search_config = pg_catalog.english
下载最新java版本对应的postgresql jdbc jar（略）
修改log4j，减少日志打印量。priority改成info，只输出最终结果，不输出产生订单的日志。（略）
配置postgres用户默认搜索路径（略）
创建用于存放生成CSV的目录（略）
生成CSV
$ ./runLoader.sh props.pg numWarehouses 1000 fileLocation /u02/digoal/soft_bak/benchcsv/
1000个仓库的数据量：
total 69G
-rw-r--r--1 digoal users 2.0GJan915:53 cust-hist.csv
-rw-r--r--1 digoal users 16GJan915:53 customer.csv
-rw-r--r--1 digoal users 898KJan915:12 district.csv
-rw-r--r--1 digoal users 7.0MJan914:22 item.csv
-rw-r--r--1 digoal users 95MJan916:14new-order.csv
-rw-r--r--1 digoal users 1.3GJan916:14 order.csv
-rw-r--r--1 digoal users 22GJan916:14 order-line.csv
-rw-r--r--1 digoal users 28GJan915:12 stock.csv
-rw-r--r--1 digoal users 84KJan914:22 warehouse.csv
导入数据库
$ ./runSQL.sh props.pg sqlTableCopies
创建约束和索引
$ ./runSQL.sh props.pg sqlIndexCreates
备份
$ pg_dump -f /u02/digoal/soft_bak/benchmarksql.dmp -F c -n benchmarksql postgres
压测：
nohup ./runBenchmark.sh props.pg >/dev/null2>./errrun.log &
测试结果：
INFO [2022-01-0922:03:39.961]Thread-7Term-00,
INFO [2022-01-0922:03:39.963]Thread-7Term-00,
INFO [2022-01-0922:03:39.963]Thread-7Term-00,Measured tpmC (NewOrders)=102494.46
INFO [2022-01-0922:03:39.963]Thread-7Term-00,Measured tpmTOTAL =256195.32
INFO [2022-01-0922:03:39.964]Thread-7Term-00,SessionStart=2022-01-0921:53:39
INFO [2022-01-0922:03:39.964]Thread-7Term-00,SessionEnd=2022-01-0922:03:39
INFO [2022-01-0922:03:39.964]Thread-7Term-00,TransactionCount=2563088
主机信息，截取压测第9分钟的数据。（略）
阶段1（测试程序和数据库在同一主机）PostgreSQL 9.5.0 对比 Oracle 12c TPC-C tpm对比如下：
测试数据仅供参考。
使用benchmarksql测试，系统还有大量空闲CPU，IO资源，所以性能应该不止于此。预计PostgreSQL可到50W tpm。
有兴趣的童鞋可以使用load runner或者sysbench或其他工具再测试一下。
------------------------------------------------------------------------------------------------------------
特别感谢给Oracle优化支招的Oracle圈子的兄弟姐妹们。
优化中，期待Oracle更好的表现。
AWR报告截图见：
http://blog.163.com/digoal@126/blog/static/1638770402022112344924835/
------------------------------------------------------------------------------------------------------------
阶段2对比，
benchmarksql放到另一台主机，主机间万兆网同一交换机下互联。
参考
http://blog.163.com/digoal@126/blog/static/163877040202201021838221/
------------------------------------------------------------------------------------------------------------
为了突破测试程序的极限，开4个schema，每个schema负责1000个仓库，数据量总共20亿左右，数据量400GB。
每个测试程序对付一个schema。
终端数保持一致，每个测试程序开24个终端，一共96个终端。
测试数据量
postgres=# \l+
List of databases
Name
Owner
Encoding
Collate
Ctype
Access privileges
Size
Tablespace
Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
test01
test01
UTF8
C
C
100 GB
pg_default
test02
test02
UTF8
C
C
100 GB
pg_default
test03
test03
UTF8
C
C
100 GB
pg_default
test04
test04
UTF8
C
C
100 GB
pg_default
benchmarksql软件目录
$ ll
drwxr-xr-x 7 digoal users 4.0KJan1014:41 benchmarksql-4.1.0_pg01
drwxr-xr-x 7 digoal users 4.0KJan1014:41 benchmarksql-4.1.0_pg02
drwxr-xr-x 7 digoal users 4.0KJan1014:41 benchmarksql-4.1.0_pg03
drwxr-xr-x 7 digoal users 4.0KJan1014:41 benchmarksql-4.1.0_pg04
测试
cd benchmarksql-4.1.0_pg01/run
nohup ./runBenchmark.sh props.pg >/dev/null2>./errrun.log &
cd ../../benchmarksql-4.1.0_pg02/run
nohup ./runBenchmark.sh props.pg >/dev/null2>./errrun.log &
cd ../../benchmarksql-4.1.0_pg03/run
nohup ./runBenchmark.sh props.pg >/dev/null2>./errrun.log &
cd ../../benchmarksql-4.1.0_pg04/run
nohup ./runBenchmark.sh props.pg >/dev/null2>./errrun.log &
cd ../..
测试结果
$ cat benchmarksql-4.1.0_pg01/run/log/benchmarksql.log
INFO [2022-01-1017:54:04.925]Thread-22Term-00,Measured tpmC (NewOrders)=45416.28
INFO [2022-01-1017:54:04.925]Thread-22Term-00,Measured tpmTOTAL =113487.61
INFO [2022-01-1017:54:04.925]Thread-22Term-00,SessionStart=2022-01-1017:44:04
INFO [2022-01-1017:54:04.925]Thread-22Term-00,SessionEnd=2022-01-1017:54:04
INFO [2022-01-1017:54:04.925]Thread-22Term-00,TransactionCount=1134913
$ cat benchmarksql-4.1.0_pg02/run/log/benchmarksql.log
INFO [2022-01-1017:54:04.943]Thread-12Term-00,Measured tpmC (NewOrders)=45292.48
INFO [2022-01-1017:54:04.943]Thread-12Term-00,Measured tpmTOTAL =113269.54
INFO [2022-01-1017:54:04.943]Thread-12Term-00,SessionStart=2022-01-1017:44:04
INFO [2022-01-1017:54:04.944]Thread-12Term-00,SessionEnd=2022-01-1017:54:04
INFO [2022-01-1017:54:04.944]Thread-12Term-00,TransactionCount=1132770
$ cat benchmarksql-4.1.0_pg03/run/log/benchmarksql.log
INFO [2022-01-1017:54:04.955]Thread-12Term-00,Measured tpmC (NewOrders)=45336.15
INFO [2022-01-1017:54:04.955]Thread-12Term-00,Measured tpmTOTAL =113247.19
INFO [2022-01-1017:54:04.956]Thread-12Term-00,SessionStart=2022-01-1017:44:04
INFO [2022-01-1017:54:04.956]Thread-12Term-00,SessionEnd=2022-01-1017:54:04
INFO [2022-01-1017:54:04.956]Thread-12Term-00,TransactionCount=1132537
$ cat benchmarksql-4.1.0_pg04/run/log/benchmarksql.log
INFO [2022-01-1017:54:04.986]Thread-23Term-00,Measured tpmC (NewOrders)=45231.67
INFO [2022-01-1017:54:04.987]Thread-23Term-00,Measured tpmTOTAL =113054.3
INFO [2022-01-1017:54:04.987]Thread-23Term-00,SessionStart=2022-01-1017:44:04
INFO [2022-01-1017:54:04.987]Thread-23Term-00,SessionEnd=2022-01-1017:54:04
INFO [2022-01-1017:54:04.987]Thread-23Term-00,TransactionCount=1130640
TPM ：
113487.61 + 113269.54 + 113247.19 + 113054.3 = 453058.64
第9分钟操作系统统计信息
TOP
top -17:38:27 up 4 days,8:32,4 users, load average:78.54,68.64,37.22
Tasks:658 total,34 running,624 sleeping,0 stopped,0 zombie
Cpu(s):70.2%us,15.7%sy,0.0%ni,5.5%id,1.5%wa,0.0%hi,7.1%si,0.0%st
Mem:264643396k total,229866068k used,34777328k free,59652k buffers
Swap:18825200k total,0k used,18825200k free,183529592k cached
iostat -x
avg-cpu:%user %nice %system %iowait %steal %idle
71.390.0022.471.260.004.88
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
dfa 0.000.003659.337008.6758538.67112050.6715.995.850.550.0668.17
dfb0.000.003714.676888.6759418.67110173.3315.995.980.560.0667.87
dfc0.000.003709.006974.3359328.00111504.0015.995.630.520.0771.60
dm-00.000.0011083.0020870.33177285.33333706.6715.9917.600.550.0392.10
测试过程oprofile报告
#/home/digoal/oprof/bin/opreport -l -f -w -x -t 0.5
Using /soft/digoal/soft_bak/oprof_test/oprofile_data/samples/ for samples directory.
WARNING!Some of the events were throttled.Throttling occurs when
the initial sample rate is too high, causing an excessive number of
interrupts.Decrease the sampling frequency.Check the directory
/soft/digoal/soft_bak/oprof_test/oprofile_data/samples/current/stats/throttled
for the throttled event names.
CPU:IntelIvyBridge microarchitecture, speed 2600MHz(estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles whennot halted)with a unit mask of 0x00(No unit mask) count100000
vma samples % app name symbol name
007a7780 26327005.2511/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_search_with_hash_value
004a92f0 18959243.7816/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_compare
006969c0 18443713.6787/soft/digoal/soft_bak/pgsql9.5/bin/postgres GetSnapshotData
0078a09017750313.5404/soft/digoal/soft_bak/pgsql9.5/bin/postgres SearchCatCache
006a4bd017253503.4413/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockAcquire
007bc3a015651903.1219/soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetAlloc
0049893014066942.8058/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_hot_search_buffer
005b8f70 9656461.9261/soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitExpr
006895d07670781.5300/soft/digoal/soft_bak/pgsql9.5/bin/postgres PinBuffer
004aaa806177411.2321/soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_checkkeys
007a2180 5880431.1729/soft/digoal/soft_bak/pgsql9.5/bin/postgres FunctionCall2Coll
006a42205758641.1486/soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockRelease
007ac6204851620.9677/soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_encoding_mbcliplen
007a3950 4711020.9396/soft/digoal/soft_bak/pgsql9.5/bin/postgres fmgr_info_cxt_security
0046c790 4415480.8807/soft/digoal/soft_bak/pgsql9.5/bin/postgres slot_deform_tuple
0048c8f0 4258670.8494/soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_any
006b2e50 4045480.8069/soft/digoal/soft_bak/pgsql9.5/bin/postgres PostgresMain
007bd0f03965100.7909/soft/digoal/soft_bak/pgsql9.5/bin/postgres palloc
0049bce0 3942010.7863/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune_opt
007bce00 3532430.7046/soft/digoal/soft_bak/pgsql9.5/bin/postgres pfree
0049b300 3358960.6700/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune
0046c580 3131450.6246/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_getsysattr
006b14a0 3117760.6219/soft/digoal/soft_bak/pgsql9.5/bin/postgres exec_bind_message
007cb070 2921060.5826/soft/digoal/soft_bak/pgsql9.5/bin/postgres HeapTupleSatisfiesMVCC
007bd2102752820.5491/soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextAllocZeroAligned
005b85302731990.5449/soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecProject
00494ba02664950.5315/soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_update
007bca10 2655560.5297/soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetFree
第二阶段Oracle 12.1.0.2.0和PostgreSQL 9.5.0 TPM 对比
------------------------------------------------------------------------------------------------------------
阶段3,
开启PostgreSQL 预读, 大页, 分组提交：
listen_addresses =0.0.0.0# what IP address(es) to listen on;
port = 1921 # (change requires restart)
max_connections = 300 # (change requires restart)
unix_socket_directories = . # comma-separated list of directories
shared_buffers = 164GB # min 128kB
huge_pages = on # on, off, or try
maintenance_work_mem = 2GB # min 1MB
dynamic_shared_memory_type = posix # the default is the first option
bgwriter_delay = 10ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round
bgwriter_lru_multiplier = 10.0 # 0-10.0 multipler on buffers scanned/round
effective_io_concurrency = 2 # 1-1000; 0 disables prefetching
wal_level = minimal # minimal, archive, hot_standby, or logical
synchronous_commit = off # synchronization level;
full_page_writes = off # recover from partial page writes
wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
commit_delay = 10 # range 0-100000, in microseconds
commit_siblings = 16 # range 1-1000
checkpoint_timeout = 35min # range 30s-1h
max_wal_size = 320GB
checkpoint_completion_target = 0.8 # checkpoint target duration, 0.0 - 1.0
effective_cache_size = 240GB
log_destination = csvlog # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on # If on, an existing log file with the
log_timezone = PRC
datestyle = iso, mdy
timezone = PRC
lc_messages = C # locale for system error message
lc_monetary = C # locale for monetary formatting
lc_numeric = C # locale for number formatting
lc_time = C # locale for time formatting
default_text_search_config = pg_catalog.english
测试结果：
$tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log
INFO [2022-01-1113:33:55.917]Thread-14Term-00,Measured tpmC (NewOrders)=48151.07
INFO [2022-01-1113:33:55.917]Thread-14Term-00,Measured tpmTOTAL =120215.48
INFO [2022-01-1113:33:55.917]Thread-14Term-00,SessionStart=2022-01-1113:23:55
INFO [2022-01-1113:33:55.917]Thread-14Term-00,SessionEnd=2022-01-1113:33:55
INFO [2022-01-1113:33:55.917]Thread-14Term-00,TransactionCount=1202222
$tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log
INFO [2022-01-1113:33:55.971]Thread-16Term-00,Measured tpmC (NewOrders)=48505.54
INFO [2022-01-1113:33:55.971]Thread-16Term-00,Measured tpmTOTAL =121182.26
INFO [2022-01-1113:33:55.971]Thread-16Term-00,SessionStart=2022-01-1113:23:55
INFO [2022-01-1113:33:55.972]Thread-16Term-00,SessionEnd=2022-01-1113:33:55
INFO [2022-01-1113:33:55.972]Thread-16Term-00,TransactionCount=1211858
$tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log
INFO [2022-01-1113:33:55.985]Thread-4Term-00,Measured tpmC (NewOrders)=48119.61
INFO [2022-01-1113:33:55.985]Thread-4Term-00,Measured tpmTOTAL =120523.98
INFO [2022-01-1113:33:55.985]Thread-4Term-00,SessionStart=2022-01-1113:23:55
INFO [2022-01-1113:33:55.985]Thread-4Term-00,SessionEnd=2022-01-1113:33:55
INFO [2022-01-1113:33:55.985]Thread-4Term-00,TransactionCount=1205271
$tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log
INFO [2022-01-1113:33:55.958]Thread-21Term-00,Measured tpmC (NewOrders)=48087.55
INFO [2022-01-1113:33:55.958]Thread-21Term-00,Measured tpmTOTAL =120461.29
INFO [2022-01-1113:33:55.958]Thread-21Term-00,SessionStart=2022-01-1113:23:55
INFO [2022-01-1113:33:55.958]Thread-21Term-00,SessionEnd=2022-01-1113:33:55
INFO [2022-01-1113:33:55.958]Thread-21Term-00,TransactionCount=1204638
TPM:
120215.48 + 121182.26 + 120523.98 + 120461.29 = 482383.01
------------------------------------------------------------------------------------------------------------
[其他优化手段]
1. PostgreSQL jdbc有一些参数可以优化，本文还未处理。例如防止类型转换，QUERY plan CACHE size。
2. PostgreSQL 代码层也有优化的空间，例如分区表的代码，快照的优化。
[特别声明]
1. 本文纯属技术交流，测试数据不具备任何指导意义。
如果有任何关于PostgreSQL的技术问题，欢迎来德歌的博客讨论。
注：BenchmarkSQL作为一款经典的开源数据库测试工具，内嵌了TPCC测试脚本，可以对EnterpriseDB、PostgreSQL、MySQL、Oracle以及SQL Server等数据库直接进行测试。
