Mysql – How to limit MySQL connections

MySQL

Last week, our MySQL database got overwhelmed with a lot of connections doing INSERT / SELECT queries.

I have talked to the developers who say it will take weeks to fix this issue.

In the mean time, I would love to have a way to limit the amount of live connections to the MySQL database if I see it getting out of control.

There is the option max-connections but I am not sure if I can throttle this so that if I the database is getting swamped, I would just set global max-connection = 100, and it would stop accepting new connections.

Is this a viable solution? What have you done before when your database gets overwhelmed?

Best Answer

You could limit the number of connections per hour per user. Restarting mysql would not be necessary for this.

For example, suppose all your web servers connect to mysql from the 10.1.2.% netblock. You should already have a user in mysql.user named something like 'myuser'@'10.1.2.%'.

To set the maximum number of queries per hour at 1000 on a given connection do this:

UPDATE mysql.user SET
max_questions = 1000
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;

To set the maximum number of updates per hour at 1000 on a given connection do this:

UPDATE mysql.user SET
max_updates = 1000
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;

To set the maximum number of connections per hour at 1000 on a given connection do this:

UPDATE mysql.user SET
max_connections = 1000
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;

For more clarification on this, please see MySQL Documentation.

BTW once you are done with the maintenance window for throttling user connections, simply set the values you changed back to zero(0) to remove the limits as follows:

UPDATE mysql.user SET
    max_questions = 0,
    max_updates = 0,
    max_connections = 0
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;
Related Question