Slow alter table

MySQL & MariaDB Other MySQL® Questions
1

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

2

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?

3

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

4

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. :wink:

Can you provide the output for SHOW PROCESSLIST?
This tells us how mysql is creating the index.

5

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

6

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

7

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.