1. Mike Steder
  2. txMysql

Source

txMysql / benchmarks / insert.py

"""
This script does a bunch of really stupid inserts into
the following table:

create table counter (
  id int not null auto_increment primary key, 
  count int
);

"""

import sys
import timeit


from txmysql import settings


TRANSACTION = False
EXECUTEMANY = True


def pymysql():
    import pymysql as db
    startTime = timeit.default_timer()
    con = db.connect(host=settings.HOSTNAME,
                     user=settings.USERNAME,
                     db=settings.DATABASE,
                     passwd="")
    with con:
        cur = con.cursor()
        if not EXECUTEMANY:
            for x in xrange(settings.ITERATIONS):
                if TRANSACTION:
                    cur.execute("begin;")
                cur.execute(
                    "insert into counter set count = %s;", (x,)
                    )
                if TRANSACTION:
                    cur.execute("commit;")
        else:
            values = [(x,) for x in xrange(settings.ITERATIONS)]
            cur.executemany("insert into counter set count = %s;", values)
                    
    endTime = timeit.default_timer()
    elapsedTime = endTime - startTime
    print "PyMySQL inserted %s times in %s"%(settings.ITERATIONS, elapsedTime)
    insertsPerSecond = settings.ITERATIONS / elapsedTime
    print "PyMySQL inserted at a rate of %s/s"%(insertsPerSecond,)
    avgInsertTime = elapsedTime / settings.ITERATIONS
    print "PyMySQL average insert time %s"%(avgInsertTime,)


def mysqldb():
    import MySQLdb as db
    startTime = timeit.default_timer()
    con = db.connect(host=settings.HOSTNAME,
                     user=settings.USERNAME,
                     db=settings.DATABASE,
                     use_unicode=settings.USE_UNICODE)
    with con:
        cur = con.cursor()
        if not EXECUTEMANY:
            for x in xrange(settings.ITERATIONS):
                if TRANSACTION:
                    cur.execute("begin;")
                cur.execute(
                    "insert into counter set count = %s;", (x,)
                    )
                if TRANSACTION:
                    cur.execute("commit;")
        else:
            values = [(x,) for x in xrange(settings.ITERATIONS)]
            cur.executemany("insert into counter set count = %s;", values)
                    
    endTime = timeit.default_timer()
    elapsedTime = endTime - startTime
    print "MySQLdb inserted %s times in %s"%(settings.ITERATIONS, elapsedTime)
    insertsPerSecond = settings.ITERATIONS / elapsedTime
    print "MySQLdb inserted at a rate of %s/s"%(insertsPerSecond,)
    avgInsertTime = elapsedTime / settings.ITERATIONS
    print "MySQLdb average insert time %s"%(avgInsertTime,)


def oursql():
    import oursql as db
    startTime = timeit.default_timer()
    con = db.connect(host=settings.HOSTNAME,
                     user=settings.USERNAME,
                     db=settings.DATABASE,
                     use_unicode=settings.USE_UNICODE)
    with con:
        cur = con.cursor()
        if not EXECUTEMANY:
            for x in xrange(settings.ITERATIONS):
                if TRANSACTION:
                    cur.execute("begin;")
                cur.execute(
                    "insert into counter set count = ?;", (x,)
                    )
                if TRANSACTION:
                    cur.execute("commit;")
        else:
            values = [(x,) for x in xrange(settings.ITERATIONS)]
            cur.executemany("insert into counter set count = ?;", values)

    endTime = timeit.default_timer()
    elapsedTime = endTime - startTime
    print "Oursql inserted %s times in %s"%(settings.ITERATIONS, elapsedTime)
    insertsPerSecond = settings.ITERATIONS / elapsedTime
    print "Oursql inserted at a rate of %s/s"%(insertsPerSecond,)
    avgInsertTime = elapsedTime / settings.ITERATIONS
    print "Oursql average insert time %s"%(avgInsertTime,)


def pymongo():
    import pymongo
    startTime = timeit.default_timer()
    con = pymongo.Connection(settings.HOSTNAME)
    db = con[settings.DATABASE]

    collection = db.counter
    if not EXECUTEMANY:
        for x in xrange(settings.ITERATIONS):
            counter = {"count": x}
            collection.insert(counter)
    else:
        counters = [{"count":x} for x in xrange(settings.ITERATIONS)]
        collection.insert(counters)
        
    con.disconnect()
    endTime = timeit.default_timer()
    elapsedTime = endTime - startTime
    print "Pymongo inserted %s times in %s"%(settings.ITERATIONS, elapsedTime)
    consPerSecond = settings.ITERATIONS / elapsedTime
    print "Pymongo inserted at a rate of %s/s"%(consPerSecond,)
    avgConTime = elapsedTime / settings.ITERATIONS
    print "Pymongo average insert time %s"%(avgConTime,)



if __name__=="__main__":
    if "mysqldb" in sys.argv:
        mysqldb()
    if "oursql" in sys.argv:
        oursql()
    if "pymongo" in sys.argv:
        pymongo()
    if "pymysql" in sys.argv:
        pymysql()