MySQL Optimization

Often on virtual servers there is a problem with the database load or the slow operation of the database, so we will give an example of optimizing the MySQL settings. The database configuration file is located at:

/etc/my.cnf
/etc/mysql/my.cnf

Remember that the parameters are configured for both MyISAM tables and InnoDB. The main parameters that the config uses to configure for the server:
max_allowed_packet — since data between the server and the client is transferred in packets, this parameter specifies its maximum size.
key_buffer_size and sort_buffer_size — these parameters are configured to work with keys and buffer sorts. It should be set to 30-40% of the available RAM. For example, if the server uses 16 GB of memory, then we will set this parameter to 128M-256M.
max_connections — the parameter specifies the maximum number of connections to the database, that is, the number of clients that can simultaneously work with the server.
innodb_buffer_pool_size — a parameter for InnoDB, which is usually set to 70-80% of the server's RAM for the buffer memory size.
query_cache_size — parameter for the cache, the recommended value is specified up to 512 MB, since large cache maintenance can create a load.
table_open_cache — the number of tables that are cached.
innodb_log_file_size — the maximum size of the log file, which should be specified as 25% of the RAM.
innodb_log_buffer_size — the size of the log buffer itself.
join_buffer_size — a parameter that is used in table join operations and specifies the buffer size for this operation.
open_files_limit — the number of open files, by default set to 1024

Let's give an example of more optimal configs with different memory sizes.

For 1GB:

max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 512M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 132
query_cache_size = 0
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M

For 2GB:

max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 1024M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 136
query_cache_size = 0
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M

For 2GB:

max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 2048M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 144
query_cache_size = 0
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M

You can also use the MySQLTuner utility to optimize the database: https://github.com/major/MySQLTuner-perl

To download, use the command:
wget http://mysqltuner.pl -O tuner.pl --no-check-certificate

Run it to scan and check:
perl tuner.pl

The output will show what can be increased and adjusted, for example:

Variables to adjust:

*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
performance_schema = ON enable PFS
innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances (=1)

Pay attention to the output section:
[!!] Maximum possible memory usage:
Here is how much RAM Mysql can take, if it is 90% - this is not normal (of course, if it is not a server with only mysql). You need to achieve a figure of 40-60%, depending on the RAM consumption of the web server. To do this, you need to reduce the parameters for the buffers: key_buffer_size , innodb_buffer_pool_size , innodb_buffer_pool_instances ...
With each change, you need to restart mysql to apply the settings.

An important point is to check the database for integrity, since the tables can be damaged due to some incorrect queries and operations. Therefore, we can use the mysqlcheck method to check, repair and optimize the tables in the database.
To perform the procedure of checking all tables in the database, execute the command:

mysqlcheck -u root -p --check --all-databases

To repair tables, execute the command:

mysqlcheck -uroot -p --repair --all-databases

If you need to optimize tables:

mysqlcheck -u root -p --optimize --all-databases

To restore one database, execute the command:

mysqlcheck -r --databases NAME_BASE -u root -p

You can also check and restore tables using myisamchk. Unlike mysqlcheck, it does not require a password for the database.
To identify all damaged tables, execute the command:

myisamchk /var/lib/mysql/{database}/*

To repair all tables in the database, execute:

myisamchk -ri /var/lib/mysql/{database}/*

This completes the simple MySQL optimization.