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.

Amazon RDS with MySql – How to create an instance!

This post will guide through how to use Amazon RDS, the steps of creating an instance and other related things.

Before going further one can take a look on few links to get a hang of what RDS is all about and how does it perform against other available solutions.
Lets get started.
1. Launch DB instance – AWS Services are relatively cheaper in USA region, so you can consider about choosing the region. Go to Amazon AWS console and select RDS from there. You will land up on the below page. Click “launch a DB Instance”.

2. Select mysql engine.
3. Production- Choose Multi-AZ Deployment.
4. Specify DB Details – On the basis of your requirements you can choose DB Instance Class. In my case I used db.t2.medium which was of 5GB capacity. Specify user and password for the db from here.
5. Configure Advanced Settings – You can define your custom parameter group so you can simply choose default parameter group here. If you have a pre-defined security group, you should choose that one here. 
6. DB Instance Created- The database has now been created. It may take couple of minutes for the db to set up completely.
7. The newly created DB instance will start reflecting in the dashboard.

Changing the default configuration variables of MySql

Now after the db instance has been created we need to change a bit of configurations and monitor the db.

I struggled a bit when I had to change the default configuration values. To change the config values (equivalent to changing values in my.cnf file) we have these following steps.
1.  Go to RDS dashboard and select the database which you have created. Choose “Parameter Group” from the left pane.
2. Click on “Create DB Parameter Group” to create a custom Parameter Group. Choose some suitable name and description.
3. Now choose “Edit” to change the config. Some important variables as – max_connections, query_cache_size etc can be changed from here. The changes will be applied and reflected.

Monitoring

One of the coolest features of RDS is its monitoring. You can check out for n different metrics. – CPU Utilizations, Number of connections etc.
Simple, isn’t it? Feel free to comment.