title: database solution for very large table
body: <p>I am dealing with some performance issues whilst working with a very large dataset. The data is a pairwise distance matrix of ~60k entries. The resulting vectors have been generated in the following format:</p>
  
  <p><code>mol_a,mol_b,score, year_a, year_b
   1,1,1,year,year
   1,2,x,year,year
   1,3,y,year,year
   ...
   1,60000,z,year,year
   2,1,x,year,year
   2,2,1,year,year
   ...</code></p>
  
  <p>where mol_a and mol_b are unique molecules (INTs) and score is their jaccard/tanimoto similarity score (FLOAT/REAL) and year_a and b are dates (INT(4)) that are associated with mol_a and b respectively. Since this is a distance matrix the values are reflected across the diagonal ie</p>
  
  <p><code>0 1 2 3 
  1 1 x y
  2 x 1 z
  3 y z 1</code></p>
  
  <p>The resulting file has ~3.6 billion rows and becomes a ~100GB sqlite3 db. It take about 10 hours to make using all the PRAGMA tweaks I have read and doing <code>executemany</code> in 5 million entry batches. I would love to throw away half of it while building the but I can't think of a good way of doing this without ending up building a (prohibitively) giant list in memory... It's constructed via 2 nested for loops:</p>
  
  <p><code>for i in MolList: 
       for j in MolList:
           a = calculate_score(i,j)
           write_buffer.append(a)</code></p>
  
  <p>Though the creation is slow it is not the prohibitive part, the actual analysis i want to do with it is.. I will be grouping things by year_a and year_b so I started creating an index on year_a, year_b and score to have a 'Covering Index' but the build is 13 hours in and occasionally using mass amounts of harddrive space on my c: drive (which is a small SSD vs the raid where the database is). The whole thing is running on 12 core workstation with 16GB of ram, Windows 7 on a 240GB SSD and data storage on a 1TB Raid 1 array (built in MoBo controller).</p>
  
  <p>I have also been attempting to build a MySql database on a small little ubuntu server that I have (intel core duo 2ghz 4GB ram 128GB SSD) but the python inserts across the network are taking forever! and I'm not convinced I'll see any real improvements.</p>
  
  <p>SQLite from what I've read seems like what I really wanted, essentially this would all be handled in Python memory if I had ~150GB of RAM at my disposal, but I needed a file based storage solution which seems like exactly what SQLite was designed for. However watching SQLite consume a pittance of memory and CPU while Disk IO bounces around 5MB/s chugs away makes me think that disk is just a bottleneck.  </p>
  
  <p>What are my options for streamlining this process on a single node (aka no Hadoop clusters at my disposal)? I'm a DB newb so please keep suggestions within the realm of possibility. Having never worked with datasets in the billions I don't quite know what I'm in for but would greatly appreciate help/advice from this sage community.</p>