-----Original Message----- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 5:27 PM To: mysql@lists.mysql.com Subject: RE: Partition Help
At 03:37 PM 9/26/2006, you wrote: > > > >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 > > >Mike, > I've done a lot of Load Data with large tables and as you no >doubt >discovered, as the number of rows in the table increases, the insert >speed >decreases. This is due to the extra effort involved in maintaining the >index as the rows are being loaded. As the index grows in size, it takes > >longer to maintain the index. This is true of any database. MyISAM >tables >are going to be faster than InnoDb in this case. > >You can speed it up by: >1) Add as much memory as possible in the machine because building the >index >will be much faster if it has lots of ram. >2) Modify your My.Cnf file so key_buffer_size=1500M or more. (Assuming >you >have 3gb or more installed) This allocates memory for building the >index. >3) If the table is empty before you add any rows to it, Load Data will >run >much faster because it will build the index *after* all rows have been >loaded. But if you have as few as 1 row in the table before running Load > >Data, the index will have to be maintained as the rows are inserted and >this slows down the Load Data considerably. >4) Try throwing an exclusive lock on the table before loading the data. >I'm >not sure but this might help. >5) If your table already has rows in it before running Load Data, and >the >table has indexes defined, it is much faster if your disable the indexes >to >the table before running Load Data, and then enable the index after Load > >Data has completed. See "Alter Table Enable/Disable Indexes" for more >info. >6) If you are using Alter Table to add indexes after the table has data, > >make sure you are adding all indexes in one Alter Table statement >because >MySQL will copy the table each time the Alter Table is run. > >If you are going to be adding 2 billion rows per day, you might want to >try >1 table per hour which will reduce the number of rows to < 100 million >which may be more manageable (assuming 24 hour day). You can then create >a >merge table on the 24 rows so you can traverse them. You can of course >create a merge table just for the morning hours, afternoon hours, >evening >hours etc.. Name each table like: 20060925_1400 for 4PM on 9/25/2006. Of > >course you may also want to summarize this data into a table so you >don't >need all of this raw data lying around. > >Hope this helps. > >Mike > > >Thanks for the advice. > >We've got 12GB of RAM, I'll increase the key_buffer_size. Unfortunately >I can't turn off indexes, then index after. At these rates, I'd never >catch up. I don't agree. It takes longer to build the index than to load the data if you have indexes active when loading the data. But if you disable the index, or not have any indexes on the table during the Load Data, then re-enable the index later, MySQL will build the index at least 10x faster if you have a large key_buffer_size because it does it all in memory. I've had Load Data go from 24 hours to 40 minutes just by adding more memory to key_buffer_size and disabling the index and re-enabling it later. I'd recommend using at least 6000M for key_buffer_size as a start. You want to try and get as much of the index in memory as possible. >I had hoped I could use partitions like in Oracle. 1 partition every >hour (or 3). I don't think the merge tables will work however. We >currently only keep 15 days of data and that fills the array. If a merge >table uses disk space, it won't work for us. A Merge Table can be built in just ms. It is a logical join between the tables and does *not* occupy more disk space. Think of it as a view that joins tables of similar schema together vertically so it looks like 1 large table. Mike Ah, very cool. Thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
