Tornado使用同步Mysql

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()