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.
Hi,
ReplyDeleteis it possible to share the sql script used for this benchmark?
Thx.
Joe