Using resource groups

You can restrict the queries to use only a certain number of system resources using the resource groups. Currently, only CPU time is a manageable resource represented by virtual CPU (VCPU), which includes CPU cores, hyperthreads, hardware threads, and more. You can create a resource group and assign the VCPUs to it. Apart from the CPU, the attribute to the resource group is thread priority.

You can assign a resource group to a thread, set the default resource group at the session level, or pass the resource group as an optimizer hint. For example, you want to run some queries (say, reporting queries) with lowest priority; you can assign them to a resource group that has minimum resources.

How to do it...

1. Set the CAP_SYS_NICE capability to mysqld:

shell> ps aux | grep mysqld | grep -v grep
mysql     5238  0.0 28.1 1253368 488472 ?      Sl   Nov19   4:04 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

shell> sudo setcap cap_sys_nice+ep /usr/sbin...