Place of all tech articles

techarticles

  • Home
  • About
  • Contact Info

Optimize MySQL table_open_cache

21st September, 2014 · techinf1 8 Comments
optimize mysql table_open_cache

Optimize MySQL table_open_cache

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable.  If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.  To optimize MySQL table_open_cache you need to consider lot of factors. However, these are the general procedure to decide whether this variable should be tuned.

1. Find current value of open_tables and opened_tables

mysql> show global status  like 'open%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Open_files               | 1583    |
| Open_streams             | 0       |
| Open_table_definitions   | 1400    |
| Open_tables              | 2000    |
| Opened_files             | 2619222 |
| Opened_table_definitions | 110583  |
| Opened_tables            | 482099  |
+--------------------------+---------+
7 rows in set (0.00 sec)

2. Find out Table cache hit rate

Table cache hit rate = table_open_cache*100/Opened_tables.  
                 = 2000*100/482099
                 = 0.41%

In general it should be more than 50%. So you need to increase value of table_open_cache, though there are lots of reasons to have a high value of Opened_tables. Like FLUSH TABLES will close all open tables and reopen it which significantly increases Opened_tables value.

At this stage you are almost sure table_open_cache system variable is not tuned properly. Now you have to optimize MySQL table_open_cache and  find out perfect value for this.  To find tuned value of  table_open_cache value follow the steps:

1. Find out total tables of your database

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
+----------+
| COUNT(*) |
+----------+
|     2020 |
+----------+
1 row in set (0.05 sec)
  1. Find threads currently connected to your database.
mysql> show global status like '%Threads_connected%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 39 |
+-------------------+-------+
1 row in set (0.00 sec)

It would be best if you take threads connected at busiest time of your database or take several times at different time and make an average.

3. Calculate  the tune value of table_open_cache and set it

Table_open_cache = total_tables*Threads_connected
         = 2020*39
         = 78780

As all the threads (user) are not generally access all tables. I think you should set 50% of the value calculated.  Because too big value of this variable has some other side effects.  So the formula becomes

Table_open_cache = total_tables*Threads_connected*.50

4. Along with table_open_cache you should also tune open_files_limit system variable.

In general it is 2x of  table_open_cache.

open_files_limit= Table_open_cache*2

open_files_limit is not a dynamic variable. So you should set it in my.cnf file and restart MySQL.

*Make sure that your operating system can cope with the number of open file descriptors required by the table_open_cache setting.

  1.  Go to your Mysql configuration file (in linux it is /etc/my.cnf) and set the table_open_cache and open_files_limit
vi /etc/my.cnf
table_open_cache=39390
open_files_limit=78780                  

6. Restart the MySQL ( In Linux it is like)

/etc/init.d/mysqld restart
or
/etc/init.d/mysql restart

The database I have taken has 2020 tables so the value of table_open_cache is little big. For your case it may be significantly small.

Top Sales Books of MySQL at amazon.com



Posted in Database, MySQL |
« Optimize MySQL query_cache_size
Change MySQL Data Directory »

8 thoughts on “Optimize MySQL table_open_cache”

  1. Pingback: Global status – table open cache | mySQL small notes

  2. Luka says:
    2017-08-21 at 11:36 am

    This is really great article about table_open_cache.
    We sent it to all our system administrators. Amazing.

    Reply
    • techinf1 says:
      2017-09-16 at 5:48 pm

      Thanks

      Reply
  3. Rick James says:
    2017-12-11 at 11:21 pm

    Opened_tables needs to be divided by Uptime to make any sense.

    Reply
  4. Syed Javeed says:
    2019-06-26 at 12:58 pm

    Superb Article Man!!!

    Awesome…
    Thanks

    Reply
    • techinf1 says:
      2019-09-24 at 4:42 pm

      Thanks for reading my blog

      Reply
  5. Renato says:
    2019-12-04 at 6:56 pm

    134/5000
    Hi, thanks for the information
    What are the side effects because I have 118230 tables so the number is quite large.

    Reply
  6. pmsteil says:
    2021-02-12 at 6:40 pm

    Hello, this article is from 2014, does it still apply and does it apply to Maria DB 10.4 and above? thanks!

    Reply

Leave a comment Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • Ansible Exam (RHCE 8) full solution with explanation
  • Install Ansible on CentOS 8
  • Recover MySQL 5.7 root Password in Redhat/CentOS 7
  • SSH login without password in Linux
  • How to write automated FTP script in Linux

Categories

Pages

  • About
  • aStore
  • Contact Info
  • google pagerank checker
  • SEO Keyword Rank
  • SEO Page Audit
  • Tools
  • whois

Archives

  • March 2022
  • February 2022
  • August 2017
  • May 2017
  • November 2015
  • October 2015
  • August 2015
  • May 2015
  • March 2015
  • February 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • August 2014
  • July 2014
  • June 2014
  • April 2014
  • March 2014
  • February 2014
  • January 2014

Categories

  • Adsense (1)
  • Ansible (2)
  • Database (17)
    • MySQL (15)
    • Oracle (2)
  • FTP (1)
  • Google Map (2)
  • HTML5 (1)
  • Linux (8)
    • AWK (1)
  • PageSpeed (1)
  • PHP (3)
  • phpMyAdmin (1)
  • Social Networking (1)
  • Varnish (3)
  • Webpage Optimization (1)
  • Webserver (5)
    • Apache (4)
    • Nginx (1)
  • Website Migration (1)
  • WordPress (6)

WordPress

  • Log in
  • WordPress
© Techinfobest is the house of tech articles
  • Home