SHARE

InnoDB vs. MyISAM

MySQL allows a few different storage engines for its tables. The two most common are InnoDB and MyISAM.

The main difference between the two is that MyISAM offers table-level locking, meaning that when data is being written into a table, the whole table is locked, and if there are other writes that must be performed at the same time on the same table, they will have to wait until the first one has finished writing data.

InnoDB, on the other hand, offers row-level locking, meaning that when data is being written to a row, only that particular row is locked; the rest of the table is available for writing.

At first, it seems that InnoDB is superior to MyISAM. However, the InnoDB engine uses more system resources (processing power, i.e., server load) than MyISAM, so you will need a more powerful server to run InnoDB. With InnoDB, the server load stays higher than with MyISAM. You may end up with a load that is so high that instead of increasing the performance of the server, you actually decrease the overall performance by switching from MyISAM to InnoDB.

The problems of table-level locking are only noticeable on very busy servers. For the typical website scenario, usually MyISAM offers better performance at a lower server cost.

Which engine you should use? Consult the documentation of the software you are using. Usually in the administrator’s guide, there is a specific section on this.

To check which engine the tables of a particular database is using, run the following query:

SHOW TABLE STATUS;

You will see the engine under “Engine.” Note that inside the same database you may have some tables using the MyISAM engine and some others using the InnoDB engine.

To change the engine of a table, run the following query, where you must replace “tablename” with the name of the table you want to reconfigure and “engine” with either “MyISAM” or “InnoDB”:

ALTER TABLE tablename ENGINE=engine;

If the load on the MySQL server is very high and the server is not using the swap file, before upgrading the server with a more expensive one with more processing power, you may want to try and alter its tables to use the MyISAM engine instead of the InnoDB to see what happens.

In the end, which engine you should use will depend on the particular scenario of the server, based on what we discussed above.

If you decide to use only MyISAM tables, you must add the following configuration lines to your my.cnf file:

default-storage-engine=MyISAM

default-tmp-storage-engine=MyISAM

If you only have MyISAM tables, you can disable the InnoDB engine, which will save you RAM, by adding the following line to your my.cnf file:

skip-innodb

Note, however, that if you don’t add the two lines presented above to your my.cnf file, the skip-innodb configuration will prevent your MySQL server from starting, since current versions of the MySQL server uses InnoDB by default.

1
2
3
4
5
6
7

Gabriel Torres is a Brazilian best-selling ICT expert, with 24 books published. He started his online career in 1996, when he launched Clube do Hardware, which is one of the oldest and largest websites about technology in Brazil. He created Hardware Secrets in 1999 to expand his knowledge outside his home country.