來自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等數據庫直接進行測試.