研發論壇
討論主題
葛宏賓109649/user/zte_ltd
昨天 16:16 .
主題:
.
用orabm測試oracle服務器的tps值
.
分類:
測試
用orabm測試oracle服務器的tps值
1、orabm簡介
orabm是一個開源的oracle性能測試工具，，包含了一套sql腳本和幾個命令行程序。
作者geoff ingram，是《high-performance oracle: proven methods for achieving optimum performance and availability》一書的作者。
該工具可以從作者的主頁（http://www.dbcool.com/）獲得，具體下載地址是http://www.linxcel.co.uk/orabm/orabm.tar
2、安裝orabm
下載的orabm是一個tar包，一般可以將該文件上傳到oracle服務器上，方便直接運行sql腳本。
本次測試的環境是：aix 5.2 ，oracle 9.2.0.1，操作系統用戶是oracle
使用orabm的用戶必須具有oracle的相關環境變量，本次測試中用的是oracle用戶。
（1）解包
$ tar xvf orabm.tar
x orabm, 0 bytes, 0 tape blocks
x orabm/src, 0 bytes, 0 tape blocks
x orabm/src/orabmload.pc, 24200 bytes, 48 tape blocks
x orabm/src/orabm.c, 9346 bytes, 19 tape blocks
x orabm/src/init.ora, 1442 bytes, 3 tape blocks
x orabm/src/build_from_source.txt, 863 bytes, 2 tape blocks
x orabm/install, 0 bytes, 0 tape blocks
x orabm/install/orabm_analyze.sql, 1068 bytes, 3 tape blocks
x orabm/install/orabm_cache.sql, 1116 bytes, 3 tape blocks
x orabm/install/orabm_ind.sql, 1124 bytes, 3 tape blocks
x orabm/install/orabm_query_cache.sql, 497 bytes, 1 tape blocks
x orabm/install/orabm_serverside_stress.sql, 8528 bytes, 17 tape blocks
x orabm/install/orabm_tab.sql, 2321 bytes, 5 tape blocks
x orabm/install/orabm_tab_rm.sql, 197 bytes, 1 tape blocks
x orabm/install/orabm_user.sql, 233 bytes, 1 tape blocks
x orabm/bin, 0 bytes, 0 tape blocks
x orabm/bin/orabm_tps.awk, 182 bytes, 1 tape blocks
x orabm/bin/orabm_tps.sh, 70 bytes, 1 tape blocks
x orabm/bin/solaris, 0 bytes, 0 tape blocks
x orabm/bin/solaris/orabmload, 85940 bytes, 168 tape blocks
x orabm/bin/solaris/orabm, 53140 bytes, 104 tape blocks
......
解包完成以后就可以開始安裝了，其實主要就是運行sql腳本建立測試環境。
安裝進度表如下：
# operation command
1 create the orabm user (assumes tools tablespace, temp temporary tablespace) sqlplus system/pwd @orabm_user
2 create the tables sqlplus system/pwd @orabm_tab
3 load the data $ orabmload warehouses 1
4 create the indexes sqlplus system/pwd @orabm_ind
5 analyze the tables and indexes sqlplus system/pwd @orabm_analyze
6 create the stress-test pl/sql procedures sqlplus system/pwd @orabm_serverside_stress
7 cache the table and index data in the sga sqlplus system/pwd @orabm_cache
（2）建數據庫用戶和表
對應于安裝進度表的第1、2兩個步驟
注意：由于所建用戶orabm的缺省表空間是tools，因此最好在安裝之前（起碼在裝載數據之前）將tools表空間擴大到150mb左右。雖然一般tools表空間都設置為autoextent，但是還是會影響裝載數據的速度，后面的步驟會看到裝載的數據是比較大的。
$ ls
orabm_analyze.sql orabm_serverside_stress.sql
orabm_cache.sql orabm_tab.sql
orabm_ind.sql orabm_tab_rm.sql
orabm_query_cache.sql orabm_user.sql
$ sqlplus "/ as sysdba" @orabm_user
sql*plus: release 9.2.0.1.0 - production on wed jan 26 14:32:48 2005
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
connected to:
oracle9i enterprise edition release 9.2.0.1.0 - 64bit production
with the partitioning and olap options
jserver release 9.2.0.1.0 - production
user created.
grant succeeded.
user altered.
user altered.
revoke succeeded.
disconnected from oracle9i enterprise edition release 9.2.0.1.0 - 64bit production
with the partitioning, olap and oracle data mining options
jserver release 9.2.0.4.0 – production
$ sqlplus "/ as sysdba" @orabm_tab
sql*plus: release 9.2.0.1.0 - production on wed jan 26 14:32:48 2005
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
connected to:
oracle9i enterprise edition release 9.2.0.1.0 - 64bit production
with the partitioning and olap options
jserver release 9.2.0.1.0 - production
table created.
table created.
table created.
table created.
table created.
table created.
table created.
table created.
table created.
disconnected from oracle9i enterprise edition release 9.2.0.1.0 - 64bit production
with the partitioning, olap and oracle data mining options
jserver release 9.2.0.1.0 - production
（3）裝載數據
對應于安裝進度表中的第3個步驟
按照步驟描述，本來這個應該在oracle服務器上運行orabmload來完成，但是程序包中的orabmload程序只有linux、solaris、windows三個平臺下的，沒有aix平臺下的。
但orabm是開源的程序，在src目錄下就是源程序
$ ls -l
total 88
-rw-r--r-- 1 oracle dba 863 nov 07 2002 build_from_source.txt
-rw-r--r-- 1 oracle dba 1442 nov 07 2002 init.ora
-rw-r--r-- 1 oracle dba 9346 nov 07 2002 orabm.c
-rw-r--r-- 1 oracle dba 24200 nov 07 2002 orabmload.pc
其中 orabm.c、orabmload.c 是兩個應用程序的源程序，build_from_source.txt 是編譯說明。我按照編譯說明上的指導，make了一下，但是最后運行程序的時候出錯，也許是還有什么編譯選項不對吧。下面是運行錯誤：
$ ./orabmload warehouse 1
exec(): 0509-036 cannot load program ./orabmload because of the following errors
:
0509-150 dependent module /home/oracle/orahome1/lib32/libclntsh.a(shr.
o) could not be loaded.
0509-124 the program is a discontinued 64-bit object file.
------------后記------------
后面發現是程序鏈接的庫文件錯了，不應該鏈接 /home/oracle/orahome1/lib32/ 目錄，這是為32位程序準備的，應該鏈接 /home/oracle/orahome1/lib/ ，缺省就是64位。
按此修改了一下主機用戶oracle的.profile文件，然后重新登錄，再編譯一下，最后運行是沒有問題的。
------------后記結束------------
還好的是，orabm預先提供windows版本的數據裝載程序，在 orabm/bin/windows 目錄下就是。
將orabm拷貝或者ftp到windows平臺的機器上，配置好oracle連接，即可裝載數據了，如下：
e:/temp>set local=ccbver
e:/temp>orabmload warehouse 1
connected to oracle as user: orabm
using timestamp=20050126103630
tpcc data load started...
loading item
.................... 20000
.................... 40000
.................... 60000
.................... 80000
.................... 100000
item done.
loading warehouse
loading stock wid=1
.................... 20000
.................... 40000
.................... 60000
.................... 80000
.................... 100000
stock done.
loading district
loading customer for did=1, wid=1
...customer done.
loading customer for did=2, wid=1
...customer done.
loading customer for did=3, wid=1
...customer done.
loading customer for did=4, wid=1
...customer done.
loading customer for did=5, wid=1
...customer done.
loading customer for did=6, wid=1
...customer done.
loading customer for did=7, wid=1
...customer done.
loading customer for did=8, wid=1
...customer done.
loading customer for did=9, wid=1
...customer done.
loading customer for did=10, wid=1
...customer done.
loading orders for d=1, w= 1
. 1000
. 2000
. 3000
orders done.
loading orders for d=2, w= 1
. 1000
. 2000
. 3000
orders done.
loading orders for d=3, w= 1
. 1000
. 2000
. 3000
orders done.
loading orders for d=4, w= 1
. 1000
. 2000
. 3000
orders done.
loading orders for d=5, w= 1
. 1000
. 2000
. 3000
orders done.
loading orders for d=6, w= 1
. 1000
. 2000
. 3000
orders done.
loading orders for d=7, w= 1
. 1000
. 2000
. 3000
orders done.
loading orders for d=8, w= 1
. 1000
. 2000
. 3000
orders done.
loading orders for d=9, w= 1
. 1000
. 2000
. 3000
orders done.
loading orders for d=10, w= 1
. 1000
. 2000
. 3000
orders done.
...data loading completed successfully
成功裝載數據，需要的時間可能有點長。
（4）建索引、分析表和索引、建壓力測試存儲過程、將數據裝載到cache
對應于安裝進度表中的第4、5、6、7步驟
這幾個步驟都是執行sql腳本，在oracle服務器上執行即可。
$ cd install
$ ls
orabm_analyze.sql orabm_ind.sql orabm_serverside_stress.sql orabm_tab_rm.sql
orabm_cache.sql orabm_query_cache.sql orabm_tab.sql orabm_user.sql
$ sqlplus "/ as sysdba" @orabm_ind
sql*plus: release 9.2.0.1.0 - production on wed jan 26 14:32:48 2005
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
connected to:
oracle9i enterprise edition release 9.2.0.1.0 - 64bit production
with the partitioning and olap options
jserver release 9.2.0.1.0 - production
......
$ sqlplus "/ as sysdba" @ orabm_analyze
...........
$ sqlplus "/ as sysdba" @ orabm_serverside_stress
......
$ sqlplus "/ as sysdba" @ orabm_cache
......
這后面執行的sql腳本都不會有什么問題，記住步驟順序即可。
至此，orabm的安裝已經完成了。
3、測試tps值
orabm安裝完成以后，就可以開始測試oracle服務器的tps值了。
同樣的，在本次測試中，由于應用程序沒有aix版本，所以只能在windows平臺作為oracle客戶端來執行測試程序了。
（1）orabm的原理
我們先通過作者主頁上的一段話來了解一下orabm的原理。
orabm works by running a user-specified number of database transactions in each of a user-specified number of concurrent database sessions.
orabm通過在用戶指定的并發下運行指定數量的事務來測試系統性能.
the transactions are executed by the orabm_serverside_stress stored procedure, under the schema orabm.
事務通過執行orabm用戶的orabm_serverside_stress過程來實現.
for each concurrent session, orabm_serverside_stress runs the number of transactions specified on the orabm command line, and returns the transactions per second (tps) value for that session during the sampling interval on completion.
對于每個并發session,orabm_serverside_stress運行在orabm命令行指定數量的事務，并返回在完成所有任務的采樣間隔內每秒的事務數量.
to ensure that all concurrent sessions are processing transactions during the sampling interval, the tps value only includes results from the middle 80% of transactions: the first 10% and last 10% are ignored.
為了保證準確性,tps值只取中間的80%數據,頭尾的10%都將被忽略.
the transactions are loosely based on the tpc-c order-status and stock-level transactions, using a predefined distribution of transactions.
事務是松散的,基于標準的tpc-c測試的訂單、庫存事務模型,使用預定義的事務分布.
the transaction split, which is based on data returned by the dbms_random package, should be:
事務基于dbms_random包的返回值進行分割,應該是:
stock-level:order-by-customer-name:order-by-customer-id
50% :30% :20%
the string returned by orabm_serverside_stress includes the transaction split during the test, to ensure that the transaction distribution is correct, subject to random fluctuations e.g.:
為了確保事務分布的正確性,orabm_serverside_stress的輸出包含事務分割比例,輸出結果具有隨機波動,例如:
...sl=4042(50.5%) on=2384(29.8%) oi=1573(19.7%)...
once you have set up the test tables, data, and indexes, you're ready to run orabm. the following command shows orabm running 20000 transactions in a single session against the oracle database identified by oracle_sid in the unix environment:
在成功安裝了orabm之后,在unix下定義好了oracle_sid環境變量之后,我們可以如下運行orabm命令進行測試:
$ orabm 1 20000
this command line runs the same workload against a remote database identified by the oracle net alias linxceld1.co.uk from a windows command box:
以下是在windows下遠程運行方式:
c:/> orabm 1 20000 linxceld1.co.uk
note: running against a remote database has little (if any), affect on the transaction throughput, because all processing takes place on the dbms server.
execution of a single orabm session should show a single cpu at close to 100% utilization, provided that all table and index data is present in the oracle block buffer cache and no other workload is running on the database server. on unix or linux, you can use the “top” command to confirm this, or check that no "db file sequential read" event waits are taking place for the oracle session using info in the v$session_event view - these indicate waits for physical i/o.
如果所有的測試表和相關索引都已經cache到內存中,系統上沒有其他任務運行,那么你可以看到單個orabm session會使用將近100%的cpu資源.在linux或者unix上，可以使用top來查看cpu使用情況,或者檢察數據庫中不存在"db file sequential read"等待事件.
alternatively, if your oracle dbms is running on linux, you can use the gkrellm performance monitor to show that cpu utilization of a single cpu is at ~100% and no physical i/o is taking place. gkrellm can be downloaded from:
http://web.wt.net/~billw/gkrellm/gkrellm.html
here's an example of the command line you would use to run 10000 transactions against a local oracle database for three iterations. in the first iteration, one session runs, in the second iteration two concurrent sessions run, and in the third iteration, six concurrent sessions run:
以下是一個測試例子,對本地數據庫進行三個階段測試,分別以1,2,6個并發執行10000個事務.
$ orabm 1,2,6 10000
keep in mind that the specified number of transactions is run in each concurrent session.
注意,這里定義的10000個事務是對每個session來說的.
note: you should specify sufficient transactions such that the tps results produced don't fluctuate significantly between runs for a given number of sessions; 100000 is a good value to choose.
注意：你應該定義足夠的事務以便tps結果在不同數量并發下波動盡量小,通常100,000是一個好的選擇.
results output
output is appended to a log file orabm.database.log, where database is either the oracle_sid or tns alias that identifies the database where the test was run e.g. orabm.t92.log. for each iteration, the tps value for each concurrent session appears between begin and end markers. for example, the following shows the contents of the log for two concurrent sessions - in this case the second iteration for the previous command line example - where txn(all) displays the total transaction count, and xn(sam) and t(sam) show the total transactions and time for the middle 80% of transactions for which sampling took place:
輸出結果的說明:
txn(all)----代表total transaction count,總的事務數量.
xn(sam)-----采樣的事務數量
t(sam)------采樣事務運行的時間.
---begin sess=2 txn=10000 oracle_sid=t92 fri nov 8 20:31:48 2002
t92.world txn(all)=10000 xn(sam)=7999 t(sam)=44 tps=182 ...
t92.world txn(all)=10000 xn(sam)=7999 t(sam)=45 tps=178 ...
---end - fri nov 8 20:32:46 2002
the total tps for this iteration is the sum of the tps for the two concurrent sessions (182+178=360).
對于以上輸出,在2個并發下,tps值為182+178=360
a shell script (orabm_tps.sh) can be used to process output from the log on unix and linux. the script aggregates the tps values for concurrent sessions in a single iteration into a total tps value for that iteration. the output based on the log info from the previous command line (3 iterations with 1, then 2, then 6 concurrent sessions) shows:
也可以用shell script(orabm_tps.sh)進行輸出日志分析.
$ orabm_tps.sh orabm.t92.log
oracle_sid=t92 sess=1 tps=182
oracle_sid=t92 sess=2 tps=360
oracle_sid=t92 sess=6 tps=364
in this example the server was a 2 cpu model - as a result, 2 concurrent sessions running in orabm are enough to completely utilize all available cpu capacity.
在這個例子中,server有兩個cpu,測試中兩個并發足以消耗所有的cpu資源.
additional sessions should result in the total tps remaining unchanged, or even falling slightly as the operating system performs context switches to share the overloaded cpu resource between more ready-to-run sessions than available cpus.
繼續增加并發并不會導致tps值得增加,甚至有可能會下降.
（2）測試tps值
下面我們來具體跑一下程序。在跑程序之前，要注意一下：前面的例子應該可以看到，在windows平臺下運行orabmload時，設置了 local 變量，這個變量相當于unix平臺下的oracle_sid，這次運行orabm程序，卻有所不同，見下面的運行日志：
e:/temp>orabm 1,2,6,10 10000
oracle_sid not set
e:/temp>set local=ccbver
e:/temp>orabm 1,2,6,10 10000
oracle_sid not set
e:/temp>set oracle_sid=ccbver
e:/temp>orabm 1,2,6,10 10000
---begin sess=1 txn=10000 oracle_sid=ccbver wed jan 26 11:03:45 2005
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=17 tps=471 sl=3918(49%) on=2483(31%) o
i=1598(20%) end=260105-10:05:58
---end - wed jan 26 11:04:09 2005
---begin sess=2 txn=10000 oracle_sid=ccbver wed jan 26 11:04:09 2005
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=17 tps=471 sl=3999(50%) on=2441(30.5%)
oi=1559(19.5%) end=260105-10:06:19
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=17 tps=471 sl=4025(50.3%) on=2396(30%)
oi=1578(19.7%) end=260105-10:06:19
---end - wed jan 26 11:04:31 2005
---begin sess=6 txn=10000 oracle_sid=ccbver wed jan 26 11:04:31 2005
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=43 tps=186 sl=4041(50.5%) on=2372(29.7
%) oi=1586(19.8%) end=260105-10:07:13
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=43 tps=186 sl=4017(50.2%) on=2404(30.1
%) oi=1578(19.7%) end=260105-10:07:13
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=42 tps=190 sl=3917(49%) on=2505(31.3%)
oi=1577(19.7%) end=260105-10:07:14
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=43 tps=186 sl=4016(50.2%) on=2394(29.9
%) oi=1589(19.9%) end=260105-10:07:14
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=43 tps=186 sl=4038(50.5%) on=2316(29%)
oi=1645(20.6%) end=260105-10:07:14
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=47 tps=170 sl=4011(50.1%) on=2380(29.8
%) oi=1608(20.1%) end=260105-10:07:15
---end - wed jan 26 11:05:26 2005
---begin sess=10 txn=10000 oracle_sid=ccbver wed jan 26 11:05:26 2005
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=72 tps=111 sl=3969(49.6%) on=2390(29.9
%) oi=1640(20.5%) end=260105-10:08:45
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=72 tps=111 sl=4061(50.8%) on=2407(30.1
%) oi=1531(19.1%) end=260105-10:08:45
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=73 tps=110 sl=3996(50%) on=2444(30.6%)
oi=1559(19.5%) end=260105-10:08:46
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=72 tps=111 sl=4089(51.1%) on=2334(29.2
%) oi=1576(19.7%) end=260105-10:08:46
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=71 tps=113 sl=3935(49.2%) on=2468(30.9
%) oi=1596(20%) end=260105-10:08:46
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=74 tps=108 sl=4015(50.2%) on=2380(29.8
%) oi=1604(20.1%) end=260105-10:08:47
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=72 tps=111 sl=4008(50.1%) on=2446(30.6
%) oi=1545(19.3%) end=260105-10:08:47
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=76 tps=105 sl=3939(49.2%) on=2454(30.7
%) oi=1606(20.1%) end=260105-10:08:48
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=76 tps=105 sl=4061(50.8%) on=2333(29.2
%) oi=1605(20.1%) end=260105-10:08:48
ccbver txn(all)=10000 xn(sam)=7999 t(sam)=77 tps=104 sl=3932(49.2%) on=2432(30.4
%) oi=1635(20.4%) end=260105-10:08:48
---end - wed jan 26 11:06:59 2005
原來還要設置 oracle_sid 變量！
我跑的這個例子是分四個案例：
第一個是1個并發session跑10000個事務；
第二個是2個并發session跑10000個事務；
第三個是6個并發session跑10000個事務；
第四個是10個并發session跑10000個事務。
測試程序運行結束后，會生成一個orabm.{oracle_sid}.log的文件，比如上面這個例子，生成的文件是 orabm.ccbver.log，該文件是累加的，如果繼續跑測試程序，其測試結果會累加到這個文件中。
（3）格式化輸出
其實從上面的運行結果中已經差不多可以看出tps的值了，把并發session的tps值相加即可。不過，orabm程序包中有一個格式化輸出結果的shell腳本，可以看得更加清楚。
當然，得把日志文件上傳到服務器上去運行，畢竟是shell程序嘛。
看結果：
$ ./orabm_tps.sh orabm.ccbver.log
oracle_sid=ccbver sess=1 tps=471
oracle_sid=ccbver sess=2 tps=942
oracle_sid=ccbver sess=6 tps=1104
oracle_sid=ccbver sess=10 tps=1089
可以看出，這臺機器的tps值大概在1100左右。
后來把事務數變為100000，再跑了一下，結果也差不多，如下：
$ ./orabm_tps.sh orabm.ccbver.log
oracle_sid=ccbver sess=1 tps=530
oracle_sid=ccbver sess=2 tps=979
oracle_sid=ccbver sess=6 tps=1106
oracle_sid=ccbver sess=10 tps=1097
