open file limit - Table cache

  • Filter
  • Time
  • Show
Clear All
new posts

  • open file limit - Table cache

    I don't have dedicated mysql server - its LAMPP with php app running on the same machine.

    Below is mysqltuner result.

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.27-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 19G (Tables: 491)
    [--] Data in ARCHIVE tables: 28B (Tables: 1)
    [!!] Total fragmented tables: 18

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 37d 1h 9m 12s (1B q [481.357 qps], 1M conn, TX: 2B, RX: 2B)
    [--] Reads / Writes: 36% / 64%
    [--] Total buffers: 1.1G global + 13.2M per thread (100 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.4G (60% of installed RAM)
    [OK] Slow queries: 0% (25K/1B)
    [OK] Highest usage of available connections: 79% (79/100)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/2.2G
    [OK] Key buffer hit rate: 99.7% (26B cached / 67M reads)
    [OK] Query cache efficiency: 57.9% (5M cached / 9M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (35 temp sorts / 227M sorts)
    [!!] Joins performed without indexes: 14973
    [OK] Temporary tables created on disk: 11% (134K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (462 created / 1M connections)
    [!!] Table cache hit rate: 0% (500 open / 94K opened)
    [OK] Open file limit used: 82% (849/1K)
    [OK] Table locks acquired immediately: 99% (123M immediate / 123M locks)

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 500)


    I see a biggest problem here is Table cache hit rate: 0% !
    I have open files limit of 1024 on ubuntu.
    I couldn't set table_cache >650 as trying above that creates problems as others are there to use the system !!

    How can i consider increasing open files limit?
    What is required to check before doing that ?

  • #2
    this is known bug.

    https://bugs.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+bu g/306541
    remove apparmor package OR upgrade to Jaunty, OR install new ourdelta builds