MySQL Forums
Forum List  »  Connector/Python

slow connection when starting database handler
Posted by: shahril kamaruzzaman
Date: June 15, 2010 02:21AM

>06/15/2010 03:13:21 PM [D:\ABC\abcd\web\infoM\logconf.py] - [ 54] [INFO ] 20 Logs will be created with size 20000000
>06/15/2010 03:13:21 PM [MedDb.py] - [ 8] [INFO ] --- Loading DataBase module
>06/15/2010 03:13:21 PM [MedDb.py] - [ 321] [DEBUG] ====== Now Its Time To Create databse handler========
>06/15/2010 03:13:51 PM [MedDb.py] - [ 30] [DEBUG] Calling INIT
>06/15/2010 03:14:31 PM [MedDb.py] - [ 38] [DEBUG] Starting Handler 1
>06/15/2010 03:15:11 PM [MedDb.py] - [ 38] [DEBUG] Starting Handler 2
>06/15/2010 03:15:51 PM [MedDb.py] - [ 38] [DEBUG] Starting Handler 3

To all Python and MYSQL guru out there, kindly need your help on this as im totally newbie in this programming language :(

Above is some text in a log file once my system started. If u can see after Loading Database Module started, the time taken of each handler are around 40 seconds per handler which previously the handler will be done in a split second.

Kindly need any of you to give some guide or info to solve this issues. Below are the codes that generate the log file and started the system

import MySQLdb
import thread
import string 
from logconf import *
import os
from elementtree import ElementTree

logger.info("--- Loading DataBase module")
class dbHndlr:
    __single =None
    dbHndlrList=[]
    lock=thread.allocate_lock()
    def __init__(self,hostStr,userName,password,dbName,identifier):
        try:
            self.host=hostStr
            self.user=userName
            self.passwd=password
            self.db=dbName
            self.conn=MySQLdb.connect(host = hostStr, user = userName, passwd = password, db = dbName)
            self.cursor=MySQLdb.cursors.DictCursor(self.conn)
            self.cursor.execute("SET AUTOCOMMIT=0") 
            self.err=''
            self.isFree=True
            self.id=identifier
        except MySQLdb.OperationalError, e:
            logger.critical("-- Database Error during connection setup => %s --",str(e))
            self.err=e

    def INIT():
        logger.debug("Calling INIT")
        dbHndlr.lock.acquire()
        try:
            if dbHndlr.__single == None:
                for i in range(1,dbConnPoolSize+1):
                    dbHndlr_obj =  dbHndlr(lhost,user,password,database,i)
                    if dbHndlr_obj.err == '':
                        dbHndlr.dbHndlrList.append(dbHndlr_obj)
                        logger.debug("Starting Handler  %s",str(i))
                dbHndlr.__single = 'done'
        except Exception, e:
            logger.critical("Exception During Database Initialization=%s",str(e))
        dbHndlr.lock.release()
        
    INIT = staticmethod(INIT)
        
    def connect(self):
    	self.conn=MySQLdb.connect(host = self.host, user = self.user, passwd = self.passwd, db = self.db)
        self.cursor=MySQLdb.cursors.DictCursor(self.conn)
        self.cursor.execute("SET AUTOCOMMIT=0")
 
    def setSavePoint(self, sPoint=None):
        qstr="START TRANSACTION"
        dbhndl_obj = None
        dbHndlr.lock.acquire()
        try:
            for tmp_obj in dbHndlr.dbHndlrList :
                if tmp_obj.isFree == True:
                    dbhndl_obj = tmp_obj
                    dbhndl_obj.isFree = False
                    logger.info("Found idle dbhandle id = %s",str(dbhndl_obj.id))
                    break
        except Exception, e:
            logger.critical("Exception During Database selection=%s",str(e))
        dbHndlr.lock.release()
        if  dbhndl_obj == None:
            logger.info("No dbhandle free....CANNOT PROCEED")
            return 'False',None
        stat,rowset = dbhndl_obj.__execute(qstr,'SAVEPOINT')
        dbhndl_obj.isFree = True
        return stat,rowset
        
       
    def Rollback(self,sPoint=None):
        dbhndl_obj = None
        dbHndlr.lock.acquire()
        try:
            for tmp_obj in dbHndlr.dbHndlrList :
                if tmp_obj.isFree == True:
                    dbhndl_obj = tmp_obj
                    dbhndl_obj.isFree = False
                    logger.info("Found idle dbhandle id = %s",str(dbhndl_obj.id))
                    break
        except Exception, e:
            logger.critical("Exception During Database selection=%s",str(e))
        dbHndlr.lock.release()
        if  dbhndl_obj == None:
            logger.info("No dbhandle free....CANNOT PROCEED")
            return 'False',None
        stat,rowset = dbhndl_obj.__execute('ROLLBACK','ROLLBACK')
        dbhndl_obj.isFree = True
        return stat,rowset

    def __execute(self, qstr,command):
        try:
            logger.info("<<<< Executing Query -- %s with dbhndl id=%s for database %s",qstr,str(self.id),str(self.db))
            self.cursor.execute(qstr)
            #logger.info(">>>> Executed Query -- %s with dbhndl id=%s",qstr,str(self.id))
        except MySQLdb.ProgrammingError, e:
            logger.critical("-- Wrong Sql Statement [Exception %s] for dbhndl id=%s--",str(e),str(self.id))
            return 'False',e
        except MySQLdb.OperationalError, e:
            # "If connection fails then "
            logger.critical("-- Database Operational Error - [%s]--for dbhndl id=%s",str(e),str(self.id))
            if (string.find(string.lower(str(e)),'2003',1,8)!=-1) or (string.find(string.lower(str(e)),'2013',1,8)!=-1):
                try:
                    logger.info("** Trying Once Again To Connect The Database for dbhndl id=%s**",str(self.id))
                    self.connect()
                except Exception ,e:
                    return 'False',e
                else:
                    logger.info('- - -Connection Restored for dbhndl id=%s- - -',str(self.id))
                    self.__execute(qstr,command)
            elif (string.find(string.lower(str(e)),'1205',1,8)!=-1) or (string.find(string.lower(str(e)),'1205',1,8)!=-1):
                stat,rowset = self.__execute('unlock tables','EXECUTE')
                self.__execute(qstr,command)
                self.commit()
            else:
                logger.critical("-- Other Then Connection Lost for dbhndl id=%s- --",str(self.id))
                return 'False' ,e
        except MySQLdb.InternalError, e:
            #"The cursor is out of Sync"
            logger.critical("-- Exception [%s]--for dbhndl id=%s",str(e),str(self.id))
            return 'False',e
        except MySQLdb.IntegrityError, e:
            #"a foreign key check fails, duplicate key, etc."
            logger.critical("-- Exception [%s]--for dbhndl id=%s",str(e),str(self.id))
            return 'False',e
        except MySQLdb.Error, e:
            logger.critical("-- Exception [%s]--for dbhndl id=%s",str(e),str(self.id))
            try:
                logger.info("** Trying Once Again To Connect The Database for dbhndl id=%s**",str(self.id))
                self.connect()
            except Exception ,e:
                return 'False',e
            else:
                logger.info('- - -Connection Restored for dbhndl id=%s- - -',str(self.id))
                self.__execute(qstr,command)
       except Exception, e:
            logger.critical("-- Exception [%s]--for dbhndl id=%s",str(e),str(self.id))
            return 'False',e
        
        # Query successful
        if command=='SELECT':
            return 'True',self.cursor.fetchall()
        elif command=='UPDATE':
            #  Unlock Tables Explicitly 
            rowcount=self.cursor.rowcount
            #logger.debug("Calling commit() after UPDATE from meddb.py on every successful update")
            #stat,row = self.commit()
            return 'True',rowcount
        elif command=='DELETE':
            #  Unlock Tables Explicitly 
            rowcount=self.cursor.rowcount
            #stat = self.unlockTables()
            return 'True',rowcount
        elif command=='INSERT':
            return 'True','Inserted'
        elif command=='EXECUTE':
            return 'True','Executed'
        elif command=='START TRANS':
            return 'True','Started'
        elif command=='ROLLBACK':
            return 'True','Rollback'
        elif command=='COMMIT':
            return 'True','Commit'
        elif command=='SAVEPOINT':
            return 'True','savepoint'
        elif command=='COMMIT TRANS':
            return 'True','Transaction Commit'
        elif command=='ROLLTRANS':
            return 'True','Transaction Rolled'
        else:
            logger.critical("Unsupported SQL command = %s",command)
    
     def select(self,qstr):
        dbhndl_obj = None
        dbHndlr.lock.acquire()
        try:
            for tmp_obj in dbHndlr.dbHndlrList :
                if tmp_obj.isFree == True:
                    dbhndl_obj = tmp_obj
                    dbhndl_obj.isFree = False
                    logger.info("Found idle dbhandle id = %s",str(dbhndl_obj.id))
                    break
        except Exception, e:
            logger.critical("Exception During Database selection=%s",str(e))
        dbHndlr.lock.release()
        if  dbhndl_obj == None:
            logger.info("No dbhandle free....CANNOT PROCEED")
            return 'False',None
        stat,rowset = dbhndl_obj.__execute(qstr,'SELECT')
        dbhndl_obj.isFree = True
        return stat,rowset

        
    def update(self,qstr):
         dbhndl_obj = None
        dbHndlr.lock.acquire()
        try:
            for tmp_obj in dbHndlr.dbHndlrList :
                if tmp_obj.isFree == True:
                    dbhndl_obj = tmp_obj
                    dbhndl_obj.isFree = False
                    logger.info("Found idle dbhandle id = %s",str(dbhndl_obj.id))
                    break
        except Exception, e:
            logger.critical("Exception During Database selection=%s",str(e))
        dbHndlr.lock.release()
        if  dbhndl_obj == None:
            logger.info("No dbhandle free....CANNOT PROCEED")
            return 'False',None
        stat,rowset = dbhndl_obj.__execute(qstr,'UPDATE')
        dbhndl_obj.isFree = True
        return stat,rowset

    
    def delete(self,qstr):

        dbhndl_obj = None
        dbHndlr.lock.acquire()
        try:
            for tmp_obj in dbHndlr.dbHndlrList :
                if tmp_obj.isFree == True:
                    dbhndl_obj = tmp_obj
                    dbhndl_obj.isFree = False
                    logger.info("Found idle dbhandle id = %s",str(dbhndl_obj.id))
                    break
        except Exception, e:
            logger.critical("Exception During Database selection=%s",str(e))
        dbHndlr.lock.release()
        if  dbhndl_obj == None:
            logger.info("No dbhandle free....CANNOT PROCEED")
            return 'False',None
        stat,rowset = dbhndl_obj.__execute(qstr,'DELETE')
        stat1,rowset1 = dbhndl_obj.__execute("commit","COMMIT")
        dbhndl_obj.isFree = True
        return stat,rowset
    
    def insert(self,qstr):
        #stat,rowset = self.__execute("SAVEPOINT aa",'savepoint')        
        #stat,rowset = self.__execute(qstr,'INSERT')
        dbhndl_obj = None
        dbHndlr.lock.acquire()
        try:
            for tmp_obj in dbHndlr.dbHndlrList :
                if tmp_obj.isFree == True:
                    dbhndl_obj = tmp_obj
                    dbhndl_obj.isFree = False
                    logger.info("Found idle dbhandle id = %s",str(dbhndl_obj.id))
                    break
        except Exception, e:
            logger.critical("Exception During Database selection=%s",str(e))
        dbHndlr.lock.release()
        if  dbhndl_obj == None:
            logger.info("No dbhandle free....CANNOT PROCEED")
            return 'False',None
        stat,rowset = dbhndl_obj.__execute(qstr,'INSERT')
        dbhndl_obj.isFree = True
        return stat,rowset

    def commit(self):
        dbhndl_obj = None
        dbHndlr.lock.acquire()
        try:
            for tmp_obj in dbHndlr.dbHndlrList :
                if tmp_obj.isFree == True:
                    dbhndl_obj = tmp_obj
                    dbhndl_obj.isFree = False
                    logger.info("Found idle dbhandle id = %s",str(dbhndl_obj.id))
                    break
        except Exception, e:
            logger.critical("Exception During Database selection=%s",str(e))
        dbHndlr.lock.release()
        if  dbhndl_obj == None:
            logger.info("No dbhandle free....CANNOT PROCEED")
            return 'False',None
        stat,rowset = dbhndl_obj.__execute('COMMIT','COMMIT')
        stat,rowset = dbhndl_obj.__execute('unlock tables','COMMIT')
        dbhndl_obj.isFree = True
        return stat,rowset

    def close(self):
        self.cursor.close()
        self.conn.close()

logger.debug("====== Now Its Time To Create databse handler========")
"""--- Read the conf.XML to read the Report data---"""
path =os.environ['INFO_INSTALL_DIR']
#path, p = os.path.split(path)
#path = os.path.join(path,p)
path=os.path.join(path,"web","infoM","conf.xml")
config={}
try:
    et=ElementTree.parse(path)
    root=et.getroot()
    
    for i in root:
        config[i.tag]=dict((k.tag,k.text) for k in i)
except Exception, e:
    logger.info("== == Error in Reading conf.xml - - %s",str(e))

lhost = config['DATABASE']['HOST']
user = config['DATABASE']['USER']
password =  config['DATABASE']['PASSWORD']
database =  config['DATABASE']['DBNAME']
dbConnPoolSize=int(config['DATABASE']['DBPOOLSIZE'])
DB=dbHndlr(lhost,user,password,database,0)
dbHndlr.INIT()



Options: ReplyQuote


Subject
Written By
Posted
slow connection when starting database handler
June 15, 2010 02:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.