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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
