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 [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.
