MySQL configuration

From ISPWiki
Jump to: navigation, search
Hierarchy: VMmanager KVM -> OTHER
VMmanager Cloud -> OTHER

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 configuration

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

MySQL allocated 4GB RAM

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 allocated 8GB RAM

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

Install MySQLTuner:

yum install mysqltuner

Upload:

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

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

or

mysqltuner --user root --pass rootpassword

Start MySQLTuner id if needed:

perl mysqltuner.pl

or

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