MySQL and Storage Engines

By | June 5, 2013
0 Comment

Introduction

MySQL is the most widely used open source relational database management system (RDBMS) and act as a database server providing multiple user access to number of databases.

SQL means structured query language.

The first version of MySQL was released on 23 May 1995 and was owned and sponsored by a Swedish Company MySQL AB and now its owned by Oracle. It is named after co-founder Michael Widenius’ daughter My.

MySQL is a better choice as database for use in web applications and it’s a main component of  widely used LAMP open source web application bundle.

MySQL works on almost all system platforms like IBM-AIX, FreeBSD, HP-UX, Linux, Mac OS X, Microsoft Windows, Novell NetWare, etc, .

MySQL paid editions are also available for commercial use with additional features. The applications which uses MySQL databases includes CMSes like Moodle, Joomla, WordPress, Drupal, etc,. and it is also used by internet giants like Google, Facebook, Wikipedia,Twitter  and YouTube.

MySQL is written in C and C++ with SQL parser which is written in YACC (Yet Another Compiler Compiler), but it uses a home-brewed lexical analyzer.

Storage Engines (Database Engine)

It is a software component on DBMS which uses to create, read, update and delete data from a database. There are two types of storage engines in MySQL. Transactional and non-transactional.

A non-transactional engine can only fetch data from a database and display it, like a list of CD’s or books in a library. An engine that supports transaction processing can execute a task using server side functions. Banking online is an example of a transaction processing engine. You access your account from the non-transacting script, the simple database. Once done the transaction engine takes over and manages your money as you choose.

 There are many storage engines available for MySQL. Each one has its own advantages and disadvantages and each are more suitable in some situations than other.

The default storage engine before MySQL 5.5 was MyISAM, and on 5.5 or later default storage engine is InnoDB.

The available storage engines are :- MyISAM, InnoDB, Memory, CSV, Merge, Archive, Federated, Blackhole. BerkeleyDB, ndbcluster, MRG_MYISAM, ISAM and  Example

 To find available storage engines, execute “show engines” command after connecting to MySQL  and it also shows whether it supports or not on your server. You can see three values there on support column, YES, NO, DEFAULT . YES means available, NO means not available, DEFAULT means the default engine.

[root@server]# mysql -u root -p

Enter Password:

mysql> show engines \G;
*************************** 1. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)

ERROR:
No query specified

mysql> show engines \G;
*************************** 1. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)

You can specify which storage engine needs to be used when creating tables.

mysql> CREATE TABLE test (id int, name varchar(20)) ENGINE = INNODB;

Also you can alter the storage engine after creating tables.

mysql> ALTER TABLE test ENGINE = MyISAM;

 

The two major storage engines of MySQL databases currently using are MyISAM and InnoDB.

 

MyISAM

InnoDB

  1. Simple to design

  2. less strict in data integrity check

  3. Implements table level lock for inserting and updating.

  4. No transactions

  5. No relationship constraints and foreign keys

  6. Poor at recovering data integrity while system crashes

  7. Full-text search index

  8. Faster in read-intensive

  1. Complex

  2. More strict in data integrity check

  3. Implements row-level lock for inserting and updating

  4. Has transactions

  5. Has foreign keys and relationship constraints

  6. Better     crash recovery at system crashes

  7. No full-text search index

  8. Faster in write-intensive

Advantages

InnoDB

  1. InnoDB would be used where data integrity comes priority because of relationship constraints and transactions.

  2. Since use of row-level locking InnoDB is faster in write-intensive (inserting and updating values) and lock the row on which data is being inserted or updated.

MyISAM

  1. While comparing with InnoDB, MyISAM is simple to design and create, thus better for beginners. Also it is not using foreign relationships between tables.

  2. Because of simple structure MyISAM is faster than InnoDB and thus much less costs of server resources.

  3. Full text-indexing and faster in read-intensive.

Disadvantages

InnoDB

  1. Since     InnoDB takes care of different relationships between tables, database administrator and scheme creators may take more time in designing the data models which are complex than MyISAM.

  2. InnoDB consumes more system resourcessuch as memory.

  3. No full text-indexing

MyISAM

  1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.

  2. MyISAM doesn’t support transactions which is essential in critical data applications such as banking.

  3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

Note : Typically MyISAM considered as faster in read-intensive, but current InnoDB improvements removes this difference.

In short, InnoDB is more useful for critical data applications such as banking which  require regular inserts and updates whereas MyISAM suits well for applications which don’t require frequent updates, that means just select and display data. Also use where data integrity comes low priority.

Also here am briefly explaining other storage engines.

  • The CSV storage engine stores data in text files using comma-separated values(CSV) format. It is not an ideal engine where we need to store large data, also where table requires large indexing. Since the data stored in CSV, we can easily export the data into another software packages like spreadsheet, excel, etc.
  • The ARCHIVE storage engine used for storing large amount of data without indexes in very small footprint. It supports only insert and select (no transactions). All the information stored in this engine are compressed and can not be modified. So it is better for storing logs and data which are not actively in use like accounting, transactions data. Since the data in compressed state, takes more to complete complex queries for data retrieval.
  • The FEDERATED storage engine was added in MySQL 5.0.3. Federated storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically executed on the remote datbase tables and no data is stored on the local tables. Currently, it works with MySQL only, using the MySQL C Client API.
  • The BLACKHOLE storage engine accepts but does not store data and retrievals always return an empty set. It is good for performance testing and other testing like databse structures, types and indexes.
  • The MEMORY storage engine provides in-memory tables. This storage engine formerly was known as the HEAP engine. It is the fastest engine. It provides table-level locking. It does not support transactions. Memory storage engine is ideal for creating temporary tables or quick lookups. The data is lost when the database is restarted.
  • The EXAMPLE storage engine is a “stub” engine that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.
  • NDB (known as NDBCLUSTER) and this clustered database engine is suited for applications that require the highest possible degree of uptime and availability.

The following table provides an overview of some storage engines provided with MySQL:

sss

 

Choosing right storage engine

Each storage engine has its own advantages and disadvantages. So some performs better under some situations and worse under others . So it would be difficult to answer which storage engine we can choose . So deeply go through each engines advantages and disadvantages and select right one which will suit for your applications.

 

Leave a Reply

Your email address will not be published. Required fields are marked *