MySQL Storage Engines viz MyISAM, innodb, archive, memory, csv..etc

MyISAM.

InnoDB.

InnoDB is transaction oriented (ACID compliant) storage engine, which supports commit, rollback.

File Types.

By Default InnoDB create auto-extending file

ibdata1 - Data file size is 10MB

ib_logfile0 & ib_logfile1 - Logfile of 5 MB each.

Important my.cnf parameter related to InnoDB storage engine.

innodb_data_home_dir : The default data directory for creating data files. (For optimal performance suggested keep datafile size < 2GB)

innodb_data_file_path : This lists the default files and their specifications.

Example :

innodb_data_file_path=ibdata1:10M:autoextend:max:100M

For Multiple files

innodb_data_file_path=/hdd1/ibdata1:10M:/hdd2/ibdata2:20M:/hdd3/ibdata3:50M:autoextend:max1GB

Note : Autoextend and Max are only applicable for Last file

innodb_buffer_pool_size : This should be normally set to 50-80 % of your Memory

innodb_additional_mem_pool : Normaly 8-10 % of Buffer Pool Size

innodb_log_file_size : This should be 25 % of buffer pool size

innodb_file_per_table : For each new table a new datafile will be created.

Federated Storage Engine.

Provides access to remote tables.

Federated Storage Engines.

Provies access to remote tables

Chennai => Contains "Customers" table that we want access to from Mumbai.

create table Customers (custid int, customer_name varchar(20))

From Mumbai

We will create a table on Mumbai database, similar to chennai's Customer table

create table Customers (custid int, customer_name varchar(20))

engine=federated connection='mysql://<username>:<password>@<chennai_hostname>:3306/<database_name>/<table_name>

.

Now on mumbai just excute select * from customers, it will go to chennai database and give us result set.