0

Recently we upgraded version of MySQL on the database server, Almost all of our tables are in MyISAM but in my.ini we have set

--default-storage-engine=InnoDB

But now we want to change this setting to MyISAM because previously we were using MyISAM as a default engine

--default-storage-engine=MyISAM

Is changing default-storage-engine on the fly is a good practice? Will it work? Even if it works, Will there be any major performance issues?

It's the legacy application for which we are using this database that is the reason almost all tables are using the MyISAM engine. We are not using DB transactions anywhere in the application.

MySQL Version : 8.0 OS: Windows Server

Vishal
  • 185
  • 1
  • 7

4 Answers4

2

Changing the default storage engine affects only new tables that are created with CREATE TABLE statement, without specifying database engine.

So, unless you plan to create new tables, there is no difference which setting you have in configuration file.

Tero Kilkanen
  • 34,499
  • 3
  • 38
  • 58
2

If multiple queries could run at the same time (e.g. multi user environment), then you should plan moving to InnoDB for all tables. I would also run MySQL on a Linux machine.

To change the engine, you should export the database with mysqldump, then change the engine in the dump and import it back. You can also use ALTER TABLE mytable ENGINE=InnoDB; for each table of your schema. Do not change the DB engine for mysql schema.

Mircea Vutcovici
  • 16,706
  • 4
  • 52
  • 80
0

MyISAM does not have transaction ACID properties. I would switch to InnoBD. I may be mistaken.

LaSu
  • 3
  • 1
  • Currently, we are not using or intend to use database transactions in the application as it's a legacy application and no active development is being done. – Vishal Jul 12 '20 at 17:21
  • Even if you are not using transactions I would still use InnoDB instead of MyISAM. – Mircea Vutcovici Jul 13 '20 at 01:22
0

default_storage_engine applies only to newly created tables. That is, changing the setting will not change existing tables.

To actually change tables, use ALTER TABLE name ENGINE=engine (for each table).

Do either of these before and after the conversion to assure yourself that the conversion happened:

SHOW TABLE STATUS;   -- has a column for "engine"

SHOW CREATE TABLE name;   -- shows the engine= clause at the end

Perhaps the only task that is likely to be significantly slower in InnoDB is

SELECT COUNT(*) FROM name;

with no WHERE clause. Do you use that a lot? We can discuss workarounds. Ditto for any other query that you identify is being slower.

I strongly encourage you to migrate to InnoDB before it is removed from MySQL. Migration issues: http://mysql.rjweb.org/doc.php/myisam2innodb

Rick James
  • 2,058
  • 5
  • 11