Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

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;
share|improve this answer
    
+1 I had no idea that this existed. –  Aaron Brown Mar 22 '12 at 18:14
    
Thanks. I will test this out. –  Carmen Mar 23 '12 at 0:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.