Should we do database calls async with tornado

Question

To my understanding, we should do database calls async since it will block IOLoop, and I also found some library/tools that working on this. But recently I just found lots of posts about this question on stackoverflow

Tornado Asynchronous Handler

How does async work in Tornado?

and something from wiki https://github.com/tornadoweb/tornado/wiki/Threading-and-concurrency

Do it synchronously and block the IOLoop. This is most appropriate for things like memcache and database queries that are under your control and should always be fast. If it's not fast, make it fast by adding the appropriate indexes to the database, etc.

So should we do database calls async?

and a related question: as I know, there are two methods to do database calls async

• For sync database(mysqldb), we can

  executor = ThreadPoolExecutor(4)

  result = yield executor.submit(mysqldb_operation)

• For async database(motor), we can use it directly.

what's the different of them based on tornado?

Answer 1

I had a similar doubt and went through some research and digging and finally have some grip over this.

Short Answer - Yes, Do it Asynchronously.

Long Answer : Database calls are generally blocking calls. Tornado is best suited as a framework when we do async/Non-blocking I/O calls to completely utilize its full potential.

Now, You can do Database Calls in Tornado in the following ways, I am giving the How and Why for each method.

Method 1 : Synchronous Database Calls - Start a number of Tornado instances behind a load balancer like for example nginx, and make synchronous database calls(as mentioned in the wiki). In this case, the process with the particular request making the blocking database call will block. So nginx will load balance the other requests by directing them to the other running tornado instances to handle the requests.

NOTE : That wiki is old, and hasn't been updated so that method mentioned is prior to the addition of coroutines in the newer versions of Tornado as mentioned by Ben Darnell, one of the lead developers of Tornado.

Update : The wiki has been updated by Ben recently and you can check it now.

Method 2 : By using multiprocessing or multithreading, concurrent.futures, similar to what you have mentioned:

For sync database(mysqldb), we can

executor = ThreadPoolExecutor(4)

result = yield executor.submit(mysqldb_operation)

Method 3 : By using Async libraries, like for instance if you are using MySql, then you can use libraries like TorMySql, Tornado-MySQL, other links given here-Tornado 3rd-party libraries, go to the MySql section. These libraries are to be used with Tornado's async decorators, tornado.gen.coroutine or tornado.web.asynchronous. Similarly, you need to use other async libraries for different databases.

NOTE : You cannot use blocking libraries otherwise its all the same as Method 1.

CONCLUSION : Method 3 is what I mentioned as my Short Answer. Method 2 is the next best solution, followed by Method 1. This is in accordance with your performance requirements. If you requirement is for not-so-heavy(few to moderate) request processing per second or per minute, than going for the easy and general Method 1 will suffice.