Merge lp:~kalebral-deactivatedaccount/drizzle-automation/drizzleslap-stddev into lp:drizzle-automation

[Lee Bieber]

[Lee Bieber (kalebral-deactivatedaccount)]

changes to support reporting standard deviation for drizzleslap

[lp:~kalebral-deactivatedaccount/drizzle-automation/drizzleslap-stddev]

107. By lbieber <lbieber@orisndriz01> on 2009-11-15

add -drizzle-protocol-port and --mysql-protocol-port options so we can start drizzled

108. By lbieber <lbieber@orisndriz01> on 2009-11-16

queries were incorrect, need to select test_name as well as concurrency

=== modified file 'drizzle/automation/drizzleslap/run.py'
--- drizzle/automation/drizzleslap/run.py	2009-11-04 22:42:07 +0000
+++ drizzle/automation/drizzleslap/run.py	2009-11-16 06:16:09 +0000
@@ -230,7 +230,7 @@
   # send email report
   if variables['with_email_report'] is True:
     import drizzle.automation.reports.drizzleslap as reports
-    email_text= reports.getDrizzleslapReport(working_dir, run_id, run_date, server_name, variables['bzr_branch'], int(variables['bzr_revision']))
+    email_text= reports.getDrizzleslapReport(working_dir, config_name, run_id, run_date, server_name, variables['bzr_branch'], int(variables['bzr_revision']))
     logging.info("Sending email...")
     from_string= ('%s <[e-mail]>' % socket.gethostname())
     util.mail(from_string, variables['drizzleslap']['report_email'], "Drizzleslap Report - %s" % server_version, email_text)

=== modified file 'drizzle/automation/reports/drizzleslap.py'
--- drizzle/automation/reports/drizzleslap.py	2009-11-12 16:15:46 +0000
+++ drizzle/automation/reports/drizzleslap.py	2009-11-16 06:16:09 +0000
@@ -1,5 +1,5 @@
 #! /usr/bin/python
-# -*- mode: c; c-basic-offset: 2; indent-tabs-mode: nil; -*-
+# -*- mode: python; c-basic-offset: 2; indent-tabs-mode: nil; -*-
 # vim:expandtab:shiftwidth=2:tabstop=2:smarttab:
 #
 # Copyright (C) 2009 Sun Microsystems
@@ -31,7 +31,72 @@
 import socket
 import commands
 
-def getDrizzleslapReport(working_dir, run_id, run_date, server_name, bzr_branch, bzr_revision):
+def sqlQueryString(last_revs, run_id):
+  sql= """
+SELECT 
+  i.test_name
+, i.concurrency
+, format(AVG(i.total_time),3) AS total_time
+, IF (AVG(i.total_time) >= agg.avg_total_time
+  , FORMAT(CONCAT('+', ROUND(((AVG(i.total_time) - agg.avg_total_time) / agg.avg_total_time) * 100, 2), '%%') ,3)
+  , FORMAT(CONCAT('-', ROUND(((agg.avg_total_time - AVG(i.total_time)) / agg.avg_total_time) * 100, 2), '%%'),3)
+  ) as pct_diff_from_avg
+, FORMAT(ROUND((AVG(i.total_time) - agg.avg_total_time), 2),3) AS diff_from_avg
+, IF(ABS(AVG(i.total_time) - agg.avg_total_time) <= agg.stddev_total_time
+  , 'within norms'
+  , 'outside norms'
+  ) as is_normal
+, FORMAT(agg.min_total_time,3) AS min_total_time
+, FORMAT(agg.max_total_time,3) AS max_total_time
+, FORMAT(agg.avg_total_time,3) AS avg_total_time
+, FORMAT(agg.stddev_total_time,3) AS stddev_total_time
+FROM sysbench_config c
+NATURAL JOIN sysbench_runs r
+NATURAL JOIN drizzleslap_run_iterations i
+INNER JOIN (
+  SELECT
+    test_name
+  , MIN(total_time) as min_total_time
+  , MAX(total_time) as max_total_time
+  , AVG(total_time) as avg_total_time
+  , STDDEV(total_time) as stddev_total_time
+  FROM drizzleslap_run_iterations iter
+  WHERE run_id IN (%s)
+  GROUP BY test_name, concurrency
+) AS agg
+  ON i.test_name = agg.test_name
+WHERE r.run_id = %d
+GROUP BY i.test_name, i.concurrency
+ORDER BY i.test_name, i.concurrency
+      """ % (
+        ",".join(last_revs)
+      , run_id
+      )
+
+  return db.get_select(sql)
+
+def printResults(results, report_text):
+  """ Print out the sql results for a query """
+
+  for result in results:
+    report_text= report_text + "\t".join(result) + "\n"
+  return report_text
+
+def printHeader(string, report_text):
+  """Returns header information for each section """
+  report_text= report_text + """
+=======================================================================================
+
+TRENDING OVER %s
+
+Test\tCon\tTime\t%% Diff from Avg   Diff\tNorm?\tMin\tMax\tAvg\tSTD
+=======================================================================================
+""" % (string)
+
+  return report_text
+
+
+def getDrizzleslapReport(working_dir, drizzleslap_config_name, run_id, run_date, server_name, bzr_branch, bzr_revision):
   """Returns a textual report of the results over a series of runs"""
 
   # Find the revision comment from BZR
@@ -57,75 +122,45 @@
   else:
     full_commentary= None
 
-  rev_range= range(int(bzr_revision) - 4, int(bzr_revision) + 1)
-  revision_versions= ""
-  for rev in rev_range:
-    revision_versions= revision_versions + "'" + bzr_branch + '-' + str(rev) + "',"
-  # Cut off the last comma...
-  revision_versions= revision_versions[0:-1]
-
   sql= """
 SELECT 
-  r.version
-, r.run_id
-, r.run_date
-, i.test_name
-, i.concurrency
-, i.total_time
-, IF (i.total_time >= agg.avg_time
-  , CONCAT('-', ROUND(((i.total_time - agg.avg_time) / agg.avg_time) * 100, 2), '%') 
-  , CONCAT('+', ROUND(((agg.avg_time - i.total_time) / agg.avg_time) * 100, 2), '%')
-  ) as pct_change 
-, ROUND((i.total_time - agg.avg_time), 2) as diff_from_avg
-, IF(ABS(i.total_time - agg.avg_time) <= agg.stddev_time
-  , 'within norms'
-  , 'outside norms'
-  ) as normal
-, agg.min_time
-, agg.max_time
-, agg.avg_time
-, agg.stddev_time
+  run_id
 FROM sysbench_config c
 NATURAL JOIN sysbench_runs r
-NATURAL JOIN drizzleslap_run_iterations i
-INNER JOIN (
-  SELECT 
-    test_name
-  , concurrency
-  , MIN(total_time) as min_time
-  , MAX(total_time) as max_time
-  , AVG(total_time) as avg_time
-  , STDDEV(total_time) as stddev_time
-  FROM drizzleslap_run_iterations
-  NATURAL JOIN sysbench_runs runs
-  WHERE runs.server = '%s'
-  GROUP BY test_name, concurrency
-) AS agg
-ON i.test_name = agg.test_name
-AND i.concurrency = agg.concurrency
-WHERE r.server = '%s' 
-AND r.version IN (%s)
-GROUP BY r.version, r.run_id, i.test_name, i.concurrency
-ORDER BY i.test_name, r.version DESC, r.run_id DESC, i.concurrency
-      """ % (
-      server_name
+WHERE c.name = '%s'
+AND r.server = '%s'
+AND r.version LIKE '%s%%'
+AND r.run_id <= %d
+ORDER BY run_id DESC
+LIMIT 20
+""" % (
+        drizzleslap_config_name
       , server_name
-      , revision_versions
+      , bzr_branch
+      , run_id
       )
+  results= db.get_select(sql)
+
+  last_5_revs= []
+  last_20_revs= []
+  x= 0
+  for result in results:
+    cur_run_id= int(result[0])
+    if x < 5:
+      last_5_revs.append(str(cur_run_id))
+    last_20_revs.append(str(cur_run_id))
+    x= x + 1
 
   report_text= """=======================================================================================
-REGRESSION REPORT
+DRIZZLESLAP REPORT
 =======================================================================================
 MACHINE:  %s
 RUN ID:   %d
 RUN DATE: %s
-SERVER: %s
+SERVER:   %s
 VERSION:  %s
 REVISION: %d
 COMMENT:  %s
-=======================================================================================
-Version\t\tRun ID\t\tDate\t\tTest\tConc\tTime\t% Chg\tDiff\tNorm?\tMin\tMax\tAvg\tSTD
-=======================================================================================
 """ % (
     socket.gethostname()
   , run_id
@@ -136,14 +171,69 @@
   , rev_comment
 )
 
-  # Calculate deviations?
+  report_text= printHeader('LAST 5 runs', report_text)
+  results= sqlQueryString(last_5_revs, run_id)
+  report_text= printResults(results, report_text)
+
+  report_text= printHeader('LAST 20 runs', report_text)
+  results= sqlQueryString(last_20_revs, run_id)
+  report_text= printResults(results, report_text)
+
+  report_text= printHeader('ALL runs', report_text)
+
+  sql= """
+SELECT 
+  i.test_name
+, i.concurrency
+, FORMAT(AVG(i.total_time),3) AS total_time
+, IF (AVG(i.total_time) >= agg.avg_total_time
+  , FORMAT(CONCAT('+', ROUND(((AVG(i.total_time) - agg.avg_total_time) / agg.avg_total_time) * 100, 2), '%%'),3) 
+  , FORMAT(CONCAT('-', ROUND(((agg.avg_total_time - AVG(i.total_time)) / agg.avg_total_time) * 100, 2), '%%'),3)
+  ) as pct_diff_from_avg
+, FORMAT(ROUND((AVG(i.total_time) - agg.avg_total_time), 2),3) as diff_from_avg
+, IF(ABS(AVG(i.total_time) - agg.avg_total_time) <= agg.stddev_total_time
+  , 'within norms'
+  , 'outside norms'
+  ) as is_normal
+, FORMAT(agg.min_total_time,3) AS min_total_time
+, FORMAT(agg.max_total_time,3) AS max_total_time
+, FORMAT(agg.avg_total_time,3) AS avg_total_time
+, FORMAT(agg.stddev_total_time,3) AS stddev_total_time
+FROM sysbench_config c
+NATURAL JOIN sysbench_runs r
+NATURAL JOIN drizzleslap_run_iterations i
+INNER JOIN (
+  SELECT
+    iter.test_name
+  , MIN(total_time) as min_total_time
+  , MAX(total_time) as max_total_time
+  , AVG(total_time) as avg_total_time
+  , STDDEV(total_time) as stddev_total_time
+  FROM sysbench_config conf
+  NATURAL JOIN sysbench_runs runs
+  NATURAL JOIN drizzleslap_run_iterations iter
+  WHERE conf.name = '%s'
+  AND runs.server = '%s'
+  AND runs.version LIKE '%s%%'
+  GROUP BY iter.test_name, iter.concurrency
+) AS agg
+  ON i.test_name = agg.test_name
+WHERE r.run_id = %d
+GROUP BY i.test_name, i.concurrency
+ORDER BY i.test_name, i.concurrency
+      """ % (
+        drizzleslap_config_name
+      , server_name
+      , bzr_branch
+      , run_id
+      )
 
   results= db.get_select(sql)
-  for result in results:
-    report_text= report_text + "\t".join(result) + "\n"
-  report_text= report_text + "=================================================================="
+  report_text= printResults(results, report_text)
+
   if full_commentary:
     report_text= report_text + """
+=======================================================================================
 FULL REVISION COMMENTARY:
 
 %s""" % full_commentary

=== modified file 'drizzle/automation/reports/sqlbench.py'
--- drizzle/automation/reports/sqlbench.py	2009-11-07 22:31:48 +0000
+++ drizzle/automation/reports/sqlbench.py	2009-11-16 06:16:09 +0000
@@ -102,8 +102,6 @@
   , rev_comment
 )
 
-  # Calculate deviations?
-
   results= db.get_select(sql)
   for result in results:
     report_text= report_text + "\t".join(result) + "\n"

=== modified file 'drizzle/automation/reports/sysbench.py'
--- drizzle/automation/reports/sysbench.py	2009-11-13 19:50:26 +0000
+++ drizzle/automation/reports/sysbench.py	2009-11-16 06:16:09 +0000
@@ -44,10 +44,6 @@
   # 1039.2.8: Jay Pipes 2009-05-31 Yet more indentation and style cleanup
   # 1039.2.7: Jay Pipes 2009-05-31 Yet more style and indentation cleanups.
   # 1039.2.6: Jay Pipes 2009-05-31 No code changes...only indentation and style cleanup.
-  # 1039.2.5: Jay Pipes 2009-05-31 Style cleanups and moves JOIN_TAB definition out into its own header.
-  # 1039.2.4: Jay Pipes 2009-05-31 Tiny indentation cleanup.
-  # 1039.2.3: Jay Pipes 2009-05-31 Phase 3 of refactoring JOIN
-  # 1039.2.2: Jay Pipes 2009-05-31 Phase 2 of JOIN refactoring.
 
   comment_lines= rev_comment_output.split("\n")
   rev_comment= comment_lines[0]

=== modified file 'drizzle/automation/server/drizzled.py'
--- drizzle/automation/server/drizzled.py	2009-10-27 19:49:11 +0000
+++ drizzle/automation/server/drizzled.py	2009-11-16 06:16:09 +0000
@@ -81,8 +81,9 @@
       start_cmd= self._profiler.getStartCmdPrefix() + " "
     else:
       start_cmd= ""
-    start_cmd= start_cmd + "%s --port=%d --basedir=%s --datadir=%s %s > error.log 2>&1 &" % (os.path.join(self._basedir, "drizzled", "drizzled")
+    start_cmd= start_cmd + "%s --drizzle-protocol-port=%d --mysql-protocol-port=%d --basedir=%s --datadir=%s %s > error.log 2>&1 &" % (os.path.join(self._basedir, "drizzled", "drizzled")
                                                                 , self._port
+                                                                , self._port+1
                                                                 , self._basedir
                                                                 , self._datadir
                                                                 , start_option_string)