In case of network problem. I test in a vlan.
Here is engine ouput and my logging: DEBUG:devsrv:Got connection from ('192.168.1.7', 63181) One device connected:('192.168.1.7', 63181) Connection object,appkey:e32c72bab0e4d8e225318f98,devicetoken:1,response:{"msg":"**#*","id":1,"flag":"*#*"},address:('192.168.1.7', 63181) current clients number is : 1 DEBUG:devsrv:one entry start point : 2014-03-13 12:01:27.322528 2014-03-13 12:01:27,325 INFO sqlalchemy.engine.base.Engine SELECT sessions_details.device_token, sessions_details.app_key, sessions_details.create_time, sessions_details.end_time, sessions_details.session_status FROM sessions_details WHERE sessions_details.app_key = %s AND sessions_details.device_token = %s INFO:sqlalchemy.engine.base.Engine:SELECT sessions_details.device_token, sessions_details.app_key, sessions_details.create_time, sessions_details.end_time, sessions_details.session_status FROM sessions_details WHERE sessions_details.app_key = %s AND sessions_details.device_token = %s 2014-03-13 12:01:27,325 INFO sqlalchemy.engine.base.Engine (u'e32c72bab0e4d8e225318f98', u'1') INFO:sqlalchemy.engine.base.Engine:(u'e32c72bab0e4d8e225318f98', u'1') 2014-03-13 12:01:27,331 INFO sqlalchemy.engine.base.Engine UPDATE sessions_details SET create_time=%s, end_time=%s, session_status=%s WHERE sessions_details.app_key = %s AND sessions_details.device_token = %s INFO:sqlalchemy.engine.base.Engine:UPDATE sessions_details SET create_time=%s, end_time=%s, session_status=%s WHERE sessions_details.app_key = %s AND sessions_details.device_token = %s 2014-03-13 12:01:27,331 INFO sqlalchemy.engine.base.Engine (datetime.datetime(2014, 3, 13, 12, 1, 27, 330055), datetime.datetime(2014, 3, 13, 12, 1, 27, 330055), '0', u'e32c72bab0e4d8e225318f98', u'1') INFO:sqlalchemy.engine.base.Engine:(datetime.datetime(2014, 3, 13, 12, 1, 27, 330055), datetime.datetime(2014, 3, 13, 12, 1, 27, 330055), '0', u'e32c72bab0e4d8e225318f98', u'1') 2014-03-13 12:01:27,334 INFO sqlalchemy.engine.base.Engine COMMIT INFO:sqlalchemy.engine.base.Engine:COMMIT DEBUG:devsrv:one entry end point : 2014-03-13 12:01:27.373776 We can see that, from the start point to end point, it takes 50 ms, including 40ms for engine commit. For 1000 entries, it takes 40 seconds, here I have one question, when 1000 connections are on-going, I watch the connections between tcp server and db server, via command watch -n 1 "netstat -anpto|grep dbserver_ip|wc -l", I find the spike value is just 26, actually, I gave 0 to engine pool_size, why it takes only 26? Is this related> 在 2014年3月12日星期三UTC+8下午8时32分53秒,Ni Wesley写道: > > 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.