InnoDB Storage Engine

InnoDB Overview

InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides an Oracle-style, non-locking read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.

The InnoDB is designed for maximum performance when dealing with large amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.

The InnoDB is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB a default table for MySQL on Windows. InnoDB Configuration

The InnoDB storage engine is allowed by default. If you do not want to use the InnoDB table, you can add the SKIP-INNODB option to the MySQL options file.

Two important disk-based resources managed by the InnoDB storage engine are the InnoDB tablespace data file and its log file.

If you specify the No INNODB configuration option, MySQL will create a 10MB size automatic extension data file named Ibdata1 in MySQL Data directory, and two log files of 5MB size named Ib_logfile0 and Ib_logfile1.

Note : InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resilience. If the operating system and hardware that you intend to run do not work as advertised, InnoDB will not be able to perform as above . Many operating systems or disk subsystems may delay or log write operations to improve performance. On some operating systems, it is the system call (Fsync ()) that waits until all data that has not been written to the refreshed file can actually be returned before it is flushed to stable memory. Because of this, operating system crashes or power-down may corrupt the currently committed data, or in the worst case scenario, because the write operation has been logged and even the database has been destroyed. If data integrity is important to you, you should do some "pull-the-plug" tests before using any program in production. Mac OS X 10.3 and later versions, InnoDB uses a special fcntl () file Refresh method. Under Linux, it is recommended that you disable write-back caching.

On the ATAPI hard drive, a similar hdparm-w0/dev/hda command might work. caution Some drives or disk controllers may not prohibit write-back caching.

Note : To get good performance, you should explicitly provide the InnoDB parameter as discussed in the following example. Naturally, you should edit the settings to suit your hardware and requirements.

To create a InnoDB tablespace file, use the Innodb_data_file_path option in the [Mysqld] section of the MY.CNF options file. On Windows, you can use the My.ini file instead. The value of Innodb_data_file_path should be a list of one or more data file specifications. If you name more than one data file, use the semicolon (';') Separate them:

innodb_data_file_path=datafile_spec1[; DATAFILE_SPEC2] ...

For example, set the explicitly created table space with the same characteristics as the default setting, as follows:

[Mysqld]

Innodb_data_file_path=ibdata1:10m:autoextend

This setting configures a single file with a scalable size of 10MB, named Ibdata1. The location of the file is not given, so the default is within the MySQL data directory.

The size is either m or G suffix to specify that the description unit is MB or GB.

A tablespace that contains a fixed-size 50MB data file named Ibdata1 in the data directory and an auto-extension file named Ibdata2 size 50MB, which can be configured like this:

[Mysqld]

Innodb_data_file_path=ibdata1:50m;ibdata2:50m:autoextend

The full suffix of a specified data file includes the file name, its size, and several optional attributes:

file_name:file_size[: Autoextend[:max:max_file_size]]

The Autoextend property and subsequent properties can only be used to innodb_data_file_path the last data file in the row.

If you specify the Autoextend option for the final data file. If the data file runs out of free space in the tablespace, InnoDB expands the data file. The amplitude of the extension is 8MB each time.

InnoDB does not perceive the maximum file size, so be careful with the file system, where the largest file size is 2GB. To specify the maximum size for an auto-extended data file, use the Max property. The following configurations allow the ibdata1 to rise to the limit of 500MB:

[Mysqld]

innodb_data_file_path=ibdata1:10m:autoextend:max:500m

InnoDB creates a tablespace file by default in the MySQL data directory. To explicitly specify a location, use the Innodb_data_home_dir option. For example, to use two files named Ibdata1 and Ibdata2, but to create them to/ibdata, configure the InnoDB as follows:

[Mysqld]

Innodb_data_home_dir =/ibdata

Innodb_data_file_path=ibdata1:50m;ibdata2:50m:autoextend

Note : InnoDB does not create a directory, so make sure the/ibdata directory does exist before starting the server. This is true for any log file directory you have configured. Use the UNIX or DOS mkdir command to create any required directories.

InnoDB a directory path for each data file by simply deploying the value of Innodb_data_home_dir to the data file name and adding a slash or backslash where needed. If the Innodb_data_home_dir option is not mentioned in my.cnf at all, the default value is "dot" directory./, this means MySQL data directory.

If you specify Innodb_data_home_dir as an empty string, you can specify an absolute path for the data file listed in the Innodb_data_file_path value. The following example is equivalent to the previous example:

[Mysqld]

Innodb_data_home_dir =

Innodb_data_file_path=/ibdata/ibdata1:50m;/ibdata/ibdata2:50m:autoextend

a simple my.cnf example. Suppose you have a computer with 128MB of memory and a hard drive. The following example shows the possible configuration parameters for InnoDB in my.cnf or My.ini, including the Autoextend attribute.

This example is suitable for most users on UNIX and Windows, who do not want to allocate InnoDB data files and log files to several disks. It creates an auto-extended data file ibdata1 and two log files Ib_logfile0 and ib_logfile1 in the MySQL data Catalog. Similarly, Innod innodb log file ib_arch_log_0000000000, which is automatically created in the data directory, also ends.

[Mysqld]

# can write your other MySQL server options here

# ...

# Data files must is able to hold your Data and indexes.

# Make sure and that is enough free disk space.

Innodb_data_file_path = Ibdata1:10m:autoextend

#

# Set buffer pool size to 50-80% of your computer ' s memory

Set-variable = innodb_buffer_pool_size=70m

Set-variable = innodb_additional_mem_pool_size=10m

#

# Set The log file size to about 25% of the buffer pool size

Set-variable = innodb_log_file_size=20m

Set-variable = innodb_log_buffer_size=8m

#

Innodb_flush_log_at_trx_commit=1

Make sure the MySQL server has the appropriate permissions to create files in the data directory. More generally, the server must have access to any directory where it needs to create a data file or log file.

Note that on some file systems, the data file must be less than 2GB. The merged size of the data file must be at least 10MB.

When you create a InnoDB table space for the first time, it is best to start the MySQL server from the command line. InnoDB then prints the information created by the database to the screen, so you can see what is happening. For example, on Windows, if Mysqld-max is located in C:\mysql\bin, you can start it as follows:

c:\> C:\mysql\bin\mysqld-max--console

If you do not send the server output to the screen, check the server's error log to see what InnoDB printed during startup.

where do you specify options on UNIX? on Unix,mysqld from the following files, if they exist. The options are read in the following order:

· /etc/my.cnf

Global options.

· $MYSQL _home/my.cnf

Server-specific options.

· Defaults-extra-file

The--defaults-extra-file option specifies the file.

· ~/.my.cnf

User-specific options.

Mysql_home represents an environment variable that contains the path to a directory containing server-specific MY.CNF files.

If you are sure that mysqld only reads the option from the specified file, you can use--defaults-option as the first option at the command line when you start the server:

Mysqld--defaults-file=your_path_to_my_cnf

a high-level my.cnf example. Suppose you have a Linux computer that has 2GB of memory and three 60GB hard disks (in directory Paths/,/DR2 and/DR3). The following examples show the possible configuration parameters for InnoDB in my.cnf.

[Mysqld]

# can write your other MySQL server options here

# ...

Innodb_data_home_dir =

#

# Data files must is able to hold your Data and indexes

Innodb_data_file_path =/ibdata/ibdata1:2000m;/dr2/ibdata/ibdata2:2000m:autoextend

#

# Set buffer pool size to 50-80% of your computer ' s memory,

# but do sure on Linux x86 all memory usage is < 2GB

innodb_buffer_pool_size=1g

innodb_additional_mem_pool_size=20m

Innodb_log_group_home_dir =/dr3/iblogs

#

Innodb_log_files_in_group = 2

#

# Set The log file size to about 25% of the buffer pool size

innodb_log_file_size=250m

Innodb_log_buffer_size=8m

#

Innodb_flush_log_at_trx_commit=1

Innodb_lock_wait_timeout=50

#

# Uncomment the next lines if you want and use them

#innodb_thread_concurrency =5

Note that this example places two data files on different disks. InnoDB begins populating the table space with the first data file. In some cases, if all data is not placed on the same physical disk, this will improve the performance of the database. It is often useful to put log files on a different disk than the data file. You can also use raw disk partitions (raw devices) as InnoDB data files, which can speed up I/O.

How do I adjust other mysqld server parameters? The following values are typical and apply to most users:

[Mysqld]

Skip-external-locking

max_connections=200

read_buffer_size=1m

sort_buffer_size=1m

#

# Set Key_buffer to 5-50% of your RAM depending on how much

# MyISAM tables, but keep Key_buffer_size + InnoDB

# Buffer Pool Size < 80% of your RAM

key_buffer_size=value innodb startup options

This section describes InnoDB-related server options, all of which can be specified in the command line or in the options file in the form of-opt_name=value .

· Innodb_additional_mem_pool_size

InnoDB the size of the memory pool used to store data directory information and other internal structures. The more tables you have in your application, the more memory you need to allocate here. If InnoDB runs out of memory in the pool, InnoDB starts allocating memory from the operating system and writes a warning message to the MySQL error log. The default value is 1MB.

· Innodb_autoextend_increment

The size (in megabytes) that is added to the extension when the auto-expansion table space is filled. The default value is 8. This option can be changed at run time as a global system variable.

· Innodb_buffer_pool_awe_mem_mb

If the buffer pool is placed in awe memory in 32-bit Windows, this parameter is the size of the buffer pool (in megabytes). (only relevant on 32-bit Windows) If your 32-bit Windows operating system supports more than 4GB of memory using so-called Address window Extensions (AWE), you can use this parameter to allocate the INNODB buffer pool into AWE physical memory. The maximum possible value for this parameter is 64000. If this parameter is specified, Innodb_buffer_pool_size is a window within the mysqld of the 32-bit address space, and InnoDB maps that AWE memory up. For the innodb_buffer_pool_size parameter, a better value is 500MB.

· Innodb_buffer_pool_size

InnoDB the size of the memory buffer used to cache its data and indexes. The higher you set this value, the less disk I/O you need to access the data in the table. On a dedicated database server, you can set this parameter up to 80% of the size of the machine's physical memory. However, do not set it too large, because the competition for physical memory may cause memory scheduling on the operating system.

· Innodb_checksums

InnoDB uses checksum validation on all page reads to the disk to ensure additional fault tolerance against hardware corruption or data files. Nonetheless, this additional security feature is unnecessary in some rare cases, such as when running a standard check. In these cases, this option (which is allowed by default) can be closed with--skip-innodb-checksums.

· Innodb_data_file_path

To separate data files and their size paths. By connecting the Innodb_data_home_dir to each path specified here, the full directory path to each data file can be obtained. The file size is specified by the end of the dimension value plus m or G in MB or GB (1024MB). The size of the file and at least 10MB. On some operating systems, the file must be less than 2GB. If you do not specify Innodb_data_file_path, the default behavior to start is to create a separate 10MB self-expanding data file named Ibdata1. On those operating systems that support large files, you can set the file size to more than 4GB. You can also use raw disk partitions as data files.

· Innodb_data_home_dir

The directory path is a common part of all InnoDB data files. If you do not set this value, the default is the MySQL data directory. You can also specify this value as an empty string, in which case you can use the absolute file path in Innodb_data_file_path.

· Innodb_doublewrite

By default, InnoDB stores all data two times, stores it to the doublewrite buffer for the first time, and then stores it in a real data file. This option can be used to disable this feature. This option is allowed by default, similar to Innodb_checksums, which is closed with--skip-innodb-doublewrite because the standard check or the need for top-level performance exceeds the focus on data integrity or possible failures.

· Innodb_fast_shutdown

If you set this parameter to 0,innodb, make a full purge and a insert buffer merge before closing. These operations take a few minutes and are set in extreme cases for several hours. If you set this parameter to 1,innodb to skip these actions at the time of closing. The default value is 1. If you set this value to 2 (without this value in NetWare), InnoDB will refresh its log and then cool off, as if MySQL crashed. Committed transactions are not lost, but a crash recovery is done at the next boot.

· Innodb_file_io_threads

The number of file I/O threads in the InnoDB. Normally, this parameter is default and the default value is 4, but large values are good for Windows disk I/O. On UNIX, increasing this number has no effect, and InnoDB always uses the default value.

· Innodb_file_per_table

This option causes InnoDB to use its own. ibd file to create each new table for storing data and indexes, instead of creating it in a shared table space.

· Innodb_flush_log_at_trx_commit

When Innodb_flush_log_at_trx_commit is set to 0, the log buffer is written once per second to the log file, and the log file is refreshed with disk operations, but no action is taken on a transactional commit. When this value is 1 (the default), when each transaction commits, the log buffer is written to the log file, and the log file is refreshed with the disk operation. When set to 2, at each commit, the log buffer is written to the file, but the log file does not flush with disk operations. However, the refresh of the log file at a value of 2 also occurs once per second. We must note that because of the process scheduling problem, a refresh per second is not guaranteed to occur 100% per second. You can not get better performance by setting this value, but you will lose One-second value in a single crash. If you set this value to 0, then any crash of the mysqld process will delete the last second of the transaction before the crash, and if you set this value to 2, then only the operating system crashes or the power down will delete the last second of the transaction. Nonetheless, InnoDB's crash recovery is not affected, and because such a crash recovery begins to function without considering this value. Note that many operating systems and some disk hardware will spoof flushing to disk operations. Although the refresh does not take place, you can tell mysqld that the refresh has been done. Even if you set this value to 1, the durability of the transaction is not guaranteed, and in the worst case, it can even corrupt the InnoDB database. In a SCSI disk controller, or on the disk itself, using a disk cache with a backup battery speeds up file refreshes and makes the operation more secure. You can also try using Unix command hdparm to disable disk write caching in the hardware cache, or use other commands that are specific to your hardware provider. The default value for this option is 1.

· Innodb_flush_method

This option is only valid on UNIX systems. If this option is set to Fdatasync (the default), InnoDB uses Fsync () to refresh the data and log files. If set to O_DSYNC,INNODB use O_sync to open and refresh the log file, but use Fsync () to refresh the data file. If O_direct is specified (available in some gnu/linux versions), InnoDB uses O_direct to open the data file and uses Fsync () to refresh the data and log files. Note that InnoDB uses Fsync () instead of Fdatasync (), and it does not use O_dsync by default because this value has been problematic on many UNIX variants.

· Innodb_force_recovery

Warning: This option is defined only in an emergency situation, when you want to dump the table from a corrupted database. The possible values are from 1 to 6. As a security measure, when this option value is greater than 0, InnoDB prevents the user from modifying the data.

· Innodb_lock_wait_timeout

The InnoDB transaction can wait for a locked timeout number of seconds before being rolled back. InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. InnoDB Use the Lock tables statement to notice the locking settings. The default value is 50 seconds.

For maximum possible durability and consistency in a replication establishment, you should use Innodb_flush_log_at_trx_commit=1 and sync-binlog=1 in the My.cnf file on the primary server.

· Innodb_locks_unsafe_for_binlog

This option turns off the next key lock in the InnoDB search and Index scan. The default value for this option is False (false).

Normally, InnoDB uses an algorithm called Next-key locking . When searching for or scanning a table index, InnoDB implements row-level locking in such a way that it sets a shared or exclusive lock on any index record encountered. Therefore, row-level locking is actually an index record lock. InnoDB the lock on the index record setting also affects "gap" before the index record is locked. If a user has a shared or exclusive lock on a record in an index, another user cannot immediately insert a new index record in the order of the index before R . This option causes InnoDB not to use the next key lock in a search or index scan. The next key lock is still used to ensure foreign key coercion and duplicate key verification. Note that using this option can cause some weird problems: suppose you want to read and lock all child records from the child table with an identifier that has a value greater than 100, and then update some columns to subsequent rows in the selected row:

SELECT * from child WHERE ID > + for UPDATE;

Suppose there is an index in the ID column. The query starts the scan index from the first record with an ID greater than 100. If the lock on the index record does not lock the insert generated at the gap, a new row is inserted into the table. If you execute the same select within the same transaction, you will see a new row in the result package returned by the query. This also means that if a new entry is added to the database, InnoDB does not guarantee continuity, although the correspondence continuity is guaranteed. Therefore, if this option is used, InnoDB guarantees read COMMITTED at most isolation levels.

This option is even less secure. InnoDB only the rows that it updates or deletes are locked in an update or delete. This greatly reduces the likelihood of deadlocks, but deadlocks can occur. Note that this option still does not allow operations such as update to overwhelm similar options (such as another update) even when similar operations affect different rows. Consider the following examples:

CREATE TABLE A (a int not NULL, B int);

INSERT into A VALUES (2,3), (3,2), (4,3), (5,2);

COMMIT;

If a connection executes a query:

SET autocommit = 0;

UPDATE A SET b = 5 WHERE B = 3;

and other connections follow the first connection to perform other queries:

SET autocommit = 0;

UPDATE A SET b = 4 WHERE B = 2;

Then query 2 is to wait for the commit or rollback of query 1, because Query 1 has an exclusive lock on the row (2,3), and query 2 also tries to take an exclusive lock on the same row (2,3) that it cannot lock while scanning the row. This is because when the innodb_locks_unsafe_for_binlog option is used, Query 2 first takes an exclusive lock on a row, then determines whether the row belongs to the result package, and if it does not, frees unnecessary locks.

Therefore, query 1 executes as follows:

X-lock (ON)

Unlock (ON)

X-lock (2,3)

Update (2,3) to (2,5)

X-lock (3,2)

Unlock (3,2)

X-lock (4,3)

Update (4,3) to (4,5)

X-lock (5,2)

Unlock (5,2)

and query 2 executes as follows:

X-lock (ON)

Update to (1,4)

X-lock (2,3)-Wait for Query 1 commit or rollback

· Innodb_log_arch_dir

If we use log files, the directory where the log file is fully written is also archived. If this parameter value is used, it should be set to be the same as Innodb_log_group_home_dir. Nonetheless, it is not required.

· Innodb_log_archive

This value is currently set to 0. Because MySQL uses its own log files to recover from backups, there is currently no need to archive InnoDB log files. The default value for this option is 0.

· Innodb_log_buffer_size

InnoDB the size of the buffer used to write operations to log files on the disk. The sensible value is from 1MB to 8MB. The default is 1MB. A large log buffer allows large transactions to run without the need to write logs to the disk when the transaction is committed. Therefore, if you have large transactions, make the log buffers larger to conserve disk I/O.

· Innodb_log_file_size

The size of each log file in the log group. The combined size of the log files on a 32-bit computer must be less than 4GB. The default is 5MB. A sensible value is one buffer pool size from 1MB to N , where N is the number of log files in the group. The higher the value, the less the buffer pool requires checkpoint refresh behavior to conserve disk I/O. But a larger log file also means that it recovers more slowly when it crashes.

· Innodb_log_files_in_group

The number of log files in the log group. The InnoDB is written into the file in a circular manner. The default is 2 (recommended).

· Innodb_log_group_home_dir

The directory path to the InnoDB log file. It must have the same value as Innodb_log_arch_dir. If you do not specify any of the InnoDB log parameters, the default is to create two 5MB size files in the MySQL data directory named IB_LOGFILE0 and Ib_logfile1.

· innodb_max_dirty_pages_pct

This is an integer range from 0 to 100. The default is 90. The main thread in InnoDB tries to write a page from the buffer pool so that the percentage of dirty pages (pages that are not written) does not exceed this value. If you have super privileges, this percentage can be changed when the server is running as follows:

SET GLOBAL innodb_max_dirty_pages_pct = value;

· Innodb_max_purge_lag

This option controls how the Insert, update, and delete operations are delayed when the cleanup operation is lagging. The default value for this parameter is zero, meaning no delay. This option can be changed at run time as a global system variable.

The InnoDB transaction system maintains a list of transactions with index records that are flagged for deletion by the update or delete operation. Make the length of this list Purge_lag. When purge_lag exceeds Innodb_max_purge_lag, each insert, update, and delete operation is delayed ((purge_lag/innodb_max_purge_ LAG) *10)-5 milliseconds. At the beginning of the cleansing batch, the delay is calculated every 10 seconds. If a consistent read view of rows is purged because of an old one, the delete operation is not delayed.

For a problematic workload, the typical setting might be 1 million, assuming our transactions are small and only 100 bytes in size, we can allow 100MB of non-purified rows in our tables.

· Innodb_mirrored_log_groups

We keep the same number of copies within the log group for the database. The current value should be set to 1.

· Innodb_open_files

In InnoDB, this option is only relevant when you are using a multi-table space. It specifies the maximum number of. ibd files that InnoDB can remain open at one time. The minimum value is 10. The default value is 300.

The file descriptor for the. ibd file is only for InnoDB. They are independent of the descriptors specified by the--OPEN-FILES-LIMIT server option, and do not affect table caching operations.

· Innodb_status_file

This option allows INNODB to create a file <datadir>/innodb_status for the show INNODB status output of the cycle. <pid>.

· Innodb_support_xa

When set to On or 1 (by default), this variable allows InnoDB to support bidirectional commits in XA transactions. Allow Innodb_support_xa to cause an additional disk refresh to prepare for the transaction. If you don't care about using XA, you can disable this variable by setting this option to off or zero to reduce the number of disk refreshes and get better innodb performance.

· Innodb_table_locks

InnoDB values lock TABLES until all other threads have released all their locks on the table, MySQL only from lock table. Write returns. The default value is 1, which means that lock tables lets InnoDB internal locking a table. In applications that use Autocommit=1, InnoDB's internal table lock can cause deadlocks. You can eliminate this problem by setting the innodb_table_locks=0 in the My.cnf file (which is a My.ini file on Windows).

· Innodb_thread_concurrency

InnoDB tries to keep the number of operating system threads within the InnoDB less than or equal to the limit given by this parameter. If there is a performance problem, and show INNODB status shows that many threads are waiting for a signal, you can let the thread "thrashing" and set this parameter smaller or larger. If your computer has multiple processors and disks, you can try this value to make better use of your computer's resources. A recommended value is the sum of the number of processors and disks on the system. A value of 500 or greater than the 500 convention prohibits calling concurrent checks. The default value is 20, and if the setting is greater than or equal to 20, the concurrency check is disabled.

· Innodb_status_file

This option allows INNODB to create a file <datadir>/innodb_status for the show INNODB status output of the cycle. <pid>.

InnoDB Storage Engine