Re: .ini settings for ADD KEY to 30Gb table on 96Gb machine?

[Peter Griffioen]

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 :)