Oracle与PostgreSQL最新版本性能对比分析

来自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 [2016-01-0922:03:39.961]Thread-7Term-00,

INFO [2016-01-0922:03:39.963]Thread-7Term-00,

INFO [2016-01-0922:03:39.963]Thread-7Term-00,Measured tpmC (NewOrders)=102494.46

INFO [2016-01-0922:03:39.963]Thread-7Term-00,Measured tpmTOTAL =256195.32

INFO [2016-01-0922:03:39.964]Thread-7Term-00,SessionStart=2016-01-0921:53:39

INFO [2016-01-0922:03:39.964]Thread-7Term-00,SessionEnd=2016-01-0922:03:39

INFO [2016-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/1638770402015112344924835/

------------------------------------------------------------------------------------------------------------

阶段2对比，

benchmarksql放到另一台主机，主机间万兆网同一交换机下互联。

参考

http://blog.163.com/digoal@126/blog/static/163877040201601021838221/

------------------------------------------------------------------------------------------------------------

为了突破测试程序的极限，开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 [2016-01-1017:54:04.925]Thread-22Term-00,Measured tpmC (NewOrders)=45416.28

INFO [2016-01-1017:54:04.925]Thread-22Term-00,Measured tpmTOTAL =113487.61

INFO [2016-01-1017:54:04.925]Thread-22Term-00,SessionStart=2016-01-1017:44:04

INFO [2016-01-1017:54:04.925]Thread-22Term-00,SessionEnd=2016-01-1017:54:04

INFO [2016-01-1017:54:04.925]Thread-22Term-00,TransactionCount=1134913

$ cat benchmarksql-4.1.0_pg02/run/log/benchmarksql.log

INFO [2016-01-1017:54:04.943]Thread-12Term-00,Measured tpmC (NewOrders)=45292.48

INFO [2016-01-1017:54:04.943]Thread-12Term-00,Measured tpmTOTAL =113269.54

INFO [2016-01-1017:54:04.943]Thread-12Term-00,SessionStart=2016-01-1017:44:04

INFO [2016-01-1017:54:04.944]Thread-12Term-00,SessionEnd=2016-01-1017:54:04

INFO [2016-01-1017:54:04.944]Thread-12Term-00,TransactionCount=1132770

$ cat benchmarksql-4.1.0_pg03/run/log/benchmarksql.log

INFO [2016-01-1017:54:04.955]Thread-12Term-00,Measured tpmC (NewOrders)=45336.15

INFO [2016-01-1017:54:04.955]Thread-12Term-00,Measured tpmTOTAL =113247.19

INFO [2016-01-1017:54:04.956]Thread-12Term-00,SessionStart=2016-01-1017:44:04

INFO [2016-01-1017:54:04.956]Thread-12Term-00,SessionEnd=2016-01-1017:54:04

INFO [2016-01-1017:54:04.956]Thread-12Term-00,TransactionCount=1132537

$ cat benchmarksql-4.1.0_pg04/run/log/benchmarksql.log

INFO [2016-01-1017:54:04.986]Thread-23Term-00,Measured tpmC (NewOrders)=45231.67

INFO [2016-01-1017:54:04.987]Thread-23Term-00,Measured tpmTOTAL =113054.3

INFO [2016-01-1017:54:04.987]Thread-23Term-00,SessionStart=2016-01-1017:44:04

INFO [2016-01-1017:54:04.987]Thread-23Term-00,SessionEnd=2016-01-1017:54:04

INFO [2016-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 [2016-01-1113:33:55.917]Thread-14Term-00,Measured tpmC (NewOrders)=48151.07

INFO [2016-01-1113:33:55.917]Thread-14Term-00,Measured tpmTOTAL =120215.48

INFO [2016-01-1113:33:55.917]Thread-14Term-00,SessionStart=2016-01-1113:23:55

INFO [2016-01-1113:33:55.917]Thread-14Term-00,SessionEnd=2016-01-1113:33:55

INFO [2016-01-1113:33:55.917]Thread-14Term-00,TransactionCount=1202222

$tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log

INFO [2016-01-1113:33:55.971]Thread-16Term-00,Measured tpmC (NewOrders)=48505.54

INFO [2016-01-1113:33:55.971]Thread-16Term-00,Measured tpmTOTAL =121182.26

INFO [2016-01-1113:33:55.971]Thread-16Term-00,SessionStart=2016-01-1113:23:55

INFO [2016-01-1113:33:55.972]Thread-16Term-00,SessionEnd=2016-01-1113:33:55

INFO [2016-01-1113:33:55.972]Thread-16Term-00,TransactionCount=1211858

$tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log

INFO [2016-01-1113:33:55.985]Thread-4Term-00,Measured tpmC (NewOrders)=48119.61

INFO [2016-01-1113:33:55.985]Thread-4Term-00,Measured tpmTOTAL =120523.98

INFO [2016-01-1113:33:55.985]Thread-4Term-00,SessionStart=2016-01-1113:23:55

INFO [2016-01-1113:33:55.985]Thread-4Term-00,SessionEnd=2016-01-1113:33:55

INFO [2016-01-1113:33:55.985]Thread-4Term-00,TransactionCount=1205271

$tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log

INFO [2016-01-1113:33:55.958]Thread-21Term-00,Measured tpmC (NewOrders)=48087.55

INFO [2016-01-1113:33:55.958]Thread-21Term-00,Measured tpmTOTAL =120461.29

INFO [2016-01-1113:33:55.958]Thread-21Term-00,SessionStart=2016-01-1113:23:55

INFO [2016-01-1113:33:55.958]Thread-21Term-00,SessionEnd=2016-01-1113:33:55

INFO [2016-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等数据库直接进行测试。