Warning: mysqli Too many connections

[guptaji]

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]

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]

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]

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]

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

 

[cPanelMichael]

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]

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]

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]

here is my mysqltuner report..

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

 

[guptaji]

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]

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]

report after 34 hrs..

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

 

[cPanelMichael]

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

Thank you.