PHP MySQL“连接太多”问题 - Cent OS

We have high con-current traffic on server setup, when the traffic crossing 100+/sec, we facing the 'too many connections' issue with PHP & MySQL. We have separate AWS nodes for Application(PHP - 5.4.34) and Database(MySQL - 5.5.40).

  • In the MySQL we have 3500 max connections limit and also we adjusted the variables in MySQL.
  • On the high traffic time, the DB node CPU utilization not exceeds 10% and RAM usage below 4GB. But still we are facing the connections issue with application node.
  • Using the MyISAM engine for Database.
  • Used the mysql_connect function and closed the connection on every page completion.
  • We monitoring the DB with Monyog tool, in that there is no slow queries or queue's.

We googled for the solution, but still we didn't get any valuable solution on the issue.

Edit:
Here are further more details on our server setup, we are using the Apache prefork mpm and current mpm setup below.

StartServers 300
MinSpareServers 300
MaxSpareServers 350
ServerLimit 1000
MaxClients 1000
MaxRequestsPerChild 99999

PHP Configuration:
memory_limit : 2048
max_execution_time : 0
timeout : 15
keep_alive : 0ff
keep_alive_timeout : 5

AWS EC2
App Node :
Core : 8 vCPUs
CPU utilization : 89%
RAM : 2048MB out of 30.5GB
Connection : mysql_connect API

its consume more CPU utilization and low RAM usage.

Edit:(Updates)
We updated the connectivity API to PDO and implemented the changes in the package. With the test run, we found same issue again. With the PDO update, we changed the Apache configuration too.
DB connection error: SQLSTATE[HY000] [2003] Can't connect to MySQL server on '172.31.26.180' (99)

https://serverfault.com/questions/370150/server-crashing-too-many-connections

StartServers          2
MinSpareThreads      25
MaxSpareThreads      75 
ThreadLimit          64
ThreadsPerChild      25
MaxClients           400
MaxRequestsPerChild  20000

Please update post with these values. It will aid in troubleshooting.

Your apache config looks a bit messed up.

You have way too many processes. http://httpd.apache.org/docs/2.0/mod/mpm_common.html#startservers

You need 1 per core. max 8 apache servers.

I would switch it to 4

Set Min Servers 4

Set Max servers 16

ServerLimit 16

Here is some info on MaxClients http://httpd.apache.org/docs/2.0/mod/mpm_common.html#maxclients

This is a ratelimit setting, I would set this to something reasonable. if you on avg get 100 rq/s i would likely set this to 400.

MaxRequestsPerChild also is a rate limit setting. I would set this to be no more than 200.

If I understand your question correctly, one of your Apache processes is announcing that it can't connect to MySQL due to "too many connections."

3500 simultaneous connections is a very large number indeed. Your database server seems to be overprovisioned, and you're web-server constrained here.

Here's what you need to do.

First, try reducing, not increasing, your apache ThreadLimit and ThreadsPerChild parameters. You want fewer, not more, apache / php threads trying to connect to your data base. What happens when you don't have enough apache threads? Your client requests go into the TCP listen() queue. There's no harm in that, because any free thread can consume requests from that queue.

You might also try cutting MaxRequestsPerChild to 100 or so. If this dramatically fixes the problem you have a connection leak somewhere.

Second, at the level of workload you have, you must use persistent (reusable) MySQL connections. To do this you'll need to switch to mysqli_ or PDO apis. This is, without doubt, worth your trouble.