threading or multiprocessing for load test script

Question

I have written some code for testing the performance of a database when users are simultaneously running queries against it. The objective is to understand how the elapsed time increases with number of users. The code contains a class User (shown below) whose objects are created by parsing XML files.

class User(object):

    def __init__(self, id, constr):
        self.id = id
        self.constr = constr
        self.queryid = list()
        self.queries = list()

    def openConn(self):
        self.cnxn = pyodbc.connect(self.constr)
        logDet.info("%s %s"%(self.id,"Open connection."))

    def closeConn(self):
        self.cnxn.close()
        logDet.info("%s %s"%(self.id,"Close connection."))

    def executeAll(self):
        self.openConn()

        for n,qry in enumerate(self.queries):
            try:
                cursor = self.cnxn.cursor()
                logTim.info("%s|%s|beg"%(self.id, self.queryid[n]))
                cursor.execute(qry)
                logTim.info("%s|%s|end"%(self.id, self.queryid[n]))

            except Exception:
                cursor.rollback()
                logDet.exception("Error while running query.")

        self.closeConn()

pyODBC is used for the connection to the database. Two logs are created -- one detailed (logDet) and one which has only the timings (logTim). The User objects are stored in a list. The queries for each user are also in a list (not in a thread-safe Queue).

To simulate parallel users, I have tried a couple of different approaches:

def worker(usr):
    usr.executeAll()

Option 1: multiprocessing.Pool

pool = Pool(processes=len(users))
pool.map(worker, users)

Option 2: threading.Thread

for usr in users:
    t = Thread(target=worker, args=(usr,))
    t.start()

Both approaches work. In my test, I have tried for #users = 2,6,..,60, and each user has 4 queries. Given how the query times are captured, there should be less than a second of delay between the end of a query and beginning of next query i.e. queries should be fired one after the other. That's exactly what happens with multiprocessing but with threading, a random delay is introduced before the next query. The delay can be over a minute (see below).

Using: python3.4.1, pyodbc3.0.7; clients running code Windows 7/RHEL 6.5

I would really prefer to get this to work with threading. Is this expected in the threading approach or is there a command that I am missing? Or how can that be re-written? Thx.

Answer 1

When you use the threading-based approach, you're starting one thread per user, all the way up to 60 threads. All of those threads must fight for access to the GIL in between their I/O operations. This introduces a ton of overhead. You would probably see better results if you used a ThreadPool limited to a smaller number of threads (maybe 2 * multiprocessing.cpu_count()?), even with a higher number of users:

from multiprocessing.pool import ThreadPool
from multiprocessing import cpu_count

pool = ThreadPool(processes=cpu_count()*2)
pool.map(worker, users)

You may want to limit the number of concurrent processes you run, too, for memory-usage reasons. Starting up sixty concurrent Python processes is pretty expensive.