How to force higher values of open_files_limit or table_open_cache?

Question

I'm having a frustrating problem where setting my.cnf to values higher than I currently have them does not 'take'.

This is MySQL 5.6.14 on MacOS X 10.8.3

I'm trying to address dropped connections and other seemingly capacity related issues.

For example: My database's current stats are:

mysql> SHOW GlOBAL STATUS LIKE "%open%";

+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Com_ha_open                | 0      |
| Com_show_open_tables       | 0      |
| Innodb_num_open_files      | 1581   |
| Open_files                 | 5557   |
| Open_streams               | 0      |
| Open_table_definitions     | 4199   |
| Open_tables                | 5000   |
| Opened_files               | 54805  |
| Opened_table_definitions   | 4247   |
| Opened_tables              | 8515   |
| Slave_open_temp_tables     | 0      |
| Table_open_cache_hits      | 104991 |
| Table_open_cache_misses    | 8479   |
| Table_open_cache_overflows | 3459   |
+----------------------------+--------+

My current my.cnf values are:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_allowed_packet=128M
wait_timeout=57600
table_open_cache = 5000
table-definition-cache = 10000
open_files_limit = 10000
max-connections = 15
thread-cache-size = 16K
max-heap-table-size = 6024M
tmp-table-size = 6024M
query-cache-size = 256K
query-cache-limit = 16M
key_buffer = 512M
long-query-time = 5
join-buffer-size = 1M
innodb_buffer_pool_size = 3046M

And that produces:

mysql> SHOW VARIABLES LIKE '%open%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| have_openssl               | DISABLED |
| innodb_open_files          | 5000     |
| open_files_limit           | 10025    |
| table_open_cache           | 5000     |
| table_open_cache_instances | 1        |
+----------------------------+----------+
5 rows in set (0.00 sec)

Which is at it should be.

I would like to raise my open limits to

table_open_cache = 10000
table-definition-cache = 20000
open_files_limit = 60000

But when I do the my.cnf seems to invalidate and mysql reports:

mysql> SHOW VARIABLES LIKE '%open%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| have_openssl               | DISABLED |
| innodb_open_files          | 400      |
| open_files_limit           | 256      |
| table_open_cache           | 400      |
| table_open_cache_instances | 1        |
+----------------------------+----------+
5 rows in set (0.00 sec)

Which obviously kills my server.

Answer 1

From the looks of your numbers, you must have hit some bug or pre-GA behavior. Why?

According to the MySQL Documentation, the default for table_open_cache is -1 for GA releases of MySQL 5.6. This tells mysqld pick a fair starting value give current OS conditions. What makes that value weird is the fact that 400 is the default for MySQL 5.6.7. The first GA release is 5.6.8. It may be a coincidence, but my guess would be that you set table_open_cache a little too high. Evidently, 5000 is a tolerable value for your DB Server.

SUGGESTION

Try experimenting with table_open_cache_instances setting it to 2 or 4. Then restart mysql and see.

Answer 2

You may have maxed out the total number of open file descriptors per process. I found this similar discussion on FreeBSD which talks about 160,000 descriptors but I suppose the limits on a 'consumer' OS like MacOS are somewhat more conservative. Check with ulimit and sysctl what your limits are.