Today we will talk again and again about mySQL. Let's understand optimization and talk about many server parameters.
Let's get started.
Start
The server we let it be on the CentOS . Will optimize the method of editing config the my.cnf .
Setting some parameters can increase the
performance of the server database several times!
To begin with, let's define what we are generally optimizing - that is, how many tables on which engine we have, what piece of hardware we have, and under what parameters we will adjust the whole thing.
For this we take htop (as a beautiful and intuitive tool):
yum install htop
Derive htop :
htop
We get something like this:
write yourself in the my.cnf :
# 3 , 4
Now let's find out the number of tables and their types.
For this we take the mysql tuner :
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Let's run:
perl mysqltuner.pl
Conclusion approximately:
Let's write to ourselves in my.cnf :
# 64M myisam, 770M innoDB
A typical config is usually recommended like this:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 0
bind-address = 127.0.0.1
skip-external-locking
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 <a
href="https://ruhighload.com/query_cache_size+%d0%bf%d0%b0%d1
%80%d0%b0%d0%bc%d0%b5%d1%82%d1%80+%d0%b2+mysql"
target="_blank" style="color: rgb(232, 95, 99);">query_cache_size</a>
= 0 slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
Now let's figure out what we will optimize here, why, how and why (especially why these parameters are not enough.
Optimization and config
First, you can scroll to the bottom of the mysql tuner output and see what it recommends. In our case, it looks something like this:
wget
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl
We will not engage in mindless substitution, and go through the parameters of the mysql , which may be of interest to us in the first place. What's what:
the skip-external-locking , - removes the outer lock, which is faster;
-name-the skip the RESOLVE , - allows MySQL to avoid the answer to request DNS checking client connection to the server MySQL .
Thus, the server MySQL will use only
IP URLs instead of hostnames that a little bit, but faster.
binlog_cache _ size, - the size of the cache to store changes in the binary log. Sets the size for the transaction cache only. Do 100M - is no longer needed.
innodb_stats_on_metadata = 0 (OFF) , - to accelerate with
INFORMATION_SCHEMA, SHOW TABLE STATUS or SHOW INDEX disable updating statistics for functions such
quer y _cache_size = 128M and query_sache_type
= 1 , - request caches. 1 - in principle enabled, 128M limit. Not
recommended to be put above 256M , considering it can lead to blockage.
Since we have more than InnoDB tables, it vanishes cache _ size bed .
With version MySQL 5.6 query_cache_size off, and with version 8.0, deleted by
default, all tables and indexes are stored in a single file, so we use innodb_file_per_table = 1.
The value innodb_open_files and table_open_cache - it is recommended to set both options in 4096 or 8192 . A generally calculated as the number of tables in all bases multiplied by 2 , approximately.
When working with InnoDB is the most important parameter innodb_buffer_pool_size , it is set on the principle of "the more, the better." It is recommended to allocate up to 70-80% of the server's RAM.
innodb_log_file_size - affects the write speed, sets the size of the operation log (operations are first written to the log and then applied to the data on the disk). The larger this log, the faster the records will work (since there are more of them in the log file). There are always two files, and their size is the same. The parameter value sets the size of one file.
!️ innodb_log_file_size MySQL, ib_logfile-n ( /var/lib/mysql/), innodb_log_file_size MySQL.
MySQL - .
Installing a large size innodb_log_file_size may lead to an increase in performance, but at the same time will increase recovery time, select from 256M to 1G .
innodb_log _ buffer_size - size of the transaction buffer. It is generally recommended not to apply if not using BLOB and TEXT large.
innodb_flush _ method , - defines the logic for flushing data to disk. In modern systems using RAID and backup sites, you will choose between ODSYNCand ODIRECT , - the first parameter is faster, safer second.
_ size bed key_buffer - buffer to work with keys and indexes, and sort_buffer - buffer for sorting. If you are not using MyISAM tables, it is recommended that you set the key_buffer_size to 32MB for storing temporary
table indexes .
Parameter thread_cache _ size indicates the number of threads (threads), leaving the cache when a client disconnects. With a new connection, the thread is not created, but taken from the cache, which saves resources under heavy loads.
innodb_flush_log_attrx_commit , - can increase the throughput of data records in the hundreds of times the base. It determines whether Mysql will dump each operation to disk (to a log file).
innodb_flush_log_at_trx_commit = 1 is used for cases
when the data retention - is the number one priority.
innodb_flush_log_at_trx_commit = 2 for the cases when a small data loss is not critical. There is also 0 (zero) - the most productive, but unsafe option.
max_connections - if you get the error "Too many connections",this option should be increased. And so there is no great benefit in optimization from it.
The number of input / output files in InnoDB streams specified options innodb_read_io_threads , innodbwrite_io_threads , this parameter is usually set to 4 or 8 , the fast-ROM set in the SSD 16. Meaning innodb_thread_concurrency set the number of cores * 2 .
The config is like this:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 0
bind-address = 127.0.0.1
skip-external-locking
skip-name-resolve
binlog_cache_size = 100M
thread_cache_size = 32
innodb_stats_on_metadata = OFF
query_cache_limit = 1M
query_cache_size = 0 query_cache_type = 1
innodb_buffer_pool_size = 3G
innodb_log_file_size = 256
innodb_log_buffer_size = 6M
innodb_additional_mem_pool_size = 16M
innodb_flush_method = O_DSYNC
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency = 6
innodb_file_per_table = 1
key_buffer_size = 32M
tmp_table_size = 64M
max_connections = 350
sort_buffer_size = 16M read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 8M
thread_stack = 1M
binlog_cache_size = 8M
tmp_table_size = 128M
table_open_cache = 2048
[mysqldump] quick
quote-names
max_allowed_packet = 16M
And finally, you can see the recommendations of the tuner and follow them.
Conclusion
Here is such an interesting config turned out. If you find it difficult, then at first you should use a mySQL calculator, which will tell you the main parameters and allow you not to go beyond the available memory - after all, everything depends on it:
Thank you for your attention. Join the discussion.