In order to report usage on our PBSPro compute cluster at work I wrote a simple set of python scripts to dump the accounting information into a MySQL database. This has been working fine for the last year churning out reports every month.
\nThis week I had cause to generate some statics aggregated across the whole three years of the data in the database. I’m using a mixture of Elixir and SQLalchemy to talk to the database. Normally I would do something like this:<\/p>\n
\r\nmybigtablequery = MyBigTable.query()\r\n\r\nfor job in mybigtablequery:\r\n if job.attribute = "thing":\r\n dosomething()\r\n<\/pre>\nWhich worked fine when the database was quite small. I was horrified to see that as this loop went on my python process used more and more memory because the database connection object never throws away a row once it has been loaded. Fortunately I found an answer<\/a> on stackoverflow.
\nSo I ended up doing the following:<\/p>\n\r\ndef batch_query(query,batch=10000):\r\n offset = 0\r\n while True:\r\n for elem in query.limit(batch).offset(offset):\r\n r = True\r\n yield elem\r\n offset += batch\r\n if not r:\r\n break\r\n r = False\r\n\r\nmybigtablequery = MyBigTable.query()\r\n\r\nfor job in batch_query(mybigtablequery,50000):\r\n if job.attribute = "thing":\r\n dosomething()\r\n<\/pre>\n“batch” is just an integer defining how many rows will be fetched by each query. The larger this is the more memory the python interpreter will use but the more efficiently the code will run.<\/p>\n","protected":false},"excerpt":{"rendered":"
In order to report usage on our PBSPro compute cluster at work I wrote a simple set of python scripts to dump the accounting information into a MySQL database. This has been working fine for the last year churning out reports every month. This week I had cause to generate some statics aggregated across the … Continue reading Stepping Through Large Database Tables in Python<\/span>