title: 30 million records a day, SQL Server can't keep up, other kind of database system needed?
body: <p>Some time ago I thought an new statistics system over, for our multi-million user website, to log and report user-actions for our customers.</p>
  
  <p>The database-design is quite simple, containing one table, with a foreignId (200,000 different id's), a datetime field, an actionId (30 different id's), and two more fields containing some meta-information (just smallints). There are no constraints to other tables. Furthermore we have two indexes each containing 4 fields, which cannot be dropped, as users are getting timeouts when we are having smaller indexes. The foreignId is the most important field, as each and every query contains this field.</p>
  
  <p>We chose to use SQL server, but after implementation doesn't a relational database seem like a perfect fit, as we cannot insert 30 million records a day (it's insert only, we don't do any updates) when also doing alot of random reads on the database; because the indexes cannot be updated fast enough. Ergo: we have a massive problem :-) We have temporarily solved the problem, yet</p>
  
  <p><em>a relational database doesn't seem to be suited for this problem!</em></p>
  
  <p>Would a database like BigTable be a better choice, and why? Or are there other, better choices when dealing with this kind of problems?</p>
  
  <p>NB. At this point we use a single 8-core Xeon system with 4 GB memory and Win 2003 32-bit. RAID10 SCSI as far as I know. The index size is about 1.5x the table size.</p>