Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012

    Question Unanswered: MySQL Intermittently... well... freaks out.

    So, I'm a web developer, not a sys or database admin. And in the last couple days, I've been thrown into both of those roles due to the departure of the guy that handled both at my company. I know this has been a recurring issue with our MySQL server, but I don't want to let it continue.

    Daily (sometimes not every day, down to maybe once or twice a week), the server that is running our MySQL instance goes haywire. CPU usage spikes, all connections to it are held and our pipes become clogged with molasses. It's a very frustrating issue that tends to require a restart of the server (so incredibly far from ideal).

    Here is an output from
     >>  MySQLTuner 1.2.0 - Major Hayden <>
     >>  Bug reports, feature requests, and downloads at
     >>  Run with '--help' for additional options and output filtering
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.49-1ubuntu8.1-log
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 2G (Tables: 91)
    [--] Data in InnoDB tables: 181M (Tables: 40)
    [!!] Total fragmented tables: 44
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 23h 7m 1s (924K q [11.107 qps], 21K conn, TX: 742M, RX: 372M)
    [--] Reads / Writes: 75% / 25%
    [--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 463.8M (5% of installed RAM)
    [OK] Slow queries: 0% (224/924K)
    [OK] Highest usage of available connections: 65% (99/151)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/618.6M
    [OK] Key buffer hit rate: 99.3% (105M cached / 735K reads)
    [OK] Query cache efficiency: 35.4% (254K cached / 717K selects)
    [!!] Query cache prunes per day: 133133
    [OK] Sorts requiring temporary tables: 0% (54 temp sorts / 142K sorts)
    [!!] Joins performed without indexes: 5636
    [OK] Temporary tables created on disk: 23% (48K on disk / 208K total)
    [OK] Thread cache hit rate: 99% (115 created / 21K connections)
    [!!] Table cache hit rate: 1% (615 open / 44K opened)
    [OK] Open file limit used: 30% (309/1K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [!!] InnoDB data size / buffer pool: 181.1M/8.0M
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 16M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 181M)
    And here are two images of our usage stats: (Load) (CPU Idle) (CPU Usage)

    Throughout these incidents, physical memory used remains pretty constant:

    Our writes don't go crazy high either:

    If there are any other stats you'd like to see, let me know - we have Server Density installed and monitoring server stats.

    We're using memcache, trying to cache our more expensive, less frequently updated queries.

    We know that there are slow queries, and those that are run without indexes on joins. We have yet to determine exactly which queries fall into the "unindexed" category, and we're working on fixing some of the slow queries.

    We're using MySQL 5.1.49.

    I know the description here is blindingly vague, but if anyone has any ideas on first steps for a non-database admin, they'd be greatly appreciated.

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    It is very difficult to say exactly what could be causing the issue. Have a look at the CPU breakdown between %idle, %user and %sys. If it is performing a lot of system cpu system calls then this suggests that you could be running out of memory. Also use a utility called "top" which will show you the top processes running on your server.

    From what you have said above, it could be that the system is running out of usable memory. This could be due to processes not releasing the memory. Also run the top process after a reboot to have something to compare against (especially the system load statistics at the top of the page). When you experience issues the next time you will have something to compare against.

    Without having more details it is very difficult to narrow down. Try the above first and see where that leads?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Tags for this Thread

Posting Permissions

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