multiprocessing module in async db query

[John Nagle]

On 3/8/2011 3:34 PM, Philip Semanchuk wrote:
>
> On Mar 8, 2011, at 3:25 PM, Sheng wrote:
>
>> This looks like a tornado problem, but trust me, it is almost all
>> about the mechanism of multiprocessing module.
>
> [snip]
>
>
>> So the workflow is like this,
>>
>> get() -->  fork a subprocess to process the query request in
>> async_func() ->  when async_func() returns, callback_func uses the
>> return result of async_func as the input argument, and send the query
>> result to the client.
>>
>> So the problem is the the query result as the result of sql_command
>> might be too big to store them all in the memory, which in our case is
>> stored in the variable "data". Can I send return from the async method
>> early, say immediately after the query returns with the first result
>> set, then stream the results to the browser. In other words, can
>> async_func somehow notify callback_func to prepare receiving the data
>> before async_func actually returns?
>
> Hi Sheng,
> Have you looked at multiprocessing.Queue objects?

     Make sure that, having made a request of the database, you
quickly read all the results.  Until you finish the transaction,
the database has locks set, and other transactions may stall.
"Streaming" out to a network connection while still reading from
the database is undesirable.

     If you're doing really big SELECTs, consider using LIMIT and
OFFSET in SQL to break them up into smaller bites.  Especially
if the user is paging through the results.

				John Nagle