Tornado是一个轻量级异步web服务器框架,linux下采用epoll模型,提供了非常强大的网络响应性能。而python没有相应的异步mysql库,我们在实际项目中使用mysql时,也需要做到异步,一般有多线程模拟、使用c++异步mysql库等方式,经测试,使用多线程模拟时,当单个进程每秒请求数超过3k时,会出现错误,超过5k时,会导致程序崩掉,而使用c++异步库时,则性能良好。这里先记录一下多线程模拟的方式。
原理就是用户执行SQL操作时,程序将SQL语句交给多个线程,若需要获取操作后的结果,则需传入一个callback函数,然后立马返回给用户,达到模拟异步的效果;真正执行SQL语句是在线程中同步执行,执行后的结果集和callback放到异步框架的大循环中,当循环到达时就会调用该callback并将结果集传给用户。
首先需要一个线程池模型:
class AsyncMixin(object):
def __init__(self,
thread_klass=None,
thread_klass_args=None,
num_threads=10,
queue_timeout=1,
ioloop=None):
super(AsyncMixin,self).__init__()
self._thread_klass = thread_klass
self._thread_klass_args = thread_klass_args
self._ioloop = ioloop or tornado.ioloop.IOLoop.current()
self._num_threads = num_threads
self._queue = Queue()
self._queue_timeout = queue_timeout
self._threads = []
self._running = True
for i in xrange(num_threads):
name = "thread_%d" % i
thread_klass_args['name'] = name
t = thread_klass(**thread_klass_args)
t.start()
self._threads.append(t)
def add_task(self, func, callback=None):
"""Add a function to be invoked in a worker thread."""
self._queue.put((func, callback))
def stop(self):
self._running = False
map(lambda t: t.join(), self._threads)
工作线程基类:
class WorkerThread(Thread):
def __init__(self,**kwargs ):
Thread.__init__(self)
self._name = kwargs.get('name',None)
self._pool = kwargs.get('pool',None)
log.info("WorkerThread created: %s" % self._name )
def run(self):
queue = self._pool._queue
queue_timeout = self._pool._queue_timeout
while self._pool._running:
try:
(func, callback) = queue.get(True, queue_timeout)
handler = self.get_handler()
ex = None
result = None
if hasattr(handler,func.func.func_name):
try:
data = func()#method in async_mysql, return tuple
result = getattr(handler,func.func.func_name)(*data)#method in mysql, return execute result
except Exception:
log.exception("%s execute %s" % (self._name,func.func.func_name))
ex = sys.exc_info()
else:
raise ValueError("%s not found" % func.func.func_name )
if callback:
self._pool._ioloop.add_callback(partial(callback, result,ex))
except Empty:
pass
if hasattr(self,'close'):
log.info("%s Do close" % self._name)
self.close()
异步Mysql封装:
@async_class
class AsyncMysql(AsyncMixin):
__async_methods__ = ['query','get','execute','insert','insertmany','replace','delete','update','updatemany','count']
def __init__(self,db_config, **kwargs):
kwargs['thread_klass'] = MysqlThread
kwargs['thread_klass_args'] = dict(pool=self,db_config=db_config)
super(AsyncMysql,self).__init__(**kwargs)
def get_thread_pool(self):
return self
def query(self,sql):
log.info("query:(%s)" % (sql))
return (sql,)
def get(self,sql):
log.info("get:(%s)" % (sql))
return (sql,)
def execute(self,sql):
log.info("execute:(%s)" % (sql))
return (sql,)
def insert(self,sql):#execute_lastrowid
log.info("insert:(%s)" % (sql))
return (sql,)
def insertmany(self,sql):#executemany_lastrowid
log.info("insertmany:(%s)" % (sql))
return (sql,)
def replace(self,sql):#execute_lastrowid
log.info("replace:(%s)" % (sql))
return (sql,)
def delete(self,sql):#execute_lastrowid
log.info("delete:(%s)" % (sql))
return (sql,)
def update(self,sql):#execute_rowcount
log.info("update:(%s)" % (sql))
return (sql, )
def updatemany(self,sql):#executemany_rowcount
log.info("updatemany:(%s)" % (sql))
return (sql, )
def count(self,sql,field):
log.info("count:(%s)" % (sql))
return (sql, field)
def close(self):
self.stop()
为了使用起来更简洁方便,这里给出异步Mysql装饰器,当实例化AsyncMysql时会先将__async_methds__中的方法加上async_thread装饰器,则调用这些方法时会先执行async_thread,即将需执行的SQL语句放到线程池队列中去:
def async_thread(func):
@wraps(func)
def wrapper(*args,**kwargs):
obj = args[0]
if isinstance(obj,AsyncMixin):
callback = None
if 'callback' in kwargs:
callback = kwargs.pop('callback')
obj.get_thread_pool().add_task(partial(func,*args, **kwargs),callback)
else:
raise ValueError("decorator must apply to a instance of AsyncMixin")
return wrapper
def async_class(klass):
if hasattr(klass,'__async_methods__'):
async_methods = getattr(klass,'__async_methods__')
for name in async_methods:
method = getattr(klass,name)
setattr(klass,"hooked_%s" % name,method)
setattr(klass,name,async_thread(method))
return klass
Mysql工作线程:
class MysqlThread(WorkerThread):
def __init__(self,*args, **kwargs):
self.db = Connection(**kwargs['db_config'])
super(MysqlThread,self).__init__(*args,**kwargs)
self.daemon = True
def get_handler(self):
return self.db
def close(self):
self.db.close()
使用:
def main():
adb = AsyncMysql(DATABASE)#DATABASE为数据库host等设置
try:
adb.insert("insert into account(id, name) values(1, \'test\')", callback=None)
tornado.ioloop.IOLoop.instance().start()
except:
adb.stop()
if __name__ == "__main__":
main()