| building new index on big tables [message #423] |
Fri, 24 November 2006 16:53  |
 rds13
Messages: 1
Registered: November 2006 |
Junior Member |
|
|
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.
|
|
|
|
| Re: building new index on big tables [message #429 is a reply to message #424 ] |
Mon, 27 November 2006 12:30  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
