MySQL InnoDB Storage Engine

InnoDB :- InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.7, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.

With the Innodb storage engine you have control of the format and the location of the tablespace, a tablespace is a logical group of one or more data files in a database, using parameters you can control the path of the home directory and if you want to use separate files or a shared tablespace.

innodb_data_file_path - Determine both the path to individual centralized data files (shared tablespace) and the size of the files
innodb_data_home_dir - The common part of the directory path for all InnoDB data files
innodb_file_per_table - If enable then any InnoDB tables will be using their own .idb file for both data and indexes rather than in the shared tablespace.

It has the following key features:
- Transactional support provided by MVCC (Multi Version Concurrency Control)
- Row-level locking
- Foreign key support
- Indexing using clustered B-tree indexes
- Configurable buffer caching of both data and indexes
- Online non-blocking backup through separate commercial backup program

The full path to each shared tablespace is formed by adding innoDB_data_home_dir to each path specified in the innoDB_data_file_path, the file sizes can be specified in Kb, Mb or Gb. By default if innoDB_data_file_path is not specified a 10MB ibdata1 file is created in the data directory (datadir). You cannot just move the data files from one system to another like you can in the MyISAM engine.

There are a number of configuration parameters that you can use with the InnoDB storage engine:

innodb_buffer_pool_size- Determines the size of the buffer that the Innodb storage engine uses to cache both data and indexes
innodb_flush_log_at_trx_commit -Configures how often the log buffer is flushed to disk
0 - log buffer is written every second
1 - every commit will make the log buffer flush
2 - basically is a combination of 0 and 1
innodb_log_file_size
- Determines the size (in bytes) of each of the Innodb log files, by default they are 5MB. Remember the bigger the file the slower

The larger the buffer the more data you can hold in memory which in turn increases performance, thus try to keep your most frequently used data in memory. Don't go too mad as to much memory could cause swapping by the operating system which in turn will degrade performance.
You can get detailed information regarding your InnoDB storage engine using the show command, there are a number of status sections which I will highlight now:

Innodb Status sections
show command	show engine innodb status
semaphores	Reports threads waiting for semaphore and statistics on how many times threads have been forced to wait for an OS call, waiting on a spin wait, or a mutex or rw-lock semaphore. A large number of threads waiting indicates that there is a disk I/O problem or contention issues within the InnoDB.
foreign key errors	Displays information about foreign key problems
deadlocks	Displays information about the last deadlock that occurred
transactions	Reports lock waits, which may highlight lock contention within your application, it can also help detecting transaction deadlocks.
file I/O	Show activity about the threads used by the I/O
insert buffer and adaptive hash index	Displays information about insert buffer including size and amount of free space.
log	show information about the log files
buffer pool and memory	show buffer pool activity, including hit rates, etc
row operations	show activity of the main thread

InnoDB uses shared tablespace for all tables and indexes that can consist of one or more files, hey are generally located in the datadir directory, the files contain metadata and are referred to as ibdata files. When the tablespace becomes fragmented the files are not shrunk, but the free space is still available to the database, you can view this free space by using the data_free field of the information_schema.tables system view and the data_free field of show table status.

You can have separate files instead by using the parameter innodb_file_per_table as mentioned above, however a small amount of metadata will be in the shared tablespace, to defrag the tablespace use the optimize table command which will be discussed in another section entitle SQL Extensions.

When working with ibdata files you can add additional files, these can be split over different disks, but with SAN replacing the old disk technology this seems a lesser requirement today, here are some examples on creating the ibdata files:

ibdata files
## Create a 1GB ibdata file that will autoextend if needed
innodb_data_file=ibdata1:1024M:autoextend
innodb_data_file=ibdata1:1G:autoextend

## create 1GB ibdata file and a second 1GB ibdata file that will autoextend
innodb_data_file=ibdata1:1024M;ibdata2:1024M:autoextend


MySQL will not stripe across disks, thus the first data file must be full before using the second data file, also some tables may be split across the two files when you start to use the second file, for example say that you have 1MB free in the first file and you create a 2MB table, 1MB will be in the first file and the second 1MB will be in the second file.

If you wish to change the size of the data files there is no easy way, the following steps must be taken
- perform a export of all the InnoDB tables
- shutdown MySQL
- change the configuration options as desired to remove or reorganize a tablespace
- move the existing ibdata file and log files to a backup location
- restart MySQL
- import the InnoDB tables
- once happy you can delete the old database files to recoup some space back

Due to the log files, InnoDB automatic crash recovery is possible, just like in other databases all changes are recorded in the log files and replayed back if the server were to crash. There are a number of parameters that can affect the log files

innodb_log_files_in_group	the log files are written in a circular manner, they are initialized to there full size when MySQL starts
innodb_log_file_size	sets the logfile size in each group, the total size of all the logfiles must be less than 4GB, but this may be due to the O/S limitations so check with the latest MySQL release
innodb_fast_shutdown	this determines if logs files are purged during a shutdown which means it may take longer to completely shutdown MySQL 0 - log files are purged after shutdown 1 - log files are not purged after shutdown (default) 2 - simulates a crash, which causes the log buffer to be written after every commit and flushes the log files to disk once per second
innodb_flush_log_at_trx_commit	controls how often the log files are written to 0 - causes the log files to be written to disk once per second 1 - causes the log files to be written after every transaction commit (default) 2 - a combination of 0 and 1
innodb_flush_method	changes how Innodb opens and flushes data log files, this is a trade off between performance and inconsistent data during a crash, I will discuss this further in tuning.
innodb_log_buffer_size	this is a write buffer for the log files, the larger the buffer the less often the log files are written to thus saving I/O.

InnoDB Size Limits

• Max # of tables: 4 G
• Max size of a table: 32TB
• Columns per table: 1000
• Max row size: n*4 GB
• 8 kB if stored on the same page
• n*4 GB with n BLOBs
• Max key length: 3500
• Maximum tablespace size: 64 TB
• Max # of concurrent trxs: 1023