Optimize MySQL server performance with user resource limits
MySQL lets you configure server resource limits on per user account basis. This feature helps you maximize system availability in situations where different users (possibly multiple business clients) share a single environment and none should be allowed to overload it. You can also use it to prevent a nonessential process from monopolizing limited MySQL resources.
In version 5.x, you configure MySQL resource limits in the default system database "mysql" and its table "user." The user table includes four resource columns for limits: max_questions, max_updates, max_connections, and max_user_connections. You can see the resource limits for an existing user just as you would retrieve the values of any other MySQL table. For instance, to retrieve the values for a user called limited_user:
mysql> select max_questions,max_updates,max_connections,max_user_connections from user where User='limited_user' AND Host='localhost';
| max_questions | max_updates | max_connections | max_user_connections |
| 1000 | 0 | 0 | 0 |
In addition to setting limits for existing users, you can also create resource limits when assigning privileges to a user for the first time. The standard MySQL GRANT command supports arguments for specifying the resource limits. However, the names of three of the four limits vary from what we've already seen:
GRANT ALL ON example_db.* TO 'limited_user'@'localhost' WITH
Here, limited_user is limited only by the first limit, max_questions. The zero value in the other limits means that they are unlimited.
These resource limits should be present in every MySQL database on a MySQL 5.x server. If they're not on your server, it is probably because the server was upgraded improperly from a previous version. Run the mysql_upgrade command to create the missing columns.
Note that the MySQL user table contains unique entries for users in combination with hosts from which these users are allowed to connect. That is, resource limits for a particular user may vary depending on the host from which the user connects to the MySQL service.
By default, every MySQL user is created without any resource limits. If you want to change a resource limit for a user, you can do so using the usual MySQL syntax for updating values. For instance, to change the max_questions limit for limited_user to 500 when connecting from localhost:
USE mysql; UPDATE user SET max_questions=500 WHERE User='limited_user' AND Host='localhost';
Type of MySQL resource limits
Let's look at these four types of user resource limits in more detail.
Max_questions is number of queries per hour that an account can execute. This is the most general limit, because it covers all statements, including select, update, and delete, with the caveat that the limit is not applied when a query finds results from the server's cache rather than retrieving them anew. The max_questions limit is usually too aggressive because when it is exceeded it denies almost all resources to the user. Still, it can be useful in environments that have no tolerance for resource abuse, such as in shared web hosting. By applying this limit to an account you may force the account owner to start using best programming practices such as the implementation of application caching, which should decrease the number of queries to the MySQL server.
Once you set the max_questions limit you can test it with a query like this:
mysql> select SQL_NO_CACHE * from wp_posts;
ERROR 1226 (42000): User 'limited_user' has exceeded the 'max_questions' resource (current value: 10)
In this command limited_user tries to query the wp_posts table, which part of a WordPress database, and uses the SQL_NO_CACHE argument, which ensures that the result is not served from the cache. The user sees an error because he has exceeded his limit of 10 results. Exceeding the other limits results in similar errors; only the name of the limit changes.
Max_updates sets the maximum number of updates a user may perform per hour. This limit does not depend on caching, so every update statement counts. If you have to comply with such a limit, your only option is to decrease the number of updates you execute. This usually means sacrificing application functionality, which might be an option when write-intensive application functionality is not essential to a business process. For instance, many WordPress extensions perform large numbers of writes, and while they may add some fancy functionality to your site, they are probably not essential to its functioning. If your project has a limited budget or resources and you have to comply with MySQL update limits, you should consider removing such extensions.
Max_connections looks at the maximum number of simultaneous connections a user may establish to the server. Two things are important about this limit: First, when this limit is enforced, application owners must make sure that each database connection is closed as soon as possible instead of being left idle. Often times developers fail to close database connections in a timely manner, which leads to system resources wasted on idle connections.
Second, even if you don't enforce a max_connections limit on a per-user basis, MySQL has such a limit that it enforces globally. By default, the global max_connections limit is 151, and it counts every user's connection to the server. This default global limit is usually too low, and results in users seeing the fatal MySQL error Too many connections, which breaks the functionality of the applications. You can raise the global limit by editing the MySQL configuration file (usually /etc/my.cnf) and adding the directive
max_connections = 1024 in the [mysqld] configuration part to allow 1024 user connections to the server. At the same time, however, you should enforce a more aggressive limit on abusive users to ensure that other MySQL users don't suffer from errors or bad performance.
Finally, max_user_connections defines the maximum number of connections a given user may establish per hour. To avoid breaking this limit you should configure your applications to reuse MySQL connections for subsequent MySQL queries. This is a good programming practice that saves system resources and avoids the time taken by frequently opening and closing new connections. At first glance it may seem that this advice contradicts the recommendations for the max_connections limit, because if you don't close the connection quickly, supposedly the number of connections will increase. However, you should reuse connections when subsequent queries are to be executed and not open new connections. Only once a connection is idle should it be closed, when there are no subsequent queries following shortly.
MySQL user resource limits can help ensure proper and healthy functioning of a MySQL environment. By taking advantage of their granular control over resource usage, you can prevent server overloads and downtime.
Do you want to receive a compilation of Wazi's top
blog posts in the past year delivered directly to your inbox?
This work is licensed under a Creative Commons Attribution 3.0 Unported License