phpmysqlnginxredis

Laravel + PHP FPM + MYSQL Issue: "Cannot assign address" while under heavy load


We have 3 digital ocean ubuntu droplets (*excluding the frontend which is nextjs):

BACKEND: 16 vcpus | 32GB ram

DB MYSQL: 8 vcpus | 16GB ram

REDIS: 4 vcpus | 32GB ram

We're running a website that gets high concurrent users sporadically.

The load would jump from 0 to 90 in an instant, but it handles it just fine aside from the system config limitations that we get hit by every step of the way.

Over a week of debugging issues we did the following, solving a bottleneck and jumping to the next:

After all of this everything seemed to be ticking fine. Although we started having another issue related to the max allowed open tcp connections per port, which the server uses to connect to MYSQL & REDIS

We've run a simple stress test by hitting a single endpoint that reads from both mysql and redis 1000 concurrent request sent 50 times for a total of 50k consecutive requests

Around the 35k requests mark, the requests would start failing with the above error. Upon further inspection and running netstat -anlp | grep :3306 | grep TIME_WAIT -wc while doing the stress test, we noticed the number grows up and up as the requests come in, it would quickly reach 35k at which point that error starts to occur. These TIME_WAIT connections seems to be closing after 60 seconds. Upon research we tried lowering that number to 3 seconds with net.ipv4.tcp_fin_timeout = 3 & sysctl -p but to no avail :/ The connections remain open for 60 seconds always and it's causing a big bottleneck under heavy load.

What should we do?

PS: RAM AND CPU were performing just fine under the simple stress test.

EDIT: Using persistent connections for both mysql and redis has solved the issue, + our CPU usage has dropped drastically on both the backend droplet and mysql.


Solution

  • Talking from Redis usage perspective, less grasp on the others:
    What is the gain with using many connections to the same server?
    You can have some pool depends on the implementation of the client you use, but creating a connection per request is useless.
    You should understand the limitations, use the maximum amount of connection Redis server can efficiently handle, and re-use the same connections.
    You should also understand the architecture of the client you use, if it is a multiplexer for example, even more than one connection is useless.

    Having more and more connections sending more requests of what Redis (or MYSQL for that matter) can serve is just misusing of resources, its using a private car for each request and sitting in traffic instead of all moving to public transportation, its not faster, and you need much much more resources.

    If you need more TPS you should scale up Redis, move to cluster mode and add more replications so you can read from more endpoints and the traffic has "load balancing" between shards, the amount of connections should be almost constant compare to the amount of nodes the cluster has.

    With the proper usage Redis will be much faster than the requests getting in (Valkey, the OSS fork of Redis, just announced Valkey 8 with 1million TPS, that's more than most apps need, and its not require connection per request).

    I guess the same is even more true for MySQL, the amount of connection should be determined by the ability of the DB to serve requests and a small margin, there's no logic holding more.