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.

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".

  • 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 '17 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 '17 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 '17 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 '17 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 '17 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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