The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Going round in circles - my.cnf and httpd

Discussion in 'Workarounds and Optimization' started by jonjon80, May 21, 2013.

  1. jonjon80

    jonjon80 Registered

    May 21, 2013
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Over 12 months ago mysql kept getting "to many connections" error and falling over so re-tune my.cnf and httpd.conf and for the last 12 months it been great..

    Until 2 weeks ago :(

    I've played around some both my.cnf and httpd trying all sorts of settings but still have the mysqld crashing about once every 2 days and of course in the middle of the night..

    I anyone can help point out the massive error I keep making I'd be so grateful!!

    Here is my my.cnf

    max_connections = 150 
    wait_timeout = 20 
    connect_timeout = 10 
    query_cache_size = 128M
    query_cache_limit = 4MB
    tmp_table_size = 50M
    max_heap_table_size = 30M
    table_cache = 84
    innodb_buffer_pool_size = 54M 
    thread_cache_size = 4
    max_allowed_packet    = 16M
    long_query_time = 5 


     >>  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.0.95-log
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 5G (Tables: 451)
    [--] Data in InnoDB tables: 54M (Tables: 161)
    [--] Data in MEMORY tables: 1M (Tables: 9)
    [!!] BDB is enabled but isn't being used
    [!!] Total fragmented tables: 46
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4h 47m 40s (1M q [114.735 qps], 114K conn, TX: 26B, RX: 623M)
    [--] Reads / Writes: 77% / 23%
    [--] Total buffers: 714.0M global + 2.7M per thread (150 max threads)
    [OK] Maximum possible memory usage: 1.1G (56% of installed RAM)
    [OK] Slow queries: 0% (29/1M)
    [OK] Highest usage of available connections: 25% (38/150)
    [OK] Key buffer size / total MyISAM indexes: 500.0M/687.3M
    [OK] Key buffer hit rate: 100.0% (889M cached / 197K reads)
    [OK] Query cache efficiency: 67.9% (998K cached / 1M selects)
    [!!] Query cache prunes per day: 76063
    [OK] Sorts requiring temporary tables: 4% (4K temp sorts / 104K sorts)
    [!!] Joins performed without indexes: 272
    [!!] Temporary tables created on disk: 46% (61K on disk / 131K total)
    [OK] Thread cache hit rate: 83% (18K created / 114K connections)
    [!!] Table cache hit rate: 1% (84 open / 8K opened)
    [OK] Open file limit used: 0% (133/65K)
    [OK] Table locks acquired immediately: 99% (936K immediate / 939K locks)
    [!!] InnoDB data size / buffer pool: 54.6M/54.0M
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-bdb to MySQL configuration to disable BDB
        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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 128M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 50M)
        max_heap_table_size (> 30M)
        table_cache (> 84)
        innodb_buffer_pool_size (>= 54M)


    Timeout 120
    KeepAlive Off
    MaxKeepAliveRequests 100
    KeepAliveTimeout 15
    <IfModule prefork.c>
    StartServers       8
    MinSpareServers    5
    MaxSpareServers   20
    ServerLimit      196
    MaxClients       196
    MaxRequestsPerChild  4000

    I've read I unlikely to be prefork?

    The system is a dual 1gz with 2gb of ram

    I've read so many blogs/forums over the last 2 weeks and now got to the point I need to ask for some help..

    Thanks for reading the thread and hope someone can help a little
  2. kdean

    kdean Well-Known Member

    Oct 19, 2012
    Likes Received:
    Trophy Points:
    Orlando, FL
    cPanel Access Level:
    Root Administrator
    You should take a look at:


    …to see what it says when MYSQL crashes. It may be possible that if you have a lot of apache and other process memory being used at the time and MYSQL is reaching the 1.1GB, that it may be exceeding your total available ram and crashing. The err log may mention that. Also, you should run mysqltuner again after 24-48 hours for a more accurate representation of the data.
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Apr 11, 2011
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Hello :)

    I agree that you should check the MySQL error log to get a better idea of why exactly MySQL is crashing. It's possible that it's an issue unrelated to the need to tune/optimize the MySQL configuration file.

    Thank you.
  4. thinkbot

    thinkbot Well-Known Member

    Oct 30, 2012
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Meantime, you can optimize those:

    wait_timeout = 10
    connect_timeout = 1

    query_cache_size = 30M
    query_cache_limit = 1MB

    table_cache = 750
    innodb_buffer_pool_size = 100M
    thread_cache_size = 8

    Upgrade MySQL to 5.5 or something, then
    long_query_time = 0.1

    Or with current mysql, you can set >= 1
    long_query_time = 1

    and add
    after long_query_time

    on http:
    there is no point really to make timeout big as 120 seconds, do you have any operation that can take that much time ?
    If you are using KeepAlive, set it to low as possible like 1-2s, based on your example you are not using it, Off is there
    Timeout 20

    And becouse of memory, I would suggest lowering
    ServerLimit 196
    MaxClients 196

    To like max 100, becouse of RAM limitations; you most likely got RAM swapping, thats the reason of problems
    Anyway your mysql got max 150 conn, so any process that wants php over 150 limit gives "too many connections"

    I would suggest checking slow query log to find out what queries takes too long time, so they stuck mysql, and makes it fill/lock 150 threads (max mysql connections)

    Thats assuming that you got some soft that kills processes (mysql) that takes too much memory,
    second option - ulimits
    so also post ulimit -a result

    chmod +x pt-query-digest

    ./pt-query-digest /var/log/mysql/mysql-slow.log > slow.txt

    run it now, then clean up mysql-slow.log file
    rm -rf /var/log/mysql/mysql-slow.log

    update mysql settings, especially slow query log time, let it run for some time, and rerun

    ./pt-query-digest /var/log/mysql/mysql-slow.log > slow2.txt

    Then check out/copy here slow.txt and slow2.txt for slow queries
    #4 thinkbot, May 22, 2013
    Last edited: May 22, 2013

Share This Page