I had same problem. I fixed it by adding/adjusting the myisam_max_sort_file_size and myisam_max_extra_sort_file_size variables in my.cnf to much larger values.
I adjust it to 4096M in my case (data = 35 Gb, index = 2.1 Gb ) and the indexes go much faster. On similar box my index creation takes about an hour or so. You need the processlist to show "repair by sort" not "repair with keycache". I'm not an expert - but this worked for me. Kyle Renfro [EMAIL PROTECTED] ----- Original Message ----- From: "Jeffrey Horner" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 29, 2004 12:05 PM Subject: Creating index on very large table > Hi folks, > > I've got a problem creating an index on a MYISAM table with 302,000,000 lines, > roughly 58 GB on disk. Attached you will find the table definition, output > of "mysqladmin variables", and "mysql -e 'show status'". > > After creating the above-mentioned table, I ran: > > ALTER TABLE test_table ENABLE KEYS; > > loaded the data and then ran (and is currently still runing): > > ALTER TABLE test_table ENABLE KEYS; > > show processlist reveals; > > +--------+----------------------+------------------------------------------- + > | Time | State | Info | > +--------+----------------------+------------------------------------------- + > | 948878 | Repair with keycache | alter table fd_aetna_trad_clm enable keys | > > and it's still running! That's roughly 11 days and it's not complete yet! > Here's the current file sizes under the mysql root directory... > > -rw-rw---- 1 mysql mysql 61361175364 Mar 18 00:51 test_table.MYD > -rw-rw---- 1 mysql mysql 7320667136 Mar 29 10:07 test_table.MYI > -rw-rw---- 1 mysql mysql 10190 Mar 17 21:16 test_table.frm > > The box has 1GB of memory and a P4 1.6GHz processor with EIDE disks (dma > enabled) and no raid. The system is Linux 2.4.21-rc7-openmosix with smp > and the filesystem is ext3, running MySQL version 4.0.13-log. > > So, if any of you out there have time to look at the attached file, I'd greatly > appreciate it. I'd like to know when the index creation will potentially end, > and also if I can get the index creation to complete in a shorter amount of > time, given the memory and cpu specs of the box. > > Thanks, > > Jeff > > ===== > J. Horner Software > www.jhorner.com > 615-347-6899 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
