Oracle 12c TPC-C 测试请参考：
http://blog.163.com/digoal@126/blog/static/1638770402015112344924835/
文件系统为XFS，优化手段如下：
本文在同样的硬件测试环境下对比PostgreSQL 9.5.0。
我们看看1月7号发布的PostgreSQL 新版本性能咋样？
benchmarkSQL 配置请参考：
编译器版本
gcc version 4.4.6 20110731 (Red Hat 4.4.6-3) (GCC)
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
wget https://jdbc.postgresql.org/download/postgresql-9.4.1207.jre7.jar
mv postgresql-9.4.1207.jre7.jar benchmarksql-4.1.0/lib/
配置benchmarksql，使用新的postgresql java驱动
$ vi runBenchmark.sh
java -cp .:../lib/postgresql-9.4.1207.jre7.jar:../lib/log4j-1.2.17.jar:../lib/apache-log4j-extras-1.1.jar:../dist/BenchmarkSQL-4.1.jar -Dprop=$1 jTPCC
$ vi runLoader.sh
java -cp .:../lib/postgresql-9.4.1207.jre7.jar:../dist/BenchmarkSQL-4.1.jar -Dprop=$1 LoadData $2 $3 $4 $5
$ vi runSQL.sh
myCP="../lib/postgresql-9.4.1207.jre7.jar"
myCP="$myCP:../dist/BenchmarkSQL-4.1.jar"
myOPTS="-Dprop=$1"
myOPTS="$myOPTS -DcommandFile=$2"
java -cp .:$myCP $myOPTS ExecJDBC
修改log4j，减少日志打印量。priority改成info，只输出最终结果，不输出产生订单的日志。
$ vi log4j.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/'>
<appender name="console" class="org.apache.log4j.ConsoleAppender">
<param name="Threshold" value="info"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %5p - %m%n"/>
</layout>
</appender>
<appender name="R" class="org.apache.log4j.rolling.RollingFileAppender">
<param name="Append" value="True" />
<rollingPolicy class="org.apache.log4j.rolling.TimeBasedRollingPolicy">
<param name="FileNamePattern" value="log/archive/benchmarksql.%d{yyyyMMddHHmm}.log"/>
<param name="ActiveFileName" value="log/benchmarksql.log"/>
</rollingPolicy>
<triggeringPolicy class="org.apache.log4j.rolling.SizeBasedTriggeringPolicy">
<param name="MaxFileSize" value="1"/>
</triggeringPolicy>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%5p\t[%d{yyyy-MM-dd HH:mm:ss.SSS}]\t%t \t%m%n"/>
</layout>
<filter class="org.apache.log4j.filter.StringMatchFilter">
<param name="StringToMatch" value ="\n" />
<param name="AcceptOnMatch" value="false" />
</filter>
</appender>
<appender name="E" class="org.apache.log4j.rolling.RollingFileAppender">
<param name="Append" value="True" />
<param name="Threshold" value="warn"/>
<rollingPolicy class="org.apache.log4j.rolling.TimeBasedRollingPolicy">
<param name="FileNamePattern" value="log/BenchmarkSQLError.%d.log"/>
<param name="ActiveFileName" value="log/BenchmarkSQLError.log"/>
</rollingPolicy>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%5p\t[%d{yyyy-MM-dd HH:mm:ss.SSS}]\t%t \t%m%n"/>
</layout>
</appender>
<root>
<priority value="info"/>
<appender-ref ref="R"/>
<appender-ref ref="E"/>
</root>
</log4j:configuration>
编辑连接配置和压测配置，与测试Oracle 12c 的保持一致。
1000 个仓库，约5亿数据量。
$ vi props.pg
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:1921/postgres
user=postgres
password=123
warehouses=1000
terminals=96
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=1
//Number of total transactions per minute
limitTxnsPerMin=0
//The following five values must add up to 100
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
newOrderWeight=40
paymentWeight=36
orderStatusWeight=8
deliveryWeight=8
stockLevelWeight=8
配置postgres用户默认搜索路径
$ psql
psql (9.5.0)
Type "help" for help.
postgres=# alter role postgres set search_path='benchmarksql','public';
创建用于存放生成CSV的目录
$ mkdir /u02/digoal/soft_bak/benchcsv
修改sqlTableCopies，指定目录
$ vi sqlTableCopies
copy benchmarksql.warehouse
(w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip)
from '/u02/digoal/soft_bak/benchcsv/warehouse.csv' WITH CSV;
copy benchmarksql.item
(i_id, i_name, i_price, i_data, i_im_id)
from '/u02/digoal/soft_bak/benchcsv/item.csv' WITH CSV;
copy benchmarksql.stock
(s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data,
s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05,
s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10)
from '/u02/digoal/soft_bak/benchcsv/stock.csv' WITH CSV;
copy benchmarksql.district
(d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1,
d_street_2, d_city, d_state, d_zip)
from '/u02/digoal/soft_bak/benchcsv/district.csv' WITH CSV;
copy benchmarksql.customer
(c_id, c_d_id, c_w_id, c_discount, c_credit, c_last, c_first, c_credit_lim,
c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, c_street_1,
c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_middle, c_data)
from '/u02/digoal/soft_bak/benchcsv/customer.csv' WITH CSV;
copy benchmarksql.history
(hist_id, h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data)
from '/u02/digoal/soft_bak/benchcsv/cust-hist.csv' WITH CSV;
copy benchmarksql.oorder
(o_id, o_w_id, o_d_id, o_c_id, o_carrier_id, o_ol_cnt, o_all_local, o_entry_d)
from '/u02/digoal/soft_bak/benchcsv/order.csv' WITH CSV;
copy benchmarksql.order_line
(ol_w_id, ol_d_id, ol_o_id, ol_number, ol_i_id, ol_delivery_d,
ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info)
from '/u02/digoal/soft_bak/benchcsv/order-line.csv' WITH CSV;
copy benchmarksql.new_order
(no_w_id, no_d_id, no_o_id)
from '/u02/digoal/soft_bak/benchcsv/new-order.csv' WITH CSV;
建立表结构
$ cd benchmarksql-4.1.0/run
$ ./runSQL.sh props.pg sqlTableCreates
生成CSV
$ ./runLoader.sh props.pg numWarehouses 1000 fileLocation /u02/digoal/soft_bak/benchcsv/
1000个仓库的数据量：
total 69G
-rw-r--r-- 1 digoal users 2.0G Jan 9 15:53 cust-hist.csv
-rw-r--r-- 1 digoal users 16G Jan 9 15:53 customer.csv
-rw-r--r-- 1 digoal users 898K Jan 9 15:12 district.csv
-rw-r--r-- 1 digoal users 7.0M Jan 9 14:22 item.csv
-rw-r--r-- 1 digoal users 95M Jan 9 16:14 new-order.csv
-rw-r--r-- 1 digoal users 1.3G Jan 9 16:14 order.csv
-rw-r--r-- 1 digoal users 22G Jan 9 16:14 order-line.csv
-rw-r--r-- 1 digoal users 28G Jan 9 15:12 stock.csv
-rw-r--r-- 1 digoal users 84K Jan 9 14: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/null 2>./errrun.log &
测试结果：
INFO [2016-01-09 22:03:39.961] Thread-7 Term-00,
INFO [2016-01-09 22:03:39.963] Thread-7 Term-00,
INFO [2016-01-09 22:03:39.963] Thread-7 Term-00, Measured tpmC (NewOrders) = 102494.46
INFO [2016-01-09 22:03:39.963] Thread-7 Term-00, Measured tpmTOTAL = 256195.32
INFO [2016-01-09 22:03:39.964] Thread-7 Term-00, Session Start = 2016-01-09 21:53:39
INFO [2016-01-09 22:03:39.964] Thread-7 Term-00, Session End = 2016-01-09 22:03:39
INFO [2016-01-09 22:03:39.964] Thread-7 Term-00, Transaction Count = 2563088
主机信息，截取压测第9分钟的数据。
TOP
top - 22:02:09 up 3 days, 12:55, 3 users, load average: 19.23, 15.97, 8.37
Tasks: 619 total, 10 running, 609 sleeping, 0 stopped, 0 zombie
Cpu(s): 35.0%us, 9.4%sy, 0.0%ni, 52.6%id, 0.1%wa, 0.0%hi, 2.9%si, 0.0%st
Mem: 264643396k total, 241719372k used, 22924024k free, 36672k buffers
Swap: 18825200k total, 0k used, 18825200k free, 196557376k cached
iostat -x
avg-cpu: %user %nice %system %iowait %steal %idle
35.07 0.00 12.30 0.12 0.00 52.51
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
dfa 0.00 0.00 57.40 743.40 918.40 11849.00 15.94 0.02 0.03 0.03 2.08
dfb 0.00 0.00 57.20 740.40 915.20 11829.00 15.98 0.02 0.03 0.03 2.04
dfc 0.00 0.00 58.40 730.80 934.40 11675.80 15.98 0.03 0.03 0.03 2.52
dm-0 0.00 0.00 173.00 2213.20 2768.00 35331.40 15.97 0.08 0.03 0.03 7.02
Oracle有AWR报告，PostgreSQL可以使用oprofile跟踪统计：
参考
找到需要优化的代码就靠它了。
CPU: Intel Ivy Bridge microarchitecture, speed 2600 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
vma samples % app name symbol name
007a7780 751274 5.1565 /soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_search_with_hash_value
004a92f0 574315 3.9419 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_compare
006a4bd0 514473 3.5312 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockAcquire
0078a090 510962 3.5071 /soft/digoal/soft_bak/pgsql9.5/bin/postgres SearchCatCache
007bc3a0 484601 3.3262 /soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetAlloc
006969c0 442341 3.0361 /soft/digoal/soft_bak/pgsql9.5/bin/postgres GetSnapshotData
00498930 352134 2.4170 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_hot_search_buffer
005b8f70 279718 1.9199 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitExpr
006895d0 249377 1.7117 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PinBuffer
006a4220 168770 1.1584 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockRelease
007ac620 161861 1.1110 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_encoding_mbcliplen
007a2180 161090 1.1057 /soft/digoal/soft_bak/pgsql9.5/bin/postgres FunctionCall2Coll
004aaa80 153079 1.0507 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_checkkeys
007a3950 147078 1.0095 /soft/digoal/soft_bak/pgsql9.5/bin/postgres fmgr_info_cxt_security
0049bce0 136680 0.9381 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune_opt
0048c8f0 130807 0.8978 /soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_any
006b2e50 130564 0.8962 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PostgresMain
0046c790 121776 0.8358 /soft/digoal/soft_bak/pgsql9.5/bin/postgres slot_deform_tuple
007bd0f0 114514 0.7860 /soft/digoal/soft_bak/pgsql9.5/bin/postgres palloc
007bd210 103245 0.7086 /soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextAllocZeroAligned
006b14a0 99669 0.6841 /soft/digoal/soft_bak/pgsql9.5/bin/postgres exec_bind_message
0049b300 94389 0.6479 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune
007bce00 87034 0.5974 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pfree
00494ba0 83545 0.5734 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_update
0046c580 79535 0.5459 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_getsysattr
005ef6e0 79099 0.5429 /soft/digoal/soft_bak/pgsql9.5/bin/postgres expression_tree_walker
007bca10 71379 0.4899 /soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetFree
0068b1b0 66956 0.4596 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ReadBuffer_common
007cb070 66438 0.4560 /soft/digoal/soft_bak/pgsql9.5/bin/postgres HeapTupleSatisfiesMVCC
00471e30 66340 0.4553 /soft/digoal/soft_bak/pgsql9.5/bin/postgres TupleDescInitEntry
004a9630 61952 0.4252 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_moveright
006a0770 61336 0.4210 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LockAcquireExtended
005b8530 60524 0.4154 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecProject
004a9950 58783 0.4035 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_first
00788880 57798 0.3967 /soft/digoal/soft_bak/pgsql9.5/bin/postgres CatalogCacheComputeHashValue
004a9510 56865 0.3903 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_binsrch
004a52b0 56152 0.3854 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_checkpage
005f2270 54679 0.3753 /soft/digoal/soft_bak/pgsql9.5/bin/postgres lappend
005c9ba0 53697 0.3686 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecIndexBuildScanKeys
00689cb0 53634 0.3681 /soft/digoal/soft_bak/pgsql9.5/bin/postgres UnpinBuffer.clone.0
007bc980 53151 0.3648 /soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetReset
005beaa0 52618 0.3612 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecScan
007a7dc0 51582 0.3540 /soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_search
007a2900 51152 0.3511 /soft/digoal/soft_bak/pgsql9.5/bin/postgres DirectFunctionCall1Coll
004ab240 50878 0.3492 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_preprocess_keys
005dfd60 50583 0.3472 /soft/digoal/soft_bak/pgsql9.5/bin/postgres appendBinaryStringInfo
005e9cc0 50034 0.3434 /soft/digoal/soft_bak/pgsql9.5/bin/postgres internal_putbytes
005bc6e0 49198 0.3377 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecMakeFunctionResultNoSets
004a1dc0 49038 0.3366 /soft/digoal/soft_bak/pgsql9.5/bin/postgres index_getnext_tid
004a8a20 48143 0.3304 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_readpage
0064a240 47657 0.3271 /soft/digoal/soft_bak/pgsql9.5/bin/postgres WaitLatchOrSocket
0079ea80 47439 0.3256 /soft/digoal/soft_bak/pgsql9.5/bin/postgres errstart
005e9d60 47317 0.3248 /soft/digoal/soft_bak/pgsql9.5/bin/postgres socket_putmessage
0048cbb0 47011 0.3227 /soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_uint32
005f4620 46989 0.3225 /soft/digoal/soft_bak/pgsql9.5/bin/postgres copyObject
006b0200 46725 0.3207 /soft/digoal/soft_bak/pgsql9.5/bin/postgres check_stack_depth
004d98b0 46404 0.3185 /soft/digoal/soft_bak/pgsql9.5/bin/postgres XLogInsertRecord
005f14a0 46255 0.3175 /soft/digoal/soft_bak/pgsql9.5/bin/postgres exprTypmod
0078ceb0 46137 0.3167 /soft/digoal/soft_bak/pgsql9.5/bin/postgres AcquireExecutorLocks
005c0120 46060 0.3161 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecBuildProjectionInfo
005b7a00 45845 0.3147 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecProcNode
005cba00 45621 0.3131 /soft/digoal/soft_bak/pgsql9.5/bin/postgres IndexOnlyNext
004dcd00 44996 0.3088 /soft/digoal/soft_bak/pgsql9.5/bin/postgres XLogInsert
007bd900 43828 0.3008 /soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextCreate
005bf590 43739 0.3002 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecClearTuple
00651290 43411 0.2980 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pgstat_report_activity
00688ad0 42177 0.2895 /soft/digoal/soft_bak/pgsql9.5/bin/postgres BufferGetBlockNumber
005cec40 42120 0.2891 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitModifyTable
007add70 41781 0.2868 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_utf_mblen
004a1cb0 41096 0.2821 /soft/digoal/soft_bak/pgsql9.5/bin/postgres index_fetch_heap
007bf720 41079 0.2820 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ResourceOwnerReleaseInternal
005b6510 39744 0.2728 /soft/digoal/soft_bak/pgsql9.5/bin/postgres standard_ExecutorStart
00689050 39400 0.2704 /soft/digoal/soft_bak/pgsql9.5/bin/postgres GetPrivateRefCountEntry
0046cc10 39374 0.2703 /soft/digoal/soft_bak/pgsql9.5/bin/postgres slot_getattr
004a8470 39371 0.2702 /soft/digoal/soft_bak/pgsql9.5/bin/postgres btgettuple
0046da10 39200 0.2691 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_fill_tuple
005cb000 38886 0.2669 /soft/digoal/soft_bak/pgsql9.5/bin/postgres IndexNext
0068bdf0 38798 0.2663 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ReadBufferExtended
0046d1a0 37687 0.2587 /soft/digoal/soft_bak/pgsql9.5/bin/postgres nocachegetattr
00795a70 37478 0.2572 /soft/digoal/soft_bak/pgsql9.5/bin/postgres SearchSysCache
005bf220 37089 0.2546 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecTypeFromTLInternal
007bce90 36895 0.2532 /soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextReset
004ee700 36585 0.2511 /soft/digoal/soft_bak/pgsql9.5/bin/postgres OverrideSearchPathMatchesCurrent
007bd580 36499 0.2505 /soft/digoal/soft_bak/pgsql9.5/bin/postgres palloc0
005b7cc0 35752 0.2454 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitNode
005f1170 33509 0.2300 /soft/digoal/soft_bak/pgsql9.5/bin/postgres exprType
004a9250 33443 0.2295 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_next
007ca070 33169 0.2277 /soft/digoal/soft_bak/pgsql9.5/bin/postgres HeapTupleSatisfiesVacuum
006b5e40 32701 0.2245 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PortalStart
007d0c90 32526 0.2232 /soft/digoal/soft_bak/pgsql9.5/bin/postgres strlcpy
0078bf60 32247 0.2213 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ScanQueryForLocks
0078d190 31821 0.2184 /soft/digoal/soft_bak/pgsql9.5/bin/postgres GetCachedPlan
004a2510 31799 0.2183 /soft/digoal/soft_bak/pgsql9.5/bin/postgres btint4cmp
00689100 31038 0.2130 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LockBuffer
005cf920 30845 0.2117 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecModifyTable
007d3720 29991 0.2059 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_comp_crc32c_sse42
006b5a90 29990 0.2058 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PortalRun
00702c10 29945 0.2055 /soft/digoal/soft_bak/pgsql9.5/bin/postgres int4eq
0068bf30 29506 0.2025 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ReleaseAndReadBuffer
0072ca70 29180 0.2003 /soft/digoal/soft_bak/pgsql9.5/bin/postgres oideq
0046f830 28791 0.1976 /soft/digoal/soft_bak/pgsql9.5/bin/postgres printtup
00652500 28686 0.1969 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pgstat_initstats
005b6360 27850 0.1912 /soft/digoal/soft_bak/pgsql9.5/bin/postgres standard_ExecutorRun
004a97d0 27303 0.1874 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_search
005eb820 26910 0.1847 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pq_copymsgbytes
0078c9b0 26717 0.1834 /soft/digoal/soft_bak/pgsql9.5/bin/postgres RevalidateCachedQuery
0046d7f0 26409 0.1813 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_compute_data_size
007bef90 25986 0.1784 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ResourceOwnerForgetCatCacheRef
006a4580 25980 0.1783 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockReleaseClearVar
007bf0b0 25691 0.1763 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ResourceOwnerForgetBuffer
007bf510 25517 0.1751 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ResourceOwnerEnlargeCatCacheRefs
007bd2f0 25285 0.1735 /soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextAlloc
004e3d60 25205 0.1730 /soft/digoal/soft_bak/pgsql9.5/bin/postgres IsSharedRelation
004992e0 25201 0.1730 /soft/digoal/soft_bak/pgsql9.5/bin/postgres relation_open
004a8ce0 24931 0.1711 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_steppage
007ccf70 24845 0.1705 /soft/digoal/soft_bak/pgsql9.5/bin/postgres GetTransactionSnapshot
007bd7f0 24785 0.1701 /soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextAllocZero
007ba4a0 24592 0.1688 /soft/digoal/soft_bak/pgsql9.5/bin/postgres set_ps_display
007bc920 24354 0.1672 /soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetDelete
005eff70 24317 0.1669 /soft/digoal/soft_bak/pgsql9.5/bin/postgres exprCollation
005ca790 24152 0.1658 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitIndexScan
005dfbc0 24115 0.1655 /soft/digoal/soft_bak/pgsql9.5/bin/postgres enlargeStringInfo
005ea540 23995 0.1647 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pq_getmessage
0069e680 23983 0.1646 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LockRelease
005eb920 23770 0.1632 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pq_getmsgint
007ace30 23767 0.1631 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_client_to_server
004a1980 23709 0.1627 /soft/digoal/soft_bak/pgsql9.5/bin/postgres index_getprocinfo
0078a010 23542 0.1616 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ReleaseCatCache
007ccc80 23300 0.1599 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PopActiveSnapshot
004cdcb0 22416 0.1539 /soft/digoal/soft_bak/pgsql9.5/bin/postgres RecoveryInProgress
007d25c0 22374 0.1536 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_qsort
005b5eb0 22083 0.1516 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecCheckRTPerms
007a6f50 21858 0.1500 /soft/digoal/soft_bak/pgsql9.5/bin/postgres get_hash_value
006aad10 21263 0.1459 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PageAddItem
005b2610 21253 0.1459 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecReScan
004a0b80 19739 0.1355 /soft/digoal/soft_bak/pgsql9.5/bin/postgres visibilitymap_test
0069f270 19567 0.1343 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LockReassignOwner
005f1a30 19556 0.1342 /soft/digoal/soft_bak/pgsql9.5/bin/postgres new_list
007663a0 19503 0.1339 /soft/digoal/soft_bak/pgsql9.5/bin/postgres GetCurrentTimestamp
005bf8d0 19422 0.1333 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecStoreTuple
005b76c0 19322 0.1326 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecEndNode
004a71b0 19143 0.1314 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_getroot
006af8e0 19088 0.1310 /soft/digoal/soft_bak/pgsql9.5/bin/postgres forbidden_in_wal_sender
005ebb10 18924 0.1299 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pq_begintypsend
007cc9f0 18916 0.1298 /soft/digoal/soft_bak/pgsql9.5/bin/postgres SnapshotResetXmin
005b8df0 18698 0.1283 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecEvalParamExtern
007bcca0 18595 0.1276 /soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextSetParent
005dff50 18305 0.1256 /soft/digoal/soft_bak/pgsql9.5/bin/postgres initStringInfo
0069d6c0 18277 0.1254 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LockRelationOid
007a8640 18167 0.1247 /soft/digoal/soft_bak/pgsql9.5/bin/postgres string_hash
0071e5a0 18155 0.1246 /soft/digoal/soft_bak/pgsql9.5/bin/postgres namestrcpy
007bc6a0 17961 0.1233 /soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetContextCreate
0063bfe0 17393 0.1194 /soft/digoal/soft_bak/pgsql9.5/bin/postgres get_leftop
005ea810 17343 0.1190 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pq_getbyte
005cea20 17261 0.1185 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecCheckPlanOutput
004d44d0 17143 0.1177 /soft/digoal/soft_bak/pgsql9.5/bin/postgres GetXLogBuffer
005b8460 17054 0.1171 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecQual
004a2030 16718 0.1147 /soft/digoal/soft_bak/pgsql9.5/bin/postgres index_endscan
004ab9b0 16717 0.1147 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_preprocess_array_keys
00472530 16616 0.1140 /soft/digoal/soft_bak/pgsql9.5/bin/postgres CreateTemplateTupleDesc
006b54f0 16267 0.1117 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PortalRunMulti
004c3bf0 16212 0.1113 /soft/digoal/soft_bak/pgsql9.5/bin/postgres TransactionIdPrecedes
006aa500 16187 0.1111 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PageRepairFragmentation
00797910 16150 0.1108 /soft/digoal/soft_bak/pgsql9.5/bin/postgres getTypeBinaryInputInfo
007bea30 16076 0.1103 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ResourceOwnerRememberCatCacheRef
007af080 16065 0.1103 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_verify_mbstr_len
0078c930 16016 0.1099 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ReleaseCachedPlan
005da020 15979 0.1097 /soft/digoal/soft_bak/pgsql9.5/bin/postgres SPI_push_conditional
007caf50 15901 0.1091 /soft/digoal/soft_bak/pgsql9.5/bin/postgres XidInMVCCSnapshot
005bf9f0 15865 0.1089 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecSetSlotDescriptor
00793590 15814 0.1085 /soft/digoal/soft_bak/pgsql9.5/bin/postgres RelationIncrementReferenceCount
005be970 15643 0.1074 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecAssignScanProjectionInfoWithVarno
0057f9f0 15487 0.1063 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PortalCleanup
0049a880 15356 0.1054 /soft/digoal/soft_bak/pgsql9.5/bin/postgres RelationGetBufferForTuple
007baf90 15353 0.1054 /soft/digoal/soft_bak/pgsql9.5/bin/postgres superuser_arg
005e15c0 15223 0.1045 /soft/digoal/soft_bak/pgsql9.5/bin/postgres secure_read
0069f3e0 15065 0.1034 /soft/digoal/soft_bak/pgsql9.5/bin/postgres GrantLockLocal
005ea4c0 15027 0.1031 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pq_getbytes
005efe10 14951 0.1026 /soft/digoal/soft_bak/pgsql9.5/bin/postgres expression_returns_set_walker
006b2c60 14765 0.1013 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ProcessClientReadInterrupt
006a0280 14639 0.1005 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LockReleaseAll
004cb500 14633 0.1004 /soft/digoal/soft_bak/pgsql9.5/bin/postgres CommitTransactionCommand
0049af30 14626 0.1004 /soft/digoal/soft_bak/pgsql9.5/bin/postgres RelationPutHeapTuple
007bee50 14621 0.1004 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ResourceOwnerForgetRelationRef
006cc090 14602 0.1002 /soft/digoal/soft_bak/pgsql9.5/bin/postgres deconstruct_array
阶段1（测试程序和数据库在同一主机）PostgreSQL 9.5.0 对比 Oracle 12c TPC-C tpm对比如下：
测试数据仅供参考。
使用benchmarksql测试，系统还有大量空闲CPU，IO资源，所以性能应该不止于此。预计PostgreSQL可到50W tpm。
有兴趣的童鞋可以使用load runner或者sysbench或其他工具再测试一下。
------------------------------------------------------------------------------------------------------------
特别感谢给Oracle优化支招的Oracle圈子的兄弟姐妹们。
优化中，期待Oracle更好的表现。
AWR报告截图见：
------------------------------------------------------------------------------------------------------------
阶段2对比，
benchmarksql放到另一台主机，主机间万兆网同一交换机下互联。
参考
------------------------------------------------------------------------------------------------------------
为了突破测试程序的极限，开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.0K Jan 10 14:41 benchmarksql-4.1.0_pg01
drwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg02
drwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg03
drwxr-xr-x 7 digoal users 4.0K Jan 10 14:41 benchmarksql-4.1.0_pg04
测试
cd benchmarksql-4.1.0_pg01/run
nohup ./runBenchmark.sh props.pg >/dev/null 2>./errrun.log &
cd ../../benchmarksql-4.1.0_pg02/run
nohup ./runBenchmark.sh props.pg >/dev/null 2>./errrun.log &
cd ../../benchmarksql-4.1.0_pg03/run
nohup ./runBenchmark.sh props.pg >/dev/null 2>./errrun.log &
cd ../../benchmarksql-4.1.0_pg04/run
nohup ./runBenchmark.sh props.pg >/dev/null 2>./errrun.log &
cd ../..
测试结果
$ cat benchmarksql-4.1.0_pg01/run/log/benchmarksql.log
INFO [2016-01-10 17:54:04.925] Thread-22 Term-00, Measured tpmC (NewOrders) = 45416.28
INFO [2016-01-10 17:54:04.925] Thread-22 Term-00, Measured tpmTOTAL = 113487.61
INFO [2016-01-10 17:54:04.925] Thread-22 Term-00, Session Start = 2016-01-10 17:44:04
INFO [2016-01-10 17:54:04.925] Thread-22 Term-00, Session End = 2016-01-10 17:54:04
INFO [2016-01-10 17:54:04.925] Thread-22 Term-00, Transaction Count = 1134913
$ cat benchmarksql-4.1.0_pg02/run/log/benchmarksql.log
INFO [2016-01-10 17:54:04.943] Thread-12 Term-00, Measured tpmC (NewOrders) = 45292.48
INFO [2016-01-10 17:54:04.943] Thread-12 Term-00, Measured tpmTOTAL = 113269.54
INFO [2016-01-10 17:54:04.943] Thread-12 Term-00, Session Start = 2016-01-10 17:44:04
INFO [2016-01-10 17:54:04.944] Thread-12 Term-00, Session End = 2016-01-10 17:54:04
INFO [2016-01-10 17:54:04.944] Thread-12 Term-00, Transaction Count = 1132770
$ cat benchmarksql-4.1.0_pg03/run/log/benchmarksql.log
INFO [2016-01-10 17:54:04.955] Thread-12 Term-00, Measured tpmC (NewOrders) = 45336.15
INFO [2016-01-10 17:54:04.955] Thread-12 Term-00, Measured tpmTOTAL = 113247.19
INFO [2016-01-10 17:54:04.956] Thread-12 Term-00, Session Start = 2016-01-10 17:44:04
INFO [2016-01-10 17:54:04.956] Thread-12 Term-00, Session End = 2016-01-10 17:54:04
INFO [2016-01-10 17:54:04.956] Thread-12 Term-00, Transaction Count = 1132537
$ cat benchmarksql-4.1.0_pg04/run/log/benchmarksql.log
INFO [2016-01-10 17:54:04.986] Thread-23 Term-00, Measured tpmC (NewOrders) = 45231.67
INFO [2016-01-10 17:54:04.987] Thread-23 Term-00, Measured tpmTOTAL = 113054.3
INFO [2016-01-10 17:54:04.987] Thread-23 Term-00, Session Start = 2016-01-10 17:44:04
INFO [2016-01-10 17:54:04.987] Thread-23 Term-00, Session End = 2016-01-10 17:54:04
INFO [2016-01-10 17:54:04.987] Thread-23 Term-00, Transaction Count = 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.39 0.00 22.47 1.26 0.00 4.88
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
dfa 0.00 0.00 3659.33 7008.67 58538.67 112050.67 15.99 5.85 0.55 0.06 68.17
dfb 0.00 0.00 3714.67 6888.67 59418.67 110173.33 15.99 5.98 0.56 0.06 67.87
dfc 0.00 0.00 3709.00 6974.33 59328.00 111504.00 15.99 5.63 0.52 0.07 71.60
dm-0 0.00 0.00 11083.00 20870.33 177285.33 333706.67 15.99 17.60 0.55 0.03 92.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: Intel Ivy Bridge microarchitecture, speed 2600 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
vma samples % app name symbol name
007a7780 2632700 5.2511 /soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_search_with_hash_value
004a92f0 1895924 3.7816 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_compare
006969c0 1844371 3.6787 /soft/digoal/soft_bak/pgsql9.5/bin/postgres GetSnapshotData
0078a090 1775031 3.5404 /soft/digoal/soft_bak/pgsql9.5/bin/postgres SearchCatCache
006a4bd0 1725350 3.4413 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockAcquire
007bc3a0 1565190 3.1219 /soft/digoal/soft_bak/pgsql9.5/bin/postgres AllocSetAlloc
00498930 1406694 2.8058 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_hot_search_buffer
005b8f70 965646 1.9261 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecInitExpr
006895d0 767078 1.5300 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PinBuffer
004aaa80 617741 1.2321 /soft/digoal/soft_bak/pgsql9.5/bin/postgres _bt_checkkeys
007a2180 588043 1.1729 /soft/digoal/soft_bak/pgsql9.5/bin/postgres FunctionCall2Coll
006a4220 575864 1.1486 /soft/digoal/soft_bak/pgsql9.5/bin/postgres LWLockRelease
007ac620 485162 0.9677 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pg_encoding_mbcliplen
007a3950 471102 0.9396 /soft/digoal/soft_bak/pgsql9.5/bin/postgres fmgr_info_cxt_security
0046c790 441548 0.8807 /soft/digoal/soft_bak/pgsql9.5/bin/postgres slot_deform_tuple
0048c8f0 425867 0.8494 /soft/digoal/soft_bak/pgsql9.5/bin/postgres hash_any
006b2e50 404548 0.8069 /soft/digoal/soft_bak/pgsql9.5/bin/postgres PostgresMain
007bd0f0 396510 0.7909 /soft/digoal/soft_bak/pgsql9.5/bin/postgres palloc
0049bce0 394201 0.7863 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune_opt
007bce00 353243 0.7046 /soft/digoal/soft_bak/pgsql9.5/bin/postgres pfree
0049b300 335896 0.6700 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_page_prune
0046c580 313145 0.6246 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_getsysattr
006b14a0 311776 0.6219 /soft/digoal/soft_bak/pgsql9.5/bin/postgres exec_bind_message
007cb070 292106 0.5826 /soft/digoal/soft_bak/pgsql9.5/bin/postgres HeapTupleSatisfiesMVCC
007bd210 275282 0.5491 /soft/digoal/soft_bak/pgsql9.5/bin/postgres MemoryContextAllocZeroAligned
005b8530 273199 0.5449 /soft/digoal/soft_bak/pgsql9.5/bin/postgres ExecProject
00494ba0 266495 0.5315 /soft/digoal/soft_bak/pgsql9.5/bin/postgres heap_update
007bca10 265556 0.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-11 13:33:55.917] Thread-14 Term-00, Measured tpmC (NewOrders) = 48151.07
INFO [2016-01-11 13:33:55.917] Thread-14 Term-00, Measured tpmTOTAL = 120215.48
INFO [2016-01-11 13:33:55.917] Thread-14 Term-00, Session Start = 2016-01-11 13:23:55
INFO [2016-01-11 13:33:55.917] Thread-14 Term-00, Session End = 2016-01-11 13:33:55
INFO [2016-01-11 13:33:55.917] Thread-14 Term-00, Transaction Count = 1202222
$tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log
INFO [2016-01-11 13:33:55.971] Thread-16 Term-00, Measured tpmC (NewOrders) = 48505.54
INFO [2016-01-11 13:33:55.971] Thread-16 Term-00, Measured tpmTOTAL = 121182.26
INFO [2016-01-11 13:33:55.971] Thread-16 Term-00, Session Start = 2016-01-11 13:23:55
INFO [2016-01-11 13:33:55.972] Thread-16 Term-00, Session End = 2016-01-11 13:33:55
INFO [2016-01-11 13:33:55.972] Thread-16 Term-00, Transaction Count = 1211858
$tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log
INFO [2016-01-11 13:33:55.985] Thread-4 Term-00, Measured tpmC (NewOrders) = 48119.61
INFO [2016-01-11 13:33:55.985] Thread-4 Term-00, Measured tpmTOTAL = 120523.98
INFO [2016-01-11 13:33:55.985] Thread-4 Term-00, Session Start = 2016-01-11 13:23:55
INFO [2016-01-11 13:33:55.985] Thread-4 Term-00, Session End = 2016-01-11 13:33:55
INFO [2016-01-11 13:33:55.985] Thread-4 Term-00, Transaction Count = 1205271
$tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log
INFO [2016-01-11 13:33:55.958] Thread-21 Term-00, Measured tpmC (NewOrders) = 48087.55
INFO [2016-01-11 13:33:55.958] Thread-21 Term-00, Measured tpmTOTAL = 120461.29
INFO [2016-01-11 13:33:55.958] Thread-21 Term-00, Session Start = 2016-01-11 13:23:55
INFO [2016-01-11 13:33:55.958] Thread-21 Term-00, Session End = 2016-01-11 13:33:55
INFO [2016-01-11 13:33:55.958] Thread-21 Term-00, Transaction Count = 1204638
TPM:
120215.48 + 121182.26 + 120523.98 + 120461.29 =
482383.01
------------------------------------------------------------------------------------------------------------
阶段4，
优化分组提交的时延，最小结束点并发事务数量，work_mem等。
操作系统优化，
优化老化脏页刷新间隔
vm.dirty_writeback_centisecs=10
优化老化脏页阈值
vm.dirty_expire_centisecs=6000
优化用户进程刷脏页阈值
vm.dirty_ratio=80
优化内核进程刷脏页阈值
vm.dirty_background_bytes=102400000
优化终端数，每个benchmarksql 20个终端，一共80个终端。
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
work_mem = 256MB # min 64kB
maintenance_work_mem = 2GB # min 1MB
autovacuum_work_mem = 2GB # min 1MB, or -1 to use maintenance_work_mem
dynamic_shared_memory_type = mmap # the default is the first option
vacuum_cost_delay = 10 # 0-100 milliseconds
vacuum_cost_limit = 10000 # 1-10000 credits
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 = 128MB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
commit_delay = 20 # range 0-100000, in microseconds
commit_siblings = 6 # range 1-1000
checkpoint_timeout = 55min # range 30s-1h
max_wal_size = 320GB
checkpoint_completion_target = 0.99 # checkpoint target duration, 0.0 - 1.0
random_page_cost = 1.0 # same scale as above
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'
update_process_title = off
track_activities = off
autovacuum = on # Enable autovacuum subprocess? 'on'
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'
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
work_mem = 256MB # min 64kB
maintenance_work_mem = 2GB # min 1MB
autovacuum_work_mem = 2GB # min 1MB, or -1 to use maintenance_work_mem
dynamic_shared_memory_type = mmap # the default is the first option
vacuum_cost_delay = 10 # 0-100 milliseconds
vacuum_cost_limit = 10000 # 1-10000 credits
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 = 128MB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
commit_delay = 20 # range 0-100000, in microseconds
commit_siblings = 6 # range 1-1000
checkpoint_timeout = 55min # range 30s-1h
max_wal_size = 320GB
checkpoint_completion_target = 0.99 # checkpoint target duration, 0.0 - 1.0
random_page_cost = 1.0 # same scale as above
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'
update_process_title = off
track_activities = off
autovacuum = on # Enable autovacuum subprocess? 'on'
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'
pg_ctl restart
测试结果
dege.zzz@rds151163048-> tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log
INFO [2016-01-12 11:55:09.461] Thread-12 Term-00, Measured tpmC (NewOrders) = 57995.55
INFO [2016-01-12 11:55:09.461] Thread-12 Term-00, Measured tpmTOTAL = 144975.59
INFO [2016-01-12 11:55:09.461] Thread-12 Term-00, Session Start = 2016-01-12 11:45:09
INFO [2016-01-12 11:55:09.461] Thread-12 Term-00, Session End = 2016-01-12 11:55:09
INFO [2016-01-12 11:55:09.462] Thread-12 Term-00, Transaction Count = 1449796
dege.zzz@rds151163048-> tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log
INFO [2016-01-12 11:55:09.499] Thread-0 Term-00, Measured tpmC (NewOrders) = 58013.75
INFO [2016-01-12 11:55:09.499] Thread-0 Term-00, Measured tpmTOTAL = 145006.74
INFO [2016-01-12 11:55:09.499] Thread-0 Term-00, Session Start = 2016-01-12 11:45:09
INFO [2016-01-12 11:55:09.500] Thread-0 Term-00, Session End = 2016-01-12 11:55:09
INFO [2016-01-12 11:55:09.500] Thread-0 Term-00, Transaction Count = 1450110
dege.zzz@rds151163048-> tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log
INFO [2016-01-12 11:55:09.541] Thread-14 Term-00, Measured tpmC (NewOrders) = 57322.05
INFO [2016-01-12 11:55:09.541] Thread-14 Term-00, Measured tpmTOTAL = 143227.03
INFO [2016-01-12 11:55:09.542] Thread-14 Term-00, Session Start = 2016-01-12 11:45:09
INFO [2016-01-12 11:55:09.542] Thread-14 Term-00, Session End = 2016-01-12 11:55:09
INFO [2016-01-12 11:55:09.542] Thread-14 Term-00, Transaction Count = 1432298
dege.zzz@rds151163048-> tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log
INFO [2016-01-12 11:55:09.574] Thread-7 Term-00, Measured tpmC (NewOrders) = 57863.92
INFO [2016-01-12 11:55:09.574] Thread-7 Term-00, Measured tpmTOTAL = 144596.45
INFO [2016-01-12 11:55:09.575] Thread-7 Term-00, Session Start = 2016-01-12 11:45:09
INFO [2016-01-12 11:55:09.575] Thread-7 Term-00, Session End = 2016-01-12 11:55:09
INFO [2016-01-12 11:55:09.575] Thread-7 Term-00, Transaction Count = 1445978
INFO [2016-01-12 11:55:09.461] Thread-12 Term-00, Measured tpmC (NewOrders) = 57995.55
INFO [2016-01-12 11:55:09.461] Thread-12 Term-00, Measured tpmTOTAL = 144975.59
INFO [2016-01-12 11:55:09.461] Thread-12 Term-00, Session Start = 2016-01-12 11:45:09
INFO [2016-01-12 11:55:09.461] Thread-12 Term-00, Session End = 2016-01-12 11:55:09
INFO [2016-01-12 11:55:09.462] Thread-12 Term-00, Transaction Count = 1449796
dege.zzz@rds151163048-> tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log
INFO [2016-01-12 11:55:09.499] Thread-0 Term-00, Measured tpmC (NewOrders) = 58013.75
INFO [2016-01-12 11:55:09.499] Thread-0 Term-00, Measured tpmTOTAL = 145006.74
INFO [2016-01-12 11:55:09.499] Thread-0 Term-00, Session Start = 2016-01-12 11:45:09
INFO [2016-01-12 11:55:09.500] Thread-0 Term-00, Session End = 2016-01-12 11:55:09
INFO [2016-01-12 11:55:09.500] Thread-0 Term-00, Transaction Count = 1450110
dege.zzz@rds151163048-> tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log
INFO [2016-01-12 11:55:09.541] Thread-14 Term-00, Measured tpmC (NewOrders) = 57322.05
INFO [2016-01-12 11:55:09.541] Thread-14 Term-00, Measured tpmTOTAL = 143227.03
INFO [2016-01-12 11:55:09.542] Thread-14 Term-00, Session Start = 2016-01-12 11:45:09
INFO [2016-01-12 11:55:09.542] Thread-14 Term-00, Session End = 2016-01-12 11:55:09
INFO [2016-01-12 11:55:09.542] Thread-14 Term-00, Transaction Count = 1432298
dege.zzz@rds151163048-> tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log
INFO [2016-01-12 11:55:09.574] Thread-7 Term-00, Measured tpmC (NewOrders) = 57863.92
INFO [2016-01-12 11:55:09.574] Thread-7 Term-00, Measured tpmTOTAL = 144596.45
INFO [2016-01-12 11:55:09.575] Thread-7 Term-00, Session Start = 2016-01-12 11:45:09
INFO [2016-01-12 11:55:09.575] Thread-7 Term-00, Session End = 2016-01-12 11:55:09
INFO [2016-01-12 11:55:09.575] Thread-7 Term-00, Transaction Count = 1445978
TPM：
144975.59 + 145006.74 + 143227.03 + 144596.45 =
577805.81
------------------------------------------------------------------------------------------------------------
阶段5，
编译器版本
INTEL编译器
CLANG编译器
$ export LD_LIBRARY_PATH=/u02/digoal/gcc4.9.3/lib:/u02/digoal/cloog/lib:/u02/digoal/gmp/lib:/u02/digoal/isl/lib:/u02/digoal/mpc/lib:/u02/digoal/mpfr/lib:$LD_LIBRARY_PATH
$ export PATH=/u02/digoal/gcc4.9.3/bin:$PATH
$ CFLAGS="-O3 -march=native -flto" CC=/u02/digoal/gcc4.9.3/bin/gcc ./configure --prefix=/u02/digoal/soft_bak/pgsql9.5 --with-blocksize=32 --with-pgport=1921 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-segsize=64
$ make world -j 32
$ make install-world -j 32
$ pg_config
测试结果
digoal tail -n 5 benchmarksql-4.1.0_pg01/run/log/benchmarksql.log
INFO [2016-01-13 02:00:49.699] Thread-15 Term-00, Measured tpmC (NewOrders) = 59092.33
INFO [2016-01-13 02:00:49.699] Thread-15 Term-00, Measured tpmTOTAL = 147832.44
INFO [2016-01-13 02:00:49.699] Thread-15 Term-00, Session Start = 2016-01-13 01:50:49
INFO [2016-01-13 02:00:49.699] Thread-15 Term-00, Session End = 2016-01-13 02:00:49
INFO [2016-01-13 02:00:49.699] Thread-15 Term-00, Transaction Count = 1478385
digoal tail -n 5 benchmarksql-4.1.0_pg02/run/log/benchmarksql.log
INFO [2016-01-13 02:00:49.704] Thread-0 Term-00, Measured tpmC (NewOrders) = 60051.49
INFO [2016-01-13 02:00:49.704] Thread-0 Term-00, Measured tpmTOTAL = 150231.54
INFO [2016-01-13 02:00:49.704] Thread-0 Term-00, Session Start = 2016-01-13 01:50:49
INFO [2016-01-13 02:00:49.704] Thread-0 Term-00, Session End = 2016-01-13 02:00:49
INFO [2016-01-13 02:00:49.704] Thread-0 Term-00, Transaction Count = 1502367
digoal tail -n 5 benchmarksql-4.1.0_pg03/run/log/benchmarksql.log
INFO [2016-01-13 02:00:49.693] Thread-16 Term-00, Measured tpmC (NewOrders) = 60273.99
INFO [2016-01-13 02:00:49.694] Thread-16 Term-00, Measured tpmTOTAL = 150601.93
INFO [2016-01-13 02:00:49.694] Thread-16 Term-00, Session Start = 2016-01-13 01:50:49
INFO [2016-01-13 02:00:49.694] Thread-16 Term-00, Session End = 2016-01-13 02:00:49
INFO [2016-01-13 02:00:49.694] Thread-16 Term-00, Transaction Count = 1506066
digoal tail -n 5 benchmarksql-4.1.0_pg04/run/log/benchmarksql.log
INFO [2016-01-13 02:00:49.715] Thread-18 Term-00, Measured tpmC (NewOrders) = 60180.69
INFO [2016-01-13 02:00:49.715] Thread-18 Term-00, Measured tpmTOTAL = 150591.78
INFO [2016-01-13 02:00:49.716] Thread-18 Term-00, Session Start = 2016-01-13 01:50:49
INFO [2016-01-13 02:00:49.716] Thread-18 Term-00, Session End = 2016-01-13 02:00:49
INFO [2016-01-13 02:00:49.716] Thread-18 Term-00, Transaction Count = 1505962
TPM
599257.69
------------------------------------------------------------------------------------------------------------
[其他优化手段]
1. PostgreSQL jdbc有一些参数可以优化，本文还未处理。例如防止类型转换，QUERY plan CACHE size。
2. PostgreSQL 代码层也有优化的空间，例如分区表的代码，快照的优化。
[特别声明]
1. 本文纯属技术交流，测试数据不具备任何指导意义。
[参考]
