Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to optimize table_cache in MySQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

The table_cache parameter sets the number of table caches. At least one table cache is opened for each connection. Therefore, the size of table_cache should be related to the setting of max_connections. For example, for 200 joins running in parallel, the table should be cached at least 200 × N, where N is the maximum number of tables in a join that can be executed. In addition, some additional file descriptors need to be reserved for temporary tables and files.

When Mysql accesses a table, if the table is already open in the cache, the cache can be accessed directly; if it is not cached, but there is still space in the Mysql table buffer, the table is opened and placed in the table buffer If the table cache is full, the currently unused tables will be released according to certain rules, or the table cache will be temporarily expanded to store. The advantage of using the table cache is that you can access the contents of the table more quickly.

Executing flush tables clears the contents of the cache. In general, you can determine whether you need to increase the value of table_cache by looking at the state values Open_tables and Opened_tables of the peak run time. Where open_tables is the number of tables currently open and Opened_tables is the number of tables already open. The following example shows the changes in these two state values:

First, clear the table cache:

> flush tables

Query OK, 0 rows affected (0.00 sec)

Check the current table cache:

Mysql > show global status like open%_tables

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 0 | |

| | Opened_tables | 543 |

+-+ +

2 rows in set (0.00 sec)

Access a table on the current connection:

Mysql > select count (*) from T1

+-+

| | count (*) |

+-+

| | 4 |

+-+

1 row in set (0.03 sec)

Mysql > show global status like open%_tables

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 1 | |

| | Opened_tables | 544 | |

+-+ +

2 rows in set (0.00 sec)

Both Open_tables and opened_tables increased by 1.

If you visit another table, you can see that both parameters are increased.

Mysql > select count (*) from T2

+-+

| | count (*) |

+-+

| | 1 |

+-+

1 row in set (0.06 sec)

Mysql > show global status like open%_tables

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 2 | |

| | Opened_tables | 545 |

+-+ +

2 rows in set (0.00 sec)

When you access table T1 again:

Mysql > select * from T1

+-+

| | id |

+-+

| | 2 |

| | 1 |

| | 1 |

| | 3 |

+-+

4 rows in set (0.02 sec)

Mysql > show global status like open%_tables

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 2 | |

| | Opened_tables | 545 |

+-+ +

2 rows in set (0.00 sec)

Neither parameter has changed because the table has been opened in the table cache and has not been opened repeatedly.

If you find that open_tables is close to table_cache and the value of Opened_tables is gradually increasing, it means that the setting of table_cache may be too small, and it is often necessary to clear out the cached table and put the new table into the cache. At this time, you can consider increasing the size of this parameter to improve the efficiency of access.

Thank you for reading this article carefully. I hope the article "how to optimize table_cache in MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.