CODEWITHSUNDEEP
mysqlamazon-web-servicesmariadbmyisamrds
Jason Hitchings
Jason Hitchings

Reputation: 677

Changing Default Storage Engine Amazon RDS MariaDB

I have hundreds of large tables that I have migrated to RDS MariaDB from my MySQL DB (using the amazon migration service). All of the storage engines went from MyISAM to InnoDB. This has a catastrophic impact on performance.

In short, I'm looking to change the default storage engine to MyISAM and then re-migrate all of the tables over. I'm open to any other ideas about how to do this as well (such as using some sort of setting in the migration tool). I don't really want to create a script to change them over, because this means I'll need to do this every time I import data.

I've tried changing the ParameterGroup default_storage_engine, but when I go into the ParameterGroup for the instance I am running, the default storage engine shows up as read-only.AWS Console Screenshot

I've tried creating a new parametergroup as well, and the same happens. The value shows up as "modifiable: false".

Any help much appreciated. I've looked around at other answers, but I don't think modifying my.cnf is relevant to RDS. If it is, please let me know.

Upvotes: 1

Views: 1778

Answers (2)

Rick James
Rick James

Reputation: 142366

A common performance problem when suddenly switching to InnoDB has to do with "transactions".

Probably what happened is that every query became its own transaction because autocommit = ON. (Don't turn it OFF, that will lead to worse problems.) You need to understand transactions, at least enough to combine statements together in blocks like

BEGIN;
multiple statements, usually more than one, but not a huge number
COMMIT;

This (in many cases) will regain a bunch of the lost performance.

More discussion on conversion is here .

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562631

Apparently RDS doesn't allow you to change that parameter.

You can change each of your tables to MyISAM, one at a time:

ALTER TABLE MyTable ENGINE=MyISAM;

You can get a list of your tables you need to change:

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB';

For future tables, don't rely on the default engine. Create tables with the engine named explicitly in the CREATE TABLE statement.

I would caution you, however: MyISAM is being phased out by MySQL. It doesn't support concurrent writes, row-level locking, transactions, or atomic changes. InnoDB is shown over and over to be superior in performance, to MyISAM, except in a vanishingly few cases:

  • SELECT COUNT(*) FROM MyTable; because MyISAM keeps this stat built into each table. This is admittedly the big weakness of InnoDB or any MVCC architecture.
  • Table-scans. But you shouldn't be doing table-scans anyway, you should be doing index lookups. An optimized query against InnoDB will generally be much better for performance than a table-scan against MyISAM.

I would encourage you to figure out your query optimization and stick with InnoDB.

Upvotes: 2

Related Questions