peewee and peewee-async: why is async slower
<p>I am trying to wrap my head around Tornado and async connections to Postgresql. I found a library that can do this at <a href="http://peewee-async.readthedocs.io/en/latest/" rel="nofollow">http://peewee-async.readthedocs.io/en/latest/</a>.</p>

<p>I devised a little test to compare traditional Peewee and Peewee-async, but somehow async works slower.</p>

<p>This is my app:</p>

<pre><code>import peewee
import tornado.web
import logging
import asyncio
import peewee_async
import tornado.gen
import tornado.httpclient
from tornado.platform.asyncio import AsyncIOMainLoop

AsyncIOMainLoop().install()
app = tornado.web.Application(debug=True)
app.listen(port=8888)

# ===========
# Defining Async model
async_db = peewee_async.PooledPostgresqlDatabase(
    'reminderbot',
    user='reminderbot',
    password='reminderbot',
    host='localhost'
)
app.objects = peewee_async.Manager(async_db)
class AsyncHuman(peewee.Model):
    first_name = peewee.CharField()
    messenger_id = peewee.CharField()
    class Meta:
        database = async_db
        db_table = 'chats_human'


# ==========
# Defining Sync model
sync_db = peewee.PostgresqlDatabase(
    'reminderbot',
    user='reminderbot',
    password='reminderbot',
    host='localhost'
)
class SyncHuman(peewee.Model):
    first_name = peewee.CharField()
    messenger_id = peewee.CharField()
    class Meta:
        database = sync_db
        db_table = 'chats_human'

# defining two handlers - async and sync
class AsyncHandler(tornado.web.RequestHandler):

    async def get(self):
        """
        An asynchronous way to create an object and return its ID
        """
        obj = await self.application.objects.create(
            AsyncHuman, messenger_id='12345')
        self.write(
            {'id': obj.id,
             'messenger_id': obj.messenger_id}
        )


class SyncHandler(tornado.web.RequestHandler):

    def get(self):
        """
        An traditional synchronous way
        """
        obj = SyncHuman.create(messenger_id='12345')
        self.write({
            'id': obj.id,
            'messenger_id': obj.messenger_id
        })


app.add_handlers('', [
    (r"/receive_async", AsyncHandler),
    (r"/receive_sync", SyncHandler),
])

# Run loop
loop = asyncio.get_event_loop()
try:
    loop.run_forever()
except KeyboardInterrupt:
    print(" server stopped")
</code></pre>

<p>and this is what I get from Apache Benchmark:</p>

<pre><code>ab -n 100 -c 100 http://127.0.0.1:8888/receive_async

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        2    4   1.5      5       7
Processing:   621 1049 256.6   1054    1486
Waiting:      621 1048 256.6   1053    1485
Total:        628 1053 255.3   1058    1492

Percentage of the requests served within a certain time (ms)
  50%   1058
  66%   1196
  75%   1274
  80%   1324
  90%   1409
  95%   1452
  98%   1485
  99%   1492
 100%   1492 (longest request)




ab -n 100 -c 100 http://127.0.0.1:8888/receive_sync
Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        2    5   1.9      5       8
Processing:     8  476 277.7    479    1052
Waiting:        7  476 277.7    478    1052
Total:         15  481 276.2    483    1060

Percentage of the requests served within a certain time (ms)
  50%    483
  66%    629
  75%    714
  80%    759
  90%    853
  95%    899
  98%   1051
  99%   1060
 100%   1060 (longest request)
</code></pre>

<p>why is sync faster? where is the bottleneck I'm missing?</p>

<p>Database ORMs introduce many complexities for async architectures. There are several places within an ORM where blocking may take place and can be overwhelming to alter to an async form. The places where blocking takes place can also vary depending on the database. My guess as to why your results are so slow is because there's a lot of unoptimized calls to and from the event loop (I could be severely wrong, I mostly use SQLAlchemy or raw SQL these days). In my experience, it's generally quicker to execute database code in a thread and yield the result when it's available. I can't really speak for PeeWee, but SQLAlchemy is well suited to run in multiple threads and there aren't too many down sides (but the ones that do exist are very VERY annoying).</p>

<p>I'd recommend you try your experiment using <a href="https://docs.python.org/dev/library/concurrent.futures.html#threadpoolexecutor" rel="nofollow">ThreadPoolExecutor</a> and the synchronous Peewee module and run database functions in a thread. You will have to make changes to your main code, however it would be worth it if you ask me. For example, let's say you opt to use callback code, then your ORM queries might look like this:</p>

<pre class="lang-python prettyprint-override"><code>from concurrent.futures import ThreadPoolExecutor

executor = ThreadPoolExecutor(max_workers=10)

def queryByName(name):
    query = executor.submit(db_model.findOne, name=name)
    query.add_done_callback(processResult)

def processResult(query):
    orm_obj = query.results()
    # do stuff with the results
</code></pre>

<p>You could use <code>yeild from</code> or <code>await</code> in coroutines, but it was a bit problematic for me. Also, I'm not well versed in coroutines yet. This snippet should work well with Tornado so long as devs are careful about deadlocks, db sessions, and transactions. These factors can really slow down your application if something goes wrong in the thread.</p>

<p>If you're feeling very adventurous, MagicStack (the company behind asyncio) has a project called <a href="https://github.com/magicstack/asyncpg" rel="nofollow"><code>asyncpg</code></a> and its supposed to be very fast! I've been meaning to try, but haven't found the time :(</p>

python postgresql tornado python-asyncio peewee