STORAGE ENGINES IN MySQL (Article -12)

Share via:

STORAGE ENGINES IN MySQL.

 

What is  Storage Engine ?

A storage engine is a software module that a database management system uses to create, read, update data from a database. There are two types of storage engines in MySQL: transactional and non-transactional.

For MySQL 5.5 and later, the default storage engine is InnoDB. The default storage engine for MySQL prior to version 5.5 was MyISAM. Choosing the right storage engine is an important strategic decision, which will impact future development. In this tutorial, we will be using MyISAM, InnoDB, Memory and CSV storage engines. If you are new to MySQL and your are studying the MySQL database management system, then this is not much of a concern. If you are planning a production database, then things become more complicated.

List of storage engines

MySQL supported storage engines:

  • InnoDB
  • MyISAM
  • Memory
  • CSV
  • Merge
  • Archive
  • Federated
  • Blackhole
  • Example

 

MySQL interaction with Storage Engines :

Every SQL statements comes it need to be parsed for syntax’s and also it need to find best optimization plans depending upon kind of indexes we have the query goes to storage engine to get the data.

But storage engine is independent of sql layer.

 

To check Engines available on MySQL we have show command .

We can see in vertical way

The default storage engine is InnoDB .

But how to check

NOTE : 

1.one database can have different storage engines

2.Storage Engines are associated with tables.

3.while creating table we can choose Engine type.

 

MyISAM Storage Engine :

  • MyISAM is the original storage engine.
  • It is a fast storage engine.
  • It does not support transactions.
  • MyISAM provides table-level locking.
  • It is used mostly in Web and data warehousing.
  • It does not support foreign key.
  • Portable storage format.
  • Table take sup very little space.
  • Most Flexible AUTO_INCREMENT.

 

Let’s Create a database and see how it stores in storage engines.

Check directories at physical layer .

When we create database automatically a directory with same name as database will be created under /usr/local/mysql/data.

Inside that directory there is file called db.opt will created.

In that file we have character set and collation details.

Now will create a table under db1 database.

 

Now will check at Physical layer :

when we a create table with MyISAM storage engine then it creates three files

  1. <table_name>.frm
  2. <table_name>.MYD
  3. <table_name>.MYI

 

.frm contains table structure  file

.MYD contains  data

.MYI contains indexes

 

We can set  MyISAM  as default Storage Engine .

Edit my.cnf  and write a variable the restart the service

Now check default storage engine .

AUTO_INCREMENT IN MYISAM :

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Create  a table with AUTO_INCREMENT .

To check the auto_increment current numbers.

The current number is 6 in a variable .

 

InnoDB Storage Engine in MySQL :

InnoDB is the most widely used storage engine with transaction support.

It is an ACID compliant storage engine.

It supports row-level locking, crash recovery and multi-version concurrency control.

It is the only engine which provides foreign key referential integrity constraint.

Oracle recommends using InnoDB for tables except for specialized use cases.

Feaures of InnoDB storage engine :

Storage limits 64TB Transactions Yes Locking granularity Row
MVCC (Multiversion concurrency control) Yes Geospatial data type support Yes Geospatial indexing support No
B-tree indexes Yes T-tree indexes No Hash indexes No
Full-text search indexes Yes Clustered indexes Yes Data caches Yes
Index caches Yes Compressed data Yes Encrypted data Yes
Cluster database support No Replication support Yes Foreign key support Yes
Backup / point-in-time recovery Yes Query cache support Yes Update statistics for data dictionary Yes

 

In InnoDB  the storage mechanism is totally different when compared to MyISAM.

In InnoDB three files are responsible to store all the data which belongs to InnoDB. They are

1.ibdata1

2.ib_logfile0

3.ib_logfile1

 

Iibdata1 is know as InnoDB tablespace

ib_logfile0  and  ib_logfile1  is known as InnoDB Transaction logfile.

There file are automatically created when we installed MySQL.

Let’s create a table and check what files will created.

I have created two tables t3 and t4 with InnoDB Storage Engine. Let’s check physical layer.

Inside database directory two files will created for InnoDB Storage engine objects.

1.tablename.frm

2.tablename.ibd

  • .frm file contains structure of the table.
  • .idb file contains data and index.
  • .idb file is also known as tablespace in MySQL
  • In MySQL every table have individual tablespaces in the form of .idb file. This is concept is known as file per table.

We have special variable for enable or disable file per table option .

By default file per table is enable . In order to disable it we need to write variable in my.cnf file.

Restart the server .

Now check the status of  innodb_file_per_table.

Now will create table under db1 and will check the files at physical layer.

For t5 table only one file is created that is .frm (structure of table).

But data will be stored under ibdata1 .

  • Ibdata1 contains data which belongs to INNODB storage engine objects.
  • Ibdata1 is also known as common tablespace.

Ibdata1 also contains MVCC,LOGBUFFER,DICTIONARIES  etc..,

Draw back of common tablespace 

  • when we dropped  a database or table in MySQL after that data will not be removed from ibdata1.
  • Reclaim the space is not possible.

 

InnoDB Logfiles :

These logfiles are known as Redo logfiles.

By default, InnoDB creates two redo log files (or just log files) ib_logfile0 and ib_logfile1 within the data directory of MySQL. In MySQL versions 5.6.8 and above, the default size of each redo log file is 48MB each. This can be configured by the user by making use of innodb_log_file_size server option.

A log group consists of a number of log files, each of same size. As of MySQL 5.6, InnoDB supports only one log group. So I’ll not discuss this further.

The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the last redo log file. Once the last redo log file has been written, then redo logs are again written from the first redo log file.

To check the information of logfiles.

Even if we deleted these logfiles will be created with the same size for the  every service startup.

To check transaction block size.

Here it is 8K

 

Thank you …

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

One thought on “STORAGE ENGINES IN MySQL (Article -12)

Add Comment