Hoy hablaremos una y otra vez sobre mySQL. Entendamos la optimización y hablemos de muchos parámetros del servidor.
Empecemos.
comienzo
El servidor lo dejamos estar en CentOS . Optimizará el método de edición de la configuración my.cnf .
La configuración de algunos parámetros puede mejorar el
rendimiento de la base de datos del servidor varias veces.
Para empezar, definamos lo que generalmente estamos optimizando, es decir, cuántas tablas en qué motor tenemos, qué pieza de hardware tenemos y bajo qué parámetros ajustaremos todo.
Para esto tomamos htop (como una herramienta hermosa e intuitiva):
yum install htop
Derivar htop :
htop
Obtenemos algo como esto: escríbete
en my.cnf :
# 3 , 4
Ahora averigüemos el número de tablas y sus tipos.
Para esto tomamos el sintonizador mysql :
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Corramos:
perl mysqltuner.pl
Conclusión aproximadamente: Escribámonos
en my.cnf :
# 64M myisam, 770M innoDB
Por lo general, se recomienda una configuración típica como esta:
[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
Ahora averigüemos qué optimizaremos aquí, por qué, cómo y por qué (especialmente por qué estos parámetros no son suficientes.
Optimización y configuración
Primero, puede desplazarse hasta la parte inferior de la salida del sintonizador de mysql y ver qué recomienda. En nuestro caso, se parece a esto:
wget
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl
No participaremos en una sustitución sin sentido y revisaremos los parámetros de mysql , que pueden ser de interés para nosotros en primer lugar. Qué es qué:
el bloqueo externo de salto , - elimina el bloqueo externo, que es más rápido;
-name-the omitir el RESOLVE , - permite que MySQL evite la respuesta para solicitar DNS que verifique la conexión del cliente al servidor MySQL .
Por lo tanto, el servidor MySQL usará solo
direcciones URL de IP en lugar de nombres de host un poco, pero más rápido.
binlog_cache _ size, - el tamaño de la caché para almacenar cambios en el registro binario. Establece el tamaño de la caché de transacciones únicamente. Haga 100M - ya no es necesario.
innodb_stats_on_metadata = 0 (OFF) , - para acelerar con
INFORMATION_SCHEMA, SHOW TABLE STATUS o SHOW INDEX deshabilite la actualización de estadísticas para funciones tales como consulta
y _cache_size = 128M y query_sache_type
= 1 , - solicite cachés. 1 - en principio habilitado, límite 128M . No se
recomienda ponerlo por encima de 256 M , ya que puede provocar un bloqueo.
Como tenemos más que tablas InnoDB , desaparece la caché _ size bed .
Con la versión MySQL 5.6 query_cache_size desactivada, y con la versión 8.0, eliminada por
defecto, todas las tablas e índices se almacenan en un solo archivo, por lo que usamos innodb_file_per_table = 1.
El valor innodb_open_files y table_open_cache - se recomienda configurar ambas opciones en 4096 o 8192 . A generalmente calculado como el número de tablas en todas las bases multiplicado por 2 , aproximadamente.
Al trabajar con InnoDB es el parámetro más importante innodb_buffer_pool_size , se establece en el principio de "cuanto más, mejor". Se recomienda asignar hasta un 70-80% de la RAM del servidor.
innodb_log_file_size : afecta la velocidad de escritura, establece el tamaño del registro de operaciones (las operaciones se escriben primero en el registro y luego se aplican a los datos del disco). Cuanto más grande sea este registro, más rápido funcionarán los registros (ya que hay más en el archivo de registro). Siempre hay dos archivos y su tamaño es el mismo. El valor del parámetro establece el tamaño de un archivo.
!️ innodb_log_file_size MySQL, ib_logfile-n ( /var/lib/mysql/), innodb_log_file_size MySQL.
MySQL - .
La instalación de un innodb_log_file_size de gran tamaño puede llevar a un aumento en el rendimiento, pero al mismo tiempo aumentará el tiempo de recuperación, seleccione de 256M a 1G .
innodb_log _ buffer_size - tamaño del búfer de transacciones. En general, se recomienda no aplicar si no usa BLOB y TEXT grandes.
innodb_flush _ method,: define la lógica para descargar datos en el disco. En los sistemas modernos que utilizan RAID y sitios de respaldo, elegirá entre ODSYNCy ODIRECT , - el primer parámetro es más rápido, segundo más seguro.
_ size bed key_buffer - búfer para trabajar con claves e índices, y sort_buffer - búfer para ordenar. Si no está utilizando tablas MyISAM , se recomienda que configure key_buffer_size en 32 MB para almacenar índices de
tablas temporales .
El parámetro thread_cache _ size indica el número de subprocesos (subprocesos) que abandonan la caché cuando un cliente se desconecta. Con una nueva conexión, el hilo no se crea, sino que se toma del caché, lo que ahorra recursos bajo cargas pesadas.
innodb_flush_log_attrx_commit , - puede aumentar el rendimiento de los registros de datos en cientos de veces la base. Determina si Mysql volcará cada operación en el disco (en un archivo de registro).
innodb_flush_log_at_trx_commit = 1 se utiliza para casos
en los que la retención de datos es la prioridad número uno.
innodb_flush_log_at_trx_commit = 2 para los casos en los que una pequeña pérdida de datos no es crítica. También hay 0 (cero), la opción más productiva pero insegura.
max_connections : si aparece el error "Demasiadas conexiones",esta opción debería incrementarse. Por tanto, la optimización no aporta grandes beneficios.
El número de archivos de entrada / salida en los flujos de InnoDB especificó las opciones innodb_read_io_threads , innodbwrite_io_threads , este parámetro generalmente se establece en 4 u 8 , el fast-ROM configurado en el SSD 16. Significado innodb_thread_concurrency establece el número de núcleos * 2 .
La configuración es así:
[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
Y finalmente, puedes ver las recomendaciones del sintonizador y seguirlas.
Conclusión
Aquí resultó una configuración tan interesante. Si le resulta difícil, al principio debe usar una calculadora mySQL , que le dirá los parámetros principales y le permitirá no ir más allá de la memoria disponible; después de todo, todo depende de ello:
Gracias por su atención. Únete a la discusión.