InnoDB Storage Engine

databases and Instances  Database: A collection instance of a physical operating system file or other form of file type (instance): The MySQL database consists of a background thread and a shared memory area. Typically, the two are a one-to-many relationship; however, there may be situations where a database is used by multiple data instances in a cluster scenario. The performance of the MySQL instance on the system is a process;  InnoDB Storage Architecture  

InnoDB in-memory buffer pool; InnoDB related disk files

InnoDB system tablespace File: Ibdata1 storage:

• Rollback segment
• All InnoDB table Metadata information (This is why InnoDB cannot directly use the table definition file table name, like the MyISAM table. frm and table data file table name. IBD Copy to another library because there is also partial metadata information in the Ibdata1 file)
• Double Write,insert buffer dump and so on

Automatic scaling mechanism Basic Parameters View configuration parameters for InnoDB

MySQL> like "%innodb%";

Basic parameters: Innodb_data_home_dir: The system tablespace file ibdata1 in which directory Innodb_log_group_home_dir: Log file Ib_logfile0/1 in which directory to store innodb_                  Data_file_path: Defines the properties of the system Tablespace file Ibdata1, innodb_autoextend_increment: The size of the system tablespace file per extension innodb_log_file_size: Ib_logfile file size (increase in write operations) Innodb_log_files_in_group: There are several ib_logfile files (you can increase the number of write operations) innodb_file_per_table: key ; when turned on, the table definition file table name is generated. frm, and table data file table name. IDB, The data for each table will then exist in its own. idb file; The data will be present in the system tablespace file Ibdata1 file, which will ibdata1 very busy and bloated, and ibdata1 can't shrink, like dropping a big table on the line, at this point the ibdata1 is not automatically scaled down (needs to be optimized using Optimiza table), and if enabled, the data exists in the. idb file and can be scaled down at any time; InnoDB data file storage structure

Characteristics:

• Fast based on primary key addressing speed
• Insert inserts with primary key value increment are more efficient
• Primary key value Random insert insert operation is inefficient
• Therefore, the InnoDB table must specify the primary key, which is recommended to use the self-increment number;

If you do not use a primary key, the system automatically adds a 6-character string to the primary key;INNODB data block Cache pool

• Data read/write needs to be cached (cached in buffer pool in memory)
• Data is read into the cache in full page (16K) bit units
• The data in the cache is swapped out with the LRU policy (minimum usage policy)
• High IO efficiency and good performance

Innodb_buffer_pool_size:

For IO efficiency, the files modified by the database are all in the memory cache, so we know that in the event of a power outage, the data in memory disappears, and how does the database guarantee the integrity of the data? That is data persistence and transaction logs; InnoDB data Persistence and transaction logs

• Transaction log real-time persistence
• Memory change data (dirty data) incremental asynchronously brushed out to disk
• Instance failure by replay log recovery
• Good performance, reliable, fast recovery;

If it goes down: Apply the log files that have been persisted, read the log files that are not persisted to the data file, and re-persist the records to our data files. Pros and Cons: If the real-time flush to disk, to find the x random location, io Consumption is large, and if the changes are flushed to the log file, because it is sequential read and write, the speed is much faster. InnoDB log Persistence related parameter Innodb_flush_log_at_trx_commit InnoDB row-level lock

• Write does not block read
• Writes of different lines do not block each other
• Good concurrency performance

InnoDB and transaction acid transaction acid features full support

• Rollback segment Failure rollback A
• Support for primary FOREIGN KEY constraint C
• Transaction version + rollback segment =MVCC I
• Transaction log Persistence D

The default repeatable read isolation level allows you to adjust InnoDB Key Features

• Insertion buffer (insert buffer)
• Write two times (Double write)
• Adaptive hash Indexes (Adaptive Hash Index)
• Asynchronous io (async IO)
• Refresh the pick page (Flush Neighbor page)

resulting in better performance and higher reliability. 1. Insert a buffer usage scenario, which is not a unique secondary index insert operation, because it is not sequential, so insert these operations into the insert buffer, and then a period of time uniformly inserted into the real index, it is very likely that there are several insert merge operations, because they are on the same index page operation, This greatly increases the efficiency.  The key is to cache some discrete operations, and then find some action items that work on the same index page to merge, which improves efficiency. 2. Two writes improve the reliability of the data page.  Before applying the redo log, the user needs a copy of the page, and when the write invalidation occurs, the page is restored by the duplicate of the page, which is duble write. Dublewrite composition

• In-memory dublewrite buffer, size 2M,
• On a physical disk, 128 consecutive pages in a shared tablespace, or 2 extents (extend), are the same size as 2M.

When a dirty page of a buffer pool is refreshed, instead of writing the disk directly, the dirty page is first copied to the in-memory doublewrite buffer through the memcpy () function, and then two times through Doublewrite, each 1M sequentially to the physical disk of the shared tablespace. In this process, because the Doublewrite page is sequential, the process is sequential and the overhead is not very large. When you finish writing the Doublewrite page, the pages in Doublewrite buffer are written to each tablespace file, and the writes are discrete.  If the operating system crashes during the writing of the page to disk, during the recovery process, InnoDB can find a copy of the page from the Doublewrite in the shared tablespace, copy it to a tablespace file, and then apply the redo log. 3. Adaptive hash Index hashing (hash) is a very fast way to find, in general, the time complexity of this lookup is O (1), that is, the general need to locate the data only once. B + Tree Search times, depending on the height of B + tree, in the spawning environment, B + Tree height is generally 3-4 layers, it is required 3-4 times query. InnoDB will monitor queries against the table's previous index page. If you observe that establishing a hash index can result in a speed increase, a hash index, called an Adaptive Hash Index (Adaptive hash Index,ahi), is established. AHI has a requirement, that is, continuous access to this page. ModeMust be the same. For example, (A, B) access mode situation: where a = xxxwhere a = xxx and b = XXX access mode refers to the condition of the query, if the two queries are alternating, then InnoDB will not construct ahi for the page, in addition Ahi has the following requirements:

• Accessed 100 times in this mode
• The page accesses n times through this mode, where the *1/16 is recorded in the n= page;

After the AHI is started, the read and write speed is increased by twice times, and the connection operation performance of the secondary index can be increased 5 times times. AHI, the database is automatically optimized, the DBA only needs to instruct the developer to use the query which conforms to the AHI condition as far as possible. to improve efficiency。 4. Asynchronous IO  Sync io: Synchronous io, which is the end of each IO operation, will continue for the next operation. However, if the user sends a query that waits for an index scan, the SQL query statement may need to scan multiple index pages, that is, IO operations that require multiple times. It is not necessary to scan one page at a time and wait for the next scanning to complete. Asynchronous IO: The user can issue an IO request immediately after another IO request, when all IO requests are sent, wait for all IO operations to complete, this is AIO. Another advantage of AIO is that multiple IO can be combined into 1 io to improve IO efficiency. For example: The user needs access to 3 pages of content, but this 3 page is continuous. Synchronous IO requires 3 io, and AIO only needs to be done one time.  75% 5 faster recovery using AIO. Refresh the picking page how it works: When a dirty page is refreshed, InnoDB detects all pages in the page's region (extent) and refreshes together if it is dirty. In doing so, multiple IO write operations are combined into one IO operation via AIO. There are significant advantages under traditional mechanical disks. The innodb_flush_neighbors parameter to control whether it is turned on. Summary

• Databases and instances
• InnoDB Related disk files:
• • IBDATA1:
  • • Rollback segment
    • Table Meta Data
    • Double Write
    • Insert buffer Dump, etc.
  • Ib_logfile0/1
  • . frm: Table definition file
  • . IBD: Data files, innodb_file_per_table=1
• Performance-related parameters:
• • Innodb_log_file_size
  • Innodb_log_files_in_group
  • Reason: When redo log uses the round-finding paradigm Ib_logfile0 write, write Ib_logfile1 finish, clear IB_LOGFILE0 and continue to write Ib_logfile0; when Ib_logfile1 finished, ib_ Logfile0 in the data is not persisted to the disk, and a new write, this time will block the new write, force flush IB_LOGFILE0 to disk, and then write, write Ib_logfile0, so that the greater the logfile of its writing is less prone to blocking, write performance is better.
• Data node 16K per page
• INNODB Data Block Cache pool
• • Data read and write through the cache pool
  • Data is read in full-page units
  • LRU policy (least used) swap out,
• InnoDB data persistence: Through the transaction log
• Innodb_flush_log_at_trx_commit
• • 0: Write and persist once per second (insecure, high performance, data loss regardless of MySQL or server downtime)
  • 1: Every commit is persistent (security, low performance, IO load Heavy)
  • 2: Each commit is written to the memory of the redo log cache, refreshed every second to disk (security, performance tradeoff, MySQL downtime data is not lost, server downtime data is lost)
• InnoDB Key Features
• • Insertion buffer (insert buffer)
  • Write two times (Double write)
  • Adaptive hash Indexes (Adaptive Hash Index)
  • Asynchronous io (async IO)
  • Refresh the pick page (Flush Neighbor page)

InnoDB Storage Engine