MySQL Open Table

Background:
MySQL often encounters too many open files,mysql on the Open_files_limit and OS level set on the open file limit what is the relationship?
The source code will also see different data structures, table, Table_share, and what is the relationship with the table?
MySQL Flush tables Again do something, these things put together, will be more confused, the following to comb:

1 Data structures

Table:mysql a Table object for each of the tables in a query SQL
Table_share:mysql Create a Table_share object for each table, corresponding to the frm file
Handler: corresponds to each Table object, the InnoDB engine creates a handler
Dict_table_t:innodb for each IBD table load a data Dictionary object

The following is a brief picture:

1. The Open Table process

Test Sql:select * from XPCHILD.PP

function Call Stack:

Open_and_lock_tables
Open_tables
Open_and_process_table
Open_table: Opens a table and assigns the value to Table_list->table

The following logic goes into the core:

Start lock Lock_open:

1.1 Table_share:

First create a table_share based on Db&&table_name.

function Call Stack:

Get_table_share_with_discover: Create or Find Table_share objects.
Get_table_share: If the global cache:table_def_cache exists, it is used directly and if it does not exist, create a Table_share
Alloc_table_share: If it does not exist, a share object is assigned first.
Open_table_def: Open frm file.
Mysql_file_open:my_open Open
Inline_mysql_file_close:my_close off
share->ref_count++;

Points:

1. The MySQL server layer maintains a global table definition cache that is Table_def_cache, so a table_share is created only once and subsequently shared.
2. During initialization of Table_share, the file system is called to open the frm file, and after initialization is complete, the frm file is closed.
3. Ref_count in the share structure to indicate how many table associations

1.2 Open Table:

Open_table_from_share

Step1: Initialize variables in table, including file handler (Get_new_handler (Share->db_type ()))
Step2: Adding various indexes, field structure
Step3:file->ha_open:ha_innobase::open creates a handler for the InnoDB table.

Points:

1. In the Open table process, InnoDB creates a handler and opens the IBD file, initializing the dict_table structure.

Release Lock Lock_open

1.3 Close table:

Before SQL execution finishes, the close table is called
Close_open_tables (THD)
Table_def_unuse_table:

Points:
1.table_share maintained two doubly linked list used_tables,free_tables, when close table,

1.1 Table->in_use=null,
1.2 Remove table from Used_tables
1.3 Adding to Free_tables

2. Global parameter table_cache_size, already current Table_cache_count count control cache replacement Strategy

2. Execute SQL again

Because the first step has been created for Table_share, and the cache is table, the process of open table is relatively simple when you execute SQL again:
2.1:get_table_share: Get the Table_share object directly from the cache
2.2:open_table_from_share: Gets the cache's available table from the S->free_tables and increments the ref count.
if (!share->free_tables.is_empty ())
Table= Share->free_tables.front ();
++share->ref_count;

3 System Count:

Opened_tables: The system in Open_table_from_share, the new table, the thd->status_var.opened_tables++ count.
Opened_shares: The system in the Open_table_def function, for the first time open Table_share thd->status_var.opened_shares++ count

Note: So when the system status:open_tables growth is more, you can appropriately increase the table_cache_size, used to cache more table, after all, open table overhead is not small.

4 Status statistics

Use the show status command for several of the Open:

{"Open_files", (char*) &my_file_opened, Show_long_noflush}
NOTE: "Global variables" MySQL and InnoDB file system opened by the count of files, here include all the files, Binlog,relay,alert,slow log and so on.

{"Open_table_definitions", (char*) &show_table_definitions, Show_func},
Note: The number of Table_share currently open by global variables server equals the number of table_def_cache.records

{"Open_tables", (char*) &show_open_tables, Show_func}
Note: The number of table currently open by global variables server, server maintains a global variable Table_cache_count

{"Opened_files", (char*) &my_file_total_opened, Show_long_noflush}
Note: The total number of files opened since global variables started

{"Opened_tables", (char*) offsetof (Status_var, Opened_tables), show_long_status}
NOTE: "Thread variables" in the real open_table_from_share process, the cumulative count

{"Opened_table_definitions", (char*) offsetof (Status_var, Opened_shares), show_long_status}
Note: "Thread variable" when really open share, the cumulative count

Note: The process of Use test:
In the process of use test, the table_share of all the tables under test DB are polled, that is, open all the frm files, and create Table_share objects, and finally close all the frm files.

In order to facilitate debugging, wrote a process monitoring program: pidmon.py

At GDB, you can see all the files that the mysqld process opens:

Too Many open files:

This includes the server layer open frm, and the InnoDB layer open IBD, when the open file exceeds limit limit, the error will be reported.
However, this limitation involves two parameters:
One is the MySQL configuration of the Open_files_limit
An open file limit for a process that is configured by the OS layer

MySQL Open Table