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 <javascript:>>
> 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
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.
