Table spaces and physical files that make up the table space

 

Related documents : Fil0fil.h fil0fil.c

function : The table space on disk and the physical files that make up the table space are managed (such as new, open, close, delete, rename, etc.); the pages in the tablespace are accessed (IO operations) on the physical file.

Introduction

Physical composition of table spaces

InnoDB uses a similar oracle-like Tablespace (tablespace) technology for managing database files. Table space is only a logical management method, and the storage of a database is still physically by file. There are three table spaces in InnoDB: System Tablespace (also known as shared tablespace), redo log table space, and stand-alone table space. What are the physical differences between the three types of table spaces? Let's take a look at the parameters associated with these three table spaces at startup msyqld:

Innodb_data_file_path	This parameter is related to the system table space: For example, set it to innodb_data_file_path=ibdata1:2g; Ibdata2:2g:ibdata3:2g:autoextend The system table space is physically composed of ibdata1, Ibdata2, ibdata3 three files, three files in the new 1GB size, In the next time, the first two files are fixed, and the last file can be self-grown due to the autoextend. Note: The files in the system tablespace can be set to not in MySQL data directory, so they can be distributed across different disk to equalize the load.
Innodb_log_files_in_group Innodb_log_file_size	These two parameters are related to redo log table space: For example, setting innodb_log_files_in_group=3 the Redo log table space is physically composed of Ib_logfile0, Ib_logfile1, Ib_logfile2 a total of 3 files, The size of each redo log file is the same, and the size of each file is specified by the Innodb_log_file_size parameter
Innodb_file_per_table	This parameter is related to a stand-alone table space: A stand-alone table space is used only when this parameter is enabled, and when this parameter is enabled, each InnoDB table created is made from a table space, called a stand-alone tablespace, which is physically composed of only one file with the file name Database_name/table_ Name.ibd Note: The interpretation of this article assumes that the standalone Tablespace function is turned on

Three table spaces are related to physical files:

Summary: A tablespace is physically composed of one or more physical files, and a separate tablespace has only one physical file, and the system tablespace and redo log table space can have one or more physical files depending on the setting of the corresponding parameter.

Logical composition of Table spaces

The previous section describes the physical composition of the tablespace, which is logically composed of a series of sequentially numbered pages. As follows:

Represents a table space containing n pages, each of which has a page_no (also called page offset), and page_no increments from 0. For non-compressed pages, the size of each page is 16KB.

Suspect explanation: The other two concepts related to tablespaces and pages are segments (segment) and zones (extent), and one idiom is that the table space consists of multiple segment, a segment consisting of multiple extent, and 1 extent is made up of 64 pages. In the note above, it has been pointed out that the table space is logically composed of successive pages, then where does segment and extent come from? This involves the management of the table space, in the InnoDB source code this piece is responsible for the FSP module, for the module is not in-depth interpretation here, it needs to be clear here is that the table empty essentially consists of continuously encoded pages, only in the InnoDB for management and performance needs, Pages are not just data pages and index pages, there are many other page types, some of which are dedicated to storing segment or extent information, and reading these pages to know the segment or extent controlled page ranges, Thus, the concept of segment and extent is deduced. To understand the table space for Segment,extent and page management needs to interpret the FSP module, which will be interpreted later, this article will focus on interpreting the FIL module.

The relationship between the physical composition of table space and logical composition--------the relationship between a file and a page

So far, the physical composition and logical composition of table space have been explained, so what is the relationship between the physical composition and logical composition of table space? So what is the relationship between the physical file and the page? The relationship between them, of course, is that the page is stored on a physical file, but for a stand-alone table space, all pages are stored on a single file, and for system and redo log table spaces, if they contain multiple physical files, all pages are distributed across different files. As follows:

It is important to note that in both the system tablespace and the Redo log table space, a page cannot span the end of a file and the beginning of a subsequent file, that is, each page must be completely stored in a file. If the size of the physical file for the specified two tablespaces is not the integer multiple of the page, each physical file is truncated by the page size, and the last superfluous part is not used (since the physical file size of the two tablespace is usually set in M or G, which must be an integer multiple of the page, So the interception of files does not happen.)

Note: Table spaces in InnoDB support compression, but only stand-alone table spaces can be compressed. For uncompressed page, the size is 16KB, and a extent is 1MB. Thus, the page size is always 16KB for both the system tablespace and the Redo log table space, and for a stand-alone table space, the page size depends on whether it is compressed and in what compression level.

Interpretation of FIL module data structure

The table space above the introduction section is located on disk, and one of the modules in InnoDB source code that is closely related to it is the FIL module. The main function of the FIL module is to manage the table space on disk and the physical files that make up the table space (such as new, open, close, delete, rename, etc.); the pages in the tablespace are accessed on the physical file (IO operation).

To implement the above functions that the FIL module is responsible for, you need to create a set of data structures in memory to manage the table space on disk. The FIL module uses three data structures for this purpose: fil_space_t, fil_node_t, and fil_system_t (fil_space_t type instance is represented by space, fil_node_t instance Fil_node, Fil_ System_t instances are represented by Fil_system). Space is used to manage open table spaces, Fil_node is used to manage the corresponding physical files, and Fil_system is used to manage all open tablespaces.

The three table space types mentioned in the introduction section are collectively referred to as space in the FIL module source code and are divided into two types: Fil_tablespace and Fil_log. System and stand-alone table spaces belong to the Fil_tablespace type, and redo log table space belongs to the Fil_log type. In the fil_space_t type, there is a unsigned long member named ID, which represents the number of each open space, number 0 always represents the system tablespace, and the space ID of the redo log tablespace must be greater than or equal to Srv_log_space_ FIRST_ID (0xfffffff0ul), but only one set of redo log groups is used in the current version of InnoDB, with a SPACE ID of srv_log_space_first_id. The ID of the stand-alone table space is between (0, srv_log_space_first_id). The maximum possible value for an ID is 0xFFFFFFFFUL (the upper layer is unsigned by a 32-bit field to the result of a long type).

Fil_system is managed by the Space_list doubly linked list for all open tablespaces, where the first node must be the space corresponding to the system tablespace, and the second node will be the space corresponding to the log tablespace. These two table spaces are always present after the server has been started and continue to occupy the front two locations in the bidirectional list, in turn. A separate tablespace is created and joined to the end of the doubly linked list after it is created or opened.

For each open space, all of the physical files it manages are also created in the in-Memory control node Fil_node. and space links these fil_node through the chain doubly linked list. That is to say, space and all the fil_node that it controls are born and destroyed. In addition, for system tablespaces and redo log table spaces, all of their physical files are open after the server has been started and the server is shut down for the entire time. However, for a stand-alone tablespace, even if space and the only Fil_node are built in memory, the files that Fil_node controls may be turned off, which can occur when you create a table with a InnoDB type, but you do not use the table later.

Fil_system has a member that is a doubly-linked header node LRU, which is used to link some of the open physical files. So why use LRU, and what other files are linked to the LRU doubly linked list? Due to operating system limitations, the number of files that a process can open at the same time is limited, such as using Uname-n on a Linux system to get result 1024, indicating that a process can only open up to 1024 files at a time. At mysqld startup, you can use the parameter Innodb_ open_files to set the maximum number of files that the InnoDB can open (set not to exceed the OS limit), and the default value is 300. The value of Innodb_ Open_files is assigned to Fil_system's member Max_n_open,max_n_open, which indicates the number of file limits that InnoDB can open simultaneously, and Fil_system has a member N_open. Used to record the number of physical files that are currently open. Because of the max_n_open limitations, when there is a database with a large number of tables and the standalone table space feature is turned on, it is not possible to open all the files that exist at the same time. To solve this problem, the LRU policy is used: to hang all the physical files that are open and separate table spaces that do not have an IO operation line on the LRU list, and when the number of open files has reached the limit, to open other stand-alone tablespace files will need to be replaced, and the LRU tail file is closed first. Then the newly opened standalone tablespace file is added to the LRU header.

Summary: The Fil_node node linked on the LRU list must meet the following three conditions: (1) Fil_node control of the file is open, that is, Fil_node->open is TRUE; (2) Fil_node control of the file does not have an IO operation is imminent, that is, fil_node->n_pending needs 0; (3) The Fil_node control file must be a stand-alone tablespace file because the system tablespace and the physical file of the Redo log table space need to be open all the time and therefore cannot be hung on the LRU.

In order to find the table space efficiently, Fil_system used two hash tables, one is to search by Space->id, one is to find by Space->name, the former is most commonly used, the corresponding function is fil_space_get_by_id.

Fil_system also has a member of the Unflushed-space, which will connect all space to be refreshed with a two-way chain link to use for the same refresh. Member mutexes in Fil_system are used to ensure that thread-mutually exclusive access to the entire set of data structures consisting of Fil_system, Fil_node, and space. Because the concurrency involved in DDL is not high, only one mutex is used to ensure that the mutex access performance of the whole data structure is not affected very much.

Data flow

The above has been the FIL module involved in the structure of the main description, but Fil_system, Fil_node, space and many other data members are not described above, specific information to see the corresponding source code. Below is how the InnoDB interacts with the FIL module when it starts, runs, and shuts down from the point of view of the data flow.

InnoDB calls the Innobase_start_or_create_for_mysql function at startup, where five functions that are closely related to the FIL module are called sequentially: Fil_init,open_or_create_data_ Files, Open_or_create_log_file, Fil_open_log_and_system_tablespace_files and dict_check_tablespaces_and_store_max_id. The Fil_init function is used to create FIL_SYSTEM data structures. Open_or_create_data_files is related to system tablespace: It first tries to create the physical file indicated in the Innodb_data_file_path, if it is the first time that the MySQL server is installed and starts mysqld, the creation is successful. However, when the physical file already exists on disk, the creation fails, and the Open_or_create_data_files function opens the file indicated in Innodb_data_file_path, checking the file size and Innodb_data_file_ If all succeeds, close the created or opened file, then call Fil_space_create to create space for the system tablespace, call fil_node_create for all Innodb_data_file_ The file specified in path is created Fil_node. The Open_or_create_log_file function is similar to the open_or_create_data_files operation process, except that it is for redo log table space. The previous section said that the physical files for the system tablespace and redo log table space are always open after InnoDB is started, so the Fil_open_log_and_system_tablespace_files function is called to open these physical files. The Innobase_start_or_create_for_mysql function then calls the DICT_CHECK_TABLESPACES_AND_STORE_MAX_ID function, Dict_check_tablespaces_and_ The role of the STORE_MAX_ID function is to scan the data dictionary (the Tablespace ID of all. ibd files, the table space name, and so on), to get the space ID and space name of the stand-alone tablespace, and then call Fil_open_ Single_table_The Tablespace function creates space and fil_node for stand-alone table spaces. At this point the InnoDB server is started, and the data structure is created as shown (this ignores the hash table and some other data members). It is important to note that the space of all tablespaces and the fil_node of all physical files will be created after InnoDB startup, but the. ibd file for the standalone tablespace is not in the closed state, and all the initial LRU is empty.

So when did the. ibd file get opened? This will refer to the Fil_io function under the FIL module. The Fil_io function is a very important function that needs to be called in many places (such as reading a page to buffer pool), which is responsible for accessing the pages in the table space on the physical file in the FIL module. When the Fil_io function is called for IO operations on a stand-alone table space, the corresponding. ibd file is checked against space_id to see if it is open, and if it is not open, it is first used to open the. ibd file and hang it on the LRU header. The Fil_node_prepare_for_io function then removes it from the LRU to prepare for IO operations, and when the IO operation is complete, fil_node_complete_io is called and the. ibd file is hung again to the LRU header.

The corresponding function in the FIL module is also called when the DDL operation of the InnoDB type table is performed during the InnoDB operation. Create a InnoDB table when called to the Fil_create_new_single_table_tablespace function, delete a InnoDB table when called to Fil_delete_tablespace, Rename a InnoDB table is called to the Fil_rename_tablespace function. Fil_create_new_single_table_tablespace will first create the. ibd file, then close the. ibd file, and then create the appropriate space and fil_node in memory. The Fil_rename_tablespace function first needs to close the corresponding. ibd file and then, based on space_id, finds space and renames the space Space->name and corresponding fil_node->name. Finally, the. ibd file is renamed, but the renamed. ibd file is no longer opened. The Fil_delete_tablespace function first finds space based on space_id, then destroys space and the corresponding Fil_node, and finally deletes the. ibd file.

When InnoDB is closed, the Innobase_shutdown_for_mysql function calls the Logs_empty_and_mark_files_at_shutdown and Fil_close functions successively. The Logs_empty_and_mark_files_at_shutdown function calls to the Fil_close_all_files function of the FIL module, and Fil_close_all_files traverses the fil_system-from the beginning. >space_list linked list, then close the files on each space, and then destroy all space and space-controlled fil_node. Therefore, the Fil_close_all_files function after the execution of the FIG.4 data structure is only left Fil_system, destroy Fil_system is by calling Fil_close function to complete.

Read (+)| Comments (0)

Table spaces and physical files that make up the table space