Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a site with a decently big database, 3Gb in size, a couple of tables with a dozen million records.

It's currently 100% on MyISAM, and I have the feeling that the server is going slower than it should because of too much locking, so I'd like to try going to InnoDB and see if that makes things better. However, I need to do that directly in production, because obviously without load this doesn't make any difference.

However, I'm a bit worried about this, because InnoDB actually has potential to be slower, so the question is:

If I convert all tables to InnoDB and it turns out i'm worse off than before, can I go back to MyISAM without losing anything?

Can you think of any problems I might encounter? (For example, I know that InnoDB stores all data in ONE big file that only gets bigger, can this be a problem?)

Thank you very much
Daniel

share|improve this question

1 Answer 1

I tried to change the DB engine several times in mysql, and I did not face any problems.

However, I prefer to do it on a testing machine. You need just to export the database and import it on the testing machine. You can then change the engine and check for errors/warnings/data loss if any. For generating the load, you can create your own script to simulate a real load, or you can use an existing tool to do it. For this point, you have to use a machine that is powerful enough to judge the performance in a reasonable way.

share|improve this answer
    
you have to consider as well the applications that connect to the database - if you require transactions when you switch to myisam from innodb then you will loose this capability. –  silviud Jan 17 '11 at 16:58
    
@silviud: Yes, you are right. The OP said his DB is MyISAM 100%. So , he should not be using transactions at the moment. Isn't it? :) –  Khaled Jan 17 '11 at 17:12
    
Yes, exactly, I know there are things that InnoDB can do and MyIsam can't, but i'm already on MyISAM, and wouldn't start using transactions until I actually get a feel for the new performance profile, so I can go back easily –  Daniel Magliola Jan 17 '11 at 17:21
    
myisam pro: - fully text searchable, lower maintenance in a way cons: no transactions innodb: pro transactional cons: not fully text searchable - but this may change soon –  silviud Jan 19 '11 at 16:25

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.