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.

Warning: mysqli Too many connections

Discussion in 'Workarounds and Optimization' started by guptaji, Dec 15, 2015.

  1. guptaji

    guptaji Active Member

    Joined:
    Sep 8, 2014
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Warning: mysqli::mysqli(): (08004/1040): Too many connections in /home/MY_ACCOUNT_NAME_HERE/public_html/core/load.php on line 124
    Unable to connect to database [Too many connections]

    i am getting this from morning..

    line 124: include ($root.'/core/config.php');

    //config.php have mysql database connection info.
     
  2. guptaji

    guptaji Active Member

    Joined:
    Sep 8, 2014
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    solved it temporarily by running Command in SSH: service mysql restart

    now i have to look at mysqli_close..


    ------------------------
    for moderators, you can mark this thread as solved or remove it..
    ------------------------
     
  3. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,146
    Likes Received:
    34
    Trophy Points:
    48
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello :),

    Yes, You can fix this by restarting mysql services on your server but it's not good idea to restart mysql services every time. I will suggest you please try to increase mysql connection limit in your mysql configuration file so that you will not face this issues again.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Yes, as mentioned, you may want to review your MySQL configuration file (/etc/my.cnf) and make adjustments to the maximum allowed connections if the issue continues.

    Thank you.
     
  5. guptaji

    guptaji Active Member

    Joined:
    Sep 8, 2014
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Can you give me steps to solve it..?? Or any tutorial link..
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  7. guptaji

    guptaji Active Member

    Joined:
    Sep 8, 2014
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I have intel core E3 1230v2
    4 cores / 8 threads.
    3.2 ghz.
    32 gb DDR3 RAM.

    Please suggest me good config for high mysql load.

    Basically i'm running ad network which is having too many quries and connection at a time..
     
    #7 guptaji, Dec 15, 2015
    Last edited: Dec 15, 2015
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You will need to run a tuner script as referenced in my previous response because there's no single configuration value that fits every server.

    Thank you.
     
  9. guptaji

    guptaji Active Member

    Joined:
    Sep 8, 2014
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    here is my mysqltuner report..
    Code:
    root@server [~/tool]# ./mysqlmymonlite.sh mysqltuner
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.4 WHM mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Tue Dec 15 23:40:49 CST 2015
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    >>  MySQLTuner 1.5.1 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at MySQLTuner-perl by major
    >>  Modified by George Liu (eva2000) at vbtechsupport.com
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.42-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 27M (Tables: 34)
    [--] Data in InnoDB tables: 82M (Tables: 46)
    [!!] Total fragmented tables: 11
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [!!] There is not basic password file list !
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9h 51m 41s (570K q [16.061 qps], 36K conn, TX: 26B, RX: 37M)
    [--] Reads / Writes: 98% / 2%
    [--] Binary logging is disabled
    [--] Total buffers: 168.0M global + 2.8M per thread (600 max threads)
    [OK] Maximum reached memory usage: 228.5M (0.71% of installed RAM)
    [OK] Maximum possible memory usage: 1.8G (5.67% of installed RAM)
    [OK] Slow queries: 0% (0/570K)
    [OK] Highest usage of available connections: 3% (22/600)
    [OK] Aborted connections: 0.17%  (60/36061)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
    [!!] Temporary tables created on disk: 57% (549 on disk / 963 total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 95% (156 open / 163 opened)
    [OK] Open file limit used: 0% (123/50K)
    [OK] Table locks acquired immediately: 99% (502K immediate / 502K locks)
    
    -------- MyISAM Metrics -----------------------------------------------------
    [!!] Key buffer used: 26.0% (2M used / 8M cache)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/5.2M
    [OK] Read Key buffer hit rate: 100.0% (5M cached / 579 reads)
    [OK] Write Key buffer hit rate: 99.5% (1M cached / 7K writes)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 128.0M/82.1M
    [OK] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 75.10% (6152 used/ 8192 total)
    [OK] InnoDB Read buffer efficiency: 100.00% (1943061592 hits/ 1943063703 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 6356 writes)
    
    -------- AriaDB Metrics -----------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
      Run OPTIMIZE TABLE to defragment tables for better performance
      MySQL started within last 24 hours - recommendations may be inaccurate
      Enable the slow query log to troubleshoot bad queries
      When making adjustments, make tmp_table_size/max_heap_table_size equal
      Reduce your SELECT DISTINCT queries without LIMIT clauses
      Set thread_cache_size to 4 as a starting value
    Variables to adjust:
      query_cache_size (>= 8M)
      tmp_table_size (> 16M)
      max_heap_table_size (> 16M)
      thread_cache_size (start at 4)
    
    Report Complete:
    Tue Dec 15 23:40:50 CST 2015
    ----------------------------
    root@server [~/tool]#
    
     
    #9 guptaji, Dec 15, 2015
    Last edited by a moderator: Dec 22, 2015
  10. guptaji

    guptaji Active Member

    Joined:
    Sep 8, 2014
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    i have changed /etc/my.cnf yesterday night ( on 2 AM).
    added these line:
    max_connections=600
    interactive_timeout=100
    wait_timeout=20
    connect_timeout=10

    changed open_files_limit=10000 to open_files_limit=50000
     

    Attached Files:

  11. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    It's important to let MySQL run at least 24 hours before running a tuner to ensure the results are accurate. You can continue to make the changes based on the recommendations of the output and see if MySQL continues to fail.

    Thank you.
     
  12. guptaji

    guptaji Active Member

    Joined:
    Sep 8, 2014
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    report after 34 hrs..
    Code:
    root@server [~/tool]# ./mysqlmymonlite.sh mysqltuner
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.4 WHM mysqlmymon.com
    compiled by George Liu (eva2000) vbtechsupport.com
    -------------------------------------------------------------
    
    Report Generated:
    Thu Dec 17 09:55:42 CST 2015
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
    >>  MySQLTuner 1.5.1 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at MySQLTuner-perl by major
    >>  Modified by George Liu (eva2000) at vbtechsupport.com
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.42-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 27M (Tables: 34)
    [--] Data in InnoDB tables: 83M (Tables: 46)
    [!!] Total fragmented tables: 14
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [!!] There is not basic password file list !
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 20h 6m 34s (2M q [15.067 qps], 415K conn, TX: 119B, RX: 181M)
    [--] Reads / Writes: 97% / 3%
    [--] Binary logging is disabled
    [--] Total buffers: 168.0M global + 2.8M per thread (600 max threads)
    [OK] Maximum reached memory usage: 267.0M (0.83% of installed RAM)
    [OK] Maximum possible memory usage: 1.8G (5.67% of installed RAM)
    [OK] Slow queries: 0% (0/2M)
    [OK] Highest usage of available connections: 6% (36/600)
    [OK] Aborted connections: 0.02%  (98/415329)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (10 temp sorts / 70K sorts)
    [!!] Temporary tables created on disk: 84% (10K on disk / 12K total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 93% (162 open / 173 opened)
    [OK] Open file limit used: 0% (124/50K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    
    -------- MyISAM Metrics -----------------------------------------------------
    [!!] Key buffer used: 82.3% (6M used / 8M cache)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/5.2M
    [OK] Read Key buffer hit rate: 100.0% (24M cached / 5K reads)
    [OK] Write Key buffer hit rate: 99.7% (6M cached / 19K writes)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 128.0M/83.1M
    [OK] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 76.78% (6290 used/ 8192 total)
    [OK] InnoDB Read buffer efficiency: 100.00% (10576185736 hits/ 10576187905 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 41807 writes)
    
    -------- AriaDB Metrics -----------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
      Run OPTIMIZE TABLE to defragment tables for better performance
      Enable the slow query log to troubleshoot bad queries
      When making adjustments, make tmp_table_size/max_heap_table_size equal
      Reduce your SELECT DISTINCT queries without LIMIT clauses
      Set thread_cache_size to 4 as a starting value
    Variables to adjust:
      query_cache_size (>= 8M)
      tmp_table_size (> 16M)
      max_heap_table_size (> 16M)
      thread_cache_size (start at 4)
    
    Report Complete:
    Thu Dec 17 09:55:43 CST 2015
    ----------------------------
    root@server [~/tool]#
    
     
    #12 guptaji, Dec 17, 2015
    Last edited by a moderator: Dec 22, 2015
  13. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator

Share This Page