MySQL:
MySQL is the SQL Database Management System and it is open source. It is used to develop different web-based software applications which is the best RDBMS. A Swedish company MySQL AB developed, marketed and supported this.

Features:
It consists of two various editions:

The open source MySQL Community Server
The proprietary Enterprise Server.
MySQL Enterprise Server is varied by a sequence of tenancy extensions that install as server plugins, other than that it distribute the version numbering system and is made from a similar code base.

MySQL Governor:
The software which is used to view and restrict MySQL usage in the shared hosting atmosphere is known as MySQL Governor. Used resources utilized census per each MySQL thread for monitoring. It just exterminates slow SELECT queries.

It consists of several modes of operations, based on the configuration. It will work on monitor-only mode. The modes of operation are listed below

Modes of operation:
Off — Monitor Only:
Here, MySQL Governor doesn’t choke clients’ queries, but it allows us to view the MySQL usage to watch the victimizer at any time. This will help to just start and want to see what is going on.

Single — Single restricted’s LVE for all restricted customers:
Once a customer reaches the described limits in the MySQL Governor, it will run every client’s query inside LVE with id 3. It denotes that when 5 customers are blocked at a time, all queries for all those 5 customers will be sharing the same LVE. When blocked customers increased- it will provide fewer resources to them.

Abusers:
In this mode, when users across limits described in the MySQL Governor, all client’s queries will assassinate inside that user’s LVE. This mode will help when the site is still fast, but MySQL is slow for a specific client. In the case of abusing MySQL, it leads to queries to share LVE with PHP processes, and PHP processes will also be attacked, results in fewer new queries being sent to MySQL and requires a dbuser-map file.

all – used to run queries inside user’s LVE:
No need for split limits for MySQL here. Based on the future, we have to use it as a primary way of operating MySQL Governor. The main advantage is that limits are applied to both PHP & MySQL at the same time, every time, preventing any spikes what so ever. Requires dbuser-map file.

If a dbuser-map file is not present on the server, the ” abusers ” mode emulates ” single “.

With single and abuser mode, once the user is blocked, the queries for that user will be reduced as long as the user is using more than limits specified. After a minute that the user is using less, we will allow that user.

Features of MySQL Governor:
Stable, simple limits counted as part of LVE limits
Automatic detection and killing of long-running queries
MySQL 5.1 – 5.7 support
MariaDB 5.x and 10.x support
Installation of MySQL Governor:
It consists of two steps:

Installing governor-MySQL itself
Switching to MySQL packages provided with CloudLinux OS
yum install governor-MySQL
/usr/share/lve/dbgovernor/mysqlgovernor.py --install
It consists of several operational modes: monitor only – sets every client to reach their limits into single predefined LVE. Always set MySQL queries into users LVE when MySQL Governor restricts to reach the default mode of operation.

Use the ‘dbctl list’ command to verify MySQL Governor limits. Here, we can obtain the user’s CPU, read and write limits. Each limit consists of four values for different time ranges. It should be 1 minute, 15 minutes, 1 hour and 1 day.

We can view MySQL usage with ‘debtor’ utility.

The ‘dbctl’ is required to modify mysql-governor configuration, for example modifying CPU and read values for the usertest1 user must be done with the command like below:

dbctl set usertest1 --cpu=150,100,70,50 --read=2048,1500,1000,800
Now verify the limits with the dbctl list:

dbctl list | grep usertest1

Admin can restrict any user by self with ‘dbctl restrict username’ command.

When queries of clients cross the limits, they are restricted by MySQL Governor. Such users index can be collected from ‘dbctl list-restricted’ command. The admin can unblock any client with ‘dbctl unrestricted’ command. Also, you may assure any user and protect them using ‘dbctl ignore username’ command.

We hope that this tutorial will be helpful to know about MySQL Governer and its features. For more information, like us on social media such as Facebook and Twitter. For video tutorials, subscribe to our YouTube channel “Dedicated server”