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 often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"?

Thanks!

share|improve this question
    
Is this all InnoDB ??? –  RolandoMySQLDBA Apr 8 '13 at 20:00
    
primarily 370+ innodb tables, 4 myisam –  JShean Apr 8 '13 at 20:07
    
Do you have innodb_file_per_table enabled ??? –  RolandoMySQLDBA Apr 8 '13 at 20:08
    
yes we have per table enabled. thanks! –  JShean Apr 8 '13 at 20:12
    
To be honest, I increased the value fairly fast and aggressively. We would get stalls when the Open_tables value hit table_open_cache and the server would stall and flush out a bunch of tables. I just doubled the value until we stopped getting the stalls. We're also 100% innodb, so this variable still matters even with innodb_open_files being tweaked –  kormoc Jun 7 '13 at 22:37
add comment

1 Answer 1

Since you are using InnoDB with innodb_file_per_table enabled, you need to raise innodb_open_files because this dictates the number of open file handles on .ibd files.

In MySQL 5.6, this setting is treated a little different.

This variable is relevant only if you use multiple InnoDB tablespaces. It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10. As of MySQL 5.6.6, the default value is 300 if innodb_file_per_table is not enabled, and the higher of 300 and table_open_cache otherwise. Before 5.6.6, the default value is 300.

The file descriptors used for .ibd files are for InnoDB tables only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.

Here is an additional reference from MySQL Performance Blog about setting innodb_open_files.

Raising innodb_open_files to a big number should not be that all that troublesome, but please be sensible about the number you choose.

share|improve this answer
    
RDS seems to have a ceiling here of 300 for innodb_open_files. how annoying-- oh i guess it takes the larger value (read your answer more closely) thanks! for 5.5? –  JShean Apr 8 '13 at 20:17
    
The excerpt above is for MySQL 5.6, not 5.5. The default would be 300 for 5.5 : dev.mysql.com/doc/refman/5.5/en/… . –  RolandoMySQLDBA Apr 8 '13 at 20:23
    
Opened_files status is increasing quickly, which would indicate that I need to increase one of these values. The open_tables status is only 595... I wonder if you may have had this experience with RDS? –  JShean Apr 8 '13 at 20:31
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.