~sergio

Optimizar o rendemento do MySQL

24-02-2023

O obxectivo é mellorar o rendemento de un servidor MySQL sometido a carga mediante, maiormente, a configuración dos parámetros para caché e buffering. Estos parámetros configuranse no arquivo my.cnf.

Parámetros de optimización

• query_cache_type: Sirve para activar ou desactivar a caché, si o poñemos a 0 desactivamos o cache de consultas do MySQL, si o poñemos 1 activamos o caché de consultas e si o poñemos a 2 activarase baixo petición. O recomendable é 1.
• max_allowed_packet: Este parámetro especifica o tamaño máximo de un paquete á hora de que o servidor MySQL traballe con él. Este parámetro normalmente tense que aumentar para importar bases de datos grandes ou mover grandes volúmenes de datos en unha base de datos.
• query_cache_size: Este parámetro especifica o tamaño da caché de consultas, esta caché garda en RAM e normalmente pónselle 64 MB de RAM por cada 1 GB de memoria física usable que teña o servidor.
• key_buffer_size: Este parámetro especifica o tamaño da caché dos índices, canto mais grande sexa esta cache, mais rápido se executarán os comandos SQL e mais rápido se obterá unha resposta do servidor MySQL. Normalmente configuranse 32 MB por cada 1 GB de memoria física usable.
• table_cache: Especifica o máximo de tablas abertas entre todos os fios de execución do MySQL, un bon valor é 64, aínda que con MySQLTuner poderemos ver si necesitamos mais ou menos número de tablas abertas.
• sort_buffer_size: Con este parámetro configuramos o tamaño da caché de búsquedas do MySQL, o recomendable é configurar 1 MB por cada 1 GB de memoria RAM física disponible.
• read_buffer_size: Con este parámetro configuramos o tamaño da caché de lecturas do MySQL, o recomendable é configurar 1 MB por cada 1 GB de memoria RAM física disponible.
• read_rnd_buffer_size: Con este parámetro configuramos o tamaño da caché de lecturas usado despóis dunha acción de búsqueda ou ordenado, o recomendable é configurar 1 MB por cada 1 GB de memoria RAM física disponible.
• join_buffer_size: Con este parámetro configuramos o tamaño da caché de JOIN sin índices, o recomendable é configurar 1 MB por cada 1 GB de memoria RAM física disponible.
• thread_cache_size: É o número máximo de fios de execución que se poden cachear e rehusar, normalmente configurase entre 32 e 64 para un uso normal.
• tmp_table_size: Esta variable especifica o tamaño máximo de unha tabla temporal en RAM, cando se alcanza o tamaño máximo especificado en este parámetro a tabla pasa a ser unha tabla temporal en MyISAM.
• max_connections: Especifica o número máximo de conexiós totales que se poden aceptar no servidor MySQL ó mismo tempo.
• wait_timeout: É o tempo de espera que tarda MySQL en pechar unha conexión.
• thread_concurrency: Especifica o número máximo de fillos en execución ou procesos abertos de MySQL, o recomendable é configurar 2 por cada 1 núcleo de CPU disponible.
• query_cache_limit: Especifica un límite de tamaño de consulta a partir do cal non se cachearán, o valor por defecto es 1 MB. Si o límite é moi alto pódese chegar a saturar o servidor MySQL.
• innodb_buffer_pool_size: É unha variable que solo afecta a InnoDB, pero que mellora bastante o rendemento xeral das tablas almacenadas en InnoDB. Un bon valor de configuración sería un valor similar ó 70 ou 80% da memoria RAM disponible, pero depende tamén do tamaño da base de datos, si traballamos con bases de datos moi pequenas non ten sentido especificar un valor tan grande.

Optimización con MySQLTuner

Resulta que os servidores MySQL mentras están funcionando están almacenando certas estadísticas de uso e rendemento pero son moi complicadas de entender, aí é donde entra o MySQLTuner.

Antes de executar o MySQLTuner é recomendable que o servicio esteña funcionando con carga durante polo menos 24 horas, aínda que o bon serían dous ou tres días. Despóis deste tempo executamos o script e devolverá un report coas variables a modificar no my.cnf, sempre tendo coidado de non superar o explicado na sección anterior, si non poderase facer que o servicio MySQL se coma toda a RAM.

O MySQLTuner pódese coller do seu github. Para usalo, básicamente é descargalo e darlle permisos de execución:

wget http://mysqltuner.pl/ -O mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl