One means of limiting use of MySQL server resources is to set the
max_user_connections
system
variable to a nonzero value. However, this limits only the number
of simultaneous connections made using a single account, and not
what a client can do once connected. In addition, this method is
strictly global, and does not allow for management of individual
accounts. Both types of control are of interest to many MySQL
administrators, particularly those working for Internet Service
Providers.
In MySQL 5.1, you can limit the following server resources for individual accounts:
The number of queries that an account can issue per hour
The number of updates that an account can issue per hour
The number of times an account can connect to the server per hour
The number of simultaneous connections to the server an account can have
Any statement that a client can issue counts against the query limit. Only statements that modify databases or tables count against the update limit.
An “account” in this context corresponds to a single
row in the user
table. That is, connections are
assessed against the User
and
Host
values in the user
table row that applies to the connection. Suppose that there is a
row in the user
table that has
User
and Host
values of
usera
and %.example.com
, to
allow usera
to connect from any host in the
example.com
domain. In this case, the server
applies resource limits collectively to all connections by
usera
from any host in the
example.com
domain because all such connections
use the same account.
Before MySQL 5.0.3, an “account” was assessed against
the actual host from which a user connects. This older method
accounting may be selected by starting the server with the
--old-style-user-limits
option. In
this case, if usera
connects simultaneously
from host1.example.com
and
host2.example.com
, the server applies the
account resource limits separately to each connection. If
usera
connects again from
host1.example.com
, the server applies the
limits for that connection together with the existing connection
from that host.
The server limits account resources based on the resource-related
columns of the user
table in the
mysql
database:
max_questions
, max_updates
,
max_connections
, and
max_user_connections
. If your
user
table does not have these columns, it must
be upgraded; see Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.
To set resource limits, use the
GRANT
statement and provide a
WITH
clause that names each resource to be
limited. For example, to create a new account that can access the
customer
database, but only in a limited
fashion, issue these statements:
mysql>CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';
mysql>GRANT ALL ON customer.* TO 'francis'@'localhost'
->WITH MAX_QUERIES_PER_HOUR 20
->MAX_UPDATES_PER_HOUR 10
->MAX_CONNECTIONS_PER_HOUR 5
->MAX_USER_CONNECTIONS 2;
The limit types need not all be named in the
WITH
clause, but those named can be present in
any order. The value for each per-hour limit should be an integer
representing a count per hour. If the
GRANT
statement has no
WITH
clause, the limits are each set to the
default value of zero (that is, no limit). For
MAX_USER_CONNECTIONS
, the limit is an integer
representing the maximum number of simultaneous connections the
account can make at any one time. If the limit is set to the
default value of zero, the
max_user_connections
system
variable determines the number of simultaneous connections for the
account.
To modify limits for an existing account, use a
GRANT USAGE
statement at the global level (ON *.*
). The
following statement changes the query limit for
francis
to 100:
mysql>GRANT USAGE ON *.* TO 'francis'@'localhost'
->WITH MAX_QUERIES_PER_HOUR 100;
This statement leaves the account's existing privileges unchanged and modifies only the limit values specified.
To remove an existing limit, set its value to zero. For example,
to remove the limit on how many times per hour
francis
can connect, use this statement:
mysql>GRANT USAGE ON *.* TO 'francis'@'localhost'
->WITH MAX_CONNECTIONS_PER_HOUR 0;
Resource-use counting takes place when any account has a nonzero limit placed on its use of any of the resources.
As the server runs, it counts the number of times each account uses resources. If an account reaches its limit on number of connections within the last hour, further connections for the account are rejected until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, further queries or updates are rejected until the hour is up. In all such cases, an appropriate error message is issued.
Resource counting is done per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.
Queries for which results are served from the query cache do not
count against the MAX_QUERIES_PER_HOUR
limit.
The current per-hour resource-use counts can be reset globally for all accounts, or individually for a given account:
To reset the current counts to zero for all accounts, issue a
FLUSH
USER_RESOURCES
statement. The counts also can be
reset by reloading the grant tables (for example, with a
FLUSH
PRIVILEGES
statement or a mysqladmin
reload command).
The counts for an individual account can be set to zero by
re-granting it any of its limits. To do this, use
GRANT USAGE
as described earlier and specify a limit value equal to the
value that the account currently has.
Counter resets do not affect the
MAX_USER_CONNECTIONS
limit.
All counts begin at zero when the server starts; counts are not carried over through a restart.
For the MAX_USER_CONNECTIONS
limit, an edge
case can occur if the account currently has open the maximum
number of connections allowed to it: A disconnect followed quickly
by a connect can result in an error
(ER_TOO_MANY_USER_CONNECTIONS
or
ER_USER_LIMIT_REACHED
) if the
server has not fully processed the disconnect by the time the
connect occurs. When the server finishes disconnect processing,
another connection will once more be allowed.
User Comments
There doesn't appear to be a way to get the current number of queries, updates, etc. e.g. to use in a dashboard. For that, you have to roll your own.
Add your own comment.