Drizzle Automation

Merge lp://qastaging/~kalebral-deactivatedaccount/drizzle-automation/drizzle-automation-sqlbench into lp://qastaging/drizzle-automation

  1. drizzle-automation-sqlbench
  2. Merge into trunk
Proposed by Lee Bieber
Status: Merged
Approved by: Jay Pipes
Approved revision: 92
Merged at revision: not available
Proposed branch: lp://qastaging/~kalebral-deactivatedaccount/drizzle-automation/drizzle-automation-sqlbench
Merge into: lp://qastaging/drizzle-automation
Diff against target: None lines
To merge this branch: bzr merge lp://qastaging/~kalebral-deactivatedaccount/drizzle-automation/drizzle-automation-sqlbench
Reviewer Review Type Date Requested Status
Jay Pipes Approve
Review via email: mp+10569@code.qastaging.launchpad.net
To post a comment you must log in.
Revision history for this message
Lee Bieber (kalebral-deactivatedaccount) wrote :

add in support for sql-bench

Revision history for this message
Jay Pipes (jaypipes) wrote :

Awesome work, Lee! Please do update the automation suite documentation on the wiki and let folks know about the new command! :)

Cheers!

jay

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'drizzle/automation/config/automation.cnf'
2--- drizzle/automation/config/automation.cnf 2009-07-20 22:01:45 +0000
3+++ drizzle/automation/config/automation.cnf 2009-08-17 02:54:54 +0000
4@@ -38,8 +38,19 @@
5
6 # Email recipients to receive a text regression report on completion of
7 # a sysbench run. Comma separated.
8-
9-report_email= name@example.com # drizzle-discuss@lists.launchpad.net
10+report_email= drizzle-benchmark@lists.launchpad.net
11+
12+[sqlbench]
13+
14+# configuration name for use in sysbench_config table
15+sqlbench_config_name= sysbench
16+# location of the sql bench repository
17+sqlbench_home= /home/drizzle/repos/sqlbench
18+# the script to run
19+sqlbench_script= run-all-tests
20+# Email recipients to receive a text regression report on completion of
21+# a sqlbench run. Comma separated.
22+report_email= drizzle-benchmark@lists.launchpad.net
23
24 [lcov]
25
26
27=== modified file 'drizzle/automation/lib/options.py'
28--- drizzle/automation/lib/options.py 2009-06-21 07:25:45 +0000
29+++ drizzle/automation/lib/options.py 2009-08-17 02:54:54 +0000
30@@ -35,6 +35,7 @@
31 valid_commands= [
32 'lcov'
33 , 'doxy'
34+, 'sqlbench'
35 , 'sysbench'
36 , 'sloc'
37 , 'valgrind'
38@@ -46,6 +47,7 @@
39 Where COMMAND is one of:
40
41 lcov Process LCOV code coverage
42+sqlbench Run benchmarks using sqlbench
43 sysbench Run benchmarks using sysbench
44 sloc Process SLOC coverage
45 doxy Process Doxygen source code documentation
46@@ -108,7 +110,7 @@
47 "--with-email-report"
48 , action="store_true"
49 , default= False
50- , help="SYSBENCH command only. Sends a text report to those recipients in [sysbench][report_email] configuration variable."
51+ , help="SQLBENCH/SYSBENCH command only. Sends a text report to those recipients in [sqlbench][sysbench][report_email] configuration variable."
52 )
53 parser.add_option(
54 "--log-file"
55
56=== added file 'drizzle/automation/reports/sqlbench.py'
57--- drizzle/automation/reports/sqlbench.py 1970-01-01 00:00:00 +0000
58+++ drizzle/automation/reports/sqlbench.py 2009-08-22 17:29:23 +0000
59@@ -0,0 +1,108 @@
60+#! /usr/bin/python
61+# -*- mode: c; c-basic-offset: 2; indent-tabs-mode: nil; -*-
62+# vim:expandtab:shiftwidth=2:tabstop=2:smarttab:
63+#
64+# Copyright (C) 2009 Sun Microsystems
65+#
66+# Authors:
67+#
68+# Jay Pipes <joinfu@sun.com>
69+# Lee Bieber <lee.bieber@sun.com>
70+#
71+# This file is part of the Drizzle Automation Project.
72+#
73+# The Drizzle Automation Project is free software: you can redistribute it and/or modify
74+# it under the terms of the GNU Lesser General Public License as published by
75+# the Free Software Foundation.
76+#
77+# The Drizzle Automation Project is distributed in the hope that it will be useful,
78+# but WITHOUT ANY WARRANTY; without even the implied warranty of
79+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
80+# GNU Lesser General Public License for more details.
81+#
82+# You should have received a copy of the GNU Lesser General Public License
83+# along with The Drizzle Automation Project (see COPYING.LESSER). If not,
84+# see <http://www.gnu.org/licenses/>.
85+
86+"""Various reports on sqlbench command results"""
87+
88+import os
89+from drizzle.automation.lib import db
90+import socket
91+import commands
92+
93+def getSysbenchRegressionReport(working_dir, run_date, server_name, bzr_branch, bzr_revision):
94+ """Returns a textual report of the regression over a series of runs"""
95+
96+ # Find the revision comment from BZR
97+ os.chdir(working_dir)
98+ (retcode, rev_comment_output)= commands.getstatusoutput("bzr log -r-1 -n0 --line")
99+
100+ # Output from above command looks like this:
101+ # jpipes@serialcoder:~/repos/drizzle/trunk-sysbench-r1046$ bzr log -r-1 -n0 --line
102+ # 1046: Brian Aker 2009-05-31 [merge] Merge Jay.
103+ # 1039.2.9: Jay Pipes 2009-05-31 Tiny cleanups
104+ # 1039.2.8: Jay Pipes 2009-05-31 Yet more indentation and style cleanup
105+ # 1039.2.7: Jay Pipes 2009-05-31 Yet more style and indentation cleanups.
106+ # 1039.2.6: Jay Pipes 2009-05-31 No code changes...only indentation and style cleanup.
107+ # 1039.2.5: Jay Pipes 2009-05-31 Style cleanups and moves JOIN_TAB definition out into its own header.
108+ # 1039.2.4: Jay Pipes 2009-05-31 Tiny indentation cleanup.
109+ # 1039.2.3: Jay Pipes 2009-05-31 Phase 3 of refactoring JOIN
110+ # 1039.2.2: Jay Pipes 2009-05-31 Phase 2 of JOIN refactoring.
111+
112+ comment_lines= rev_comment_output.split("\n")
113+ rev_comment= comment_lines[0]
114+ if len(comment_lines) > 1:
115+ full_commentary= "\n".join(comment_lines[1:])
116+ else:
117+ full_commentary= None
118+
119+ rev_range= range(int(bzr_revision) - 3, int(bzr_revision) + 1)
120+ revision_versions= ""
121+ for rev in rev_range:
122+ revision_versions= revision_versions + "'" + bzr_branch + '-' + str(rev) + "',"
123+ # Cut off the last comma...
124+ revision_versions= revision_versions[0:-1]
125+
126+ sql= """SELECT r.version, i.operation_name, i.seconds, i.usr, i.sys, i.cpu
127+ FROM sysbench_config c
128+ NATURAL JOIN sysbench_runs r
129+ NATURAL JOIN sqlbench_run_iterations i
130+ WHERE r.server = '%s'
131+ AND r.version IN (%s)
132+ GROUP BY i.operation_name, r.version
133+ ORDER BY i.operation_name, r.version
134+ """ % (
135+ server_name
136+ , revision_versions
137+ )
138+
139+ report_text= """==================================================================
140+SQLBENCH REGRESSION REPORT
141+==================================================================
142+MACHINE: %s
143+RUN DATE: %s
144+VERSION: %s
145+REVISION: %d
146+COMMENT: %s
147+==================================================================
148+""" % (
149+ socket.gethostname()
150+ , run_date
151+ , bzr_branch
152+ , int(bzr_revision)
153+ , rev_comment
154+)
155+
156+ # Calculate deviations?
157+
158+ results= db.get_select(sql)
159+ for result in results:
160+ report_text= report_text + "\t".join(result) + "\n"
161+ report_text= report_text + "=================================================================="
162+ if full_commentary:
163+ report_text= report_text + """
164+FULL REVISION COMMENTARY:
165+
166+%s""" % full_commentary
167+ return report_text
168
169=== modified file 'drizzle/automation/runner.py'
170--- drizzle/automation/runner.py 2009-06-22 19:27:11 +0000
171+++ drizzle/automation/runner.py 2009-08-17 02:54:54 +0000
172@@ -128,6 +128,8 @@
173 from drizzle.automation.sloc import run as runner
174 elif command == 'lcov':
175 from drizzle.automation.lcov import run as runner
176+ elif command == 'sqlbench':
177+ from drizzle.automation.sqlbench import run as runner
178 elif command == 'sysbench':
179 from drizzle.automation.sysbench import run as runner
180 elif command in ('doxy','doxygen'):
181
182=== added directory 'drizzle/automation/sqlbench'
183=== added file 'drizzle/automation/sqlbench/__init__.py'
184--- drizzle/automation/sqlbench/__init__.py 1970-01-01 00:00:00 +0000
185+++ drizzle/automation/sqlbench/__init__.py 2009-08-17 02:54:54 +0000
186@@ -0,0 +1,31 @@
187+#! /usr/bin/python
188+# -*- mode: c; c-basic-offset: 2; indent-tabs-mode: nil; -*-
189+# vim:expandtab:shiftwidth=2:tabstop=2:smarttab:
190+#
191+# Copyright (C) 2009 Sun Microsystems
192+#
193+# Authors:
194+#
195+# Jay Pipes <joinfu@sun.com>
196+# Lee Bieber <kalebral@gmail.com>
197+#
198+# This file is part of the Drizzle Automation Project.
199+#
200+# The Drizzle Automation Project is free software: you can redistribute it and/or modify
201+# it under the terms of the GNU Lesser General Public License as published by
202+# the Free Software Foundation.
203+#
204+# The Drizzle Automation Project is distributed in the hope that it will be useful,
205+# but WITHOUT ANY WARRANTY; without even the implied warranty of
206+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
207+# GNU Lesser General Public License for more details.
208+#
209+# You should have received a copy of the GNU Lesser General Public License
210+# along with The Drizzle Automation Project (see COPYING.LESSER). If not,
211+# see <http://www.gnu.org/licenses/>.
212+
213+"""The Drizzle Automation Project Library - Benchmarking"""
214+
215+__copyright__= "Copyright 2009 Sun Microsystems"
216+
217+__all__= ['config', 'run']
218
219=== added file 'drizzle/automation/sqlbench/run.py'
220--- drizzle/automation/sqlbench/run.py 1970-01-01 00:00:00 +0000
221+++ drizzle/automation/sqlbench/run.py 2009-08-22 05:03:28 +0000
222@@ -0,0 +1,256 @@
223+#! /usr/bin/python
224+# -*- mode: c; c-basic-offset: 2; indent-tabs-mode: nil; -*-
225+# vim:expandtab:shiftwidth=2:tabstop=2:smarttab:
226+#
227+# Copyright (C) 2009 Sun Microsystems
228+#
229+# Authors:
230+#
231+# Jay Pipes <joinfu@sun.com>
232+# Lee Bieber <lee.bieber@sun.com>
233+#
234+# This file is part of the Drizzle Automation Project.
235+#
236+# The Drizzle Automation Project is free software: you can redistribute it and/or modify
237+# it under the terms of the GNU Lesser General Public License as published by
238+# the Free Software Foundation.
239+#
240+# The Drizzle Automation Project is distributed in the hope that it will be useful,
241+# but WITHOUT ANY WARRANTY; without even the implied warranty of
242+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
243+# GNU Lesser General Public License for more details.
244+#
245+# You should have received a copy of the GNU Lesser General Public License
246+# along with The Drizzle Automation Project (see COPYING.LESSER). If not,
247+# see <http://www.gnu.org/licenses/>.
248+
249+"""Script to automate running and processing of benchmarks."""
250+
251+import sys
252+import socket
253+import os
254+import os.path
255+from drizzle.automation.lib import logging
256+import commands
257+import re
258+import time
259+import datetime
260+
261+def log_sysbench_run(run_id, config_id, server_name, server_version, run_date):
262+ """Creates a new run record in the database for this run"""
263+ sql= """INSERT INTO sysbench_runs (
264+ run_id
265+ , config_id
266+ , server
267+ , version
268+ , run_date
269+ ) VALUES (%d, %d, '%s', '%s', '%s')
270+ """ % (
271+ run_id
272+ , config_id
273+ , server_name
274+ , server_version
275+ , run_date)
276+ from drizzle.automation.lib import db
277+
278+ db.execute_sql(sql)
279+
280+# Stores data for a sqlbench iteration in the database
281+# input file has a section that looks like the following:
282+# Operation seconds usr sys cpu tests
283+# alter_table_add 13.00 0.01 0.00 0.01 100
284+# alter_table_drop 12.00 0.00 0.00 0.00 91
285+# connect 2.00 0.84 0.36 1.20 2000
286+# .......
287+# We want to start grabbing lines at the alter_table_add line until the end of the file
288+#
289+def log_sqlbench_iteration(run_id, server_name, bzr_revision, port):
290+
291+ logging.info("Logging %s revision %s results to database for run %d" % (server_name, bzr_revision, run_id))
292+
293+ # file name is in the sqlbench_home/results-bzr_revision directory
294+ # file name is "RUN-server_name-bzr_revision-hostname"
295+ # example - results-1125/RUN-drizzled-1125-orisndriz05
296+ infilename= ("results-%s/RUN-%s-%s-%s" % (bzr_revision, server_name, bzr_revision, port))
297+ fileHandle= open(infilename)
298+ inlines= fileHandle.readlines()
299+ found_block= False
300+
301+ for inline in inlines:
302+ # look for first line that starts with alter_table_add
303+ if inline.startswith("alter_table_add"):
304+ found_block= True
305+ if found_block == False:
306+ continue
307+ else:
308+ # parse line
309+ fields= inline.split()
310+ # insert into database
311+ sql= ""
312+ sql= sql + """
313+ INSERT INTO sqlbench_run_iterations (run_id, operation_name, seconds, usr, sys, cpu, tests) VALUES (%d,'%s', %0.2f, %0.2f, %0.2f, %0.2f, %d); """ % (run_id, fields[0], float(fields[1]), float(fields[2]), float(fields[3]), float(fields[4]), int(fields[5]))
314+
315+ from drizzle.automation.lib import db
316+ db.execute_sql(sql)
317+
318+ fileHandle.close()
319+
320+def getConfigId(sqlbench_config_name):
321+ """Returns the integer ID of the sqlbench configuration used in this run."""
322+
323+ # If we have not already done so, we query the local DB for the ID
324+ # matching this sqlbench config name. If none is there, we insert
325+ # a new record in the sysbench_config table and return the newly generated
326+ # identifier.
327+
328+ sql= "SELECT config_id FROM sysbench_config WHERE name = '%s'" % sqlbench_config_name
329+
330+ from drizzle.automation.lib import db
331+ result= db.get_select(sql)
332+
333+ if len(result) == 0:
334+ # Insert a new record for this config and return the new ID...
335+ sql= "INSERT INTO sysbench_config (config_id, name) VALUES (NULL, '%s')" % sqlbench_config_name
336+ db.execute_sql(sql)
337+ return getConfigId(sqlbench_config_name)
338+ else:
339+ config_id= int(result[0][0])
340+
341+ return config_id
342+
343+def getNextRunId():
344+ """Returns a new run identifier from the database.
345+ The run ID is used in logging the results of the run iterations."""
346+ sql= "SELECT MAX(run_id) as new_run_id FROM sysbench_runs"
347+
348+ from drizzle.automation.lib import db
349+ result= db.get_select(sql)
350+ if result[0][0] == "NULL":
351+ new_run_id= 1
352+ else:
353+ new_run_id= int(result[0][0]) + 1
354+
355+ return new_run_id
356+
357+def prepare_for_run(client):
358+ """Prepares the sqlbench run. A client instance is passed to prepare()
359+in order for the sqlbench instance to create its necessary schema."""
360+
361+ logging.info("Dropping any test database previously setup.")
362+ if not client.execute("DROP DATABASE IF EXISTS test"):
363+ return False
364+
365+ logging.info("Creating test database.")
366+ if not client.execute("CREATE DATABASE test"):
367+ return False
368+
369+ return True
370+
371+def run_sqlbench(sqlbench_script, sqlbench_options):
372+ """Runs sqlbench for the supplied client Returns the text output on a successful run."""
373+ (retcode, output)= commands.getstatusoutput("%s %s " % (sqlbench_script, sqlbench_options))
374+ if retcode != 0:
375+ logging.error("Failed to run sqlbench. Got error:\n%s." % (output))
376+ sys.exit(1)
377+ else:
378+ return output
379+
380+def execute(processing_mode, variables):
381+ # Set/verify some required variables, depending on the server we are benchmarking.
382+
383+ working_dir= variables['working_dir']
384+ run_date= datetime.datetime.now().isoformat()
385+ server_name= variables['server']
386+ # need the location where the sqlbench repository is located
387+ sqlbench_home= variables['sqlbench']['sqlbench_home']
388+ # the name of the script to run
389+ sqlbench_script= variables['sqlbench']['sqlbench_script']
390+ # sqlbench_config_name is set to "sqlbench" for now
391+ sqlbench_config_name= variables['sqlbench']['sqlbench_config_name']
392+ os.chdir(working_dir)
393+
394+ # For BZR mode, the "version" of the server (used in identifying the
395+ # command run when logging to the database) is the branch and revision number.
396+ #
397+ # For MySQL Sandbox mode, the version is the text after the "msb_" prefix
398+ # on the sandbox.
399+ if processing_mode == 'bzr':
400+ server_version= "%s-%s" % (variables['bzr_branch'], variables['bzr_revision'])
401+ else:
402+ server_version= variables['mysql_sandbox'].split('_')[1] # Sandbox names are msb_XXXX (e.g. msb_6011 for MySQL 6.0.11)
403+
404+ # We must instantiate a server and a client adapter which
405+ # are used to start/stop the server and to query it. Depending
406+ # on the type of server we are testing/processing, we may also
407+ # instantiate a "Builder" object, which is used to build the
408+ # server from source...
409+ if server_name in ['drizzled','drizzle']:
410+
411+ from drizzle.automation.builder.drizzled import DrizzledBzrBuilder as builder_adapter
412+ from drizzle.automation.client.drizzledb import DrizzleClient as client_adapter
413+
414+ # Here, depending on the revision, we use a different adapter
415+ # for the older drizzleadmin-controlled server...
416+ if str(variables['bzr_revision']) != 'last:1' and int(variables['bzr_revision']) < 950:
417+ from drizzle.automation.server.olddrizzled import DrizzledServer as server_adapter
418+ else:
419+ from drizzle.automation.server.drizzled import DrizzledServer as server_adapter
420+
421+ elif server_name in ['mysqld','mysql']:
422+ from drizzle.automation.builder.mysqld import MySQLBzrBuilder as builder_adapter
423+ from drizzle.automation.client.mysql import MySQLClient as client_adapter
424+ from drizzle.automation.server.mysqld import MySQLdServer as server_adapter
425+
426+ else:
427+ logging.error("Not yet implemented!")
428+ sys.exit(1)
429+
430+ server= server_adapter(variables['working_dir'])
431+ client= client_adapter(variables['working_dir'], server.getPort())
432+
433+ if variables['no_build'] is False:
434+ builder= builder_adapter(variables['working_dir'])
435+ builder.build(variables['force_build'])
436+
437+ server.stopAll()
438+
439+ # clean data directory (var)
440+ server.clear()
441+
442+ # Start up the server...
443+ server.start()
444+
445+ # create test database
446+ if not prepare_for_run(client):
447+ server.stop()
448+ sys.exit(1)
449+
450+ run_id= getNextRunId()
451+ config_id= getConfigId(sqlbench_config_name)
452+
453+ os.chdir(sqlbench_home)
454+ logging.info("Running sqlbench --server=%s --dir=results-%s --log --connect-options=port=%d --bzr-repo=%s --machine=%s" % (server_name, variables['bzr_revision'], server.getPort(), working_dir, socket.gethostname()))
455+ sqlbench_options= "--server=%s --dir=results-%s --log --connect-options=port=%d --bzr-repo=%s --machine=%s" % (server_name, variables['bzr_revision'], server.getPort(), working_dir, socket.gethostname())
456+ result= run_sqlbench(sqlbench_script, sqlbench_options)
457+
458+ if variables['no_store_db'] is False:
459+
460+ log_sysbench_run(run_id, config_id, server_name, server_version, run_date)
461+
462+ # We now log the results of this run -
463+ log_sqlbench_iteration(run_id, server_name, variables['bzr_revision'], socket.gethostname())
464+
465+ server.stop()
466+
467+ time.sleep(3)
468+
469+ # send email report
470+ if variables['with_email_report'] is True:
471+ import drizzle.automation.reports.sqlbench as reports
472+ from drizzle.automation.lib import util
473+ email_text= reports.getSysbenchRegressionReport(working_dir, run_date, server_name, variables['bzr_branch'], int(variables['bzr_revision']))
474+ logging.info("Sending email...")
475+ from_string= ('%s <drizzle-benchmark@lists.launchpad.net>' % socket.gethostname())
476+ util.mail(from_string, variables['sysbench']['report_email'], "SQLBENCH Regression Report - %s" % server_version, email_text)
477+
478+ return True
479
480=== modified file 'drizzle/automation/sysbench/run.py' (properties changed: -x to +x)
481--- drizzle/automation/sysbench/run.py 2009-06-23 02:33:46 +0000
482+++ drizzle/automation/sysbench/run.py 2009-08-22 17:29:23 +0000
483@@ -507,6 +507,7 @@
484 import drizzle.automation.reports.sysbench as reports
485 from drizzle.automation.lib import util
486 email_text= reports.getSysbenchRegressionReport(working_dir, sysbench_config, run_date, server_name, variables['bzr_branch'], int(variables['bzr_revision']))
487- util.mail('jay.pipes@sun.com', variables['sysbench']['report_email'], "SYSBENCH Regression Report - %s" % server_version, email_text)
488+ from_string= ('%s <drizzle-benchmark@lists.launchpad.net>' % socket.gethostname())
489+ util.mail(from_string, variables['sysbench']['report_email'], "SYSBENCH Regression Report - %s" % server_version, email_text)
490
491 return True
492
493=== modified file 'setup.py'
494--- setup.py 2009-06-23 21:48:41 +0000
495+++ setup.py 2009-08-21 05:33:54 +0000
496@@ -66,6 +66,7 @@
497 "drizzle.automation.server",
498 "drizzle.automation.sloc",
499 "drizzle.automation.sysbench",
500+ "drizzle.automation.sqlbench",
501 ],
502 entry_points= {
503 'console_scripts': [

Subscribers

People subscribed via source and target branches

to all changes: