Smarter than max_connections?

MySQL & MariaDB Other MySQL® Questions
1

We have a MySQL server which handles queries for 4 web servers. On the server we have max_connections set to 1600 (400 connections per web server). Anyway, this usually works great - and our app gracefully gives the users a ‘sorry’ message if the max_connections has been exceeded.

The problem we have is that if for some reason the web servers are making a lot of expensive queries (this just happened when our sql cache mechanism failed - no queries were cached!) then the server gets really overloaded. And because the max_connections has not been met - it takes on even more queries, even though it’s already really stressed.

So, my question is - is there any way to make MySQL refuse connections based on how high it’s load is (and not simply based on the number of current connections) ??

Or are there any clever tricks at the app level to do this? (we are using PHP).

Thanks!
Hamish

2

Hamish,
Unfortunately there is no way to limit connections to MySQL server based on it’s load.

3

Not in mysql directly, but you could do this in application layer. If you maintain current load status of mysql server on the webservers, and then check this before issuing a connection attempt, you could prevent excessive connections causing higher load.

At the most basic level, something like a keyless ssh connection every minute (or more often) to the db server to pull the load and store it locally would do.
Or you could use snmp polling or anything more sophisticated.

The point is this is all done outside mysql.

mancdaz