CODEWITHSUNDEEP
pythontornadordbmsblockingnonblocking
simon
simon

Reputation: 560

Tornado RDBMS integration

Typically the RDBMS drivers are blocking, while Tornado is a non-blocking server. This leads to irrational use of async, when performing CRUD operations, because the IOLoop will be blocked until that SQL query finishes.

I am working on a project that uses RDBMS as a DB (because of ACID), but which also requires Websockets to orchestrate some fancy functionlaity (i.e. push notifications) and after some googling I am thinking of using Tornado, because both usual REST and Websockets can be implemented in one app.

Browsing the web I found this snippet (here: https://gist.github.com/methane/2185380):

import time

from tornado.concurrent import run_on_executor
from concurrent.futures import ThreadPoolExecutor   # `pip install futures` for python2

MAX_WORKERS = 4


class Handler(tornado.web.RequestHandler):
    executor = ThreadPoolExecutor(max_workers=MAX_WORKERS)

    @run_on_executor
    def background_task(self, i):
        """ This will be executed in `executor` pool. """
        time.sleep(10)
        return i

    @tornado.gen.coroutine
    def get(self, idx):
        """ Request that asynchronously calls background task. """
        res = yield self.background_task(idx)
        self.write(res)

What it basically does is pushes the CRUD task to a separate thread. My question is: is this the default approach of dealing with blocking RDBMS drivers when using an async HTTP server? Are there any other ways of minimizing these blocking bottlenecks? Is it even reasonable to use a blocking RDBMS driver with an async server?

Upvotes: 0

Views: 335

Answers (1)

Michael Robellard
Michael Robellard

Reputation: 2358

There are multiple ways of dealing with an RDBMS in Tornado.

There are some libraries for various DB's for doing them async in Tornado. https://github.com/tornadoweb/tornado/wiki/Links

You can also use GEvent to get asyncronous db access in Tornado.

Another option is to use a Python3 async io library with Tornado such as: https://aiopg.readthedocs.io/en/stable/

As you noted another option is to offload the DB workload elsewhere. You could use a Message Queue like ZeroMQ or RabbitMQ, multiple threads, multiple processes, To an API on another box.

You can also use standard blocking database access mechanisms, they will still work in Tornado, they just block until they return, this may or may not be a problem.

I gave a talk about Momoko, Aiopg, and GEvent at PyOhio last year: http://pyvideo.org/video/3698/from-synchronous-to-asynchronous-postgres-with-to

Upvotes: 1

Related Questions