18 Agustus, 2015

error pada mysql, tutorialnya bagus nih

http://www.ericshalov.com/2014/12/02/why-am-i-seeing-error-usrsbinmysqld-cant-open-file-databasenametablename-frm-errno-24/
Why am I seeing “[ERROR] /usr/sbin/mysqld: Can’t open file: ‘./databasename/tablename.frm’ (errno: 24)”?
Posted by Eric on 2 December 2014, 5:28 pm
Problem:
MySQL stops responding for a few minutes and during that time, the logs show messages like “Error in accept: Too many open files” and “Can’t open file”:

Dec 2 07:14:34 www mysqld: 141202 7:14:34 [ERROR] Error in accept: Too many open files
Dec 2 07:15:06 www mysqld: 141202 7:15:06 [ERROR] /usr/sbin/mysqld: Can't open file: './databasename/tablename.frm' (errno: 24)
Dec 2 07:15:13 www mysqld: 141202 7:15:13 [ERROR] /usr/sbin/mysqld: Can't open file: './databasename/tablename.frm' (errno: 24)
Upon starting MySQL, you might also see messages like:
ERROR 23 (HY000) at line 1: Out of resources when opening file './totallyfreeimages/word_occurs.MYD' (Errcode: 24)
(Detected after upgrade to Debian package “mysql-server-5.5″, version “5.5.40-0+wheezy1″.)
Explanation:
The MySQL server is running out of file handles, as it’s hitting the per-process open file handle limit. If your server has a lot of databases and tables (or
if you use the server option “innodb_file_per_table”, or if you need to increase your table cache size) then this can appear. Every table the server is
accessing will use up a system file-handle.
To see the current server file-handles limit:
mysql> SHOW VARIABLES LIKE 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
1 row in set (0.00 sec)
mysql>
You can see how many open files the server is currently using:
mysql> SHOW GLOBAL STATUS LIKE 'Open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 296 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
About “upstart” and /etc/init/mysql.conf
Debian and Ubuntu use a new service management package championed by Ubuntu called “Upstart”, configured in /etc/init/ (not to be confused with /etc/init.d/). Files in /etc/init are configuration files telling Upstart how and when to start, stop, reload the configuration, or query the status of a service. In newer versions of Debian and Ubuntu, there’s a transition from SysVinit to Upstart, which is why many services come with SysVinit scripts even though Upstart configuration files are preferred. In fact, the SysVinit scripts are processed by a compatibility layer in Upstart. Solution:
Add this to the [mysqld] section of your /etc/mysql/my.cnf:
open_files_limit=8192
..and re-start MySQL. If the “open_files_limit” hasn’t increased, you may need to do the following:
In the past, these per-process file-handle limits were set in /etc/security/limits.conf, and you would add the following to your /etc/security/limits.conf:

mysql hard nofile 8192
mysql soft nofile 1200

On newer Debian and Ubuntu systems, MySQL is started by “upstart”.

Modify the upstart config file for MySQL and add the following lines before the pre-start block:

# We need to raise the open-file limits here
limit nofile 32000 32000
limit nproc 32000 32000
..or create a new /etc/init/mysql.conf, if you don’t have one:

# We need to raise the open-file limits here
limit nofile 32000 32000
limit nproc 32000 32000
References:
http://serverfault.com/questions/440878/changing-open-files-limit-in-mysql-5-5
http://askubuntu.com/questions/5039/what-is-the-difference-between-etc-init-and-etc-init-d
http://dev.mysql.com/doc/refman/5.1/en/table-cache.html

Tidak ada komentar: