Learning Mysql

Resource-Limit Controls
MySQL 4.0.2 added new resource-limit controls for users. These are maintained along with the global privileges, and affect users rather than client connections. With these controls, you can limit The number of SQL statements per hour, using the
MAX_QUERIES_PER_HOUR
clause.
All statements executed by a user are counted toward this limit The number of updates per hour, using the
MAX_UPDATES_PER_HOUR
clause. Any statement that modifies a database or its tables counts toward this limit The number of connections per hour, using the
MAX_CONNECTIONS_PER_HOUR
clause.
Any connection, from the monitor, a program, or a web script, counts toward this limit.
These clauses can be added to a
GRANT
statement, or you can set them manually using
SQL as discussed later in Managing Privileges with SQL.”
For example, to set limits for the existing user 'partmusic'@'localhost'
, giving this user a maximum of 100 queries per hour, 10 updates, and 5 connections, you’d type:
mysql> GRANT USAGE ON *.* to 'partmusic'@'localhost' WITH
-> MAX_QUERIES_PER_HOUR 100
-> MAX_UPDATES_PER_HOUR 10
-> MAX_CONNECTIONS_PER_HOUR 5;
Query OK, 0 rows affected (0.06 sec)
Since we’ve used USAGE, the privileges aren’t affected when the new limits are imposed.
After imposing these limits, you’ll find an error message is returned when you exceed them. For example, after running and quitting the monitor five times in succession,
you’ll see this mysql --user=partmusic
ERROR 1226 (42000): User 'partmusic' has exceeded the 'max_connections'
resource (current value Remember, these limits apply per user and not per connection. If you start two separate instances of the MySQL monitor client and login to the server as the same user, both connections contribute towards reaching the user’s hourly limits.
Another useful parameter to manage the MySQL server load is the
MAX_USER_CONNEC
TIONS
option. This limits the number of simultaneous clients that can access the server and is usually set when you start mysqld or in an options file. We discuss options files in Chapter 11.

Download 4.24 Mb.

Share with your friends: