Friday, May 7, 2010

OTLP Benchmark - Baseline


I'm ready to start benchmarking, but before I start we need to talk about the hardware I'm running the benchmark against and produce some baseline results.

Server/Storage Properties

2 Dual Core CPUs
4GB RAM
OS: 64 bit RHEL5
Informix: 11.50.FC6

rootdbs: 8GB RAID10 spread over 6 physical drives, currently holds a 4GB physical log
llogdbs: 8GB RAID10 spread over 8 physical drives, currently holds 32 256 MB logical logs
dbspace1: 49GB RAID10 spread over 6 physical drives
dbspace2: 108GB RAID10 spread over 8 physical drives
dbspace3: 69GB RAID10 spread over 8 physical drives
tempdbs: 4GB RAID10 spread over 10 physical drives

The client (the ptpcc.py process that runs the benchmark) will run on a separate but identical server.

For the baseline benchmark, here is a sampling of the ONCONFIG values I used

PHYSBUFF 128
LOGBUFF 64
NETTYPE soctcp,1,200,NET
VPCLASS cpu,num=4,noage
VP_MEMORY_CACHE_KB 8192
VPCLASS aio,num=10,noage
CLEANERS 8
AUTO_CKPTS 1
RTO_SERVER_RESTART 120
STMT_CACHE 0
USELASTCOMMITTED NONE
BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default
OPTCOMPIND 0
BUFFERPOOL size=2K,buffers=50000,lrus=8,lru_min_dirty=50,lru_max_dirty=60
AUTO_LRU_TUNING 1

All tables used in the baseline benchmark were created with row level locking in dbspace1 and the original set of indexes.

Before each benchmark I will bounce the engine and allow the benchmark to run for 5 minutes without collecting statistics. After this 5 minute ramp up time the benchmark will run for 15 minutes and produce some statistics that look like this:


2010-05-07 15:11:26: benchmark - 100 terminals running for 900 seconds
2010-05-07 15:31:28:                       sql        total          min          max          avg          num
2010-05-07 15:31:28:          dbSelDistOrderId   264.492183     0.000589     4.998652     0.120443         2196
2010-05-07 15:31:28:             dbSelDistPymt   666.518798     0.000611     0.874549     0.028071        23744
2010-05-07 15:31:28:             dbSelCustById  3865.562448     0.000819     2.452900     0.162802        23744
2010-05-07 15:31:28:          dbSelNewestOrder  3540.358412     0.000530    13.473251     1.700460         2082
2010-05-07 15:31:28:             dbSelCustData    69.895807     0.000703     0.853232     0.030310         2306
2010-05-07 15:31:28:           dbSelOrderLines    33.498745     0.000373     0.484876     0.016090         2082
2010-05-07 15:31:28:                dbSelOrder    69.810965     0.000608     0.529128     0.033531         2082
2010-05-07 15:31:28:          dbSelCustByOrder  1081.806514     0.000592     1.636825     0.049017        22070
2010-05-07 15:31:28:               dbUpdOOrder  1071.331497     0.000262     2.102251     0.048542        22070
2010-05-07 15:31:28:                 dbUpdDist   369.739051     0.000248     0.864587     0.015036        24591
2010-05-07 15:31:28:           dbSelCountStock  5653.657284     0.006115    14.663261     2.574525         2196
2010-05-07 15:31:28:             dbUpdDistPymt  3211.747694     0.000262     6.936206     0.135266        23744
2010-05-07 15:31:28:                dbUpdStock  3607.889070     0.000283     1.362896     0.015618       231006
2010-05-07 15:31:28:            dbSelCustCount   207.250488     0.017086     2.808686     0.166466         1245
2010-05-07 15:31:28:             dbUpdWhsePymt  2955.763594     0.000259     5.403844     0.124485        23744
2010-05-07 15:31:28:   dbUpdCustBalanceAndData    32.769989     0.000403     0.146913     0.014211         2306
2010-05-07 15:31:28:             dbInsHistPymt   368.780551     0.000269     1.330070     0.015532        23744
2010-05-07 15:31:28:          dbUpdCustBalance   345.092451     0.000297     1.336634     0.016097        21438
2010-05-07 15:31:28:        dbSelCustByNameOrd    45.854596     0.000788     0.640742     0.038566         1189
2010-05-07 15:31:28:             dbSelNewOrder  1986.016858     0.003527     6.152908     0.089987        22070
2010-05-07 15:31:28:             dbSelCustWhse  5416.771947     0.000852     6.570103     0.220275        24591
2010-05-07 15:31:28:          dbSelCustByIdOrd   108.330620     0.003462     1.446552     0.121311          893
2010-05-07 15:31:28:            dbUpdOrderLine  1515.644348     0.000358     2.189919     0.068674        22070
2010-05-07 15:31:28:                dbSelStock 26748.686979     0.000714     5.385313     0.115792       231006
2010-05-07 15:31:28:         dbSelOrderLineAmt   688.818336     0.000692     1.372060     0.031211        22070
2010-05-07 15:31:28:             dbSelWhsePymt   708.854190     0.000605     0.904277     0.029854        23744
2010-05-07 15:31:28:            dbInsOrderLine  4126.250988     0.000295     2.563897     0.017862       231006
2010-05-07 15:31:28:               dbInsOOrder   604.548543     0.000291     2.471327     0.024584        24591
2010-05-07 15:31:28:             dbDelNewOrder   479.087660     0.000269     8.575327     0.021708        22070
2010-05-07 15:31:28:                 dbSelDist  3854.715855     0.000630     7.683246     0.156753        24591
2010-05-07 15:31:28:             dbInsNewOrder   412.719221     0.000263     2.343799     0.016783        24591
2010-05-07 15:31:28:         dbUpdCustDelivery  2324.551898     0.000327     2.426604     0.105326        22070
2010-05-07 15:31:28:                 dbSelItem 12098.294207     0.000647     1.985179     0.052372       231006
2010-05-07 15:31:28:                    totals 88535.111786            -            -            -      1383948
2010-05-07 15:31:28:
2010-05-07 15:31:28: time: 901.863525, total transactions: 54876, TPM: 3650.840630, SPM: 92072.556084


The last line shows the total results for the entire benchmark. time is the actual run time of the benchmark in seconds, TPM is the number of transactions per minute and SPM is the number of SQLs executed per minute.

Above the total results we see metrics for each SQL statement that make up each transaction. sql is a name for the SQL statement, total is the total number of seconds spent executing the SQL, min, max and avg are the minimum, maximum and average number of seconds for a single SQL and num is the number of times this SQL was executed.

Now the fun can begin and will most likely involve increasing the buffers to something reasonable to take advantage of the 4GB of memory I have on this machine.



No comments:

Post a Comment