0

We noticed that adding open_files_limit in /etc/my.cnf is making server slow.

Reference : What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?

  • Much more detail should be provided on questions like this. What did you set the value to? What specification of machine/VM are you using? Is it slow to start, slow to run a particular short of query, slow to run any statement, what? Any comparative stats like "this statement took X.xx seconds before but Y.yy after" and so on. – David Spillett Mar 10 '14 at 7:35
  • Please see reference or should I copy everything to this question? I am new to this site. – Jai Mar 10 '14 at 15:23
  • You could have added this back to the original question. However, since the open_files_limit produced a new problem, I decided to add my answer here to address it. – RolandoMySQLDBA Mar 10 '14 at 15:28
  • Percona has a free eBook related to InnoDB performance, actually titled "InnoDB performance optimization." Here's the url to get that - it should help. form.percona.com/innodb_performance_ebook.html – Tom Diederich Mar 10 '14 at 15:35
0

Looking back at my answer to your original post, I recommended a value for open_files_limit. Apparently, mysqld saw that 3145728 was the default value deemed safe enough to use. Hopefully, you changed ULIMIT in the OS to match. Otherwise, you will get lots of table cache issues.

NOTE: mysqld will try to choose a fair default value for open_files_limit based on the amount of memory available at the time mysqld starts. There is a warning from the MySQL Documentation:

mysqld may attempt to allocate more than the requested number of descriptors (if they are available), using the values of max_connections and table_open_cache to estimate whether more descriptors will be needed.

Even if you raise innodb_open_files, there is the possibility of caching file handles against the open .ibd files. See Percona's take on it : http://www.mysqlperformanceblog.com/2009/11/18/how-innodb_open_files-affects-performance/. That may also attribute somewhat to the slowness you are experiencing.

In your accepted answer, you stated you set open_files_limit to 1048570, a value mysqld felt was too low. Removing open_files_limit=1048570 from my.ini simply allowed open_files_limit to be defaulted to 3145728. You could increase it from that value, not lower it.

  • Actually 1048570 should be more than required but MySQL is taking OS default which was raised to 3145728. My question was why would it make MySQL start to take hours instead of minutes? – Jai Mar 11 '14 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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