.ini settings for ADD KEY to 30Gb table on 96Gb machine?
Hopefully an easy question....I have 11 30Gb tables of satellite image data (1 per year) that I have loaded with "LOAD DATA INFILE..". I need to add a primary key and an index to these and later make a merge table linking them all. This is on a 96Gb 8 core Win7 64 workstation.
After 24 hours of grinding away on the ADD PRIMARY KEY command it still has not added a single index. I've tried setting my.ini parameters to pretty high values but I must be missing one that would allow MYSQL (5.5.3 64bit) to this in reasonable time.
My question is, given about 90Gb of RAM to play with, what settings should I set various buffers to make a couple of indicies on each of these tables in reasonable time? Note that once each year's data is loaded the tables are in effect read-only.
I'm thinking I could change my.ini memory usage to get the indicies on and then cut it back to a less memory-hogging state to run day-to-day. I could also set up a 40gb or so RAM disk if that would help.
Thanks in advance for any help.
Peter G
EG of Table to be indexed
=========================
'modispixelstack_2000', 'CREATE TABLE `modispixelstack_2000` (
`PointId` int(11) NOT NULL,
`ModisTileLoadId` smallint(6) NOT NULL,
`Blue` smallint(6) DEFAULT NULL,
`Red` smallint(6) DEFAULT NULL,
`NIR` smallint(6) DEFAULT NULL,
`MIR` smallint(6) DEFAULT NULL,
`EVI` smallint(6) DEFAULT NULL,
`NDVI` smallint(6) DEFAULT NULL,
`VIQA` smallint(5) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1'
INDEX COMMAND
=====================
ALTER TABLE `modisimagestack`.`modispixelstack_2000` ADD PRIMARY KEY (`PointId`, `ModisTileLoadId`),
ADD INDEX `FlagsIndex`(`VIQA`);
Table INFO
===============
Name modispixelstack_2000
Engine 'MyISAM'
Version 10
Row Format 'Fixed'
Rows 1328408635
Avg Row Length 21
Data Length 27896581335
Max Data Length 5910974510923775
Index Length 1024
Data Free 0
Auto Increment
Create Time '2010-10-22 17:28:32'
Update time '2010-10-22 21:40:24'
Check Time ''
Collation 'latin1_swedish_ci'
show variables like '%buffer%';
================================
bulk_insert_buffer_size 16777216
innodb_buffer_pool_size 10485760000
innodb_change_buffering inserts
innodb_log_buffer_size 2147482624
join_buffer_size 1048576000
key_buffer_size 8388608000
myisam_sort_buffer_size 4294967295
net_buffer_length 1048576
preload_buffer_size 268435456
read_buffer_size 268435456
read_rnd_buffer_size 268435456
sort_buffer_size 4294967295
sql_buffer_result OFF
show variables like '%myisam%';
===============================
myisam_data_pointer_size 6
myisam_max_sort_file_size 16777216000
myisam_mmap_size 18446744073709551615
myisam_recover_options OFF
myisam_repair_threads 4
myisam_sort_buffer_size 4294967295
myisam_stats_method nulls_unequal
myisam_use_mmap OFF