Mysql InnoDB Introduction

InnoDB is the first engine on MySQL to provide a foreign key constraint, in addition to providing transaction processing, InnoDB also supports row locks, providing no lock reading consistent with Oracle, increasing the number of concurrent read users and improving performance without increasing the number of locks.

InnoDB's design goal is to maximize performance while processing large volumes of data, and its CPU utilization is the most efficient of all other disk-based relational database engines.

InnoDB is a set in the MySQL background of the complete database system, InnoDB has its own buffer pool, can buffer data and index, InnoDB also put data and index in the table space, may contain several files, this and MyISAM table is completely different, in MyISAM, Tables are stored in separate files, the size of the InnoDB table is limited to the size of the operating system files, typically 2GB.

MySQL source code, starting from 3.23.34a contains InnoDB table, and in Mysql-max binary version of the activation.

If you are using the source version download, you need to use the--WITH-INNODB option:

./configure--with-innodb

When using InnoDB on mysql-max-3.23, you must specify the startup parameters in the [mysqld] section of the MY.CNF or My.ini file:

innodb_data_file_path=ibdata:30m

InnoDB is issued under the GNU GPL version 2 license.

MYSQL-MAX-3.23: This product can be used in production environment.

MySQL-4.0: This is the development version, compared with 3.23 added multiple table deletions, query results buffering, SSL Communications, 4.0.1 for Beta.

In 3.23, you must specify at least the size of the Innodb_data_file_path, but in MySQL-4.0 this value defaults to 64MB and the file name is ibdata1.

For good performance, you must specify the InnoDB parameter, for example:

For a 128MB RAM, the Windows NT machine for the 10GB hard drive is configured as follows:

[Mysqld]

Innodb_data_home_dir = C:/ibdata

# data files must be able to accommodate the data and index

Innodb_data_file_path = ibdata1:2000m;ibdata2:2000m

# Set buffer pool size to 50-80 of memory

Set-variable = innodb_buffer_pool_size=70m

Set-variable = innodb_additional_mem_pool_size=10m

Innodb_log_group_home_dir = C:/iblogs

# Log_arch_dir must be the same as Log_group_home_dir

Innodb_log_arch_dir = C:/iblogs

Innodb_log_archive=0

Set-variable = innodb_log_files_in_group=3

# Set the log file size to 15% of the buffer pool.

Set-variable = innodb_log_file_size=10m

Set-variable = innodb_log_buffer_size=8m