On Mar 12, 2014, at 8:32 AM, Ni Wesley <nisp...@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
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.
