16

I am using AWS RDS Read Replica. It constantly has issues with Magento's Memory engine tables. For backup and read replicas RDS loves InnoDB. Can I safely change all tables to InnoDB?

In addition I get the following warning from AWS:

DB Instance magento-monin-prod-db contains MyISAM tables that have not been migrated to InnoDB. These tables can impact your ability to perform point-in-time restores. Consider converting these tables to InnoDB. Please refer to http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html#MySQL.CommonDBATasks.Tables

Plausible Answer

Still interested in feedback. I will add this as an answer if I don't find any issues within the next 24 hours. The steps I took below appear to be safe, so far. My biggest concern was Magento's Memory Engine tables (tables ending in_tmp) and the impact it might have on indexing.

Here is what I did:

  1. SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE (ENGINE = 'Memory' OR ENGINE='MyIsam') AND TABLE_SCHEMA='magento_db'

    • For me this returned mostly temporary index tables and magento module tables, so not a lot of critical core tables to be concerned about and few enough tables that I can easily execute another alter table if stuff hits the fan.

  2. For each table returned I executed: Alter table {table-name} ENGINE=InnoDB;

I'd be nervous to try this if none of your tables are InnoDB. But, as I said before, there were only a few core tables on my instance that needed to be modified.

Improve this question
5
  • Have you been running this in production for long? If so, how is it going - did it cause you any issues? Thinking primarily about the *_tmp index tables which are currently the MEMORY engine.
    – Michael Parkin
    Jan 24, 2016 at 12:04
  • 1
    I haven't noticed anything unusual.
    – tylersDisplayName
    Jan 24, 2016 at 14:39
  • Great, thanks for confirming - we will give this a go and report back too.
    – Michael Parkin
    Jan 28, 2016 at 18:42
  • @michael parkin keep in mind that we are using Solr search. See the other answers that speak to how this could potentially impact search.
    – tylersDisplayName
    Jan 28, 2016 at 19:08
  • 1
    We've been running this in most of our production sites (MariaDB 10.0), all tables (including Memory) running as InnoDB - works great
    – Michael Parkin
    May 8, 2016 at 18:36

3 Answers 3

Reset to default
12

It is fine to change the data type to InnoDB, assuming one of the following is true:

  1. You are using MySQL 5.6.4+ where the InnoDB storage engine supports fulltext search
  2. You are not using the default Magento Search functionality which relies on underlying MyISAM fulltext search capabilities. That functionality is troublesome to begin with and the feature set provided in default Magento Search leaves a lot to be desired so I'd suggest using Lucene or Sphinx or best yet Algolia hosted search.

Personally i'd recommend doing this with the Magento DB Repair Tool to minimize risk and also check for any other DB configuration drift or problems. InnoDB is the ideal engine, it's fulltext limitations notwithstanding.

Improve this answer
5
  • 1
    We're using Solar search. So, we should be in the clear as far as search is concerned.
    – tylersDisplayName
    Aug 23, 2015 at 17:52
  • I wouldn't consider InnoDB the ideal engine at all. It's extremely slow compared to MyISAM if you have a lot of search queries. I often hear that InnoDB is faster doing updates and most queries are updates so it's faster. I see the total opposite. For every site I have, I have far more search queries that update/add queries. I tried switching all my tables to InnoDB and page load time went from basically no delay (maybe 0.1 second) to 10 seconds! I converted everything back to MyISAM because it's the ideal engine for speed (and it support fulltext search).
    – Tim Eckel
    Oct 20, 2015 at 19:24
  • Tim, I "KIND" of agree, mostly because I've seen time & again that @ben-lessani-sonassi is just flat-out proven correct & MySQL is rarely a REAL drag on overall perf w/ the # of OTHER systems that need optimization for sub-800ms responses even at load BUT InnoDB is key b/c Mage Core is writing to DB ~ 10X to log each user "view" action plus Solr is best for search perf & qualilty :)
    – Bryan 'BJ' Hoffpauir Jr.
    Jan 28, 2016 at 18:59
  • 1
    So, how does converting what's supposed to be InnoDB handle all those foreign key relationships and how complete are deletions that depend on delete on cascade from those fkey relationships? In other words, how do you handle the litter that will be left over by not having the relationships in place?
    – Fiasco Labs
    Feb 15, 2016 at 6:07
  • @FiascoLabs It's been a while since I have checked out this comment thread but the focus of the Q/A is to convert FROM MyISAM TO InnoDB. I'm guessing the ones that have the Relational features your mention probably already are InnoDB. MyISAM doesn't support FK's nor Transactions as of MySQL 5.7 though InnoDB does though it deviates from the SQL standard a little
    – Bryan 'BJ' Hoffpauir Jr.
    Oct 26, 2017 at 23:38
2

Afaik you should not convert all tables to InnoDB.

catalogsearch_fulltext should stay MyISAM, because InnoDB has no fulltext search support, at least not until MySQL 5.6 (iirc).

For all other tables, though, it should be safe.

Improve this answer
1
2

I just changed the MySQL default engine to InnoDB and most of my Magento tables just miraculously transformed themselves to InnoDB (a few are still MyISAM and some are Memory).

Just thought I'd share this...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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