Hi guys, I hit a problem when using sqlalchemy operating mysql. First, let me clarify my code frames.
I have a web service to receive http requests, then, it send task to a tcp server via zeromq pull/push mode, tcp server pull and do some push work to cell phone. I hit a problem that, tcp server pushing to cell phone is too slow...and finally I find the bottleneck is sqlalchemy operating on mysql. if without query/insert/update mysql, for 1000 requests, takes 1.5 seconds to handle all, but if with db operation, takes about 100 seconds... So, here paste my sqlalchemy code: engine = create_engine(db_url, pool_size=0,max_overflow=200,echo=engine_echo,pool_recycle=3600) session = scoped_session(sessionmaker(bind=engine)) metadata = MetaData(bind=engine) class DBWriter(object): def __init__(self,tablename): self.tablename = tablename try: self._table=Table(tablename, metadata, autoload=True) except Exception,e: exctrace('db','1','DBWriter init failed',dba_logger,'DBWriter init failed','Exception when DBWriter initing table:%s' % str(e)) #dba_logger.log(40,'Exception when DBWriter initing table:%s' % str(e)) def dbwrite(self,**kwargs): """ Used to insert exception info into database. Params: module : module name, indicating who raises the exception, e.g. android,ios,psg,adpns,db .etc type : exception type, 0 means service level while 1 is system level. message : exception description,length limit to 256 bytes """ try: session = scoped_session(sessionmaker(bind=engine)) i=self._table.insert().values(**kwargs) session.execute(i) session.commit() session.close() except Exception,e: #dba_logger.log(40,'Exception when dbwriter:%s' % str(e)) #dba_logger.log(20,'Exception detail:%s' % str(kwargs)) exctrace('db','1','Error happened when writing db',dba_logger,'Exception when dbwriter:%s' % str(e),'Exception detail:%s' % str(kwargs)) session.rollback() session.close() def dbupdate(self,whereclause,**kwargs): """ Used to insert exception info into database. Params: module : module name, indicating who raises the exception, e.g. android,ios,psg,adpns,db .etc type : exception type, 0 means service level while 1 is system level. message : exception description,length limit to 256 bytes """ try: session = scoped_session(sessionmaker(bind=engine)) i=self._table.update().values(**kwargs).where(whereclause) session.execute(i) session.commit() session.close() except Exception,e: #dba_logger.log(40,'Exception when dbwriter:%s' % str(e)) #dba_logger.log(20,'Exception detail:%s' % str(kwargs)) exctrace('db','1','Error happened when updating db',dba_logger,'Exception when dbupdate:%s' % str(e),'Exception detail:%s' % str(kwargs)) session.rollback() session.close() def dbquery(self,whereclause): try: session = scoped_session(sessionmaker(bind=engine)) i=self._table.select().where(whereclause) res = session.execute(i) return res session.close() except Exception,e: #dba_logger.log(40,'Exception when dbwriter:%s' % str(e)) #dba_logger.log(20,'Exception detail:%s' % str(kwargs)) exctrace('db','1','Error happened when querying db',dba_logger,'Exception when dbquery:%s' % str(e),'Exception detail:%s' % str(whereclause)) #session.rollback() session.close() #res = session.execute(connection_writer._table.select().where(connection_writer._table.c.app_key==self.app_key).where(connection_writer._table.c.device_token==self._devicetoken)) messages_writer = DBWriter('messages') connection_writer = DBWriter('sessions_details') messages_details_writer = DBWriter('messages_details') So, for any place needing db query, for example, query/update/insert on table 'sessions_details', import connection_writer and use it's dbwrite/dbupdate/dbquery method. So, anybody has suggestion to improve the performance? Thanks. Wesley -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.