5

I created a new index on a table with 35million records and its been running for nearly 1 day now. Previously when I created indexes it took 20 minutes, there columns were however floats. The new idnex is on a varchar(45)

I used the processlist command which shows the index creation is still in progress with the following output

65417 | Repair with keycache | CREATE INDEX insert_index on checkins(dateinserted)

I was wondering if anyone could give me advice on finding out if the query is actually dead and is just sitting in the process list. Maybe something has gone wrong at some stage and I'm not aware.

Thanks

MozenRath
  • 9,652
  • 13
  • 61
  • 104
Steve
  • 21,163
  • 21
  • 69
  • 92

2 Answers2

8

Your index is building, but very slowly.

MySQL has two methods available for building indexes:

  1. by sorting. This is the fastest method, but uses up a lot of memory.
  2. by keycache. Slow, slow, slow - but uses up little memory.

The keycache method is a bit like insertion sort: values are inserted into the index one at a time. This is the same method used by the server when the INSERT statement is used to add rows to the table.

The sorting method sorts all the values using quicksort, and then builds the index from that. It is very fast, but requires a lot of memory and temporary disk space.

Some server variables can increase the space available to the sorting method, and so allow it to work with larger tables. See myisam_max_sort_file_size

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_myisam_max_sort_file_size

On Linux, you can track the progress of the index repair by checking the size of the temporary files that are used to build the index. The following command will list all the files held open by the MySQL process: 

sudo ls -l /proc/[mysql-pid]/fd

Then check out the size of ones with hashes in their name - these are the temporary files.

Martin
  • 9,674
  • 5
  • 36
  • 36
  • will everything break if i start inserting data again? – Steve Oct 03 '10 at 16:52
  • If the index build is still running, MySQL will not allow you to insert more data into that table. – Martin Oct 03 '10 at 17:18
  • Also I tried to see if the temp file is expanding however I do not how sudo on the box. Any idea of another approach? I'm afraid that the process is in some way crashed and I'm just idling in the mean time. – Steve Oct 03 '10 at 17:28
  • You can run "top" and "iostat". Top should show that one of your MySQL threads is very busy, and iostat should show lots of reading and writing to disk. It is very likely that MySQL is running fine - just taking ages to work through the index. – Martin Oct 03 '10 at 17:56
2

Keep in mind the index size will be 35M*45 at least. If it's a utf8 column then it will be 35M*45*3. That's over 4 gigs! If you don't have tons of RAM to support that it's going to have to do a lot of disk access and really kill performance.

Can you normalize this column out into another table?

If not do the values tend to vary sufficiently in say the first 8 characters? You might be able to get away with just indexing the first 8 then.

monitorme
  • 96
  • 2
  • This is something that I didn't consider at all as I didnt know about designing it correctly :) As a matter of fact the data could easily be normalized down into shorter values in another table. – Steve Oct 03 '10 at 22:20