<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>[15726] twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs</title>
</head>
<body>
<style type="text/css"><!--
#msg dl.meta { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dl.meta dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer, #logmsg { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; }
#msg dl a { font-weight: bold}
#msg dl a:link { color:#fc3; }
#msg dl a:active { color:#ff0; }
#msg dl a:visited { color:#cc6; }
h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; }
#msg pre { overflow: auto; background: #ffc; border: 1px #fa0 solid; padding: 6px; }
#logmsg { background: #ffc; border: 1px #fa0 solid; padding: 1em 1em 0 1em; }
#logmsg p, #logmsg pre, #logmsg blockquote { margin: 0 0 1em 0; }
#logmsg p, #logmsg li, #logmsg dt, #logmsg dd { line-height: 14pt; }
#logmsg h1, #logmsg h2, #logmsg h3, #logmsg h4, #logmsg h5, #logmsg h6 { margin: .5em 0; }
#logmsg h1:first-child, #logmsg h2:first-child, #logmsg h3:first-child, #logmsg h4:first-child, #logmsg h5:first-child, #logmsg h6:first-child { margin-top: 0; }
#logmsg ul, #logmsg ol { padding: 0; list-style-position: inside; margin: 0 0 0 1em; }
#logmsg ul { text-indent: -1em; padding-left: 1em; }#logmsg ol { text-indent: -1.5em; padding-left: 1.5em; }
#logmsg > ul, #logmsg > ol { margin: 0 0 1em 0; }
#logmsg pre { background: #eee; padding: 1em; }
#logmsg blockquote { border: 1px solid #fa0; border-left-width: 10px; padding: 1em 1em 0 1em; background: white;}
#logmsg dl { margin: 0; }
#logmsg dt { font-weight: bold; }
#logmsg dd { margin: 0; padding: 0 0 0.5em 0; }
#logmsg dd:before { content:'\00bb';}
#logmsg table { border-spacing: 0px; border-collapse: collapse; border-top: 4px solid #fa0; border-bottom: 1px solid #fa0; background: #fff; }
#logmsg table th { text-align: left; font-weight: normal; padding: 0.2em 0.5em; border-top: 1px dotted #fa0; }
#logmsg table td { text-align: right; border-top: 1px dotted #fa0; padding: 0.2em 0.5em; }
#logmsg table thead th { text-align: center; border-bottom: 1px solid #fa0; }
#logmsg table th.Corner { text-align: left; }
#logmsg hr { border: none 0; border-top: 2px dashed #fa0; height: 1px; }
#header, #footer { color: #fff; background: #636; border: 1px #300 solid; padding: 6px; }
#patch { width: 100%; }
#patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;}
#patch .propset h4, #patch .binary h4 {margin:0;}
#patch pre {padding:0;line-height:1.2em;margin:0;}
#patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;}
#patch .propset .diff, #patch .binary .diff {padding:10px 0;}
#patch span {display:block;padding:0 10px;}
#patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;}
#patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;}
#patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;}
#patch .lines, .info {color:#888;background:#fff;}
--></style>
<div id="msg">
<dl class="meta">
<dt>Revision</dt> <dd><a href="http://trac.calendarserver.org//changeset/15726">15726</a></dd>
<dt>Author</dt> <dd>cdaboo@apple.com</dd>
<dt>Date</dt> <dd>2016-06-30 09:49:16 -0700 (Thu, 30 Jun 2016)</dd>
</dl>
<h3>Log Message</h3>
<pre>Improve next_job() performance - notably for Oracle.</pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#twextbranchesuserscdaboobetternextjobtwextenterprisejobsjobitempy">twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/jobitem.py</a></li>
<li><a href="#twextbranchesuserscdaboobetternextjobtwextenterprisejobsqueuepy">twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/queue.py</a></li>
<li><a href="#twextbranchesuserscdaboobetternextjobtwextenterprisejobstesttest_jobspy">twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/test/test_jobs.py</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="twextbranchesuserscdaboobetternextjobtwextenterprisejobsjobitempy"></a>
<div class="modfile"><h4>Modified: twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/jobitem.py (15725 => 15726)</h4>
<pre class="diff"><span>
<span class="info">--- twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/jobitem.py 2016-06-30 14:43:42 UTC (rev 15725)
+++ twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/jobitem.py 2016-06-30 16:49:16 UTC (rev 15726)
</span><span class="lines">@@ -60,13 +60,14 @@
</span><span class="cx">
</span><span class="cx"> JobTable.addColumn("JOB_ID", SQLType("integer", None), default=Sequence(inSchema, "JOB_SEQ"), notNull=True, primaryKey=True)
</span><span class="cx"> JobTable.addColumn("WORK_TYPE", SQLType("varchar", 255), notNull=True)
</span><del>- JobTable.addColumn("PRIORITY", SQLType("integer", 0), default=0)
- JobTable.addColumn("WEIGHT", SQLType("integer", 0), default=0)
</del><ins>+ JobTable.addColumn("PRIORITY", SQLType("integer", 0), default=0, notNull=True)
+ JobTable.addColumn("WEIGHT", SQLType("integer", 0), default=0, notNull=True)
</ins><span class="cx"> JobTable.addColumn("NOT_BEFORE", SQLType("timestamp", None), notNull=True)
</span><ins>+ JobTable.addColumn("IS_ASSIGNED", SQLType("integer", 0), default=0, notNull=True)
</ins><span class="cx"> JobTable.addColumn("ASSIGNED", SQLType("timestamp", None), default=None)
</span><span class="cx"> JobTable.addColumn("OVERDUE", SQLType("timestamp", None), default=None)
</span><del>- JobTable.addColumn("FAILED", SQLType("integer", 0), default=0)
- JobTable.addColumn("PAUSE", SQLType("integer", 0), default=0)
</del><ins>+ JobTable.addColumn("FAILED", SQLType("integer", 0), default=0, notNull=True)
+ JobTable.addColumn("PAUSE", SQLType("integer", 0), default=0, notNull=True)
</ins><span class="cx">
</span><span class="cx"> return inSchema
</span><span class="cx">
</span><span class="lines">@@ -168,14 +169,14 @@
</span><span class="cx"> @param overdue: number of seconds after assignment that the job will be considered overdue
</span><span class="cx"> @type overdue: L{int}
</span><span class="cx"> """
</span><del>- return self.update(assigned=when, overdue=when + timedelta(seconds=overdue))
</del><ins>+ return self.update(isAssigned=1, assigned=when, overdue=when + timedelta(seconds=overdue))
</ins><span class="cx">
</span><span class="cx">
</span><span class="cx"> def unassign(self):
</span><span class="cx"> """
</span><span class="cx"> Mark this job as unassigned by setting the assigned and overdue columns to L{None}.
</span><span class="cx"> """
</span><del>- return self.update(assigned=None, overdue=None)
</del><ins>+ return self.update(isAssigned=0, assigned=None, overdue=None)
</ins><span class="cx">
</span><span class="cx">
</span><span class="cx"> def bumpOverdue(self, bump):
</span><span class="lines">@@ -208,6 +209,7 @@
</span><span class="cx"> delay = self.lockRescheduleInterval if locked else self.failureRescheduleInterval
</span><span class="cx"> delay *= (self.failed + 1)
</span><span class="cx"> return self.update(
</span><ins>+ isAssigned=0,
</ins><span class="cx"> assigned=None,
</span><span class="cx"> overdue=None,
</span><span class="cx"> failed=self.failed + (0 if locked else 1),
</span><span class="lines">@@ -358,12 +360,16 @@
</span><span class="cx">
</span><span class="cx"> @classmethod
</span><span class="cx"> @inlineCallbacks
</span><del>- def nextjob(cls, txn, now, minPriority):
</del><ins>+ def nextjob(cls, txn, now, minPriority, rowLimit):
</ins><span class="cx"> """
</span><span class="cx"> Find the next available job based on priority, also return any that are overdue. This
</span><span class="cx"> method uses an SQL query to find the matching jobs, and sorts based on the NOT_BEFORE
</span><span class="cx"> value and priority.
</span><span class="cx">
</span><ins>+ The C{rowLimit} value is used to help with concurrency problems when the underlying DB does
+ not support a proper "LIMIT" term with the query (Oracle). It should be set to no more than
+ 1 plus the number of app-servers in use).
+
</ins><span class="cx"> @param txn: the transaction to use
</span><span class="cx"> @type txn: L{IAsyncTransaction}
</span><span class="cx"> @param now: current timestamp - needed for unit tests that might use their
</span><span class="lines">@@ -371,6 +377,8 @@
</span><span class="cx"> @type now: L{datetime.datetime}
</span><span class="cx"> @param minPriority: lowest priority level to query for
</span><span class="cx"> @type minPriority: L{int}
</span><ins>+ @param rowLimit: query at most this number of rows at a time
+ @type rowLimit: L{int}
</ins><span class="cx">
</span><span class="cx"> @return: the job record
</span><span class="cx"> @rtype: L{JobItem}
</span><span class="lines">@@ -387,7 +395,7 @@
</span><span class="cx"> # being fetched is locked and existing locked rows are skipped.
</span><span class="cx">
</span><span class="cx"> job = None
</span><del>- jobID = yield Call("next_job", now, minPriority, returnType=int).on(txn)
</del><ins>+ jobID = yield Call("next_job", now, minPriority, rowLimit, returnType=int).on(txn)
</ins><span class="cx"> if jobID:
</span><span class="cx"> job = yield cls.load(txn, jobID)
</span><span class="cx"> else:
</span><span class="lines">@@ -416,7 +424,7 @@
</span><span class="cx"> ascending=False,
</span><span class="cx"> forUpdate=True,
</span><span class="cx"> noWait=False,
</span><del>- limit=1,
</del><ins>+ limit=rowLimit,
</ins><span class="cx"> )
</span><span class="cx"> job = jobs[0] if jobs else None
</span><span class="cx">
</span><span class="lines">@@ -425,14 +433,20 @@
</span><span class="cx">
</span><span class="cx"> @classmethod
</span><span class="cx"> @inlineCallbacks
</span><del>- def overduejob(cls, txn, now):
</del><ins>+ def overduejob(cls, txn, now, rowLimit):
</ins><span class="cx"> """
</span><span class="cx"> Find the next overdue job.
</span><span class="cx">
</span><ins>+ The C{rowLimit} value is used to help with concurrency problems when the underlying DB does
+ not support a proper "LIMIT" term with the query (Oracle). It should be set to no more than
+ 1 plus the number of app-servers in use).
+
</ins><span class="cx"> @param txn: the transaction to use
</span><span class="cx"> @type txn: L{IAsyncTransaction}
</span><span class="cx"> @param now: current timestamp
</span><span class="cx"> @type now: L{datetime.datetime}
</span><ins>+ @param rowLimit: query at most this number of rows at a time
+ @type rowLimit: L{int}
</ins><span class="cx">
</span><span class="cx"> @return: the job record
</span><span class="cx"> @rtype: L{JobItem}
</span><span class="lines">@@ -441,7 +455,7 @@
</span><span class="cx"> if txn.dialect == ORACLE_DIALECT:
</span><span class="cx"> # See L{nextjob} for why Oracle is different
</span><span class="cx"> job = None
</span><del>- jobID = yield Call("overdue_job", now, returnType=int).on(txn)
</del><ins>+ jobID = yield Call("overdue_job", now, rowLimit, returnType=int).on(txn)
</ins><span class="cx"> if jobID:
</span><span class="cx"> job = yield cls.load(txn, jobID)
</span><span class="cx"> else:
</span><span class="lines">@@ -450,7 +464,7 @@
</span><span class="cx"> (cls.assigned != None).And(cls.overdue < now),
</span><span class="cx"> forUpdate=True,
</span><span class="cx"> noWait=False,
</span><del>- limit=1,
</del><ins>+ limit=rowLimit,
</ins><span class="cx"> )
</span><span class="cx"> job = jobs[0] if jobs else None
</span><span class="cx">
</span></span></pre></div>
<a id="twextbranchesuserscdaboobetternextjobtwextenterprisejobsqueuepy"></a>
<div class="modfile"><h4>Modified: twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/queue.py (15725 => 15726)</h4>
<pre class="diff"><span>
<span class="info">--- twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/queue.py 2016-06-30 14:43:42 UTC (rev 15725)
+++ twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/queue.py 2016-06-30 16:49:16 UTC (rev 15726)
</span><span class="lines">@@ -463,6 +463,12 @@
</span><span class="cx"> highPriorityLevel = 80 # Percentage load level above which only high priority jobs are processed
</span><span class="cx"> mediumPriorityLevel = 50 # Percentage load level above which high and medium priority jobs are processed
</span><span class="cx">
</span><ins>+ # Used to help with concurrency problems when the underlying DB does not
+ # support a proper "LIMIT" term with the query (Oracle). It should be set to
+ # no more than 1 plus the number of app-servers in use). For a single app
+ # server always use 1.
+ rowLimit = 1
+
</ins><span class="cx"> def __init__(self, reactor, transactionFactory, useWorkerPool=True, disableWorkProcessing=False):
</span><span class="cx"> """
</span><span class="cx"> Initialize a L{ControllerQueue}.
</span><span class="lines">@@ -577,7 +583,7 @@
</span><span class="cx"> txn = nextJob = None
</span><span class="cx"> try:
</span><span class="cx"> txn = self.transactionFactory(label="jobqueue.workCheck")
</span><del>- nextJob = yield JobItem.nextjob(txn, nowTime, minPriority)
</del><ins>+ nextJob = yield JobItem.nextjob(txn, nowTime, minPriority, self.rowLimit)
</ins><span class="cx"> if nextJob is None:
</span><span class="cx"> break
</span><span class="cx">
</span><span class="lines">@@ -706,7 +712,7 @@
</span><span class="cx"> txn = overdueJob = None
</span><span class="cx"> try:
</span><span class="cx"> txn = self.transactionFactory(label="jobqueue.overdueCheck")
</span><del>- overdueJob = yield JobItem.overduejob(txn, nowTime)
</del><ins>+ overdueJob = yield JobItem.overduejob(txn, nowTime, self.rowLimit)
</ins><span class="cx"> if overdueJob is None:
</span><span class="cx"> break
</span><span class="cx">
</span></span></pre></div>
<a id="twextbranchesuserscdaboobetternextjobtwextenterprisejobstesttest_jobspy"></a>
<div class="modfile"><h4>Modified: twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/test/test_jobs.py (15725 => 15726)</h4>
<pre class="diff"><span>
<span class="info">--- twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/test/test_jobs.py 2016-06-30 14:43:42 UTC (rev 15725)
+++ twext/branches/users/cdaboo/better-next-job/twext/enterprise/jobs/test/test_jobs.py 2016-06-30 16:49:16 UTC (rev 15726)
</span><span class="lines">@@ -193,13 +193,14 @@
</span><span class="cx"> create table JOB (
</span><span class="cx"> JOB_ID integer primary key default 1,
</span><span class="cx"> WORK_TYPE varchar(255) not null,
</span><del>- PRIORITY integer default 0,
- WEIGHT integer default 0,
</del><ins>+ PRIORITY integer default 0 not null,
+ WEIGHT integer default 0 not null,
</ins><span class="cx"> NOT_BEFORE timestamp not null,
</span><ins>+ IS_ASSIGNED integer default 0 not null,
</ins><span class="cx"> ASSIGNED timestamp default null,
</span><span class="cx"> OVERDUE timestamp default null,
</span><del>- FAILED integer default 0,
- PAUSE integer default 0
</del><ins>+ FAILED integer default 0 not null,
+ PAUSE integer default 0 not null
</ins><span class="cx"> );
</span><span class="cx"> """
</span><span class="cx"> )
</span><span class="lines">@@ -463,6 +464,7 @@
</span><span class="cx"> self.assertTrue(len(jobs) == 1)
</span><span class="cx"> self.assertTrue(jobs[0].workType == "DUMMY_WORK_ITEM")
</span><span class="cx"> self.assertTrue(jobs[0].assigned is None)
</span><ins>+ self.assertEqual(jobs[0].isAssigned, 0)
</ins><span class="cx">
</span><span class="cx"> @transactionally(dbpool.connection)
</span><span class="cx"> def checkWork(txn):
</span><span class="lines">@@ -488,6 +490,7 @@
</span><span class="cx"> jobs = yield inTransaction(dbpool.connection, checkJob)
</span><span class="cx"> self.assertTrue(len(jobs) == 1)
</span><span class="cx"> self.assertTrue(jobs[0].assigned is None)
</span><ins>+ self.assertEqual(jobs[0].isAssigned, 0)
</ins><span class="cx">
</span><span class="cx"> @inlineCallbacks
</span><span class="cx"> def assignJob(txn):
</span><span class="lines">@@ -498,6 +501,7 @@
</span><span class="cx"> jobs = yield inTransaction(dbpool.connection, checkJob)
</span><span class="cx"> self.assertTrue(len(jobs) == 1)
</span><span class="cx"> self.assertTrue(jobs[0].assigned is not None)
</span><ins>+ self.assertEqual(jobs[0].isAssigned, 1)
</ins><span class="cx">
</span><span class="cx">
</span><span class="cx"> @inlineCallbacks
</span><span class="lines">@@ -512,7 +516,7 @@
</span><span class="cx"> # Empty job queue
</span><span class="cx"> @inlineCallbacks
</span><span class="cx"> def _next(txn, priority=WORK_PRIORITY_LOW):
</span><del>- job = yield JobItem.nextjob(txn, now, priority)
</del><ins>+ job = yield JobItem.nextjob(txn, now, priority, 1)
</ins><span class="cx"> if job is not None:
</span><span class="cx"> work = yield job.workItem()
</span><span class="cx"> else:
</span><span class="lines">@@ -1196,6 +1200,7 @@
</span><span class="cx"> jobs = yield check
</span><span class="cx"> self.assertTrue(len(jobs) == 1)
</span><span class="cx"> self.assertTrue(jobs[0].assigned is None)
</span><ins>+ self.assertEqual(jobs[0].isAssigned, 0)
</ins><span class="cx"> self.assertTrue(jobs[0].failed == 1)
</span><span class="cx"> self.assertTrue(jobs[0].notBefore > datetime.datetime.utcnow())
</span><span class="cx">
</span><span class="lines">@@ -1230,6 +1235,7 @@
</span><span class="cx"> jobs = yield check
</span><span class="cx"> self.assertTrue(len(jobs) == 1)
</span><span class="cx"> self.assertTrue(jobs[0].assigned is None)
</span><ins>+ self.assertEqual(jobs[0].isAssigned, 0)
</ins><span class="cx"> self.assertTrue(jobs[0].failed == 1)
</span><span class="cx"> self.assertTrue(jobs[0].notBefore > datetime.datetime.utcnow() + datetime.timedelta(seconds=90))
</span><span class="cx">
</span><span class="lines">@@ -1244,8 +1250,8 @@
</span><span class="cx">
</span><span class="cx"> oldNextJob = JobItem.nextjob
</span><span class="cx"> @inlineCallbacks
</span><del>- def _nextJob(cls, txn, now, minPriority):
- job = yield oldNextJob(txn, now, minPriority)
</del><ins>+ def _nextJob(cls, txn, now, minPriority, rowLimit):
+ job = yield oldNextJob(txn, now, minPriority, rowLimit)
</ins><span class="cx"> work = yield job.workItem()
</span><span class="cx"> if work.a == -2:
</span><span class="cx"> raise ValueError("oops")
</span><span class="lines">@@ -1277,9 +1283,11 @@
</span><span class="cx"> jobs = yield check
</span><span class="cx"> self.assertEqual(len(jobs), 2)
</span><span class="cx"> self.assertEqual(jobs[0].assigned, None)
</span><ins>+ self.assertEqual(jobs[0].isAssigned, 0)
</ins><span class="cx"> self.assertEqual(jobs[0].failed, 0)
</span><span class="cx"> self.assertEqual(jobs[0].notBefore, fakeNow - datetime.timedelta(20 * 60))
</span><span class="cx"> self.assertEqual(jobs[1].assigned, None)
</span><ins>+ self.assertEqual(jobs[1].isAssigned, 0)
</ins><span class="cx"> self.assertEqual(jobs[1].failed, 0)
</span><span class="cx"> self.assertEqual(jobs[1].notBefore, fakeNow - datetime.timedelta(20 * 60, 5))
</span><span class="cx">
</span><span class="lines">@@ -1327,6 +1335,7 @@
</span><span class="cx"> jobs = yield check
</span><span class="cx"> self.assertEqual(len(jobs), 1)
</span><span class="cx"> self.assertEqual(jobs[0].assigned, None)
</span><ins>+ self.assertEqual(jobs[0].isAssigned, 0)
</ins><span class="cx"> self.assertEqual(jobs[0].failed, 1)
</span><span class="cx"> self.assertGreater(jobs[0].notBefore, datetime.datetime.utcnow() + datetime.timedelta(seconds=30))
</span><span class="cx">
</span><span class="lines">@@ -1380,9 +1389,11 @@
</span><span class="cx"> jobs = yield check
</span><span class="cx"> self.assertEqual(len(jobs), 2)
</span><span class="cx"> self.assertEqual(jobs[0].assigned, None)
</span><ins>+ self.assertEqual(jobs[0].isAssigned, 0)
</ins><span class="cx"> self.assertEqual(jobs[0].failed, 0)
</span><span class="cx"> self.assertEqual(jobs[0].notBefore, fakeNow - datetime.timedelta(20 * 60))
</span><span class="cx"> self.assertEqual(jobs[1].assigned, None)
</span><ins>+ self.assertEqual(jobs[1].isAssigned, 0)
</ins><span class="cx"> self.assertEqual(jobs[1].failed, 0)
</span><span class="cx"> self.assertEqual(jobs[1].notBefore, fakeNow - datetime.timedelta(20 * 60, 5))
</span><span class="cx">
</span><span class="lines">@@ -1872,7 +1883,7 @@
</span><span class="cx"> @inlineCallbacks
</span><span class="cx"> def _testNone(txn):
</span><span class="cx"> nowTime = datetime.datetime.utcfromtimestamp(reactor.seconds() + 10)
</span><del>- job = yield JobItem.nextjob(txn, nowTime, WORK_PRIORITY_HIGH)
</del><ins>+ job = yield JobItem.nextjob(txn, nowTime, WORK_PRIORITY_HIGH, 1)
</ins><span class="cx"> self.assertTrue(job is None)
</span><span class="cx">
</span><span class="cx"> yield _testNone
</span></span></pre>
</div>
</div>
</body>
</html>