bzr commit into mysql-falcon branch (vvaintroub:238)

[Vladislav Vaintroub]

#At bzr+ssh://bk-internal.mysql.com/bzrroot/mysql-falcon/

  238 Vladislav Vaintroub	2008-07-18
      sysbench (kinda) in python, so it can works on Windows.
      (python MySQLdb has to be is installed)
      work in progress, currently accepts no parameters and 
      runs 10 minute workload against local database on 9306 port
added:
  sysbench.py

=== added file 'sysbench.py'
--- a/sysbench.py	1970-01-01 00:00:00 +0000
+++ b/sysbench.py	2008-07-18 19:42:30 +0000
@@ -0,0 +1,171 @@
+import sys
+import random
+import MySQLdb
+from time import time
+from threading import Thread
+from random import Random
+
+#benchmark parameter
+RECORDS  = 1000
+THREADS  = 20
+DURATION = 600
+#database parameter
+PORT     = 9306
+
+
+def connect():
+  global PORT
+  conn = MySQLdb.connect (host = "localhost",
+                          user = "root",
+                          passwd = "",
+                          db = "test",
+                          port= PORT)
+  
+  return conn
+
+def prepare(N):
+  conn = connect()
+  cursor = conn.cursor()
+  cursor.execute ("""
+  SET GLOBAL general_log = 'OFF';
+  """)
+  cursor.execute ("DROP TABLE IF EXISTS sbtest")
+  cursor.execute ("""
+  CREATE TABLE sbtest (
+          id int(10) unsigned NOT NULL auto_increment,
+          k int(10) unsigned NOT NULL default '0',
+          c char(120) NOT NULL default '',
+          pad char(60) NOT NULL default '',
+          PRIMARY KEY  (id),
+          KEY k (k)
+      ) engine=falcon
+  """)
+  for i in range(N): 
+    cursor.execute ("""
+           INSERT INTO sbtest (k,c,pad)
+           VALUES(%s,'abc12345678','126546125341652416524165243')
+             
+    """,i)
+  cursor.close ()
+  conn.close ()
+
+g_reads  = 0
+g_writes = 0
+g_transactions    = 0
+g_deadlocks = 0
+rand = Random(1234)
+
+class client(Thread):
+  def __init__ (self,my_id):
+      Thread.__init__(self)
+      self.my_id = my_id
+      
+  def run(self):
+    global g_reads
+    global g_writes
+    global g_transactions 
+    global g_deadlocks
+    global rand
+    global RECORDS
+    
+    conn = connect()
+    cursor = conn.cursor()
+    N = rand.randint(0,RECORDS-1)
+    M = rand.randint(0,RECORDS-1)
+    if(N > M):
+      N,M = M,N
+    cursor.execute ("""
+      set autocommit=0
+      """)
+    cursor.execute ("""
+      set falcon_consistent_read=off
+      """)
+    
+  
+    while (time() < endtime):
+      try:
+        cursor.execute ("""
+        SELECT c FROM sbtest WHERE id=%s
+        """,N)
+        g_reads = g_reads + 1
+        #cursor.fetchone ()
+
+        cursor.execute ("""
+        SELECT c FROM sbtest WHERE id BETWEEN %s AND %s 
+        """,(N,M))
+        g_reads = g_reads + 1
+        #cursor.fetchone ()
+
+        
+        cursor.execute("""
+        SELECT c FROM sbtest WHERE id between %s and %s ORDER BY c
+        """,(N,M))
+        g_reads = g_reads + 1
+        #cursor.fetchall ()
+
+        cursor.execute("""
+        SELECT DISTINCT c FROM sbtest WHERE id BETWEEN %s and %s ORDER BY c
+        """,(N,M))
+        g_reads = g_reads + 1
+        #cursor.fetchall ()
+        
+        cursor.execute("""
+          UPDATE sbtest SET k=k+1 WHERE id=%s
+        """,(N))
+        g_writes = g_writes + 1
+        
+        cursor.execute("""
+         UPDATE sbtest SET c='%s' WHERE id=%s
+        """,(M,N))
+        g_writes = g_writes + 1
+        
+        #N = rand.randint(0,9999)
+        N = rand.randint(0,RECORDS-1)
+        cursor.execute("""
+        DELETE FROM sbtest WHERE id=%s
+        """, N)
+        g_writes = g_writes + 1
+        
+        cursor.execute("""
+          INSERT INTO sbtest (id,k,c,pad)
+          VALUES(%s,1,'abc12345678','126546125341652416524165243')
+        """, N)
+        g_writes = g_writes + 1
+   
+      except MySQLdb.IntegrityError, message:
+        conn.rollback()
+        rc = message[0]
+        if (rc  != 1062):
+          raise message
+        else:
+          g_deadlocks = g_deadlocks + 1
+      except MySQLdb.OperationalError, message:
+        conn.rollback()
+        rc = message[0]
+        if (rc != 1213 and rc != 1205 and  rc != 1020):
+          raise message
+        else:
+          g_deadlocks = g_deadlocks + 1
+          
+      g_transactions = g_transactions +1 
+      conn.commit()
+    cursor.close()
+    conn.close()
+
+prepare(RECORDS)
+clientlist = []
+duration = DURATION
+endtime = time() + duration
+for i in range(THREADS):
+  current = client(i)
+  clientlist.append(current);
+  current.start()
+
+for c in clientlist:
+  c.join()
+print 'Read  operations=%d' % g_reads
+print 'Write operations=%d' % g_writes
+print 'deadlocks       =%d' % g_deadlocks
+print 'tps             =%d' % (g_transactions/duration)
+  
+