PostgreSQL 9.5.0 PK Oracle 12.0.1.2.0 TPC-C性能极限 / iVAN

Oracle 12c TPC-C 测试请参考：
http://blog.163.com/digoal@126/blog/static/1638770402015112344924835/

文件系统为XFS，优化手段如下：

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

本文在同样的硬件测试环境下对比PostgreSQL 9.5.0。

我们看看1月7号发布的PostgreSQL 新版本性能咋样？

benchmarkSQL 配置请参考：

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

编译器版本

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/'>

</layout>

</appender>

</rollingPolicy>

</triggeringPolicy>

</layout>

</filter>

</appender>

</rollingPolicy>

</layout>

</appender>

<root>

<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跟踪统计：

参考

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

找到需要优化的代码就靠它了。

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报告截图见：

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

-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------

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'

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

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。

http://www.postgresql.org/docs/9.2/interactive/libpq-connect.html

2. PostgreSQL 代码层也有优化的空间，例如分区表的代码，快照的优化。

[特别声明]

1. 本文纯属技术交流，测试数据不具备任何指导意义。

[参考]

1. http://commons.apache.org/proper/commons-logging/apidocs/org/apache/commons/logging/impl/Log4JLogger.html

时间： 2024-10-30 04:39:32

PostgreSQL 9.5.0 PK Oracle 12.0.1.2.0 TPC-C性能极限

PostgreSQL 9.5.0 PK Oracle 12.0.1.2.0 TPC-C性能极限的相关文章

09. WebApp2.0时代启程：倒立者赢，挑战Android性能极限

解决Oracle 12.1.0.2 的OLAP API组件无效问题

探索Oracle之数据库升级九 12.1.0.1 Update 12.1.0.2

Linux 8.0 安装 Oracle 9i

【Oracle】Oracle 11gR2发布11.2.0.3 Patchset补丁集

Oracle现使用CVSS 3.0对漏洞进行评级

Oracle Database RAC 11.2.0.3 for AIX6.1TL7安装记录(5)

Oracle Database RAC 11.2.0.3 for AIX6.1TL7安装记录(2)

Oracle Database RAC 11.2.0.3 for AIX6.1TL7安装记录(3)