MySQL configuration

MySQL optimal configuration is required for correct operation of VMmanager, especially if the control panel works with a large number of virtual machines. Otherwise, the panel will hang and you will have troubles with managing and replicating MySQL database (if you run VMmanager Cloud).

The control panel doesn't modify MySQL. MySQL configuration is specific for every server depending on its resources and software applications, that's why they require manual configuration.

The configuration file of the MySQL server part is located in /etc/my.cnf.d/server.cnf.

Storage subsystem

We recommend that you use InnoDB as the storage subsystem.

Recommended configurations

The following is a list of the main parameters for MySQL optimization.

Note

You can use the following data, but for the ideal configuration of your system, you need to analyze MySQL performance on the real server and adjust the settings if needed.

MySQL is allocated 4GB RAM

Section [server]

table_open_cache = 256 # the maximum number of tables the server can keep open in any one table cache instance 
sort_buffer_size = 512K # the size of the buffer that is allocated when sorting MyISAM indexes
net_buffer_length = 4M # connection and thread buffer size for every client thread 
join_buffer_size = 256K # minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans
query_cache_size = 32M # cache size
query_cache_limit = 512K # maximum size of requests in cache
max_connections = 300 # maximum number of simultaneous connections to the server
innodb_buffer_pool_size = 3G # buffer pool size for InnoDB
innodb_additional_mem_pool_size = 4M # memory pool size for InnoDB that is used to keep information about internal data structure 
innodb_lock_wait_timeout = 60 # time in seconds a transaction will wait for a resource before “giving up”

MySQL is allocated 8GB RAM

Section [server]

table_open_cache = 512 # the maximum number of tables the server can keep open in any one table cache instance 
sort_buffer_size = 1M # the size of the buffer that is allocated when sorting MyISAM indexes
net_buffer_length = 8M # connection and thread buffer size for every client thread  
join_buffer_size = 512K # minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans
query_cache_size = 32M # cache size
query_cache_limit = 512K # maximum size of requests in cache
max_connections = 2000	# maximum number of simultaneous connections to the server
innodb_buffer_pool_size = 4G # buffer pool size for InnoDB
innodb_additional_mem_pool_size = 4M # memory pool size for InnoDB that is used to keep information about internal data structure 
innodb_lock_wait_timeout = 60 # time in seconds a transaction will wait for a resource before “giving up”

MySQLTuner

MySQLTuner is a script which analyses MySQL performance statistics and gives recommendations based on its results.

For better results, we recommend that you run the MySQL server at lease for 24-48 hours without changing its configuration. However, even in that case, you should analyze MySQLTuner recommendations.

Install MySQLTuner:

yum install mysqltuner

or upload:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.p

You may face the following errors during the upload process:

ERROR: cannot verify raw.githubusercontent.com's certificate, issued by '/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert SHA2 High Assurance Server CA': Unable to locally verify the issuer's authority. To connect to raw.githubusercontent.com insecurely, use `--no-check-certificate'.

Start the process with the --no-check-certificate key:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl --no-check-certificate

Start MySQLTuner if it was not installed:

mysqltuner

mysqltuner --user root --pass rootpassword

Start MySQLTuner id if needed:

perl mysqltuner.pl

perl mysqltuner.pl --user root --pass rootpassword