# Slow alter table **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [March 21, 2007, 1:00pm UTC](https://forums.percona.com/t/slow-alter-table/252 "2007-03-21T13:00:38Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![ale.capu](https://avatars.discourse-cdn.com/v4/letter/a/b9e5f3/32.png) [@ale.capu](https://forums.percona.com/u/ale.capu)\ **Post date:** [March 21, 2007, 1:00pm UTC](https://forums.percona.com/t/slow-alter-table/252/1 "2007-03-21T13:00:38Z")
Hi, I’ve a BIG table of 80.000.000 record (quite 30GByte of data and 7GByte of index). I need to do some alter table. Before do that I make a DISABLE KEYS and when I finish all ALTER TABLE i make a ENABLE KEYS on the tables. My alters table are simply ADD 3 INDEX and 2 FIELDs… I use a Intel Core 2 DUO with 2GHz and 3G of RAM on a 400GByte IDE disk at 10k rounds… All this operations have take 4 Days… is normal ??? Tnk ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [March 21, 2007, 7:17pm UTC](https://forums.percona.com/t/slow-alter-table/252/2 "2007-03-21T19:17:27Z")
You have a lot of rows but it still sounds like an awful lot of time. Which storage engine are you using? Is it CPU load or IO load that is high on the server while creating the indexes? ---
**Author:** ![ale.capu](https://avatars.discourse-cdn.com/v4/letter/a/b9e5f3/32.png) [@ale.capu](https://forums.percona.com/u/ale.capu)\ **Post date:** [March 22, 2007, 2:32am UTC](https://forums.percona.com/t/slow-alter-table/252/3 "2007-03-22T02:32:37Z")
Hi, tnk you for you reply… I’m using a MyIsam storare engine and my CPU load is very very low (3-4%)… my problem is that MySQL use massively my IDE disk about 200.000.000 of read and 50.000.000 of write !!! Probably MySQL is very wrong configured… I’m using my-medium coniguration file. Tnk a lot for your help, best regards, Ale ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [March 22, 2007, 5:00am UTC](https://forums.percona.com/t/slow-alter-table/252/4 "2007-03-22T05:00:55Z")
Is this server doing a lot of other things or why have you chosen the my-medium configuration? My-medium sounds to small for a setup like yours. One of the main things that slowes down index creation is the server variable: myisam\_sort\_buffer\_size If this is to small then basically all sorting when creating the index will be against disk and that is as you noticed _very_ slow. 😉 Can you provide the output for SHOW PROCESSLIST? This tells us how mysql is creating the index. ---
**Author:** ![ale.capu](https://avatars.discourse-cdn.com/v4/letter/a/b9e5f3/32.png) [@ale.capu](https://forums.percona.com/u/ale.capu)\ **Post date:** [March 22, 2007, 5:59am UTC](https://forums.percona.com/t/slow-alter-table/252/5 "2007-03-22T05:59:52Z")
Oh… I need to run again my ALTER TABLEs for test SHOW PROCESSLIST… I’m preparing a TEST machine (like one on service) for tuning it… Have you some configuration to suggest me?? Or, do you know if exists a tools for automatic tuning MySql ?? Tnk you very much for your help, Ale ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [March 22, 2007, 6:51am UTC](https://forums.percona.com/t/slow-alter-table/252/6 "2007-03-22T06:51:37Z")
I din’t understand if this was a standalone server or not but hare comes a few tip. Start the OS and the other applications that need to run on that server. Check how much RAM you have free. As a rule of thumb, when you are running MyISAM tables, 25% of this is what you should reserve for MySQL internal cache and the remaining 75% should be used for OS file cache. Then you can take a look at my-large or my-huge configs and decide which mostly relates to your needs. The most important variables that you can tune is: key\_buffer\_size sort\_buffer\_size and for the index creation: myisam\_sort\_buffer\_size myisam\_max\_sort\_file\_size ---
**Author:** ![tanj](https://avatars.discourse-cdn.com/v4/letter/t/e5b9ba/32.png) [@tanj](https://forums.percona.com/u/tanj)\ **Post date:** [March 22, 2007, 8:10am UTC](https://forums.percona.com/t/slow-alter-table/252/7 "2007-03-22T08:10:55Z")
Hello, MySQL can be very slow when it comes to data warehousing. Trust me, I’ve got the same problems as yours ) You can use the variables that sterin gave you, but I did not see the performance rise by a large amount. ALTER TABLE operations are typically slower the more rows you have. I’ve already posted some bugs at MySQL and the guys there seem to be aware of this problem. I’m considering migrating to PostgreSQL for large data, the 8+ versions are considered to provide good performance for data logging purposes.