Hi all
Some of you may have been following my attempts to modify my asyncio app so
that it does not block when accessing the database. Here is an update.
I came up with what felt like a good idea. Run the database handler in a
separate thread, pass requests to it using a queue.Queue, and get it to pass
results back using an asyncio.Queue.
It works, but I had a vague sense that performance was a bit sluggish, so I
tried the 'recommended' approach of using asyncio.run_in_executor() to
execute database calls in a separate thread. It felt a bit faster.
Now I have written a proper timing test, and the recommended approach is
much faster. I am not 100% sure of the reason, but I think the problem is
that, with my method, when the database tries to 'put' a row on the return
queue, it has to use 'loop.call_soon_threadsafe()', and this seems to create
a bottleneck.
It would not matter so much if I used cur.fetchall(), and sent all the rows
back in one 'put', but I really wanted to iterate over the cursor and 'put'
a row at a time. The idea was that it would be truly asynchronous from end
to end - the database handler would retrieve rows one at a time, and via the
queue my app would process them one at a time, all without blocking.
I can switch to fetchall(), but then there is no benefit over the
recommended approach. Although fetchall() is non-blocking, once the rows are
received as a list the function that processes them effectively does block.
If that became a problem one could use an Asynchronous Iterator to process
the list, but I have not had that need yet.
Writing timing tests is tricky. It is possible that under some
circumstances, with a heavy load, a large table, and a time-consuming
function to process each row, the overhead of call_soon_threadsafe() would
be minimised and my approach might be effective. For now, however, I will
regretfully abandon my approach and stick with run_in_executor().
Frank Millman
--
https://mail.python.org/mailman/listinfo/python-list