At 08:18 AM 5/12/2008, you wrote:
Hi All,
We have a table which is around 100 Million rows. Its a myisam table, but
the db default is innodb.
CREATE TABLE `dc_data` (
`id` decimal(22,0) NOT NULL,
`words` varchar(255) NOT NULL,
`lf_id` decimal(22,0) NOT NULL,
`occurence` bigint(20) NOT NULL,
`date_modified` timestamp NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP) ENGINE=MyIsam DEFAULT CHARSET=utf8
indexs are as below
PRIMARY KEY (`id`),
KEY `KI_IDX_0805090456` (`words`,`id`),
KEY `CI_IDX_0805090456` (`lf_id`)
we have 8 cpu, 8 gb ram.
We use set below parameters at session level
myisam_sort_buffer_size=300MB
myisam_max_sort_file_size=10GB
Each index creation is taking 10hrs, is there any way i can speed up index
creation.
regards
anandkl
Anandkl,
The reason it is taking so long is it is building the index using
the hard drive. You can speed up index creation considerably by allocating
up to 30% of your memory to the key buffer size. This will allow it to
build the index mostly in memory and will be at least 10x faster.
This is done in your My.Cnf file:
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=2666M
Once you have made the change, you will need to restart MySQL.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
