|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
September 27th, 2004, 06:07 AM
|
|||
|
|||
|
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... Quote:
Last edited by howard.cole : September 28th, 2004 at 03:55 AM. Reason: No reply to original question
|
|
#2
September 28th, 2004, 06:51 AM
|
|||
|
|||
|
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
|
|
#3
September 28th, 2004, 07:26 AM
|
|||
|
|||
|
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
|
|
#4
September 28th, 2004, 04:26 PM
|
|||
|
|||
|
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.
|
|
#5
September 29th, 2004, 06:28 AM
|
|||
|
|||
|
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?
|
|
#6
September 29th, 2004, 11:25 AM
|
|||
|
|||
|
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
|
|
#7
September 29th, 2004, 10:26 PM
|
|||
|
|||
|
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.
|
|
#8
September 30th, 2004, 05:14 AM
|
|||
|
|||
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
|
|
#9
January 6th, 2006, 12:35 PM
|
|||
|
|||
|
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
|
|
#10
January 25th, 2006, 05:06 PM
|
|||
|
|||
|
Quote:
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.
|
|
#11
January 26th, 2006, 12:50 PM
|
|||
|
|||
|
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.
|
|
| Viewing: Dev Shed Forums > Databases > MySQL Help > Slow to add an index |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
