building new index on big tables

MySQL & MariaDB Other MySQL® Questions
1

Hye,

We have tables with over 10 or 20 millions of records (MyISAM tables if that matters).
Building new index, removing old index is taking at least a quarter of hour.
While doing this operation, MySQL seems to lock the table or at least the load is so big that any operations on the table is delayed after the end of index operation.

I wonder how DBA usually deal with this kind of problem on MySQL ?

I cannot imagine a strategy which would help keeping the service a hundred per cent available while achieving an index reconfiguration.

2

Hi,

I guess there’s no real solution for that problem. We’re having the same problem on our database (also 10/20 millions records per table).

If you can afford to stop your service while you’re generating the indexes, you should try stopping the server, change my.cnf’s parameters for best memory usage on creating indexes, alter the index, and than undo your change and start mysql again. I’m not sure what would be the best parameters to speed up index generation in mysql, but I guess you should try to increase myisam_sort_buffer_size?

Maybe Peter can help you more with that. )

Greetings
Christian

3

Right,

This is surely the problem with MySQL operations.

There are two tricks which I use to make large index creation in MySQL less painful (or any alter table for that matter)

  1. Using smaller tables with data spread across them. It works for some application it does not for others.

  2. Use two MySQL servers in Master-Master Active-Passive configuration and perform “upgrade” on the slave then switch the roles and repeate.

Sometimes you can also make things read-only and copy stuff in new table of appropriate structure instead of runnin ALTER TABLE