MySQL Open File Limit

Trial and error is not a proper methodology for mysql tuning. That number is too high.

To test your MySQL server performance, you may try:
•mysqltuner: https://github.com/rackerhacker/MySQLTuner-perl (or http://mysqltuner.pl for the script only)
•MySQL Tuning Primer: https://launchpad.net/mysql-tuning-primer
•phpMyAdmin 3.5+: Status Monitor and Advisor are excellent tools, but require proper phpMyAdmin setup

These scripts will analyze MySQL performance and make suggestions regarding your my.cnf configuration. mysqltuner is now deprecated, but it still widely used and is very simple. Tuning Primer is the most up-to-date, and provides more complete recommendations.

Post your results!
 
Your Open File Limit is going to be directly dependent on the amount of memory available on the server and the amount of power you have (CPU). Then it's a factor of swap space, threads, cycles and so forth. Changing a number on a whim is definitely not the way to fix an issue. If anything, you've now just allowed MySQL to run out of control and continue to pile up processing requests without any failsafe.

the two links that Collabora posted are excellent tools. One reads a little differently than the other and provides some helpful suggestions, but again, these are SUGGESTIONS and it more or less takes into consideration that you know what you're doing when you make a change.

While I can't provide you with a "perfect configuration" you might want to search Google for my.cnf Small, my.cnf Medium and my.cnf Large.. these are sample configurations depending on the amount of memory available and then you would adjust accordingly.

open_files_limit is generally 2-3 times the value of the table_cache limit if you're doing a lot of MyISAM database transactions. I can tell you one thing, that even with some of the Hex Core servers we're using with 16GB, we do not have any of those servers set with 300,000 - and these are BUSY eCommerce sites!
 
Got my issues sorted thanks guys, Just needed to find a good balance. Mysqltuner did give me some nice guidance on this. Great tool
 
You must log in or register to reply here.

Supporters

Back
Top