Insane MySQL Problem

Posted on November 23rd, 2008 by admin
  • All of a sudden, about two weeks ago, I began suffering an almost random problem - MySQL will randomly start complaining that it has too many files open. The problem lasts anywhere from ten minutes to an hour to several hours - my entire site fails with errors like the following:
    Invalid SQL: ***SQL COMMAND***
    mysql error: Can't find file'***TABLE***.frm' (errno: 24)

    mysql error number: 1017

    The failure is because MySQL has too many files open, and thus cannot open any more tables. Looking at MySQL's status reveals the following:
    Open_tables 20
    Open_files 4294966801
    Open_streams 0

    WTF is that all about? The restriction on Open_files is dictated by max_connections and table_cache, so I've turned these really low:
    set-variable = max_connections=450
    set-variable = table_cache=20
    So how the devil is MySQL managing to get 4294966801 open files when the above should mean a theoretical maximum of around 9500? And why does it occur at random times? I've been tearing my hair out try to sort this out as it's causing major outages on my site - I thought it might have been the upgrade to MySQL 4.0.15, so I downgraded to 4.0.14a and even 3.23.58 but they still suffered the same problem?!?!? Why - I haven't changed anything.... :mad: :mad: :mad: :mad: :mad: :mad: :mad:

    Here's my config:
    [mysqld]
    basedir=***PATH TO MYSQL***
    datadir=***PATH TO DATA***
    tmpdir=***PATH TO TEMP***
    port=***PORT***
    server-id=1
    skip-locking
    skip-innodb
    skip-bdb
    set-variable = max_connections=450
    set-variable = query_cache_size=64M
    set-variable = query_cache_limit=2M
    set-variable = query_cache_type=1
    set-variable = key_buffer=32M
    set-variable = join_buffer=4M
    set-variable = read_buffer_size=4M
    set-variable = record_buffer=2M
    set-variable = sort_buffer_size=8M
    set-variable = table_cache=20
    set-variable = max_allowed_packet=1M
    set-variable = thread_cache=8
    set-variable = thread_cache_size=128
    set-variable = thread_stack=256
    set-variable = thread_concurrency=8
    set-variable = myisam_sort_buffer_size=64M
    set-variable = wait_timeout=1800
    set-variable = max_connect_errors=10


  • And I'm not using persistent connections either...


  • What is MaxClients in Apache? Can you send an output of top?

    not knowing how much ram and what else is going on in your system its hard to diagnose but your my.cnf file is a little stramge and these variables are probably too high:

    set-variable = query_cache_size=64M
    set-variable = join_buffer=4M
    set-variable = sort_buffer_size=8M
    set-variable = thread_cache_size=128
    set-variable = thread_concurrency=8


  • yeah what's the maxclients in httpd.conf set to ?

    table_cache value in my.cnf sets a limit but mysql can dynamically /temporarily go over this limit if needed.... setting it too low in the first place i.e. 20 can cause server load issues in itself

    http://www.mysql.com/doc/en/Table_cache.html

    When the table cache fills up, the server uses the following procedure to locate a cache entry to use:

    Tables that are not currently in use are released, in least-recently-used order.
    If the cache is full and no tables can be released, but a new table needs to be opened, the cache is temporarily extended as necessary.
    If the cache is in a temporarily extended state and a table goes from in-use to not-in-use state, the table is closed and released from the cache.


    can you post your mysqlinfo.php url ?

    tried upgrading to mysql 4.0.15a which fixes a few things from 4.0.15


  • table_cache was originally high, I reduced it to 20 to try and fix the problem - the mysql doco said that setting should be the most tables a single query might access at one time. One of my own systems queries around 15, but in the future could be up to 20, so I set it at 20 for now. Made no difference though.

    In any case I'm baffled how 4.2 BILLION files could possibly be open...?!?!?

    I think I have httpd.conf set at 200 max clients atm...?


    • Tags: badahutong.com edit //