What MySQL Governor limits should be?

Issue

Often the limits for LVE and DB Governor are set incorrectly while it's an important step to lower server load and restrict users. 

Solution

General principles of choosing the limits:

• currentandshortcan be more than the LVE limit and should not be less
• setting thecurrentandshortlimits more than the LVE limit prevents bottlenecks in SQL request processing
• middlelimit can be more or less that the LVE limit
• longon the contrary, should not be more than the LVE limit
• setting themiddleandlonglimits less than the LVE limit prevent 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 100/95/87/75. 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 – thecurrentandshortIO limits for MySQL Governor can exceed IO LVE limits, but themiddleandlongcannot.

It should be set with this way:

current=LVE_CPUSPEED*1
short=LVE_CPUSPEED*0.95
middle=LVE_CPUSPEED*0.87
long=LVE_CPUSPEED*0.75

current=LVE_IO*1
short=LVE_IO*0.83
middle=LVE_IO*0.76
long=LVE_IO*0.59

It is important to take into account that MySQL IO value is the sum of the equivalent read and write values, i.e., the total value of 200 MB/s = 100 MB/s read + 100 MB/s write:

[root@server ~]# grep -A 4 'USERNAME' /etc/container/mysql-governor.xml
  <user name="USERNAME" mode="restrict">
    <limit name="cpu" current="400" short="380" mid="348" long="300"/>
    <limit name="read" current="104857600" short="87031808" mid="79691776" long="61865984"/>
    <limit name="write" current="104857600" short="87031808" mid="79691776" long="61865984"/>
  </user>

[root@server ~]# dbctl list
 user             cpu(%)                     read(MB/s)                        write(MB/s)
USERNAME          400/380/348/300            100/83/76/59                      100/83/76/59

Useful links

https://docs.cloudlinux.com/cloudlinuxos/cloudlinux_os_components/#mysql-governor-limits