[Dbix-class] Help with Titanic Number of Inserts

[Hartmaier Alexander]

Hi!

My network monitoring database runs Oracle 10 and the monthly data is over 2GB.
The tables with historical data are partitioned per month, which helps to speedup searches, especially monthly reports, because using a single partition is enough to limit the time range.
My largest historical table currently holds just over 1 billion rows (data of 2 years).
The partitions also make it very easy to drop old data you don't need any more: just remove the partition usage in every table and then drop the tablespace.

My data collector runs every 5 minutes and has to insert about 5.000 rows each interval which is 1.440.000 rows per day (and still a lot less than you have to insert).
Take a look at DBIC::Schema->populate! In 0.07xxx it just calls create multiple times, but in 0.08 that will change so that the statement is only prepared once and executed multiple times which should boost performance again.

I hope that helps ;-)

-Alex


> -----Original Message-----
> From: Ryan Richt [mailto:rjr2 at cec.wustl.edu]
> Sent: Wednesday, January 24, 2007 2:01 AM
> To: dbix-class at lists.rawmode.org
> Subject: [Dbix-class] Help with Titanic Number of Inserts
> 
> 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 at lists.rawmode.org/

*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*