InnoDB Chinese Reference Manual---12 file space management and disk I/O

Reference | Reference Manual | Chinese InnoDB chinese Reference Manual---canine (heart sail) Translation 12 file space management and disk i/o12.1 disk I/O and raw devices
InnoDB uses simulated asynchronous (simulated asynchronous) disk I/O to build Innodb:innodb create many I/O threads to handle I/O operations, just like Read-ahead.

Starting from 3.23.40b, InnoDB uses a novel file dump Cleanup (flush) technology called "Double Write (Doublewrite)". This increases the security of crash fixes after an operating system crash or outage, and improves performance in some Unix systems due to reduced fsync operations and the necessary number of times.

InnoDB the dual Write (Doublewrite) method of writing a page to a neighboring table space before writing it to a data file is a double write buffer (doublewrite buffer). InnoDB writes the page to the appropriate location in the data file only after the write and dump purge (flush) Double write buffering is complete. If the operating system crashes during page writes, InnoDB will find a good copy (copy) in the double write buffer to recover.

Starting with 3.23.41, you can also use a raw disk partition (a raw device) as a data file. When you create a new data file you must add the keyword Newraw immediately after innodb_data_file_path the data file size you set. This partition must be >= the size you specify. Note that 1 MB is 1024 x 1024 bytes in InnoDB and 1 MB in the disk specification is 1000 bytes.
Innodb_data_home_dir= Innodb_data_file_path=/dev/hdd1:3gnewraw;/dev/hdd2:2gnewraw

　

When you start the database system again, you must change the keyword to raw. Otherwise InnoDB will rewrite your partition! Starting with 3.23.44, as a security measure, InnoDB will prevent users from modifying any data in the partition specified by Newraw. After you add a new partition, close the database server, modify the my.cnf file, replace the Newraw with raw, and then restart.
Innodb_data_home_dir= Innodb_data_file_path=/dev/hdd1:3graw;/dev/hdd2:2graw
Without buffering (non-buffered) I/O can be implemented in Windows and some unixes systems by using a raw disk.
　

There are two heuristic (heuristics) Read-ahead heuristics in InnoDB: Sequential (sequential) Read-ahead and random (random) read-ahead. In sequential Read-ahead, InnoDB accesses a fragment of a table space in a partition in a sequential manner. Thus InnoDB will read the database page to the I/O system in a batch. In random Read-ahead, InnoDB reads a table space that appears to be in the buffer pool for some of the useful space in the process. Thus the InnoDB post remains read to the I/O system.
12.2 File Space Management
The data file defined in the configuration file forms a InnoDB tablespace. The file is simply coupled to form a table space and should not be stripped in use. Usually you don't know where the space used by your table is assigned, unless you use the following fact: In a newly created tablespace, InnoDB begins allocating space from the end of the file.

Table spaces consist of database pages that default to KB. Each 64 consecutive pages are composed of one area (extent). "Files" within a tablespace are called segments (segments) in InnoDB. The rollback segment (rollback segment) is slightly misleading because it is actually composed of a number of segments in the table space.

Each index in the InnoDB is assigned two segments (segments): One for the B-tree node (non-leaf nodes) and the other for the leaf node (leaf nodes). This is to achieve a better order of leaf nodes that contain data (sequentiality for the leaf nodes).

When a segment in the tablespace grows, InnoDB assigns the original 32 pages to it individually. The entire area of the segment (extents) is then InnoDB. InnoDB will add a large segment by 4 regions per time (extents) to ensure a good order of data.

Some of the pages in the table space contain bitmaps (bitmaps) for other pages, so some areas within the InnoDB tablespace (extents) cannot be assigned to segments as a whole, but as individual pages.

When issuing a query show TABLE STATUS from ... Like ... To inquire about the amount of space left in the tablespace, InnoDB reports the parts that are actually available in all of the free areas (extents) in the table space. InnoDB typically preserves areas for clean-up and other internal purposes; These reserved areas are not included in the remaining free space.

When you delete data from a table, InnoDB shrinks the corresponding index in B-tree. This is a deletion mode that relies on releasing individual pages or regions (extents) to allow other users to use the remaining space. removing (drop) a table or deleting all records ensures that space is freed to other users, but the delete record row is physically removed only if it is not needed after the transaction rolls back or consistent read.
12.3 Defragment a table
If there is a random insertion and deletion within the index of a table, the index will produce fragmentation. Fragmentation means that the physical order of index pages on disk is not close to the alphabetical records of the page, or that there are too many unused pages in the 64 page blocks assigned to the index. If you periodically dump the table to a file file by mysqldump, remove the (drop) table and overload it from the dump file, which will increase the speed of the index scan. Another way to defragment it is to change the table type to MyISAM and then to InnoDB. Note that the MyISAM table must be a separate file on your operating system.

If the inserts in the index always rise and are always removed from behind, then the InnoDB file space management algorithm guarantees that the fragments in the index will not appear.