René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Avoid running out of memory during slow iteration: set worker_queue maxsize to be 1, and/or make it configurable","articleBody":" **Is your feature request related to a problem? Please describe.**\r\n\r\nWhen using `to_dataframe_iterable` for a large result set (with nested/repeated records) on a system with a fast upstream connection to BigQuery, but _slow_ downstream processing, Python can use all the memory on the system, and get killed.\r\n\r\nI think I have tracked it down to the `worker_queue` that is used to pass data from the worker threads back to the main thread: it does not have a `maxsize`.\r\n\r\nhttps://github.com/googleapis/python-bigquery/blob/cc3394f80934419eb00c2029bb81c92a696e7d88/google/cloud/bigquery/_pandas_helpers.py#L657\r\n\r\nThis means that if items are not pulled from the queue fast enough, then all the memory on the system can be used\r\n\r\n **Describe the solution you'd like**\r\n\r\nI think a reasonable solution would be to set the `maxsize` to be 1:\r\n\r\n```python\r\nworker_queue = queue.Queue(maxsize=1)\r\n```\r\n\r\nThis would still effectively be a buffer size of \"number of threads + 1\" pages since each thread would fetch into a variable, and _then_ wait if the queue is full.\r\n\r\nHowever, it being configurable would also be reasonable I think.\r\n\r\n **Describe alternatives you've considered**\r\n\r\nI've monkey-patched for now, and it seems to work. But ideally, it wouldn't be necessary\r\n\r\n```python\r\n# ...\r\n\r\ndef _monkey_patch_queue_maxsize_1():\r\n    OriginalQueue = queue.Queue\r\n\r\n    class QueueWithMaxsize1(OriginalQueue):\r\n        def __init__(self):\r\n            super().__init__(maxsize=1)\r\n\r\n    def _restore():\r\n        queue.Queue = OriginalQueue\r\n\r\n    queue.Queue = QueueWithMaxsize1\r\n\r\n    return _restore\r\n\r\n# ....\r\n\r\nquery = bqClient.query(sql)\r\nresult_rows = query.result()\r\n\r\nensure_original_queue = _monkey_patch_queue_maxsize_1()\r\n\r\npages = result_rows.to_dataframe_iterable(bqStorageClient)\r\n\r\nfor page in pages:\r\n   ensure_original_queue()\r\n   # ... something slow, even time.sleep can do it\r\n```","author":{"url":"https://github.com/michalc","@type":"Person","name":"michalc"},"datePublished":"2021-03-19T08:10:46.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/561/python-bigquery/issues/561"}