Warning: mysqli Too many connections

guptaji · Dec 15, 2015

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.

guptaji · Dec 15, 2015

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..
------------------------

24x7server · Dec 15, 2015

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.

cPanelMichael · Dec 15, 2015

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.

guptaji · Dec 15, 2015

Can you give me steps to solve it..?? Or any tutorial link..

cPanelMichael · Dec 15, 2015

The following thread is helpful if you want to tune your MySQL configuration:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

guptaji · Dec 15, 2015

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..

cPanelMichael · Dec 15, 2015

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.

guptaji · Dec 15, 2015

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]#

guptaji · Dec 16, 2015

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

cPanelMichael · Dec 16, 2015

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.

guptaji · Dec 17, 2015

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]#

cPanelMichael · Dec 17, 2015

I've moved this thread to our "Optimization" forum where you may receive additional user-feedback.

Thank you.

Forums

The Community Forums

Interact with an entire community of cPanel & WHM users!

Warning: mysqli Too many connections

guptaji Active Member

guptaji Active Member

24x7server Well-Known Member

cPanelMichael Forums Analyst
Staff Member

guptaji Active Member

cPanelMichael Forums Analyst
Staff Member

guptaji Active Member

cPanelMichael Forums Analyst
Staff Member

guptaji Active Member

guptaji Active Member

Attached Files:

Screenshot_1.jpg

cPanelMichael Forums Analyst
Staff Member

guptaji Active Member

cPanelMichael Forums Analyst
Staff Member

Kernel reboot warnings on virtual servers

/usr/local/cpanel/3rdparty/bin/freshclam --quiet --no-warnings

Remote Root Account Transfer had warnings

Error loading module Rvskinwarning

AutoSSL warning

Share This Page

Useful Searches

The Community Forums

Interact with an entire community of cPanel & WHM users!

Warning: mysqli Too many connections

guptaji Active Member

guptaji Active Member

24x7server Well-Known Member

cPanelMichael Forums Analyst Staff Member

guptaji Active Member

cPanelMichael Forums Analyst Staff Member

guptaji Active Member

cPanelMichael Forums Analyst Staff Member

guptaji Active Member

guptaji Active Member

Attached Files:

Screenshot_1.jpg

cPanelMichael Forums Analyst Staff Member

guptaji Active Member

cPanelMichael Forums Analyst Staff Member

Kernel reboot warnings on virtual servers

/usr/local/cpanel/3rdparty/bin/freshclam --quiet --no-warnings

Remote Root Account Transfer had warnings

Error loading module Rvskinwarning

AutoSSL warning

Share This Page

cPanelMichael Forums Analyst
Staff Member

cPanelMichael Forums Analyst
Staff Member

cPanelMichael Forums Analyst
Staff Member

cPanelMichael Forums Analyst
Staff Member

cPanelMichael Forums Analyst
Staff Member