I have not used cprofile before, but I enable engine echo and some logging,
here is output:
INFO:devsrv:httpMQ started...
DEBUG:devsrv:Got connection from ('10.0.3.96', 62560)
One device connected:('10.0.3.96', 62560)
Connection
object,appkey:e32c72bab0e4d8e225318f98,devicetoken:1,response:{"msg":"**#*","id":1,"flag":"*#*"},address:('10.0.3.96',
62560)
current clients number is : 1
DEBUG:devsrv:one entry start point : 2014-03-12 22:25:20.432274
2014-03-12 22:25:20,434 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2014-03-12 22:25:20,435 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-12 22:25:20,435 INFO sqlalchemy.engine.base.Engine
(u'e32c72bab0e4d8e225318f98', u'1')
INFO:sqlalchemy.engine.base.Engine:(u'e32c72bab0e4d8e225318f98', u'1')
2014-03-12 22:25:20,448 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2014-03-12 22:25:20,450 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-12 22:25:20,450 INFO sqlalchemy.engine.base.Engine
(datetime.datetime(2014, 3, 12, 22, 25, 20, 439990),
datetime.datetime(2014, 3, 12, 22, 25, 20, 439990), '0',
u'e32c72bab0e4d8e225318f98', u'1')
INFO:sqlalchemy.engine.base.Engine:(datetime.datetime(2014, 3, 12, 22, 25,
20, 439990), datetime.datetime(2014, 3, 12, 22, 25, 20, 439990), '0',
u'e32c72bab0e4d8e225318f98', u'1')
2014-03-12 22:25:20,453 INFO sqlalchemy.engine.base.Engine COMMIT
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:devsrv:one entry end point : 2014-03-12 22:25:20.510025
This is just one device connection operation.
So, seems commit operation takes 60 ms.
Even this, every device takes 80ms, 1000 should take 8 seconds if
everything is well, but acctually, 1000 devices takes 100 seconds.
BTW, do you got a google talk account, my is nisp...@gmail.com, could I
invite you to my friend list?
在 2014年3月13日星期四UTC+8上午10时18分01秒，Michael Bayer写道：
>
> hows your SQL debug output and your profiling going?
>
>
> On Mar 12, 2014, at 9:56 PM, Ni Wesley <nis...@gmail.com <javascript:>>
> wrote:
>
> Just have a try.
>
> Move all operations upon session directly to engine.execute.
>
> Almost the same performance.
>
> How to improve? :-(
>
>
> 在 2014年3月12日星期三UTC+8下午9时35分08秒，Michael Bayer写道：
>>
>>
>> On Mar 12, 2014, at 8:32 AM, Ni Wesley <nis...@gmail.com> wrote:
>>
>> > 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)
>> >
>> > 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()
>>
>>
>> I’m not thrilled about the pattern here where you’re opening/closing new
>> Sessions for every request, if you’re just using Core you can stick with
>> engine.connect() at least which will do less work, though the overhead of a
>> Session used just for execute() shouldn’t be terrible.
>>
>> for deep performance tuning you first need to look at queries and how
>> fast they are and then beyond that look at python profiling. An
>> introduction to all these techniques is here:
>> http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677
>>
>>
>>
>>
> --
> 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 tosqlalchemy+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com<javascript:>
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
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.
