MySQL Forums
Forum List  »  MyISAM

Re: .ini settings for ADD KEY to 30Gb table on 96Gb machine?
Posted by: Peter Griffioen
Date: November 03, 2010 08:16PM

Hmm, I'm pretty sure merge tables are the way to go. I've already started down that route.

Here's something that was a surprise: Its quicker to :

Test A
-------
1. Disable keys
2. Load Data Infile to MYISAM on hard drive
3. Enable keys

which takes about 8 hours for 115 File Loads and 30gb data and then about 4 hours to create 35gb indicies.

than to

Test B
-------
1. Disable Keys
2. Load Data Infile to MEMORY (30 minutes here!)
3. Enable just primary key (PointId, ModisTileLoadId) (less than an hour here)
4. Alter Table to MYISAM (??? long long time here!)

for the same data. This is because step 4 seems to take FOREVER! Stopped after 4 hours and only 25% written to disk going on files sizes from test A.

I don't have enough memory to do both the Primary index and the flags index on a 30gb Memory Table as it seems even with BTree indicies rather than the Hash default, the MEMORY indicies take more memory than that used on disk for a corresponding MYISAM table.

Thus I'm stuck to doing this the slow way (Test A) which I've done for 4 of the 11 years so far. At least the queries should have reasonable performance.

Now my boss is talking about buying a Dual 6-core Xeon Dell with 192gb of memory! Maybe I should co-opt that beast :)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.