Hoje vamos falar novamente e novamente sobre mySQL. Vamos entender a otimização e falar sobre muitos parâmetros do servidor.
Vamos começar.
Começar
O servidor que deixamos estar no CentOS . Otimizará o método de edição de configuração do my.cnf .
Definir alguns parâmetros pode melhorar o
desempenho do banco de dados do servidor várias vezes!
Para começar, vamos decidir o que estamos otimizando em geral - isto é, quantas tabelas em que mecanismo temos, que peça de hardware temos e sob quais parâmetros ajustaremos tudo.
Para isso, tomamos o htop (como uma ferramenta bonita e intuitiva):
yum install htop
Derive htop :
htop
Temos algo assim:
escreva-se no my.cnf :
# 3 , 4
Agora vamos descobrir o número de tabelas e seus tipos.
Para isso, usamos o sintonizador mysql :
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Vamos correr:
perl mysqltuner.pl
Conclusão aproximadamente:
vamos escrever para nós mesmos em my.cnf :
# 64M myisam, 770M innoDB
Uma configuração típica é geralmente recomendada assim:
[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
Agora vamos descobrir o que otimizaremos aqui, por que, como e por quê (especialmente por que esses parâmetros não são suficientes.
Otimização e configuração
Primeiro, você pode rolar até o final da saída do sintonizador mysql e ver o que ele recomenda. Em nosso caso, é mais ou menos assim:
wget
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl
Não nos envolveremos em substituições estúpidas e examinaremos os parâmetros do mysql , que podem ser de interesse para nós em primeiro lugar. O que é o quê:
o skip-external-locking , - remove o bloqueio externo, que é mais rápido;
-nome-o ignora o RESOLVE , - permite que o MySQL evite a resposta para solicitar a verificação de DNS da conexão do cliente com o servidor MySQL .
Assim, o servidor MySQL usará apenas URLs de
IP em vez de nomes de host um pouco, mas mais rápido.
binlog_cache _ size, - o tamanho do cache para armazenar mudanças no log binário. Define o tamanho apenas para o cache de transação. Faça 100M - não é mais necessário.
innodb_stats_on_metadata = 0 (OFF) , - para acelerar com
INFORMATION_SCHEMA, SHOW TABLE STATUS ou SHOW INDEX desabilita a atualização de estatísticas para funções como
quer y _cache_size = 128M e query_sache_type
= 1 , - requisita caches. 1 - habilitado em princípio, limite de 128M . Não
recomendado para ser colocado acima de 256M , considerando que pode levar a um bloqueio.
Como temos mais de tabelas InnoDB , ele elimina o cache _ size bed .
Com a versão MySQL 5.6 query_cache_size desativada e com a versão 8.0, excluída por
padrão, todas as tabelas e índices são armazenados em um único arquivo, então usamos innodb_file_per_table = 1.
O valor innodb_open_files e table_open_cache - é recomendado definir ambas as opções em 4096 ou 8192 . A geralmente calculado como o número de tabelas em todas as bases multiplicado por 2 , aproximadamente.
Ao trabalhar com InnoDB é o parâmetro mais importante innodb_buffer_pool_size , é definido no princípio de "quanto mais, melhor." Recomenda-se alocar até 70-80% da RAM do servidor.
innodb_log_file_size - afeta a velocidade de gravação, define o tamanho do log de operação (as operações são primeiro gravadas no log e depois aplicadas aos dados no disco). Quanto maior o log, mais rápido os registros funcionarão (já que há mais registros no arquivo de log). Sempre há dois arquivos e seus tamanhos são os mesmos. O valor do parâmetro define o tamanho de um arquivo.
!️ innodb_log_file_size MySQL, ib_logfile-n ( /var/lib/mysql/), innodb_log_file_size MySQL.
MySQL - .
A instalação de um innodb_log_file_size de tamanho grande pode levar a um aumento no desempenho, mas ao mesmo tempo aumentará o tempo de recuperação, selecione de 256M a 1G .
innodb_log _ buffer_size - tamanho do buffer de transação. Geralmente, é recomendado não aplicar se não estiver usando BLOB e TEXT grande.
innodb_flush _ method , - define a lógica para liberar dados para o disco. Em sistemas modernos que usam RAID e sites de backup, você escolherá entre ODSYNCe ODIRECT , - o primeiro parâmetro é mais rápido, o segundo mais seguro.
_ size bed key_buffer - buffer para trabalhar com chaves e índices, e sort_buffer - buffer para classificação. Se você não estiver usando tabelas MyISAM , é recomendado que você defina key_buffer_size para 32 MB para armazenar índices de
tabelas temporárias .
O parâmetro thread_cache _ size indica o número de threads (threads), deixando o cache quando um cliente se desconecta. Com uma nova conexão, o encadeamento não é criado, mas retirado do cache, o que economiza recursos sob cargas pesadas.
innodb_flush_log_attrx_commit , - pode aumentar a taxa de transferência de registros de dados em centenas de vezes a base. Ele determina se o Mysql irá despejar cada operação no disco (em um arquivo de log).
innodb_flush_log_at_trx_commit = 1 é usado para casos
em que a retenção de dados - é a prioridade número um.
innodb_flush_log_at_trx_commit = 2 para os casos em que uma pequena perda de dados não é crítica. Também há 0 (zero) - a opção mais produtiva, mas não segura.
max_connections - se você receber o erro "Muitas conexões",esta opção deve ser aumentada. E, portanto, não há grande benefício na otimização a partir disso.
O número de arquivos de entrada / saída em fluxos InnoDB opções especificadas innodb_read_io_threads , innodbwrite_io_threads , este parâmetro é geralmente definido como 4 ou 8 , o fast-ROM definido no SSD 16. Significado innodb_thread_concurrency define o número de núcleos * 2 .
A configuração é assim:
[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
E por último, você pode ver as recomendações do sintonizador e segui-las.
Conclusão
Aqui está uma configuração tão interessante produzida. Se você achar difícil, em primeiro lugar você deve usar uma calculadora mySQL , que irá lhe dizer os principais parâmetros e permitir que você não vá além da memória disponível - afinal, tudo depende disso:
Obrigado pela atenção. Junte-se à discussão.