Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
Cheers, it's a quad-core Intel Xeon Xserve, so I've changed it to 4 for the sake of consistency. It will take some time to see if it has helped the issue. –  chrisale Oct 29 '13 at 21:47
add comment

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.

share|improve this answer
    
for the mysql user ulimit -S -n reports soft limit: 256 ulimit -H -n reports hard limit: unlimited Considering it's already had more than 256 file descriptors open, this appears not to be an issue, or mysqld is allocating sufficient initial resources and then failing later? (for others, to act as mysql user, "su root" first then "su mysql") –  chrisale Oct 29 '13 at 22:08
    
An ''unlimited'' value for ulimit -H -n seems bogus to me... MySQL can (and probably does) raise its soft limit programmatically to the hard limit. So we now need to figure out what the hard limit really is. There can even be a limit on the number of open files per filesystem, but I doubt it's as a low as 5000. Maybe an even better question is why you need more than 5000 open files/connections... that's an awful lot of tables. –  JvO Oct 29 '13 at 22:46
    
multi-site wordpress install with 120 sites = ~3000 tables –  chrisale Oct 29 '13 at 22:58
    
I've fiddled a bit with the table_open_cache and found I can go to 6000 and mysql seems to adjust innodb_open_files and open_files_limit accordingly upward. When I reach 7000 then it seems to snap and takes the open_files_limit at 10000 as the limiter and the other two snap to 4987. But if I change the open_files_limit to say 20000 it breaks and goes down to the tiny values. –  chrisale Oct 29 '13 at 23:03
    
If I change the open_files_limit to any value up to 12000 it resets the table_open_cache accordingly up to 5987. Past 12000, it breaks and goes down to tiny values. I understand from other documentation that table_open_cache, table-definition-cache, and open_files_limit work in a sort of team. So this behaviour makes sense at that level. What doesn't make sense is that it just gives up and goes back to tiny defaults after an magic limit is obviously crossed. –  chrisale Oct 29 '13 at 23:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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