#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    103
    Rep Power
    11

    Slow to add an index


    Perhaps if i rephrase the questiion ...

    When I use the alter table ... add index four times on a large table (1,000,000 records) It takes ages (4 - 8 minutes for each index).

    What can I do to speed the add index up?

    T

    Original question...
    Hi,

    I have a few tables that I use for offline viewing that have approximately 1,000,000 records and 20 fields, many of which are large varchars. The tables are actually created by merging several InnoDB tables, which makes the processing of the tables very fast. Unfortunately, the time to create these ISAM tables grows every day. The biggest time spent creating the tables is adding the indexes. Firstly the ISAM tables are created witout adding indexes (taking an acceptable 2 minutes), and then the indexes are added once the table inserts are complete. Each index takes a minimum of four minutes to create, increasing to 8 minutes for the fourth index. What can I do to speed up the index creation?
    Last edited by howard.cole; September 28th, 2004 at 03:55 AM. Reason: No reply to original question
    Howard Cole
    www.selestial.com
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    103
    Rep Power
    11
    Partial solution:

    The problem with the alter table ... add index command is that it seems to create a full copy of the table and then populate the table then rename the table. So...
    If I put all the new indexes into one alter table command, it works much faster than multiple alter table commands!

    Old code:

    Code:
     alter table t add index index1(col1);
       alter table t add index index2(col2);
       alter table t add index index3(col3);
       alter table t add index index4(col2, col3);
    New code:

    Code:
       alter table t add index index1(col1),
         add index index2(col2), 
         add index index3(col3),
         add index index4(col2, col3);
    I shall also experiment with the mysql variable myisam_sort_buffer_size and see if that improves speed further. I shall post any results here
    Howard Cole
    www.selestial.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    103
    Rep Power
    11
    For anyone interested, the doubling of the myisam_sort_buffer_size to 16M made little or no difference to the speed of the alter table command. It still takes 5 minutes.

    Howard Cole
    www.selestial.com
    Howard Cole
    www.selestial.com
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2002
    Location
    Binghamton, NY
    Posts
    536
    Rep Power
    25
    Generally you will want to set myisam_sort_buffer_size as large as possible. Like if you have a gig of ram, you might set it to 800MB before adding the indexes. You can do this on a per-session basis, such as:
    Code:
    SET SESSION myisam_sort_buffer_size = 800*1024*1024;
    ALTER TABLE foo ADD INDEX ...;
    Keep in mind that you still don't want to swap out, but it should generally be large. The buffer is only used when REPAIR'ing or ALTER'ing or bulk loading data, so it will only be allocated occasionally.

    The last thing to look at, when you are doing the actual ALTER TABLE, look in the SHOW PROCESSLIST; and see if it saying something like: Repair by sort; or Repair by keycache. myisam_sort_buffer_size only matters for the Repair by sort, not for the keycache one.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    103
    Rep Power
    11
    Thanks HarrisonF, I set the session sort buffer size to approx 70% of memory size, and it speeded the alter command from 5.5 mins to approximately 5.0 mins. Looking at the output from the process list the following occurs:

    160 seconds : Copy to tmp table
    140 seconds : Repair by sort
    ~1 second: rename table

    The major part of the query is therefore the copy to tmp table. Can this be speeded up somehow?
    Howard Cole
    www.selestial.com
  10. #6
  11. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    What do you get if you look at the OS system load during index creation?
    Are you running windows or *nix?
    How large is your table?
    Could it be about 1.4GB?

    A small test on my workstation with a 800GB file gives me a copy time in the vicinity of 65-75 seconds. Depending on if I copy to another disk or the same disk. This sums up to somewhere round 10-12MB/sec which is fairly average on normal IDE disks.

    If that is the case then the problem currently is in the disk speed so unless you want to speed up your disks you can add more RAM to the system to be used as file cache. But if you have this size of files then the OS usually wants to write a lot to disk anyway.

    One suggestion:
    If you are using InnoDB as source. Why not use InnoDB for this table also. I'm not sure which procedure that InnoDB uses to create their indexes but it might be faster than the MyISAM.

    Or you can wait and read the advice from HarrisonF he usually knows what he is talking about
    /Stefan
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2002
    Location
    Binghamton, NY
    Posts
    536
    Rep Power
    25
    Thanks sr

    InnoDB is actually slower for creating indexes than MyISAM. MyISAM has many optimizations that it does for bulk loading.

    With that said, how big is this table? How fast do you expect it to take? When you started, it took about 30 minutes to add the indexes, and now you do them all in ~5? Adding 4 indexes isn't the fastest thing in the world. You didn't mention the hardware you are doing this on. If you have more than one CPU, you can set myisam_repair_threads to however many CPU's you have. That way it will actually create multiple indexes all at the same time, which then can go across multiple CPUs.
    You could test this out even if you have just a single CPU, but I doubt you would gain much, since you are CPU/memory bound for creating the indexes.

    One last idea, is that you can create the table with the indexes already there. But then disable them with something like:
    Code:
    ALTER TABLE foo DISABLE KEYS;
    Then load the data into the table thru your normal means.
    Then finally turn the indexes back on, which causes MySQL to build them in a batch.
    Code:
    ALTER TABLE foo ENABLE KEYS;
    This should avoid the entire Copy to tmp table; part in theory, which should cut that 160 seconds off. Let me know if that works.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    103
    Rep Power
    11

    Smile


    Great thinking HarrisonF, got the times down to approximately 2.5 minutes using the disable keys method, presumably by not creating a temporary table.

    So... optimised the index creation from 30 to 2.5 minutes. Not bad!

    Many thanks to sr and especially HarrisonF.

    Howard Cole
    www.selestial.com
    Howard Cole
    www.selestial.com
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2006
    Posts
    1
    Rep Power
    0

    Why is drop index so slow?


    I am having a similar issue when I attempt to drop an existing index from an INNODB database. I am curious why droping takes so long since it doesn't have to rebuild any indexes.

    Thanks
    Chris McIntosh
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    103
    Rep Power
    11
    Originally Posted by compavalanche
    I am having a similar issue when I attempt to drop an existing index from an INNODB database. I am curious why droping takes so long since it doesn't have to rebuild any indexes.

    Thanks
    Chris McIntosh
    If things have not changed in InnoDB tables since I last looked then this is probably the same issue. Although the indexes are not rebuilt, you will probably find that a table copy still occurs - which is what is taking the time. You can check this by monitoring what happens during the DROP.

    If this does turn out to be the case then you can attempt the suggestions used in this thread. i.e disabling the key or tuning the appropriate variables.

    Hope this helps.
    Howard Cole
    www.selestial.com
  20. #11
  21. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Which index are you trying to drop?

    The data in InnoDB tables is stored as leafs on the binary tree which is the primary key. If you are trying to drop the existing primary key InnoDB will create a pseudo primary key.
    In that case you actually just switching one key for another.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo