cpence/rletters — Issue #6: Problems with big Solr / SQL
Author: cpence
Created: 2011-10-26 03:41:09+00:00
State: open
Labels: controllers, models, solr

Several problems are going to begin to crop up everywhere when the dataset query size and SQL query size get huge:

- Solr responses are going to get massive. A 500,000 document Solr response will weigh in around 20 MB.
- SQL queries are going to get massive. An INSERT for 500,000 dataset entries will weigh in around 20 MB.

Note that a "reasonable" limit, to keep SQL and Solr sizes around 1 MB, would be 25,000 documents per dataset.

There are several ways to go here:

1. Suck it up and figure out how to optimize such that a query that big is going to work. Remember that it's *only* the create and save that are the issues. Any analysis sits in background tasks, so if it's slow, it's slow. If something like this could work, I suppose I would be okay with dropping the max-dataset-size to around 100,000.
2. Implement a feature whereby we will turn a *really* big search into *multiple* datasets. So you click "save dataset" on a 500,000 record set, and you get 20 datasets created for you, automatically.
3. Both of these.

Note that the SQL query size just is *not* going to scale: default MySQL clamps query size at 1M, and I think that's probably with damned good reason.