MySQL Index Ongoing, More Than 12 Hours!

Posted on 2011-10-08
Last Modified: 2012-05-12
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?


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?
Question by:ADFB
    LVL 24

    Accepted Solution

    That means MySQL is using the (very much) slower method to index.  Can't explain it better, so just pasting from: :

    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

    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.

    Author Comment

    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?
    LVL 24

    Expert Comment

    I guess you'll need to ask support from your hosting company to do stuff via console.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now