guptaji

Active Member
Sep 8, 2014
34
0
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.
 

guptaji

Active Member
Sep 8, 2014
34
0
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..
------------------------
 

24x7server

Well-Known Member
Apr 17, 2013
1,913
99
78
India
cPanel Access Level
Root Administrator
Twitter
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

Administrator
Staff member
Apr 11, 2011
47,883
2,256
463
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

Active Member
Sep 8, 2014
34
0
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..
 
Last edited:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,883
2,256
463
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

Active Member
Sep 8, 2014
34
0
6
cPanel Access Level
Root Administrator
here is my mysqltuner report..
Code:
[email protected] [~/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 <[email protected]>
>>  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
----------------------------
[email protected] [~/tool]#
 
Last edited by a moderator:

guptaji

Active Member
Sep 8, 2014
34
0
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
 

Attachments

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,883
2,256
463
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

Active Member
Sep 8, 2014
34
0
6
cPanel Access Level
Root Administrator
report after 34 hrs..
Code:
[email protected] [~/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 <[email protected]>
>>  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
----------------------------
[email protected] [~/tool]#
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,883
2,256
463
I've moved this thread to our "Optimization" forum where you may receive additional user-feedback.

Thank you.