SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Converting to InnoDB

    Hi there,

    We have a live web application that's been running successfully for 3 years. Over time, our data has become more sensitive and requires support for MySQL transactions to guarantee data to the end user.

    All of our existing tables are using the MyISAM engine and we'd like to convert to InnoDB. It seems like the switch is relatively painless (just fire the queries). However, I do have a couple of questions:

    • Are there any specific kinds of queries that would need to be rewritten because of the switch to InnoDB (i.e. queries that would begin to fail because of InnoDB)?
    • Any recommendations for InnoDB tuning?
    • How much more RAM would an entire InnoDB database consume versus a predominantly MyISAM database?
    • What does InnoDB not "play friendly" with that works with ease using MyISAM, if any at all? I know full-text search doesn't exist with InnoDB.

    Essentially, I don't want to crash the application because of this switch. Any pointers/tips will be much appreciated for the migration...

    Thanks in advanced.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    http://www.mysqlperformanceblog.com/...sam-to-innodb/

    I found that via a google search, don't know how true each bit is, but before you do migrate over make sure you do a full backup of all tables before you proceed. If you have a development server, you could test whether it would work either with a copy of the live data or with a sample data set.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    To convert an already existing table from one engine type to another you just need to ALTER the table and use a new engine. For example, if you have a table called "countries" which is using MyISAM to convert this you simply issue: ALTER TABLE countries ENGINE = InnoDB;

    This same technique can also be used to reorganize the table when lots of INSERTs and DELETEs have taken place which leaves the table fragmented. This will defragment your tables.

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    In most cases the switch should be painless. If you have lots of data you may need to fine-tune some queries or add indexes as mysql can choose different optimisation paths for innodb.

    One thing that comes to my head is about inserts - if you do a lot of them in a loop somewhere in your code then you really must surround them in a transaction since isolated inserts are very slow for innodb.

    If you have access to the server administration then it's a good idea to turn on innodb_file_per_table before you change the engine type. If it's off then the server may never free up the hard drive space when you delete some large amounts of data in the innodb tables.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •