#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb import sys from timeit import Timer def benchmark ( cur ) : """ execute query """ cur. execute ( "SELECT * FROM Writers" ) rows = cur. fetchall ( ) # for row in rows: # print row def create_fixtures ( ) : """ Create dummy datas for test """ with con: cur = con. cursor ( ) cur. execute ( "DROP TABLE IF EXISTS Writers" ) cur. execute ( "CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))" ) cur. execute ( "INSERT INTO Writers(Name) VALUES('Jack London')" ) cur. execute ( "INSERT INTO Writers(Name) VALUES('Honore de Balzac')" ) cur. execute ( "INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')" ) cur. execute ( "INSERT INTO Writers(Name) VALUES('Emile Zola')" ) cur. execute ( "INSERT INTO Writers(Name) VALUES('Adolf Hitler')" ) cur. execute ( "INSERT INTO Writers(Name) VALUES('Ronald McDonalds')" ) cur. execute ( "INSERT INTO Writers(Name) VALUES('Mamie Nova')" ) cur. execute ( "INSERT INTO Writers(Name) VALUES('Sam & Max')" ) def set_query_cache ( query_cache_type = 1 ) : """ Set query cache 0 : Don't cache results in or retrieve results from the query cache. 1 : Cache all query results except for those that begin with SELECT S_NO_CACHE. 2 : Cache results only for queries that begin with SELECT SQL_CACHE """ with con: cur = con. cursor ( ) cur. execute ( "SET GLOBAL query_cache_size = 16777216" ) cur. execute ( "SET SESSION query_cache_type = %s" % str ( query_cache_type ) ) def start_benchmark ( nb_queries = 1000 , cached_queries = 1 ) : """ """ # use cached query benchmark print "Starting benchmark: %s reads - query cache type = %s" % ( nb_queries , cached_queries ) # set query cache set_query_cache ( cached_queries ) # run the test t = Timer ( "queries()" , "from __main__ import queries" ) print t. timeit ( number = nb_queries ) if __name__ == '__main__' : # connect to db con = mdb. connect ( 'localhost' , 'root' , '12345' , 'test' ) ; # create dummy datas create_fixtures ( ) with con: cur = con. cursor ( ) queries = lambda : benchmark ( cur = cur ) # launch benchmark start_benchmark ( nb_queries = 10000 , cached_queries = 1 ) start_benchmark ( nb_queries = 10000 , cached_queries = 0 ) con. close ( )