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.
     
    #1 guptaji, Dec 15, 2015
  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..
    ------------------------
     
    #2 guptaji, Dec 15, 2015
  3. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,358
    Likes Received:
    50
    Trophy Points:
    28
    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.
     
    #3 24x7server, Dec 15, 2015
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    36,434
    Likes Received:
    1,215
    Trophy Points:
    363
    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.
     
    #4 cPanelMichael, Dec 15, 2015
  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..
     
    #5 guptaji, Dec 15, 2015
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    36,434
    Likes Received:
    1,215
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    #6 cPanelMichael, Dec 15, 2015
  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:
    36,434
    Likes Received:
    1,215
    Trophy Points:
    363
    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.
     
    #8 cPanelMichael, Dec 15, 2015
  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:

    #10 guptaji, Dec 16, 2015
  11. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    36,434
    Likes Received:
    1,215
    Trophy Points:
    363
    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.
     
    #11 cPanelMichael, Dec 16, 2015
  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:
    36,434
    Likes Received:
    1,215
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    I've moved this thread to our "Optimization" forum where you may receive additional user-feedback.

    Thank you.
     
    #13 cPanelMichael, Dec 17, 2015
(You must log in or sign up to post here.)
Loading...
Similar Threads - Warning mysqli many
  1. John Schmerold

    Remote Root Account Transfer Warnings

    John Schmerold, Jul 15, 2017, in forum: Data Protection
    Replies:
    11
    Views:
    96
    cPanelMichael
    Jul 25, 2017 at 9:14 AM
  2. saifu4net

    A warning occurred while processing this directive

    saifu4net, Jul 11, 2017, in forum: General Discussion
    Replies:
    3
    Views:
    87
    cPanelMichael
    Jul 12, 2017
  3. linux4me2

    AutoSSL Log Warnings On Addon Domain That Used To Be a Main Domain

    linux4me2, Jun 16, 2017, in forum: Security
    Replies:
    4
    Views:
    88
    linux4me2
    Jun 16, 2017
  4. Dansih

    warning email () has been disabled for security reasons

    Dansih, Jun 13, 2017, in forum: E-mail Discussions
    Replies:
    3
    Views:
    132
    webhostuk
    Jun 15, 2017
  5. savastgfx

    Backup configuration warning: NOTE: Filesystem quotas are enabled.

    savastgfx, Jun 11, 2017, in forum: Data Protection
    Replies:
    2
    Views:
    109
    Infopro
    Jun 12, 2017

Share This Page