1

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

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
4
  • 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, 2011 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, 2011 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, 2011 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, 2011 at 16:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .