СloudLinux Blog

MySQL Governor: Moving from All to Abusers mode

Written by Ivan Zhmud | Jun 24, 2021 5:04:08 PM

We deprecated the “All” mode for MySQL Governor. And we will stop supporting it starting the first of September.

Why we deprecated the “All” mode

CloudLinux OS has been carrying the mission to make Linux secure, stable, and profitable for many years. During that time we faced huge numbers of customer servers with different configurations and diverse infrastructures.

Based on years of experience in supporting our customers' servers, we made a decision to deprecate the “All” mode in MySQL Governor. Let me explain the core reason why we’re doing so – using the “All” mode can lead to:

  • high load average
  • high system CPU usage
  • unstable work of the MySQL service

We deprecated the “All” mode for MySQL Governor. And we will stop supporting it starting the first of September.

Instead of the "All” mode, it is recommended to use the "Abusers" mode. Below, I'll compare these two modes and explain why the “Abusers” mode suits better and cover all needs.

"Abusers" VS "All" mode: short description and features

In the “All” mode, it’s entering to and leaving from an LVE container while processing each SQL query. The overhead of working with LVE is expressed in increased system CPU usage.

Some other processes (PHP, for example) can lead to high CPU usage, too. But when MySQL queries are processed under LVE, LVE throttling works for them too. This means that the database is not the reason for exceeding the limits but it definitely suffers from the fact that processes work under LVE – which affects performance unreasonably.

The “Abusers” mode addresses this issue. In the “Abusers” mode diving into LVE custom SQL requests handlers is performed only upon exceeding the MySQL Governor limits. And so the total numbers of LVE enter/leave processes in the system reduces significantly. This leads to a decrease in system CPU usage compared to the "All" mode.

But as LVE limits in the "Abusers" mode are not applied to the MySQL processes right away but after exceeding the MySQL Governor limits, then short-term exceeding of LVE limits becomes possible. Correct setting of the MySQL Governor limits will allow you to minimize these exceedances and ensure fast MySQL performance.

MySQL Governor Limit Setting Recommendations

Please note that these recommendations on setting limits for MySQL Governor are general. The exact values of the limits for the effective work of MySQL and the site as a whole depend on many factors. Let me list some of them:

  • The load of the virtual server with incoming requests
  • Database size
  • SQL queries efficiency
  • Absolute values of the LVE limits

MySQL Governor allows setting the burstable limits for accounts. To provide that possibility, four levels of limits are defined: "current", "short", "middle", and "long". Correctly set limits can give users more CPU without having a bottleneck on MySQL. 

General principles of choosing the limits:

  • "Current" and "short" can be more than the LVE limit and should not be less.
  • Setting the "current" and "short" limits more than the LVE limit prevents bottlenecks in SQL request processing.
  • "Middle" and "long" on the contrary should not be more than the LVE limit.
  • Setting the "middle" and "long" limits less than the LVE limit prevents abuse of other processes in the account (Apache, PHP) by MySQL.

Example of choosing MySQL Governor limits

With the default LVE SPEED limit is 100, the possible values of the MySQL Governor CPU limits can be 250/150/110/90. I.e., we admit the short-term exceeding of the limits for processing SQL requests.

If you face spike CPU consumption with these limits, it is recommended to reduce the excess of the "current" and "short" limits over the LVE limit. For example, to the values 150/110/100/90.

If the average level of CPU consumption is too high, then it is recommended to reduce the "middle" and "long" limits, too. For example, to the values 150/100/80/50.

Then MySQL processes will fall into LVE and be limited by LVE limits more often.

The same clues are applicable to the IO limits – the "current" and "short" IO limits for MySQL Governor can exceed IO LVE limits, but the "middle" and "long" cannot.

What to do if you use the "All" mode

After the "All" mode will be deprecated on September 1, 2021:
  • the users, having it, will continue to work with this mode;
  • all new installation will not have the "All" mode;
  • moving to the "All" mode will be forbidden.

Stay in touch

Please give our product team feedback, share your ideas and feature requests through feedback@cloudlinux.com or via comments and social media.