1

I was wondering if there's a way to decrease the opened files in mysql.

Details :

  • mysql 5.0.92

  • engine used : MyISAM

  • SHOW GLOBAL STATUS LIKE 'Opened_tables' : 150K

  • SHOW VARIABLES LIKE '%open%' : 

        open_files_limit    200000
    table_open_cache    40000

Solutions tried :

  • restart server : it works the opened tables counter is 0 but this isn't a good solution from my pov since you will need a restart every week because the counter will increase fast

  • FLUSH TABLES : like the mysql doc said it should force all tables in use to close but this doesn't happen

So any thoughts on this matter?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Stephan
  • 8,000
  • 3
  • 36
  • 42

1 Answers1

4

Generally, many open tables are nothing to worry about. If you come close to OS limits, you can increase this limits in the kernel settings:

How do I change the number of open files limit in Linux?

MySQL opens tables for each session independently to have better concurrency.

The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. Many operating systems permit you to increase the open-files limit, although the method varies widely from system to system.

In detail, this is explained here

http://dev.mysql.com/doc/refman/5.5/en/table-cache.html

EDIT

To verify your assumption you could decrease max_connections and table_open_cache temporarily by SET GLOBAL table_open_cache := newValue.

The value can be adjusted dynamically without a server restart.

Prior MySQL 5.1 this variable is called table_cache

What I was trying to tell, is, that decreasing this value will probably even have a negative impact on performance in terms of less possible concurrent reads (queue get's longer), instead you should try to increase the OS limit and increase max_open_files, but maybe I just don't see the point here

Community
  • 1
  • 1
Michel Feldheim
  • 17,625
  • 5
  • 60
  • 77
  • Thx for ur input Michel. Yes i'm aware that a table can be opened multiple times by concurrent connections but my main problem here is that i think the I/O on the server is severely crippled by the fact that mysql has a lot of opened files. This is a assumption of mine which may not be valid. – Stephan Jan 25 '13 at 10:14
  • to verify you can decrease `max_connections` and `table_open_cache` temporarily by `SET GLOBAL table_open_cache := newValue`. Prior MySQL 5.1 this variable is called [table_cache](http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_table_cache) The value can be adjusted dynamically without a server restart – Michel Feldheim Jan 25 '13 at 10:34
  • Yes ur right, i will try that. Also regarding ur suggestion to increase the limit we already have open_files_limit=200k and it hasn't been reached yet. – Stephan Jan 25 '13 at 11:10