Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a mysql 5.1 server with a database of approximately 450 tables, taking up 4GB . The vast majority of these tables (all but 2) are MyIsam. This has been fine for the most part (don't need transactions), but the application has been gaining traffic and certain tables have been impacted because of table-locking on updates. That's the reason 2 of the tables are InnoDB now.

The conversion on the smaller tables (100k rows) don't take long at all, causing minimal downtime. However a few of my tracking tables are approaching 50 million rows. Is there a way to speed up an ALTER TABLE...ENGINE InnoDB on large tables? And if not, are there other methods to convert minimizing downtime on these write-heavy tables?

share|improve this question
1  
Something to keep in mind: multiple questions in a single post tend to discourage people who can answer one of the questions from posting an answer. –  BenV Jan 6 '11 at 3:37
    
I VtC as this is rather complicated to answer. You should open as several questions individually. –  jcolebrand Jan 6 '11 at 4:31
    
i'll gladly take the advice to make it into a single question, but is it recommended to delete this question and just open a new one? the rewrite would mostly be removing the second 2 bullets and altering the first (I updated the title as well to reflect which storage engines) –  Derek Downey Jan 6 '11 at 4:49
    
Either would be fine. It's usually easier to write two other questions and delete the one. However, you could just as easily leave this one for "reference" and have the other two refer back to it, as a "this is my overall goal" question. –  jcolebrand Jan 6 '11 at 4:52
    
edit this one down into one bullet, then post the followup questions. –  Brian Ballsun-Stanton Jan 6 '11 at 5:19

3 Answers 3

up vote 7 down vote accepted

Let me start by saying, I hate ALTER. It's evil, IMHO.

Say, this is your current table schema -

CREATE TABLE my_table_of_love (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=MyISAM CHARSET=utf8;

Here's the path I recommend -

Create a new table object that will replace the old one:

CREATE TABLE my_table_of_love_NEW (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARSET=utf8

Insert all the rows from the old table by name into the new table:

INSERT INTO my_table_of_love_NEW (id,my_value,date_created)
SELECT id,my_value,date_created FROM my_table_of_love;

Smoke test your migration:

SELECT COUNT(*) FROM my_table_of_love_NEW;
SELECT COUNT(*) FROM my_table_of_love;
SELECT a.id,a.my_value,a.date_created FROM my_table_of_love_NEW a
LEFT JOIN my_table_of_love b ON (b.id = a.id)
WHERE a.my_value != b.my_value;

Swap table names so that you can maintain a back up in case you need to rollback.

RENAME TABLE my_table_of_love TO my_table_of_love_OLD;
RENAME TABLE my_table_of_love_NEW TO my_table_of_love;

Proceed to regression testing.

This approach becomes more and more preferable with tables with multiple indexes and millions of rows.

Thoughts?

share|improve this answer
    
Agreed ... although if it's heavily transactional, you might need to take the database down while doing this. (but an alter table's going to give you a longer period of downtime, most likely) –  Joe Jan 6 '11 at 23:46
    
Yeah, I figured it would require downtime for the more active tables. I'm going to have to run some tests, but why would ALTER TABLE take longer than INSERT INTO...SELECT on 50 million rows? –  Derek Downey Jan 7 '11 at 14:15
    
It won't. Basically MySQL does internally exactly as this poster suggested. It creates a copy of the definition and trickle loads into the copy. –  Morgan Tocker Mar 22 '11 at 15:11
    
I like this method because it skips the "copy to tmp" part, which can take some time for large tables. –  Haluk Jul 28 '12 at 1:43

1) Loss protection is a function of paranoia. Always make a backup. If you're really paranoid, make a backup then restore from the backup.

2) This page of the MySQL manual has instructions to convert table types.

The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... ENGINE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM ....

3) PostgreSQL does full-text search, The Sphinx Engine seems to do it for MySQL

share|improve this answer
    
i will definitely look into sphinx, as I've only recently heard about it. –  Derek Downey Jan 6 '11 at 12:57

It's X times easier to optimize whole server (memory configuration, caches, indexes) when you have only one engine used. Mixing myisam with innodb on large databases will always stuck at some point forced by some comprosise for both engine to work good (but not excelent :)

I recommend You to interest in some dedicated full text search engines like sphinx, lucene (solr) and get rid of it from database layer.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.