Open Source Software Technical Articles

Want the Best of the Wazi Blogs Delivered Directly to your Inbox?

Subscribe to Wazi by Email

Your email:

Connect with Us!

Current Articles | RSS Feed RSS Feed

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 
MAX_QUERIES_PER_HOUR 300 
MAX_UPDATES_PER_HOUR 100 
MAX_CONNECTIONS_PER_HOUR 100 
MAX_USER_CONNECTIONS 50;

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
Creative Commons License.

Comments

Replica Handbags have been in actual the fake or Replica Handbags UK from the original branded carriers. There are numerous such designers that are recognized for their deigning as well as creativity regarding-handbags. The Cheap handbags which are produced by these Designer Replica Handbags appear really elegant as well as fashionable. But furthermore, there will be an issue as well. As well as, this really relates to the affordability of those Designer Handbags.  
Posted @ Monday, July 21, 2014 1:33 AM by kenny
 
With forthcoming Yuletide, I prefer to help advocate it is retro product- this travelling event connected with chanel replica Pégase second there’s 55 Small business to be a cheerful holiday break treat available for you, the pricey followers, appreciate it on your comfy service! Traveling in vogue having fake rolex this Pégase second there’s 55 Small business with sturdy Damier canvas, measurement connected with 15. 7 inches width long, 7. 6 inches width with excess weight, in addition to 7. 9 inches width tall. It is big volume is usually extremely versatile to help fake rolex store your complete travelling needs, featuring a entry padded jean pocket for just a notebook computer, a couple inner surface zipped purses intended for precious stuff, a couple outfits safeguard flaps intended for apparel tidying, in addition to a external surfaces zipped jean pocket intended for gucci replica regular prerequisite. Moreover, some sort of extractible bag comprising safety sleeve delivers far more efficiency to this particular spectacular stunner. That log home measurement gear is included with a couple levels telescopic cope with to help fastener the item press button within the cart cope with. An immensely even, silent running process produces sleek worth to help fake rolex that very good advantage for virtually every small business excursion.
Posted @ Wednesday, July 30, 2014 3:50 AM by sdf
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics