1

Moved a few sites to a new server running CentOS 7, Plesk Onyx, 32gb, 2x480gb SSD, Intel(R) Xeon(R) CPU D-1521 @ 2.40GHz.

[root@server ~]# mysql -V
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Trying to tune MySQL, but am stuck on the table_open_cache not going above 431.

| Open_tables                              | 431         |

My /etc/my.cnf.d/server.cnf as follows:

skip-name-resolve               = 1

innodb_file_per_table           = 1
innodb_buffer_pool_size         = 16G
innodb_buffer_pool_instances    = 16
innodb_log_file_size            = 2G

tmp_table_size                  = 256M
max_heap_table_size             = 256M

thread_cache_size               = 4
table_open_cache                = 2000

query_cache_type                = 0
query_cache_size                = 12M

Already checked the OS limit:

[root@server /]# ulimit -n
4096

Not sure where to look next. Any ideas or help would be awesome.

Improve this question

1 Answer 1

Reset to default
1

Based on Open_tables being 431, I assume that you have only 431 tables in all the databases? Or at least only that many are in use?

What is the value of table_open_cache?

What is the value of Table_open_cache_misses / Uptime? If that is less than, say, 1/sec, then the table_open_cache is "big enough".

Improve this answer
10
  • There are 660 tables on the server. As for how many are in use I would guess 90% of those. The value of table_open_cache is 2000. I am not seeing a 'table_open_cache_misses" value in SHOW GLOBAL STATUS; Would it be somewhere else?
    – Luke Pittman
    Feb 23, 2017 at 2:18
  • Oops; that did not show up until 5.6.6. Check out Opened_tables / Uptime. Under 2/second is reasonable. Since the structure is a cache, it is not necessary to have room for all tables.
    – Rick James
    Feb 23, 2017 at 2:41
  • opened_tables (~15k) / uptime (~90k) is roughly 0.17. I'll continue to watch this, however it appears opened_tables is no longer climbing. While this makes me feel better about the setting being okay, it doesn't help with figuring out why the setting isn't going above 431, which I'd like to figure out.
    – Luke Pittman
    Feb 23, 2017 at 18:25
  • In the first hour after starting mysql, Opened_tables/Uptime will be rather high -- because you are running queries, which need tables, which go into the table_cache. It is wise to ignore SHOW GLOBAL STATUS until the system has been running a day.
    – Rick James
    Feb 23, 2017 at 22:19
  • I have failed to explain '431', but I hope I have convinced you that it is not a problem.
    – Rick James
    Feb 23, 2017 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.