asyncio and blocking - an update

[Frank Millman]

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