hows your SQL debug output and your profiling going?
On Mar 12, 2014, at 9:56 PM, Ni Wesley <[email protected]> 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 <[email protected]> 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 [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. -- 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.
