Writing a simple benchmark in python for your environment is easy and a good way to get a feel for the maximum transaction rates of your platform. If you know this information you will be able to identify the limits of your system and prepare for growth before you hit those limits. I want to show you how I wrote a script to benchmark for the account lookup and balance update for our stored value telecom application using threads to simulate our multiple front ends accessing the account database.

My python script was written in one afternoon, but it gets the job done. I create an AccountDbThread class that extends the Thread class and continuously reads in account numbers from a flat file, attempts to select, lock, update and unlock these accounts until it is told to stop. From this benchmark I can easily see the maximum number of account lookup and balance update transactions my database engines can handle per second. If I see the number of production transactions per second trending toward this number I can intervene months before there are problems.

You will need python (www.python.org) and the informixdb python module (informixdb.sourceforge.net) if you would like to modify and run the following script against your test environment.

import threading
import informixdb
import time

# number of seconds test will run
runTime = 1800

# threads will stop when the main thread sets keepGoing to 0
keepGoing = 1

class AccountDbThread (threading.Thread):

   # AccountDbThread constructor
   def __init__(self, name, accountFile, dbName, user, password):
      # just store some stuff for now
      self.name = name
      self.accountFile = accountFile
      self.dbName = dbName
      self.user = user
      self.password = password

      # and call the parent thread constructor
      threading.Thread.__init__(self)

   # run will be called when the thread is started
   # when the thread is started it will connect to the database, open the account file and
   # continously process account lookup and balance update transactions until the
   # thread is told to stop
   def run(self):
      global keepGoing

      # total number of accounts processed by this thread
      totalAccounts = 0

      # connect this thread to the database, set the isolation level and set the lock mode
      dbConn = informixdb.connect(self.dbName, self.user, self.password, autocommit = True)
      dbCur = dbConn.cursor(rowformat=informixdb.ROW_AS_OBJECT)
      dbCur.execute("set isolation committed read")
      dbCur.execute("set lock mode to wait 5")
      dbCur.close()

      print "%s - connected to %s" % (self.name, self.dbName)

      # prepare the 4 SQL statements that make up a account lookup and
      # balance update transaction

      # lookup the account
      dbCur1 = dbConn.cursor(rowformat=informixdb.ROW_AS_OBJECT)
      dbCur1.prepare("select balance, status from account where account_id = :1 and customer_id = :2")

      # lock the account
      dbCur2 = dbConn.cursor(rowformat=informixdb.ROW_AS_OBJECT)
      dbCur2.prepare("insert into locked_accounts (account_id, customer_id, lock_time) values (:1, :2, CURRENT)")

      # update the account balance
      dbCur3 = dbConn.cursor(rowformat=informixdb.ROW_AS_OBJECT)
      dbCur3.prepare("update account set balance = balance - :1 where account_id = :2 and customer_id = :3")

      # unlock the account
      dbCur4 = dbConn.cursor(rowformat=informixdb.ROW_AS_OBJECT)
      dbCur4.prepare("delete from locked_accounts where account_id = :1 and customer_id = :2")

      # open the file containing our account numbers
      accountFile = open(self.accountFile, 'r')
      print "%s - opened %s" % (self.name, self.accountFile)

      # save the start time of the benchmark
      startTime = time.time()

      # continuously read each line of the account flat file one line at a time
      while True:
         for line in accountFile:
            # increment the number of accounts processed
            totalAccounts = totalAccounts + 1

            # initialize accountLocked to false
            accountLocked = False

            # each line of the file is in the form of account_id|customer_id|
            # we need to split this into variables to be passed to our prepared sql
            fields = line.split('|')
            accountId = fields[0]
            customerId = fields[1]

            # lookup the account to see if it exists
            dbCur1.execute(dbCur1.command, (accountId, customerId))
            dbRow = dbCur1.fetchone()
            if dbRow != None:
               # the account exists, attempt to lock it
               try:
                  dbCur2.execute(dbCur2.command, (accountId, customerId))
                  accountLocked = True

               except informixdb.DatabaseError, e:
                  if e.sqlcode == -268:
                     print "account already in use"

               if accountLocked == True:
                  # update the account balance
                  dbCur3.execute(dbCur3.command, (10, accountId, customerId))

                  # unlock the account
                  dbCur4.execute(dbCur4.command, (accountId, customerId))

            # print status every 10000 accounts
            if totalAccounts % 10000 == 0:
               elapsedTime = time.time() - startTime

               print "%s - %ld transactions in %f seconds, %f transactions per second" % 
                              (self.name, totalAccounts, elapsedTime, totalAccounts / elapsedTime)

            if keepGoing == 0:
               break;

         if keepGoing == 0:
            break;

         # we must have run out of lines in our input file
         # close and open the file containing our account numbers
         accountFile.close()
         accountFile = open(self.accountFile, 'r')

      elapsedTime = time.time() - startTime

      print "%s complete - %ld transactions in %f seconds, %f transactions per second" % 
                              (self.name, totalAccounts, elapsedTime, totalAccounts / elapsedTime)

      accountFile.close()


# instantiate 5 threads to simulate a production environment
thread1 = AccountDbThread("thread1", "thread1_accounts.unl", "accountdb@testsvr", "user1", "12345")
thread2 = AccountDbThread("thread2", "thread2_accounts.unl", "accountdb@testsvr", "user1", "12345")
thread3 = AccountDbThread("thread3", "thread3_accounts.unl", "accountdb@testsvr", "user1", "12345")
thread4 = AccountDbThread("thread4", "thread4_accounts.unl", "accountdb@testsvr", "user1", "12345")
thread5 = AccountDbThread("thread5", "thread5_accounts.unl", "accountdb@testsvr", "user1", "12345")

# start the 5 threads
thread1.start()
thread2.start()
thread3.start()
thread4.start()
thread5.start()

# have the main thread sleep for however long we want to run our test
time.sleep(runTime)

# and instruct the threads to stop running when we wake up to end our test
keepGoing = 0

# wait for the 5 threads to finish
thread1.join()
thread2.join()
thread3.join()
thread4.join()
thread5.join()

print "test complete"

Running this benchmark against test hardware and data that mirrors production I get the following results

thread1 - connected to accountdb@testsvr
thread3 - connected to accountdb@testsvr
thread1 - opened thread1_accounts.unl
thread3 - opened thread3_accounts.unl
thread4 - connected to accountdb@testsvr
thread2 - connected to accountdb@testsvr
thread5 - connected to accountdb@testsvr
thread5 - opened thread5_accounts.unl
thread2 - opened thread2_accounts.unl
thread4 - opened thread4_accounts.unl
account already in use
account already in use
account already in use
account already in use
account already in use
account already in use
account already in use
account already in use
thread3 - 10000 transactions in 52.273773 seconds, 191.300520 transactions per second
thread1 - 10000 transactions in 52.491554 seconds, 190.506838 transactions per second
thread4 - 10000 transactions in 52.502110 seconds, 190.468535 transactions per second
thread5 - 10000 transactions in 52.649253 seconds, 189.936218 transactions per second
thread2 - 10000 transactions in 52.703727 seconds, 189.739902 transactions per second
account already in use
thread3 - 20000 transactions in 104.554338 seconds, 191.288093 transactions per second
thread4 - 20000 transactions in 105.068118 seconds, 190.352701 transactions per second
thread1 - 20000 transactions in 105.105420 seconds, 190.285144 transactions per second
thread5 - 20000 transactions in 105.350764 seconds, 189.842002 transactions per second
thread2 - 20000 transactions in 105.536202 seconds, 189.508431 transactions per second
account already in use
account already in use
account already in use
account already in use
thread3 - 30000 transactions in 157.190037 seconds, 190.851790 transactions per second
thread1 - 30000 transactions in 157.445474 seconds, 190.542156 transactions per second
thread5 - 30000 transactions in 157.948753 seconds, 189.935023 transactions per second
thread4 - 30000 transactions in 158.176442 seconds, 189.661619 transactions per second
thread2 - 30000 transactions in 158.329003 seconds, 189.478867 transactions per second
.
.
.
thread1 - 320000 transactions in 1696.054366 seconds, 188.673197 transactions per second
thread5 - 320000 transactions in 1700.813605 seconds, 188.145249 transactions per second
thread2 - 320000 transactions in 1703.851234 seconds, 187.809824 transactions per second
thread3 - 320000 transactions in 1706.857070 seconds, 187.479084 transactions per second
thread4 - 320000 transactions in 1716.252144 seconds, 186.452790 transactions per second
account already in use
account already in use
account already in use
thread1 - 330000 transactions in 1748.419306 seconds, 188.741910 transactions per second
thread5 - 330000 transactions in 1753.094668 seconds, 188.238551 transactions per second
thread2 - 330000 transactions in 1756.496155 seconds, 187.874024 transactions per second
thread3 - 330000 transactions in 1759.540814 seconds, 187.548932 transactions per second
account already in use
account already in use
account already in use
account already in use
account already in use
thread4 - 330000 transactions in 1769.437438 seconds, 186.499954 transactions per second
account already in use
thread5 complete - 338864 transactions in 1799.783685 seconds, 188.280404 transactions per second
thread3 complete - 337639 transactions in 1799.793745 seconds, 187.598718 transactions per second
thread2 complete - 338187 transactions in 1799.789034 seconds, 187.903690 transactions per second
thread1 complete - 339628 transactions in 1799.799293 seconds, 188.703263 transactions per second
thread4 complete - 335589 transactions in 1799.791470 seconds, 186.459935 transactions per second
test complete

Each front end can service around 190 transactions per second so the system can handle about 950 transactions per second. This is good information for me to know. If my peak transaction rate is 100 per second in the production environment, I can feel pretty confident that I do not need to worry about running into performance problems with my account lookup and balance update transactions.