[Dbix-class] Help with Titanic Number of Inserts

[Shane McEwan]

Wow! And I thought my 30 years and 960,000,000 rows worth of
meteorological data was a pain in the ass! Thankfully I only need to do
60 inserts per minute so I used raw SQL for the bulk import of
historical data and then DBIx::Class for the live data.

If you're committed to using DBIx::Class for the inserts then you'll
probably have to split your inserts across multiple machines. 30 insert
processes using transactions would just about keep up with the daily
amount of data.

I'd also be interested to know how quick your SELECTs run with that sort
of data quantities. I know mine are horribly slow if I try to do
anything other than very basic SELECT statements. However that's more an
issue with MySQL and (more likely) my (non-existent) database design
skills.

Good luck!

On Tue, 2007-01-23 at 19:01 -0600, 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/
-- 
         Shane McEwan - Bureau of Meteorology - Cape Grim BAPS
------------------------------------------------------------------------
         Don't anthropomorphise computers, they don't like it!
------------------------------------------------------------------------
 159 Nelson St, Smithton, Tasmania - Ph 03 6452 1629 - Fax 03 6452 2600