MySQL Index Ongoing, More Than 12 Hours!

I'm creating an index on a MySQL table with 25,000,000 rows.

The index is on 4 columns which are all VARCHAR(45), average being around 5 characters.

I started this 12 hours ago and it's still running, it's using 14/16 of the server load, around 10% of the RAM. The server is dual quad core 3Ghz, 12GB RAM.

I can't login to SSH to check on progress of anything because of the high load. Basically I'm locked out of the server while it's doing its processing and I have no idea how long it's going to last.

Is it supposed to take this long? Anything I can do to check on progress and/or speed it up?

Thanks!

EDIT: I managed to check running processes through WHM and it's currently on "Repair with keycache". Is that near the beginning or the end?
ADFBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
That means MySQL is using the (very much) slower method to index.  Can't explain it better, so just pasting from: http://stackoverflow.com/questions/3850582/determine-status-of-an-index :

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ADFBAuthor Commented:
Thank you for the information...

The problem that I am faced with now is that I can't login to SSH because of the server load. It just hangs when I try to connect. How to solve that?
0
johanntagleCommented:
I guess you'll need to ask support from your hosting company to do stuff via console.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.