-----Original Message----- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 3:40 PM To: mysql@lists.mysql.com Subject: Re: Partition Help
At 02:03 PM 9/26/2006, you wrote: >I'm working on a project in which we'd like to convert from Oracle to >MySQL. We need to partition our data for speed concerns. Currently in >Oracle I create 8, 3 hour partitions for each day (Currently running >450M -750M rec inserts/day). I was looking for matching functionality in >MySQL, but it seams daily partitions are as close as I'm going to come. > > > >We're running 5.1.10 and I'm having a bit of trouble creating partitions >in both new tables and altering old tables. Below is one example of >what I've tried. > > > >Can anyone shed some light on this subject? > > > >-Mike Mike, How is this table being updated? a) From one source like a batch job? b) Or from hundreds of users concurrently? If a), then why not just create 1 table per day (or 3 tables per day) and when you want to reference (the entire day or) a week, just create a Merge Table? http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html If b), then you need to use InnoDb tables because that has row locks compared to MyISAM's table locks. Mike We're using the Load infile function to load the data generated by another process. We do not do updates, but occasionally need to either walk the table or run a query against it. On Oracle, we currently need 3 hour partitions to keep the 5 indexes timely. This system handles 450-750 Million inserted rows per day with 5 fields being indexed. This number will be closer to 2 Billion records / day by Spring 2007 we've been told. For example, I diverted the full flow of data to MySQL for 15 minutes and inserted 9 Million records with a back up of loader files. I need to speed this up. Unfortunately, table structure and indexes are static and cannot be changed. -Mike >create table t1 (c1 int default NULL, c2 varchar(30) default NULL, c3 >datetime default NULL) engine=myisam PARTITION BY RANGE(to_days(c3)) > > PARTITION p0 VALUES LESS THAN (to_days('2006-09-24'))( > > SUBPARTITION s0a > > DATA DIRECTORY = '/FW_data1' > > INDEX DIRECTORY = '/FW_indx1' > > ), > > PARTITION p1 VALUES LESS THAN (to_days('2006-09-26'))( > > SUBPARTITION s1a > > DATA DIRECTORY = '/FW_data2' > > INDEX DIRECTORY = '/FW_indx2' > > ) > > PARTITION p2 VALUES LESS THAN (to_days('2006-09-28'))( > > SUBPARTITION s2a > > DATA DIRECTORY = '/FW_data3' > > INDEX DIRECTORY = '/FW_indx3' > > ) > >); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]