What is the difference between InnoDB and MyISAM?

Which database engine to choose when creating a new database? This question is very often asked at the stage of designing a new application or system that will use MySQL database. There are usually two engines to choose from: MyISAM and InnoDB. The first engine is the default mechanism for storing data in older MySQL series. Data is stored in files (frm – table definition, .MYD (MYData) – data file, .MYI (MYIndex) – table indexes). The second engine (which is the default from MySQL 5.5) is InnoDB. It has mechanisms such as transaction handling and foreign keys.

Before choosing an engine, it is necessary to consider the advantages and disadvantages of each of them. Database will be viewed differently by the client, who cares about its performance, and differently by the programmer or administrator, who will be worried about data backups or stability of the whole server.

The MyISAM engine has always been seen as one that performs SELECT queries faster, is easier to manage, backups and recreates data. InnoDB, on the other hand, was considered to be slower, but supporting transactions and foreign keys.

How does a server crash affect the data?

There are no ideal systems and sometimes faults or major breakdowns occur. Even the best protected systems like to fail, whether due to a disconnected power plug or more serious problems. How does InnoDB deal with failures and how does MyISAM do it? Which of them handles failures better and will we be able to recover data or at least some of them after a server failure?

Let’s start with the MyISAM engine. It does not have transaction support, so it may happen that during a server failure some data will be added, deleted or changed in the database and some will not. Imagine that during UPDATE query execution the server was shut down. When the server boots up, it will start the process of repairing tables, which can take a very long time in case of large databases. It may take several hours to repair the tables. During this time, the disk and the server processor will be heavily loaded. Then we will start the database, with a partially changed content of records. Data in the database will be inconsistent.

And what about InnoDB? Usually, after a failure, the server will get up very quickly and elegantly, unless the transaction log file is corrupted. Then the situation gets a little bit more complicated. The InnoDB engine is a transaction engine that supports ACID. ACID is an abbreviation for atomicity, consistency, isolation, durability.

The atomicity of a transaction means that we either do it entirely or not at all. There can be no situation in which some queries are performed, as in the case of MyISAM. As far as consistency is concerned, this means that the system will be consistent after the transaction, i.e. no integrity rules will be violated. Isolation of transactions means that if two transactions are executed simultaneously, they usually (depending on the level of isolation) do not see the changes they make. Data robustness means that the system can boot up and make available consistent, intact and up-to-date data recorded in approved transactions, for example after a sudden power failure.

Everything looks beautiful, but how is it really? By default, the engine should run in full ACID compatibility mode (parameter innodb_flush_log_at_trx_commit is 1), i.e. after each COMMIT command, data from buffers are written to disk and the flush() function is called. Unfortunately, this option reduces the maximum number of supported transactions, and affects the efficiency of the database. However, we can be sure that the data is stored on the disk.

If this parameter is set to 0, data from buffers are written once per second to the log file. For 2, buffer data is written after each commit, but no flush is executed. The flush function is executed once per second. However, this does not give 100% certainty that we will not lose data. For maximum durability and consistency of the data, you can set the options in the my.cnf file: innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1.

Uwaga
Several operating systems and hard disk controllers pretend to be flush() operations. They report that data has been written to the disk while it is still in the device’s buffers. If there is a power outage, you may lose data (unless the controller is powered up by batteries). In Linux, you can disable data caching using the hdparm -W0 /dev/sda command

What if the server with InnoDB engine is damaged? When the server starts up, the transaction log is viewed. All operations that have not been completed with a commit are undone, and the rest are placed on tables. This process usually takes a very short time. This mechanism is also used by Percona XtraBackup software.

Backups

If the server crashes and the default methods of repairing the tables do not work, you will need to use a backup. From which engine is it easier to make such a backup? The most popular method is to use mysqldump. This way we dump the data into SQL format, which we can then import to the server without any problems. These operations take quite a long time in case of huge databases, but importing SQL file will be faster for MyISAM engine. InnoDB engine is not able to build an index using sorting during import, so the import operation takes much longer.

Another method is to back up data files. For MyISAM tables, the data is stored in files:

• .frm – definition of the table
• .MYD (MYData) – data file
• .MYI (MYIndex) – indexes of the table

The InnoDB engine, depending on the server settings, passes the data also in .frm files (definition of tables), while the data is kept in one or more files that make up the tablespace. The variable innodb_file_per_table is responsible for the settings. By default from server version >= 5.5.7 is set to OFF. For MySQL server versions >= 5.5.0, <= 5.5.6, it was set to ON by default. Enabling this option means creating a separate tablespace file for each table. Then a .idb file will also be created, which contains the data and indexes of the table.

Copying the relevant files is simple and quite fast. In case of MyISAM we have to block access to the tables with the command FLUSH TABLES WITH READ LOCK; or simply stop the server. Unfortunately, this solution is not efficient because we have to cut off access to write to the server. In case of InnoDB engine we can use Percona XtraBackup software and make a copy without stopping the database.

Performance of the database engine

Very often you will find the opinion that the MyISAM engine is faster than the InnoDB. About 5 years ago maybe that was the case. Recently, however, and with every new version of MySQL server, Oracle is developing the InnoDB engine. The work on MyISAM is practically at a standstill. Percona is also working on its own version of InnoDB called XtraDB. On average, every few months the next version of the server appears. It is worth noting that since MySQL 5.4, MyISAM is no longer the default engine. So there is something to be done.

MyISAM has always been thought to be faster, mainly for SELECT queries. However, as it turns out, this is not so obvious. InnoDB uses the index clustering mechanism, which in some cases significantly accelerates the operation of the database. Such indexes work by storing also data within the index. When searching the arrays, the sorted indexes are searched, and then the data indicated by the index is retrieved. In this case, the database performs two operations: reading the index and then reading the data. Clustering indexes significantly speeds up these operations. We save one data reading operation on each query.

But using a database is not just about searching and retrieving records. It’s also about adding, deleting or modifying records. How does MyISAM and InnoDB do here? In the case of the first engine we are dealing with blocking at the table level. By adding records to a table, we block it for the duration of the entire operation. Other queries that refer to this table at that time, must wait in the queue. This causes that in the case of a large number of queries and modified records – the efficiency of the database decreases. InnoDB is blocking at the record level, so that parallel queries can work on the same table.

MyISAM in many situations can work much faster than InnoDB, but this is at the expense of data integrity. There is simply no such functionality in it.

The difference between the two engines is also memory management. MyISAM has the ability to cache only indexes, where the size of the cache is determined by the key_buffer_size variable. Recommended buffer size is 25% of all memory that the server has. In the case of the second engine we have the ability to cache all data and not only indexes. The parameter innodb_buffer_pool_size, which sets the buffer size to 128MB by default, is responsible for this. It is recommended to set this buffer to 80% of all memory that the server has.

Testing of engine performance

The tests were performed on a server based on Red Hat Enterprise Linux 5.7 64 bit version. The machine had Quad-Core AMD Opteron(tm) Processor 2374 HE processors and 8GB of RAM. SysBench software was used for testing. Before testing, you must fill in the database with sample data. A command will be used for this purpose:

[root@dbtest thecamels]# sysbench --test=oltp --mysql-user=root --mysql-db=test --oltp-table-size=20000000 --myisam-max-rows=20000000 --mysql-table-engine=myisam --oltp-table-name=myisam prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'myisam'...
Creating 20000000 records in table 'myisam'...

A similar command is used to create a table for the InnoDB engine:

sysbench --test=oltp --mysql-user=root --mysql-db=test --oltp-table-size=20000000 --myisam-max-rows=20000000 --mysql-table-engine=innodb --oltp-table-name=innodb prepare

Now is the time to test the engines. At the beginning we will check how fast SELECT queries are executed (parameter –oltp-read-only=on). UPDATE, DELETE, INSERT queries will not be executed. Correspondingly, we give commands first for MyISAM:

[root@dbtest ~]# sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000 --num-threads=128 --max-requests=1000000 --mysql-table-engine=myisam --mysql-db=test --oltp-table-name=myisam --myisam-max-rows=20000000 --oltp-read-only=on --oltp-test-mode=simple run
...
OLTP test statistics:
    queries performed:
        read:                            1000111
        write:                           0
        other:                           0
        total:                           1000111
    transactions:                        1000111 (8839.28 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1000111 (8839.28 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          113.1439s
    total number of events:              1000111
    total time taken by event execution: 14458.9161
    per-request statistics:
         min:                                  0.06ms
         avg:                                 14.46ms
         max:                                299.95ms
         approx.  95 percentile:              41.53ms

Threads fairness:
    events (avg/stddev):           7813.3672/128.22
    execution time (avg/stddev):   112.9603/0.15

then for InnoDB:

[root@dbtest ~]# sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000 --num-threads=128 --max-requests=1000000 --mysql-table-engine=innodb --mysql-db=test --oltp-table-name=innodb --myisam-max-rows=20000000 --oltp-read-only=on --oltp-test-mode=simple run    
... 
OLTP test statistics:
    queries performed:
        read:                            1000103
        write:                           0
        other:                           0
        total:                           1000103
    transactions:                        1000103 (8620.56 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1000103 (8620.56 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          116.0136s
    total number of events:              1000103
    total time taken by event execution: 14844.5125
    per-request statistics:
         min:                                  0.05ms
         avg:                                 14.84ms
         max:                                266.96ms
         approx.  95 percentile:              42.62ms

Threads fairness:
    events (avg/stddev):           7813.3047/97.54
    execution time (avg/stddev):   115.9728/0.00

and finally for XtraDB:

[root@dbtest ~]# sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000 --num-threads=128 --max-requests=1000000 --mysql-table-engine=innodb --mysql-db=test --oltp-table-name=innodb --myisam-max-rows=20000000 --oltp-read-only=on --oltp-test-mode=simple run    
...
OLTP test statistics:
    queries performed:
        read:                            1000637
        write:                           0
        other:                           0
        total:                           1000637
    transactions:                        1000637 (35228.19 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1000637 (35228.19 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          28.4044s
    total number of events:              1000637
    total time taken by event execution: 3473.6785
    per-request statistics:
         min:                                  0.09ms
         avg:                                  3.47ms
         max:                               5815.52ms
         approx.  95 percentile:               3.05ms

Threads fairness:
    events (avg/stddev):           7817.4766/2766.84
    execution time (avg/stddev):   27.1381/0.62

As you can see, the MyISAM engine is not much faster than the InnoDB during SELECT queries. The second engine performs comparatively fast. You will also notice that XtraDB is much faster than InnoDB or MyISAM when performing SELECT queries. The test was carried out on it with lightning speed.

And what about operations that modify the data? Here we will have to deal with various types of queries modifying, deleting and adding records. For this purpose, we will use the appropriate commands:

sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000  --num-threads=128 --max-requests=1000000 --mysql-table-engine=myisam --mysql-db=test --oltp-table-name=myisam --myisam-max-rows=20000000 --oltp-read-only=off --oltp-test-mode=complex run
...
OLTP test statistics:
    queries performed:
        read:                            14000000
        write:                           5000000
        other:                           2000000
        total:                           21000000
    transactions:                        1000000 (229.04 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19000000 (4351.78 per sec.)
    other operations:                    2000000 (458.08 per sec.)

Test execution summary:
    total time:                          4366.0247s
    total number of events:              1000000
    total time taken by event execution: 558754.8210
    per-request statistics:
         min:                                  4.88ms
         avg:                                558.75ms
         max:                               1240.84ms
         approx.  95 percentile:             792.23ms

Threads fairness:
    events (avg/stddev):           7812.5000/2.61
    execution time (avg/stddev):   4365.2720/0.27

and for the InnoDB engine:

sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000  --num-threads=128 --max-requests=1000000 --mysql-table-engine=innodb --mysql-db=test --oltp-table-name=innodb --myisam-max-rows=20000000 --oltp-read-only=off --oltp-test-mode=complex run 
...
OLTP test statistics:
    queries performed:
        read:                            14000784
        write:                           5000280
        other:                           2000112
        total:                           21001176
    transactions:                        1000056 (924.42 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19001064 (17563.92 per sec.)
    other operations:                    2000112 (1848.83 per sec.)

Test execution summary:
    total time:                          1081.8238s
    total number of events:              1000056
    total time taken by event execution: 138408.7900
    per-request statistics:
         min:                                  2.98ms
         avg:                                138.40ms
         max:                               3589.94ms
         approx.  95 percentile:             284.44ms

Threads fairness:
    events (avg/stddev):           7812.9375/46.88
    execution time (avg/stddev):   1081.3187/0.15

and finally for XtraDB:

sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000  --num-threads=128 --max-requests=1000000 --mysql-table-engine=innodb --mysql-db=test --oltp-table-name=innodb --myisam-max-rows=20000000 --oltp-read-only=off --oltp-test-mode=complex run 
...  
OLTP test statistics:
    queries performed:
        read:                            14001148
        write:                           5000410
        other:                           2000164
        total:                           21001722
    transactions:                        1000082 (919.01 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19001558 (17461.13 per sec.)
    other operations:                    2000164 (1838.01 per sec.)

Test execution summary:
    total time:                          1088.2202s
    total number of events:              1000082
    total time taken by event execution: 139270.7414
    per-request statistics:
         min:                                  3.01ms
         avg:                                139.26ms
         max:                              12718.80ms
         approx.  95 percentile:             285.29ms

Threads fairness:
    events (avg/stddev):           7813.1406/50.75
    execution time (avg/stddev):   1088.0527/0.03

As you can see in this case, InnoDB did much better with the performance test. Blocking at the table level slows down the operation of the database very much, which translates into a smaller number of supported transactions. XtraDB performance in this test was similar to InnoDB.

For the next test, a table with 2,000,000 records filled in with text has been prepared. The total area was about 35 GB. The test query was to search the table using the LIKE operator.

Query result for MyISAM:

select count(*) from table_myisam where txt LIKE '%lipsum%';
1 row in set (10 min 44.08 sec)

Query result for InnoDB:

select count(*) from table_innodb where txt LIKE '%lipsum%';
1 row in set (14 min 29.96 sec)

Query result for XtraDB:

select count(*) from table_xtradb where txt LIKE '%lipsum%';
1 row in set (12 min 14.21 sec)

As you can see in this case the MyISAM engine was definitely faster than the others. However, these differences are not as great as compared to XtraDB.

Which engine to choose for the database? MyISAM or InnoDB?

The answer to this question is not simple, because everything depends on the purposes for which we will use the database, whether we will care about the integrity of the data or rather about the performance when downloading records.

MyISAM
Advantages

• quick reading of tables
• simpler backups
• suitable for tables with a small amount of data

Disadvantages

• lack of transaction handling
• lack of mechanisms responsible for data integrity
• with large tables, long REPAIR TABLE execution times after server failure

InnoDB
Advantages

• transaction handling
• guarantees data integrity
• default engine from MySQL 5.5
• works better during master-slave replication

Disadvantages

• slower reading of data
• difficulties in making backups