MySql Database Engines – InnoDB and MyISAM

A database storage engine is a component which determines the way data is structured in the database and this structure determines the way CRUD is done on the database. MySql has different types of engines of which InnoDB and MyISAM are the most heard of.
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

InnoDB replaced MyISAM as MySql’s default storage engine after 5.5 release.

MyISAM and InnoDB:

When we have a scenario with very less number of writes and heavy read and massive querying, MyISAM fits the best. It has a feature of table lock which on any write operation locks the whole table and then writes. So, in case we have an application which is write heavy will suffer a lag because of the sequential write with a table lock. When a table is table locked, only one query can run against it at one time. This is where a nice robust hardware support would be required.

MyISAM also does not support atomic transactions which is a key feature of (one of ACIDs) a database. You cannot commit and rollback with MyISAM. Due to this, our data can go into an inconsistence state and we might have to face serious issues. Let’s say you issue an update query which has to update a lot many rows. The table is locked and the data is being updated. In the mean time, some connection error occurs because of which the querying is interrupted and you know you are in a trouble. Imagine the situation when you have to update your table frequently. MyISAM does not guarantee any data integrity. InnoDB on the other hand supports ACID feature. It uses transactions and validations to ensure data doe not corrupt. In case of any crash, the database will recover itself to the last save point. For maintainance MyISAM has some commands which one should run on regular intervals – Optimize, Analyze, Check etc.

Comparing the sizes the tables take in both storage engines, MyISAM tables take less space because of the less overheads. The data can further be compressed if it is sure to be ready only. It also does not support/use foreign keys as a concept. InnoDB has to do a lot of house keeping because of its relational nature, so the data size is relatively higher.

There are very limited options available for instrumentation and tweaking MyISAM. There are some cool aspects of using MyISAM, ex: there is not deadlock. MyISAM has seen very limited development in past years. One of the crash-safe database extensions of MyISAM is MariaDB which uses storage engine ARIA.

Using Spring with Hibernate and c3p0 Connection Pool.

C3P0 is a very nice tool to manage database connections. I had hard time configuring Apache DBCP/2 so tried c3p0. There are many config options to set and the setting has to be done carefully so that we do not end up choking our database. Let us understand some of the config options.
  • testConnectionOnCheckin validates the connection when it is returned to the pool.
  • testConnectionOnCheckOut would ensure active connections before use, would be too expensive to do.
  • idleConnectionTestPeriod sets a limit to how long a connection will stay idle before testing it. Without preferredTestQuery, the default is DatabaseMetaData.getTables() – which is database agnostic, and although a relatively expensive call, is probably fine for a relatively small database. If you’re paranoid about performance use a query specific to your database (i.e. preferredTestQuery="SELECT 1")
  • maxIdleTimeExcessConnections will bring back the connectionCount back down to minPoolSize after a spike in activity, the connection is removed from the pool and returned back to db.
  • numHelperThreads it will help c3p0 spawns helper threads to manage the connections and returning them back

My spring configuration goes as –

<bean id="dataSource"  class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass">
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property name="jdbcUrl">
        <value>jdbc:mysql://localhost:3306/api</value>
    </property>
    <property name="user">
        <value>root</value>
    </property>
    <property name="password">
        <value></value>
    </property>
    <property name="idleConnectionTestPeriod">
        <value>300</value>
    </property>
    <property name="maxIdleTimeExcessConnections" value="180"/>
    <property name="maxPoolSize">
        <value>100</value>
    </property>
    <property name="acquireIncrement">
        <value>1</value>
    </property>
    <property name="maxStatements">
        <value>0</value>
    </property>
    <property name="minPoolSize">
        <value>10</value>
    </property>
    <property name="unreturnedConnectionTimeout">
        <value>3600</value>
    </property>
    <property name="preferredTestQuery">
        <value>SELECT 1</value>
    </property>
    <property name="initialPoolSize">
        <value>10</value>
    </property>
</bean>
Things to keep in mind:
  • To get an idea, please try to check the number of connection the app has hooked up with the database. For MySql try –  SHOW STATUS WHERE variable_name = ‘Threads_connected’;
  • When using Hibernate try to take care of opening and closing of sessions. If sessions are not properly closed, the connections are not freed and eventually it will choke the database.
Further Read - How to configure c3p0