def reportOnQueues(self): if self.reportCollectionRef: priorities = ['H', 'M', 'L'] qDict = {'qCheckTime': datetime.now()} for priority in priorities: qArray = [] if priority == 'H': theQ = self.sqlBHandler.sqlQname_H elif priority == 'M': theQ = self.sqlBHandler.sqlQname_M elif priority == 'L': theQ = self.sqlBHandler.sqlQname_L qlen = self.getQLens(priority) qlenKey = f'{priority}__len' qcontentKey = f'{priority}_content' qDict[qlenKey] = qlen qcontents = self.sqlBHandler.redis_client.lrange(theQ, -10, -1) for element in qcontents: elementParsed = json.loads(element, cls=mu.RoundTripDecoder) qArray.append(elementParsed) qDict[qcontentKey] = qArray docUid = mu.makeAscendingUid() self.reportCollectionRef.document(docUid).set(qDict)
def persistErrorDetailInFB(self, exception, attempt, willBeRetried=True): lst = dir(exception) dict_ = { 'attempt': attempt, 'createdAt': datetime.now(), 'willBeRetried': willBeRetried, '_type': str(type(exception)) } if 'orig' in lst: try: dict_['orig_args'] = exception.orig.args[0] except: dict_['orig_args_M'] = 'Unable to get this' if 'args' in lst: dict_['args'] = str(exception.args) if 'code' in lst: dict_['code'] = exception.code if 'statement' in lst: dict_['statement'] = exception.statement if 'params' in lst and exception.params: dict_['params'] = list(exception.params) if 'connection_invalidated' in lst: dict_['connection_invalidated'] = exception.connection_invalidated if 'detail' in lst: dict_['detail'] = exception.detail if self.fb_db: docuid = mu.makeAscendingUid() destDoc = self.fb_db.collection('logging/sqlQ/errors').document( docuid) print( f'Error record will be written to Firebase as docUid={docuid}: {dict_}' ) destDoc.set(dict_)
def sendFlare(self, messageData='awaken', flareDeadspaceSeconds=10): """ Sends a "flare" (a message to the pubsub topic path = self.topic_path) which will spark the SQL worker to jump into action. A flare will only be sent if there's been no flare sending in the last (flareDeadspaceSeconds) seconds (defaults to 10). """ flareSendable = False rightNow = datetime.now(timezone.utc) data = messageData.encode("utf-8") if self.redis_client: # Get the last flare-send time lastFlare = self.redis.get_project_human_val( 'SQLHandlerFlareSendTime') if not lastFlare: flareSendable = True # If the seconds-elapsed since this is longer than flareDeadspaceSeconds, send another one. else: eat it. elif mu.dateDiff('second', lastFlare, rightNow) >= flareDeadspaceSeconds: flareSendable = True if self.pubsub and flareSendable: self.redis.set_project_human_val('SQLHandlerFlareSendTime', 'datetime', rightNow) print(f'sending flare!') future = self.pubsub.publish(self.topic_path, data) future.result()
def goToWork(self, forHowLong=60, inactivityBuffer=10): print(f'XXX goToWork. ForHowLong={forHowLong}') startTs = datetime.now(timezone.utc) i = 0 howLong = 0 queuesAreEmpty = False while howLong <= forHowLong - inactivityBuffer and not queuesAreEmpty: i += 1 self.cypherQueues.getQLens() if self.cypherQueues.totalInWorkingQueue >= 10: self.lookForExpiredWorkingBlocks() ctb = self.popBlockFromWaitingQueues() if ctb: stm = ctb.statements print(f'XXX got CTB from Q statements = {stm}') print(ctb.transactionUid) self.executeBlock(ctb) else: queuesAreEmpty = True self.lookForExpiredWorkingBlocks() howLong = um.dateDiff('sec', startTs, datetime.now(timezone.utc)) print(f'Running for how long: {howLong}') if howLong >= forHowLong - inactivityBuffer and self.cypherQueues.totalInWaitingQueues > 0: # numFlares = self.cypherQueues.totalInWaitingQueues / 10 for k in range(3): print(f'sending flare (max 3) {k}') self.pubsub.publish_message('awaken') time.sleep(0.5)
def goToWork(self, forHowLong=60, inactivityBuffer=10, batchSize=50): print(f'XXX goingToWork. ForHowLong={forHowLong} s') priorities = ['H', 'M', 'L'] startTs = datetime.now() i = 0 howLong = 0 self.reportOnQueues() for priority in priorities: queuesAreEmpty = False while howLong <= forHowLong - inactivityBuffer and not queuesAreEmpty: i += 1 k = 0 batch = [] while not queuesAreEmpty and k < batchSize: sqlB, queuesAreEmpty = self.popNextBlock(priority=priority) if sqlB: batch.append(sqlB) k += 1 sortedBatches, transactionList = self.sortBatch(batch) for sb in sortedBatches: sqb = MonkeeSQLblock(query=sb[0], insertList=sb[1], numRetries=sb[2], maxRetries=sb[3], soloExecution=sb[4], lastExecAttempt=sb[5]) self.executeBlock(sqb, priority=priority) for transaction_i in transactionList: # transaction_i is a sqb, probably with more than one transactionSqb entry sqb = MonkeeSQLblock( query=transaction_i['query'], insertList=transaction_i['insertList'], numRetries=transaction_i['numRetries'], maxRetries=transaction_i['maxRetries'], soloExecution=transaction_i['soloExecution'], lastExecAttempt=transaction_i['lastExecAttempt'], transactionSqb=transaction_i['transactionSqb']) self.executeBlock(sqb, priority=priority) howLong = mu.dateDiff('sec', startTs, datetime.now()) #print(f'sqlw Running for how long: {howLong}') qlen = self.getQLens(priority=priority) if qlen == 0: queuesAreEmpty = True else: queuesAreEmpty = False if howLong >= forHowLong - inactivityBuffer and qlen > 0: numFlares = 3 for k in range(numFlares): self.sqlBHandler.sendFlare()
def sortBatch(self, batch): batchDict = {} batchList = [] transactionList = [] for line in batch: isTransaction = mu.getval(line, "isTransaction", 0) # The reason that non-transactions are broken up out of their sqbs is so that similar queries can be batched, # meaning that all sqbs in this batch where the query part is the same, but only the parameters differ are bundled together and executed as one. if isTransaction == 0: query = mu.getval(line, "query") soloExecution = mu.getval(line, "soloExecution", 0) numRetries = mu.getval(line, "numRetries", 0) maxRetries = mu.getval(line, "maxRetries", 0) lastExecAttempt = mu.getval(line, "lastExecAttempt") if query: if soloExecution == 0: if query not in batchDict: batchDict[query] = [] batchDict[query].append(line["insertList"]) elif soloExecution == 1: # soloExecution = flagging this element to be executed on its own, i.e. not as part of a batch batchList.append([ query, line["insertList"], numRetries, maxRetries, soloExecution, lastExecAttempt ]) elif isTransaction == 1: # if this is a transaction, there is no batching. This implies that the sqb (containing the transaction) can be passed through directly. transactionList.append(line) for q in batchDict: batchList.append([q, batchDict[q], 0, 30, 0, lastExecAttempt]) return batchList, transactionList
def popNextBlock(self, priority): if priority == 'H': theQ = self.sqlBHandler.sqlQname_H elif priority == 'M': theQ = self.sqlBHandler.sqlQname_M elif priority == 'L': theQ = self.sqlBHandler.sqlQname_L elif priority == 'ERR': theQ = self.sqlBHandler.sqlQname_ERR popped = self.sqlBHandler.redis_client.blpop(theQ, 1) if not popped: print( f"SQL_Q {priority} is EMPTY_________________________________________" ) else: dataFromRedis = json.loads(popped[1], cls=mu.RoundTripDecoder) numRetries = mu.getval(dataFromRedis, "numRetries", 0) lastExecAttempt = mu.getval(dataFromRedis, "lastExecAttempt") remainInErrorQUntil = mu.getval(dataFromRedis, "remainInErrorQUntil", datetime.now()) if numRetries == 0: return dataFromRedis, False # elif lastExecAttempt and datetime.now() >= lastExecAttempt + timedelta(seconds=1.5 ** numRetries): elif priority in [ 'H', 'M', 'L' ] and lastExecAttempt and datetime.now( ) >= lastExecAttempt + timedelta(seconds=2 * numRetries): return dataFromRedis, False elif priority in ['ERR'] and remainInErrorQUntil and datetime.now( ) >= remainInErrorQUntil: return dataFromRedis, False else: sqlB = MonkeeSQLblock() sqlB.makeFromSerial(serial_=dataFromRedis) self.sqlBHandler.toQ(sqlB, priority=priority) return None, True
def _statementsAsTransaction(self, tx, statements): results = [] duration = [] statementList = [] for statement in statements: # try: startTs = datetime.now(timezone.utc) if "batch" in statement: cyph = statement["cypher"][0:100] print(f'batch-mode statement[cypher] to be run: {cyph}') res = tx.run(statement["cypher"], statement["parameters"], batch=statement["batch"]) else: cyph = statement["cypher"][0:100] print(f'statement[cypher] to be run: {cyph}') res = tx.run(statement["cypher"], statement["parameters"]) results.append(res) endTs = datetime.now(timezone.utc) duration.append(um.dateDiff('sec', startTs, endTs)) statementList.append({ "cypher": statement["cypher"], "parameters": statement["parameters"], "duration": um.dateDiff('sec', startTs, endTs), "status": "OK", "error": None }) # except Exception as e: # endTs = datetime.now(timezone.utc) # duration.append(um.dateDiff('sec', startTs, endTs)) # logging.error(repr(e)) # statementList.append({"cypher": statement["cypher"], "parameters": statement["parameters"], "duration": um.dateDiff('sec', startTs, endTs), # "status": "ERROR", "error": repr(e)}) return results, statementList
def lookForExpiredWorkingBlocks(self, expiryInSeconds=60): wb = self.copyBlockFromWorkingQueue() if wb: matchingSerial = json.dumps(wb.instanceToSerial(), cls=um.RoundTripEncoder) if wb.statements: timeDiff = um.dateDiff('s', wb.lastUpdatedAt, datetime.now()) print(f' Age of workingQ item ={timeDiff}') if timeDiff >= expiryInSeconds: stmt = wb.statements print( f'Picking up item from workingQ. Statements = {stmt}') rem = self.removeBlockFromWorkingQueue(matchingSerial) if rem > 0: wb.registerChangeInSql('outOfWorkingQ') wb.registerChangeInSql('RecycledToWaiting') wb.lastUpdatedAt = datetime.now() wb.numRetries += 1 self.cypherQueues.pushCtbToWaitingQ(ctBlock=wb, jumpTheQ=True)
def makeFromSerial(self, serial_): self.isTransaction = mu.getval(serial_, "isTransaction", 0) if self.isTransaction == 0: self.query = mu.getval(serial_, "query") self.insertList = mu.getval(serial_, "insertList") self.queryTypeId = mu.getval(serial_, "queryTypeId") self.numRetries = mu.getval(serial_, "numRetries") self.maxRetries = mu.getval(serial_, "maxRetries") self.soloExecution = mu.getval(serial_, "soloExecution") self.lastExecAttempt = mu.getval(serial_, "lastExecAttempt") self.priority = mu.getval(serial_, "priority") self.remainInErrorQUntil = mu.getval(serial_, "remainInErrorQUntil") self.serial_ = self.instanceToSerial() elif self.isTransaction == 1: self.statements = mu.getval(serial_, "statements", []) self.transactionSqb = mu.getval(serial_, "transactionSqb", []) self.numRetries = mu.getval(serial_, "numRetries") self.maxRetries = mu.getval(serial_, "maxRetries") self.lastExecAttempt = mu.getval(serial_, "lastExecAttempt") self.priority = mu.getval(serial_, "priority") self.remainInErrorQUntil = mu.getval(serial_, "remainInErrorQUntil") if len(self.statements) > 0 and len(self.transactionSqb) == 0: for statement in self.statements: sqb = MonkeeSQLblock() sqb.query = mu.getval(statement, "query") sqb.insertList = mu.getval(statement, "insertList") sqb.queryTypeId = mu.getval(statement, "queryTypeId") sqb.numRetries = mu.getval(statement, "numRetries") sqb.maxRetries = mu.getval(statement, "maxRetries") sqb.soloExecution = mu.getval(statement, "soloExecution") sqb.lastExecAttempt = mu.getval(statement, "lastExecAttempt") sqb.serial_ = sqb.instanceToSerial() self.transactionSqb.append(sqb) self.instanceToSerial()
def makeFromSerial(self, serial_): self.isTransaction = mu.getval(serial_, "isTransaction", 0) if self.isTransaction == 0: self.query = mu.getval(serial_, "query") self.insertList = mu.getval(serial_, "insertList") self.queryTypeId = mu.getval(serial_, "queryTypeId") self.numRetries = mu.getval(serial_, "numRetries") self.maxRetries = mu.getval(serial_, "maxRetries") self.soloExecution = mu.getval(serial_, "soloExecution") self.lastExecAttempt = mu.getval(serial_, "lastExecAttempt") self.serial_ = self.instanceToSerial() elif self.isTransaction == 1: self.statements = self.query = mu.getval(serial_, "statements", []) for statement in self.statements: sqb = MonkeeSQLblock() sqb.query = mu.getval(statement, "query") sqb.insertList = mu.getval(statement, "insertList") sqb.queryTypeId = mu.getval(statement, "queryTypeId") sqb.numRetries = mu.getval(statement, "numRetries") sqb.maxRetries = mu.getval(statement, "maxRetries") sqb.soloExecution = mu.getval(statement, "soloExecution") sqb.lastExecAttempt = mu.getval(statement, "lastExecAttempt") sqb.serial_ = sqb.instanceToSerial() self.transactionSqb.append(sqb)
def makeFromSerial(self, serial): if not isinstance(serial, dict): dict_ = json.loads(serial, cls=um.RoundTripDecoder) else: dict_ = serial self.priority = um.getval(dict_, "priority") self.numRetries = um.getval(dict_, "numRetries") self.createdAt = um.getval(dict_, "createdAt") self.lastUpdatedAt = um.getval(dict_, "lastUpdatedAt") self.transactionUid = um.getval(dict_, "uid") self.origin = um.getval(dict_, "origin") self.statements = um.getval(dict_, "statements") self.runTime = um.getval(dict_, "runTime") self.status = um.getval(dict_, "status") self.errors = um.getval(dict_, "errors") self.durations = um.getval(dict_, "durations") self.callingCF = um.getval(dict_, "callingCF") self.timeInQ = um.getval(dict_, "timeInQ") self.docUid = um.getval(dict_, "docUid") self.appUid = um.getval(dict_, "appUid") self.setJson()
def executeBlock(self, ctBlock: CypherTransactionBlock): """ Executes all statments in the ctb as one transaction. Returns success boolean """ matchingSerial = json.dumps(ctBlock.instanceToSerial(), cls=um.RoundTripEncoder) if ctBlock.statements: try: ctBlock.registerChangeInSql('executeStart') with self.neoDriver.session() as session: startTs = datetime.now(timezone.utc) print('to NEO: {}'.format(ctBlock.statements)) _, durations = session.write_transaction( self._statementsAsTransaction, ctBlock.statements) print('back from NEO') endTs = datetime.now(timezone.utc) elapsedSec = um.dateDiff('sec', startTs, endTs) ctBlock.runTime = elapsedSec ctBlock.durations = durations ctBlock.status = 'done' ctBlock.registerChangeInSql('executeEnd') rem = self.removeBlockFromWorkingQueue(matchingSerial) if rem > 0: ctBlock.registerChangeInSql('outOfWorkingQ') self.cypherQueues.pushCtbToCompletedQ(ctBlock) return True except CypherSyntaxError as e: print('CypherSyntaxError') logging.error(repr(e)) ctBlock.numRetries += 1 ctBlock.status = 'CypherSyntaxError' ctBlock.errors = repr(e) rem = self.removeBlockFromWorkingQueue(matchingSerial) if rem > 0: ctBlock.registerChangeInSql('outOfWorkingQ') self.cypherQueues.pushCtbToCompletedQ(ctBlock) ctBlock.registerChangeInSql('error', repr(e)) return False except ServiceUnavailable as e: print('ServiceUnavailable') logging.error(repr(e)) ctBlock.numRetries += 1 ctBlock.status = 'ServiceUnavailable' ctBlock.errors = repr(e) rem = self.removeBlockFromWorkingQueue(matchingSerial) if rem > 0: ctBlock.registerChangeInSql('outOfWorkingQ') self.cypherQueues.pushCtbToWaitingQ(ctBlock) ctBlock.registerChangeInSql('error', repr(e)) return False except ConstraintError as e: print('ConstraintError') logging.error(repr(e)) ctBlock.numRetries += 1 ctBlock.status = 'ConstraintError' ctBlock.errors = repr(e) rem = self.removeBlockFromWorkingQueue(matchingSerial) if rem > 0: ctBlock.registerChangeInSql('outOfWorkingQ') ctBlock.setJson() if ctBlock.numRetries <= 50: print(f'>>>>ctBlock.numRetries={ctBlock.numRetries}') self.cypherQueues.pushCtbToWaitingQ(ctBlock) ctBlock.registerChangeInSql('error', repr(e)) else: self.cypherQueues.pushCtbToCompletedQ(ctBlock) ctBlock.registerChangeInSql('givenUp', 'GIVEN UP') return False except ClientError as e: print('ClientError') logging.error(repr(e)) ctBlock.numRetries += 1 ctBlock.status = 'ClientError' ctBlock.errors = repr(e) rem = self.removeBlockFromWorkingQueue(matchingSerial) if rem > 0: ctBlock.registerChangeInSql('outOfWorkingQ') ctBlock.setJson() self.cypherQueues.pushCtbToCompletedQ(ctBlock) ctBlock.registerChangeInSql('error', repr(e)) return False except Exception as e: logging.error(repr(e)) rem = self.removeBlockFromWorkingQueue(matchingSerial) if rem > 0: ctBlock.registerChangeInSql('outOfWorkingQ') self.cypherQueues.pushCtbToWaitingQ(ctBlock) return False