来自PostgreSQL中国社区发起人之一,阿里数据库内核高级技术专家德歌的分享。使用BenchmarkSQL测试Oracle 12c的TPC-C性能,并在同样的硬件测试环境下,测试了PostgreSQL 9.5.0。相关数据对比如下。
测试机:
3 * PCI-E SSD,逻辑卷条带,XFS,数据块对齐,16核开HT,256G内存。
benchmarkSQL 配置请参考:
gcc version
4.4
.
6
20110731
(
Red
Hat
4.4
.
6
-
3
)
(
GCC
)
Oracle 12c TPC-C 测试请参考:
http://blog.163.com/digoal@126/blog/static/1638770402015112344924835/
文件系统为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
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
------------------------------------------------------------------------------------------------------------
[其他优化手段]
1. PostgreSQL jdbc有一些参数可以优化,本文还未处理。例如防止类型转换,QUERY plan CACHE size。
2. PostgreSQL 代码层也有优化的空间,例如分区表的代码,快照的优化。
[特别声明]
1. 本文纯属技术交流,测试数据不具备任何指导意义。
[参考]
注:BenchmarkSQL作为一款经典的开源数据库测试工具,内嵌了TPCC测试脚本,可以对EnterpriseDB、PostgreSQL、MySQL、Oracle以及SQL Server等数据库直接进行测试。