Downloading csv of all Documents causes out of memory error, empty file

[blms]

Marina, via Slack:

I'm attempting to download all PGP metadata into a .csv and I can't do it. I've downloaded 5 .csvs of all the metadata in the normal way — select all, then either drop-down or button at upper right — and each time it contains only the headers, no actual metadata.

dev notes

• This seems to be caused by psycopg2 running out of memory: psycopg2.DatabaseError: out of memory for query result
• Probably an issue with fields recently added to the export, maybe prefetching

[blms]

I’ve been working a bit more on the this problem today. Here are my findings:

• The only new thing that I have added to the export in the past year is Document.dating_set, so it’s likely to be the culprit.
• I did see a notable difference in memory usage when comparing the export before and after that change: through the Django shell 2.14 GB before and 2.30 GB after; and through the admin UI 3.28 GB before, 3.44 GB after.
  • A significant amount either way, but we are clearly hitting some sort of server limit at 3.44 GB.
  • I’m not sure how to account for the massive discrepancy between the web UI and the Django shell. All I’m doing in the shell is this:

    exporter = AdminDocumentExporter(queryset=Document.objects.all())
    for c in exporter.http_export_data_csv().streaming_content:
    print(c)

• Oddly, simply removing dating_set from prefetch_related actually reduces the amount of memory used almost all the way back down: to 2.16 GB in the Django shell and 3.28 GB in the admin UI. I suspect this is because the prefetch caches everything in memory before executing the queries, intending to offload that chunk of the work from the Postgres server into the application server memory, but I don’t know for sure.
• I noticed that Python doesn’t seem to release the memory right away after the export completes, though initiating a second export does release the memory immediately, and then borrows it all back again. Otherwise it seems to take some time to release the memory, no idea what’s going on behind the scenes there.

It seems counterintuitive but… is the solution really just to remove the prefetching of dating_set?

[blms]

Additional info from RSK:

• We can probably resolve this using chunking along the lines of parasolr: https://github.com/Princeton-CDH/parasolr/blob/cad72d6864baf2375935191ecc687049dae8a03e/parasolr/indexing.py#L174-L190
• Can probably adapt prep_index_chunk for the metadata needed for prefetch.

We also bumped the production server's memory up to 32GB so it's unlikely this will be reached again for a while. But still good to optimize. Moving to backlog for now.

[richmanrachel]

@blms - Marina hasn't noticed this bug in a while, did it get resolved?

[blms]

@richmanrachel Temporarily resolved—my comment from earlier:

We also bumped the production server's memory up to 32GB so it's unlikely this will be reached again for a while. But still good to optimize. Moving to backlog for now.

Maybe good for the icebox.

[kseniaryzhova]

@blms solved this issue by bumping up memory, so I'm closing!