Re: inserts on large tables (60G) very slow

[Manik Dhawan]

Do you mean the MYD file for the corresponding table is 60G?
Thats awful big. What I have figured out on MyISAM tables is that the bigger the table size, the more time to takes to load data into that table because it takes too much time to recreate the Index and then write to MYI file.

Now, I am not sure if you have a table with a Primary or Unique key.

But in my case, I had Primary and unique key based table and even the 'LOAD DATA INFILE' was taking too much time once the table started growing more than 8-10G.

Finally, I decided to create a merge table based on collection of 90 daily tables (as we wanted to store 90 days data). Now, the individual daily tables are no more than 5G and loading happens fast. We now get 3 months data from merged table with a combined size of 400G.

Its all hassle-free insertions now.



Edited 1 time(s). Last edit at 02/22/2010 09:13PM by Manik Dhawan.