Table_open_cache of MySQL performance optimization

Source: Internet
Author: User

MySQL default Table_open_cache is 64, this value is small, if the max_connections is large, it is easy to cause performance problems.

Performance: Database query efficiency is slow, show processlist found that more than the query is opening table.

Further confirmation, execute the following statement:

Mysql> show global status like ' open%tables% ';

+---------------+---------+

| variable_name | Value |

+---------------+---------+

| Open_tables | 345 |

| Opened_tables | 9734116 |

+---------------+---------+

The Opened_tables value is very large, indicating that the cache is too small, causing the open table to be used frequently to view the current Table_open_cache settings:

Mysql> Show variables like '%table_open_cache% ';

+------------------+-------+

| variable_name | Value |

+------------------+-------+

|     Table_open_cache | 64|

+------------------+-------+

The default is 64, and some data recommend setting this value to (the number of tables used by the max_connections* query). I found in practice that the general setting for Max_connections is no problem (if not enough, you can continue to increase, but you cannot set the big outrageous, may cause other problems). 4G memory machine, recommended setting to 2048 immediate effect settings:

mysql> set global table_open_cache=2048;

Query OK, 0 rows Affected (0.00 sec)

After setting, you can observe that if opening table no longer appears, the modification is valid and added to the MySQL configuration file so that the setting can still be maintained after the database restarts.


Mysql> Show variables like '%table_open_cache% ';

+----------------------------+-------+

| variable_name | Value |

+----------------------------+-------+

| Table_open_cache | 400 |

| table_open_cache_instances | 1 |

+----------------------------+-------+

2 rows in Set (0.00 sec)



Values that are more appropriate:

Open_tables/opened_tables >= 0.85

Open_tables/table_open_cache <= 0.95


Table_open_cache of MySQL performance optimization

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.