Improve MySQL's table_cache_MySQL

Improve MySQL's table_cache bitsCN.com

The following are my broken ideas at work, and the records are messy ......

........................................ ................................

In mysql, there is a table_cache parameter. when the value is too large, the efficiency will be significantly reduced. This is because when scanning the open_cache hash table, the linear scan is used, the time complexity is O (n), and someone on the mysql bug list provides a patch (http://bugs.mysql.com/bug.php? Id = 33948), which can reduce the time to o (1). The basic idea is to add three pointers to the table instance to maintain an idle linked list.

First, let's analyze how mysql works when opening a table:

Table_cache is an important parameter in mysql. Because of the multi-thread mechanism, each thread opens the object descriptor that it needs, rather than sharing the opened object.

1. table_cache key (see create_table_def_key)

In the memory, table cache uses hash tables for storage, and the key is database_name/0table_name/0 + (optional, used for temporary tables)

The temporary table is specially processed here, and additional information needs to be added to ensure that the temporary table is unique on the slave end.

Add 8 bytes: the first 4 bytes are the master thread id, and the last 4 bytes are the slavb.

2. processing when opening a table: open_table

***************

Necessary check: whether the thread stack is sufficient and whether thd is killed

**************

Global lock: lock_open

*************************

First, determine whether it is a temporary table.

*************************

Here is a very interesting logic. when you need to open a table, you always need to first look for the table from the temporary table chain table. That is to say, when a temporary table with the same name as the actual table exists, the temporary table is always operated.

If (! Table_list-> skip_temporary)

{

For (table = thd-> temporary_tables; table = table-> next)

{

**************************************** ******

Non-temporary table in pre-locked or lock_tables mode (thd-> locked_tables | thd-> prelocked_mode)

That is, the thread has opened or locked some tables and queried from thd-> open_tables. If no table exists, an error is returned.

**************************************** ******

If (thd-> locked_tables | thd-> prelocked_mode)

{// Using table locks

TABLE * best_table = 0;

Int best_distance = INT_MIN;

For (table = thd-> open_tables; table = table-> next)

{

**************************************** ***************

Normal situation:

1. first try to get the table from the table cache

2. when the TABLE instance is found to be nam-locked, or some threads are flush tables, we need to wait until the lock is released.

3. if such a TABLE does not exist, we need to create a TABLE and add it to the cache.

! Global locks are required for these operations: LOCK_open to protect table cache and table definitions on disks.

**************************************** ***************

If this is the first table opened by the query: set thd-> version = refresh_version. in this way, when we open the remaining table, if the version changes, we need to back off, close all opened tables and reopen them.

Currently, refresh_version will only be changed by the flush tables command.

If (thd-> handler_tables)

Mysql_ha_flush (thd); // refresh (close and Mark as re-open) all tables that require reopen

The process of querying table cache:

For (table = (TABLE *) hash_first (& open_cache, (uchar *) key, key_length, // search for the hash table based on the same key

& State );

Table & table-> in_use;

Table = (TABLE *) hash_next (& open_cache, (uchar *) key, key_length,

& State ))

{

**********************************

Flush tables marked for flush.

Normally, table-> s-> version contains the value

Refresh_version from the moment when this table was

(Re-) opened and added to the cache.

If since then we did (or just started) FLUSH TABLES

Statement, refresh_version has been increased.

For "name-locked" TABLE instances, table-> s-> version is set

To 0 (see lock_table_name for details ).

In case there is a pending flush tables or a name lock, we

Need to back off and re-start opening tables.

If we do not back off now, we may dead lock in case of lock

Order mismatch with some other thread:

C1: name lock t1; -- sort of exclusive lock

C2: open t2; -- sort of shared lock

C1: name lock t2; -- blocks

C2: open t1; -- blocks

*********************************

If (table-> needs_reopen_or_name_lock () // Is this instance of the table shocould be reopen or represents a name-lock?

{}

}

If (table)

************

Remove the newly found table from the unused_tables linked list

************

Else

***********

Create a new table instance and insert it into open cache.

***********

While (open_cache.records> table_cache_size & unused_tables) // release unused TABLE instances when the cache is full.

Hash_delete (& open_cache, (uchar *) unused_tables );

If (table_list-> create) // create a new table

{

*******

Check whether check_if_table_exists exists

*******

Create a placeholder (placeholder) in the hash of table cache: table_cache_insert_placeholder

Link the placeholder to the open tables list:

Table-> open_placeholder = 1;

Table-> next = thd-> open_tables;

Thd-> open_tables = table;

Return table

}

Create a new table instance

Memory allocation table = (TABLE *) my_malloc (sizeof (* table), MYF (MY_WME ))

Error = open_unireg_entry (thd, table, table_list, alias, key, key_length,

Mem_root, (flags & OPEN_VIEW_NO_PARSE ));

If the view or error <0 releases the memory, return;

My_hash_insert (& open_cache, (uchar *) table)

------------------------------------------------

Patch: http://bugs.mysql.com/bug.php? Id = 33948

Add three pointers:

Hash_head:

Hash_prev: always point to unused table cached items

Hash_next: always point to used table cached items

Modified functions:

Free_cache_entry // release the memory of a table.

Close_thread_table // move one table to free list

Reopen_name_locked_table // open the table again to maintain the linked list structure.

Table_cache_insert_placeholder

Open_table

------------------------------------------------------------------------

Summary:

Three pointers are added:

Hash_head:

Hash_prev:

Hash_next:

!.............................! Head !.........................!

The left side of the head is the idle item linked list.

The right side of the head is the list of occupied items.

All items use hash_prev and hash_next for bidirectional pointers.

Hash_head of the item on the right points to head

Linked list:

1) insert a new idle item: add it before the head node

2) insert a new occupied item: add it after the head

3) delete an item from the linked list:

--- If this item is head, modify the hash_head of the item on the right of the head to point to head-> next

--- Otherwise, delete the item and release the memory ..

Query Idle nodes:

1) locate the head

2) Check whether the head is in_use. if the value is False, the table is set to head. if the value is true, the system finds table = head-> prev.

3) when the table is not NULL, it indicates that an item is found and inserted to the right of the head.

3) the table is still NULL ----> create a new item and insert it to the right of the head.

------------------------------

Reprinted please note: Yin Feng

BitsCN.com