Results 1 to 26 of 26
  1. #1
    Join Date
    Aug 2005
    Posts
    529

    mysql issues - too many open files

    guys i've been busting my balls over this issue for over a week now.

    Basically mysql is behaving very very intermittently. Crashes were every 4 hours, I've brought them down to once every 8 or so hours but mysql keeps dying.

    the error log will show the same routine each time.

    on mysql start:
    091101 21:58:03 [Warning] option 'open_files_limit': unsigned value 120000 adjusted to 65535
    091101 21:58:03 [Warning] Could not increase number of max_open_files to more than 65535 (request: 200110)
    091101 21:58:03 [Note] /usr/sbin/mysqld: ready for connections.
    Then we'll see errors due to crashed databases:
    091102 0:33:07 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './<nameofdatabase.frm>'
    following this a heap of:
    091102 0:36:35 [ERROR] /usr/sbin/mysqld: Can't open file: '>another database here.frm>
    091102 0:36:36 [ERROR] /usr/sbin/mysqld: Sort aborted
    091102 0:36:52 [ERROR] /usr/sbin/mysqld: Sort aborted
    091102 0:43:00 [ERROR] Error in accept: Too many open files
    can you guys give me some advice as to how to fix this?

  2. #2
    Join Date
    Jun 2009
    Location
    Kochi,India
    Posts
    177
    Try to run this as your cron job
    mysqlcheck --auto-repair mysqlmysqlcheck --auto-repair mysql

    Also find which query is using this much of resource by

    mysqladmin proc
    Ezeelogin -
    The ultimate multiple server administration software.
    *Parallel shell *rm -rf protection *SSH logging*automated password changes*encrypted storage*
    AdMod.com -Delivering innovative web hosting solutions

  3. #3
    Join Date
    Aug 2005
    Posts
    529
    Quote Originally Posted by Rekhatitus View Post
    Try to run this as your cron job
    mysqlcheck --auto-repair mysqlmysqlcheck --auto-repair mysql

    Also find which query is using this much of resource by

    mysqladmin proc
    have run the mysqlcheck and myisamcheck already. some of these tables just do seem corrupted, it says they cannot be repaired.

    in terms of mysqladmin proc practically no connections. definitely the handful now is no comparison to the thousands required to cause issue?

  4. #4
    Join Date
    Oct 2009
    Posts
    58
    You can increase the open files limit in mysql configuration file my.cnf Add the following to your my.cnf file
    ----
    open-files-limit=XXXXX, where X indicates the value needed.

    Note: they are hyphens instead of underscores
    Windows/Linux Expert; Plesk/Cpanel/Ensim Guru.
    ..and an expert spam tracker.

  5. #5
    Join Date
    Aug 2005
    Posts
    529
    Quote Originally Posted by samurai7 View Post
    You can increase the open files limit in mysql configuration file my.cnf Add the following to your my.cnf file
    ----
    open-files-limit=XXXXX, where X indicates the value needed.

    Note: they are hyphens instead of underscores

    tried that. increase attempt to 120,000 yields:

    091102 13:03:11 [Warning] option 'open_files_limit': unsigned value 120000 adjusted to 65535
    091102 13:03:11 [Warning] Could not increase number of max_open_files to more than 65535 (request: 200110)

  6. #6
    Join Date
    Mar 2009
    Posts
    2,218
    Quote Originally Posted by chamelion View Post

    can you guys give me some advice as to how to fix this?
    Well, there's probably a bug in a program that is using MySQL; so you need to find it and fix it.

    When did the problem start happening, and were there any changes just before that?

  7. #7
    Join Date
    Aug 2005
    Posts
    529
    Quote Originally Posted by tim2718281 View Post
    Well, there's probably a bug in a program that is using MySQL; so you need to find it and fix it.

    When did the problem start happening, and were there any changes just before that?

    i had just imported a number of accounts across from an old server. old server mysql has no issues, and this server had no issues prior to the import.

    i thought it was to do with corrupt mysql tables but would that cause mysql to crash??

    i'm keeping an eye out on the .err file to see what tables cause issues and will just drop them and reimport them or something...

  8. #8
    Join Date
    Mar 2009
    Posts
    2,218
    Quote Originally Posted by chamelion View Post
    i had just imported a number of accounts across from an old server. old server mysql has no issues, and this server had no issues prior to the import.
    So that's a clue.

    Are these SQL accounts? Can you check that all the imported accounts are working properly? Have you set the correct permissions for the accounts?

  9. #9
    Join Date
    Aug 2005
    Posts
    529
    the accounts were nicely packaged cpanel accounts migrated directly from the old server... imported accounts work fine, just a few of these tables have gone haywire. permissions etc were set by cpanel on account restore, but from what i can see they look ok too.

  10. #10
    Join Date
    Aug 2005
    Posts
    529
    ah this is so frustrating. mysql is going out with "too many open files" errors now, even with no complaints on bad tables.

    I really need to increase this open file limit - I think it's an OS thing, not a mysql thing. I hear whispers of ulimit and stuff like that, any idea how to allow the OS to have more open files?

  11. #11
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    How many databases do you have on this server?

    How many tables does each database have?
    Do you see anything that stands out as excessive there?

    Cuz, to me this seems more like a stupid user/malicious user problem... you could try and make the system able to open more inodes... but you'll probably just make the whole system become sluggish to unresponsive much faster.
    Daved @ Lightwave Networking, LLC.
    AS1426 https:/www.lightwave.net
    Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
    Xen PV VPS Hosting

  12. #12
    Join Date
    Aug 2005
    Posts
    529
    Quote Originally Posted by Lightwave View Post
    How many databases do you have on this server?

    How many tables does each database have?
    Do you see anything that stands out as excessive there?

    Cuz, to me this seems more like a stupid user/malicious user problem... you could try and make the system able to open more inodes... but you'll probably just make the whole system become sluggish to unresponsive much faster.

    there are quite a few databases / tables - but in terms of USAGE it's not high. when I run the proclist for mysql majority of the times i check it'll only be root doing the processlist command, and during peak hours i may see 1-2 other users. that is not heavy. also in terms of the system itself, load is low (<1/2 of capacity), no high io wait, no high cpu, the system is quite relaxed. even with the mysql outages, if i have a top window running there's absolutely nothing out of the ordinary load wise.

    according to munin, inode usage is <20% for each one of my mounts, i'm not hitting the limit there. if i look at mysql throughput / number of queries / slow queries / threads in the munin logs, once again no spikes in any of those.

  13. #13
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    That's nice, but you didn't answer any of the questions.

    fwiw the hard max is 65k, so you're not going to get above that unless you want to try and change MySQL source code and recompile.

    MySQL is saying it wants to open 200k simultaneous inodes...
    Someone either has a really bad designed database, or you've screwed up one of the other variables like max_connections which is used in the system guessing how many file descriptors it should try and reserve.
    Daved @ Lightwave Networking, LLC.
    AS1426 https:/www.lightwave.net
    Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
    Xen PV VPS Hosting

  14. #14
    Join Date
    Aug 2005
    Posts
    529
    1,160 databases total. How do I check how many tables there are?

    my my.cnf:

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-innodb
    max_connections = 100
    key_buffer = 128M
    myisam_sort_buffer_size = 128M
    long_query_time = 10
    join_buffer_size = 4M
    read_buffer_size = 2M
    sort_buffer_size = 4M
    table_cache = 100000
    thread_cache_size = 128
    wait_timeout = 10
    connect_timeout = 5
    max_allowed_packet = 10M
    max_connect_errors = 5
    query_cache_limit = 1M
    query_cache_size = 128M
    tmp_table_size = 256M
    max_heap_table_size = 384M
    open-files-limit=120000
    log-slow-queries = /var/log/mysql-slow.log

    [mysqld_safe]
    err-log=/var/log/mysqld.log
    open-files-limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 32M
    read_buffer = 16M
    write_buffer = 16M

    host system has 6gb of RAM.

  15. #15
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    The easiest way for me is to just look at the server with phpMyAdmin...

    You could try editing /etc/init.d/mysqld
    and adding a ulimit -n 4096
    or something in there... but I still worry that putting an excessive number in there will cause more problems than it helps.
    Daved @ Lightwave Networking, LLC.
    AS1426 https:/www.lightwave.net
    Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
    Xen PV VPS Hosting

  16. #16
    Join Date
    Aug 2005
    Posts
    529
    Quote Originally Posted by Lightwave View Post
    The easiest way for me is to just look at the server with phpMyAdmin...

    You could try editing /etc/init.d/mysqld
    and adding a ulimit -n 4096
    or something in there... but I still worry that putting an excessive number in there will cause more problems than it helps.

    yep i did look through phpmyadmin. i clicked on "databases" and i see 1,160 databases there. but in each of those there will be tables, and i have no idea how many tables there are in all - whether its below 65k.

    added the ulimit parameter, lets see if that helps. is there no OS limit though as well that I could modify?

  17. #17
    Join Date
    Mar 2009
    Posts
    2,218
    You could list the open files with 'lsof', and see if that casts any light on what's happening.

    Actually, it's worth counting them, with 'lsof | wc' , just to make sure you are not hitting the Linux limit. It's pretty unlikely; you can check the Linux limit with

    'cat /proc/sys/fs/file-max' to find the current limit

    By the way, I think the 'open file limit' is actually a limit on file descriptors, so it includes pipes and sockets as well as disk files.

    You can list open sockets with 'netstat'

    You may find there are many sockets in 'CLOSE_WAIT'.

  18. #18
    Join Date
    Aug 2005
    Posts
    529
    lsof | wc: 64895 589488 8513619
    cat /proc/sys/fs/file-max: 300,000

    netstat: not many close_waits but heaps of time_waits and fin_waits. would that cause an issue?

  19. #19
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    the "window" on the left side might say something like:
    * cacti (48)
    * information_schema (28)
    * mysql (23)
    * test (0)

    the number in parens is how many tables the database has...
    but it's possible your system doesnt have it displayed the same...
    Daved @ Lightwave Networking, LLC.
    AS1426 https:/www.lightwave.net
    Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
    Xen PV VPS Hosting

  20. #20
    Join Date
    Aug 2005
    Posts
    529
    Quote Originally Posted by Lightwave View Post
    the "window" on the left side might say something like:
    * cacti (48)
    * information_schema (28)
    * mysql (23)
    * test (0)

    the number in parens is how many tables the database has...
    but it's possible your system doesnt have it displayed the same...
    it does but i'd have to go through 12 pages of databases and calculate for the 1,160 databases Taking a quick browse through I realize I need an average over 50 tables per database for each one of those 1160 to hit the 65k limit, and i'm quite certain i'm nowhere close to that.

  21. #21
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    I wouldn't expect the average to seem unusual... but perhaps look for one which seems excessively high.
    Daved @ Lightwave Networking, LLC.
    AS1426 https:/www.lightwave.net
    Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
    Xen PV VPS Hosting

  22. #22
    Join Date
    Aug 2005
    Posts
    529
    still goign through them.

    p.s. can anyone make sense of this:
    http://mail-index.netbsd.org/netbsd-...9/02/0000.html

    seems like an identical issue where he resolved it.

  23. #23
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    hard to say there... a little hard to compare when that's talking about a netbsd kernel from 2002
    Daved @ Lightwave Networking, LLC.
    AS1426 https:/www.lightwave.net
    Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
    Xen PV VPS Hosting

  24. #24
    Join Date
    Aug 2005
    Posts
    529
    lol fair enough.

    right now i'm tailing my mysql .err log. i noticed before the most recent crash (couple of minutes ago) it said incorrect information in file. i dug around for this file and seems that phpmyadmin shows it as "in use" and the mysql folder has no MYI for the file. I've dropped that table. lets see if there are any others - i found 3 such occurrences so far.

  25. #25
    Join Date
    Mar 2009
    Posts
    2,218
    Quote Originally Posted by chamelion View Post
    lsof | wc: 64895 589488 8513619
    cat /proc/sys/fs/file-max: 300,000
    OK, that shows you're not hitting the Linux limit, but you are near the MySQL limit.
    Quote Originally Posted by chamelion View Post
    netstat: not many close_waits but heaps of time_waits and fin_waits. would that cause an issue?
    It depends how many.

    How many open connections is netstat saying there are altogether?

  26. #26
    Join Date
    Mar 2009
    Posts
    2,218
    Quote Originally Posted by chamelion View Post
    lol fair enough.

    right now i'm tailing my mysql .err log. i noticed before the most recent crash (couple of minutes ago) it said incorrect information in file. i dug around for this file and seems that phpmyadmin shows it as "in use" and the mysql folder has no MYI for the file. I've dropped that table. lets see if there are any others - i found 3 such occurrences so far.
    .myi are index files; you can fix missing index files by

    check table xxx
    repair table xxx

    (Silly question: are you running out of disk space, preventing MySQL from updating index files? )

Similar Threads

  1. mysql open files limit
    By jon-f in forum Hosting Security and Technology
    Replies: 7
    Last Post: 01-04-2011, 03:22 AM
  2. Replies: 5
    Last Post: 03-27-2008, 03:05 PM
  3. Too many open files
    By rainy_day in forum Hosting Security and Technology
    Replies: 8
    Last Post: 10-07-2005, 06:28 AM
  4. How to open files from MSDOS 3 !!
    By asmar in forum Programming Discussion
    Replies: 9
    Last Post: 03-04-2005, 04:43 AM
  5. How to open .dmg and s.it files
    By lokodezine in forum Web Hosting Lounge
    Replies: 7
    Last Post: 10-29-2003, 10:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •