[Dbix-class] Help with Titanic Number of Inserts

[Lee Standen]

Step 1:
Ditch SQLite - it's not optimized for large databases (to the best of my 
knowledge)

Step 2:
Get a seperate machine to host the database server in the interim. 
MySQL with MyISAM tables will go a long way towards speeding things up, 
because there's very little that will beat MySQL in speed as long as you 
don't need things like foreign keys.

Step 3:
Get someone who knows whatever database you're using (MySQL or 
otherwise) inside and out so it can be tweaked for the usage you're 
going to be putting it through (ie. Mass inserts and low reads will 
require different configurations than high read, low writes).  MySQL is 
GENERALLY considered better for reading, and I'm assuming that you'll be 
doing more read than write?


That's about the best you can do.  The bottleneck is likely to be the 
database rather than the application itself...you can always through 
more front end machines to pump the data in, it's the database back end 
which is more difficult to upgrade.


Ryan Richt wrote:
> Hey Guys and Gals...
> 
>     We are just starting a DBIx::Class project here at the Washington 
> University Genome Sequencing Center in St. Louis, and __of__course__ we 
> choose DBIx::Class over all that other stuff ;-).
> 
> Anyway, we need to make > 112,000,000 inserts per day.  I _know_ that 
> DBIx::Class is not concerned with performance, but with the (wonderful) 
> design/interface/features.
> 
> However, other than wrapping it in a transaction, do you have any other 
> tips on making this manageable?  Currently this would take 130 DAYS to 
> insert w/o transactions, and something like 30 DAYS with transactions.
> 
> (As far as setup goes, this is currently in SQLite and soon to be Oracle 
> once SQLite explodes, testing on a newer dual processor  3gig ram 
> machine, and could deploy on anything from 1000+ node cluster or 50 node 
> cluster of Opertons or 5 x 100gig ram itanium quad processor machines, 
> if that could help us in any way!).
> 
> Thank you very much!
> 
> Ryan
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: 
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>