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.




Comments