Often while managing and creating new users, we use all the default options and tend not to use extra features provided by MySQL. These extra options could prevent a user from using all the resources and degrading the performance of MySQL. In this blog, we will discuss a few such features that will put resource restrictions on users.
max_user_connections
Sometimes, due to unprecedented growth or huge transactions, a single user makes too many connections, and the MySQL server gets starved of free connections. This blocks the DBA from logging into MySQL to fix it. To fix it in MySQL 5.7 and below, we have to do a restart. For MySQL 8, it can be done without a restart, but we have to configure the admin interface beforehand. You can read more about it here in Dealing With “Too Many Connections” Error in MySQL 8.
But it can be prevented earlier if we restrict the expected user to a certain number of concurrent connections. It can be done at the creation of the user, or we can execute an alter command to modify this configuration.
1 2 | CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'YourPasswordHere' WITH MAX_USER_CONNECTIONS 100; ALTER USER 'monitor_user'@'%' WITH MAX_USER_CONNECTIONS 100; |
There is also an option to restrict all users, and not just individual user accounts, by setting up the variable max_user_connections. It is a dynamic variable, it can be set up globally, and it can also be changed by making the change in the configuration file.
1 2 | mysql> SET GLOBAL max_user_connections=100; Query OK, 0 rows affected (0.00 sec) |
1 2 | [mysqld] max_user_connections =100; |
In this case, this will restrict all users to only 100 concurrent logins. If the user tries to log in after it has made more than 100 concurrent logins, an error message will appear.
1 2 | [root@centos]# mysql -u 'monitor_user' -p ERROR 1226 (42000): User 'monitor_user' has exceeded the 'max_user_connections' resource (current value: 100) |
max_connections_per_hour
Instead of setting up max_user_connections for users, we can also restrict the limit to per hour. This will add a time-based restriction to the user, only allowing a specific number of connections per hour.
1 2 | mysql> ALTER USER 'john_smith'@'%' WITH MAX_CONNECTIONS_PER_HOUR 10; Query OK, 0 rows affected (0.00 sec) |
The user will get an error message telling them the cause and the current value of max_connections_per_hour:
1 2 | [root@centos]# mysql -u 'john_smith' -p ERROR 1226 (42000): User 'john_smith' has exceeded the 'max_connections_per_hour' resource (current value: 10) |
max_queries_per_hour
This option will set a limit for any kind of queries that a user can run per hour and will only allow the user to run a pre-defined number of queries per hour.
1 2 | mysql> ALTER USER 'john_smith'@'%' WITH MAX_QUERIES_PER_HOUR 10; Query OK, 0 rows affected (0.00 sec) |
The queries that are restricted are not just DML operations but for any kind of queries.
1 2 3 4 5 | mysql> use app_schema Database changed mysql> show tables; ERROR 1226 (42000): User 'john_smith' has exceeded the 'max_questions' resource (current value: 10) |
max_updates_per_hour
If the user tries to run more than the defined updates per hour, the following error message will appear.
1 2 | mysql> UPDATE some_table SET col1='Value d' WHERE id=2; ERROR 1226 (42000): User 'john_smith' has exceeded the 'max_updates' resource (current value: 5) |
If the user tries to run more than the defined updates per hour, the following error message will appear.
1 2 | mysql> UPDATE some_table SET col1='Value d' WHERE id=2; ERROR 1226 (42000): User 'john_smith' has exceeded the 'max_updates' resource (current value: 5) |
Conclusion
These options must be used with a lot of caution and thought, as you can block important users from the database that could hamper day-to-day operations. MAX_USER_CONNECTIONS can be used for any monitoring user to avoid a pile-up of connections. It will act as a failsafe to not starve the database off any connections. MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR, and MAX_QUERIES_PER_HOUR are useful if you want to set a resource-based limit for individual users. The common use case for these could be where the number of queries per hour is dependent on the price paid by the user. It is not recommended to use these settings for app-based connections. If you are facing such issues from app users, it is recommended to use ProxySQL as a Multiplexing intermediary.
If you don’t want to restrict users from running queries or logging in but still want to manage resource limit, you can configure resource groups that set CPU affinity and thread priority for a thread. You can read more about it in MySQL 8.0 RESOURCE_GROUP Overview and use them to fine-tune the load at MySQL 8: Load Fine Tuning With Resource Groups.
Setting up a resource limit is an overlooked configuration in resource management; it is fast, easy, and simple to use. However, these settings must be used very carefully and with proper planning, as any mistake in setting up the limits could result in users not being able to use the database.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
