Tuning de un servidor MySQL

Traducción del original en catalán. Léela en: CatalàEnglish

Esta última semana he tenido algunos problemas de rendimiento con un servidor MySQL, de hecho, había en concreto una tabla que tenía hasta 53 millones de registros, a pesar de que su espacio tampoco era tan grande, alrededor de 4Gb, el acceso de lectura y escritura a la tabla era muy lento, sobre todo para funciones de filtrado un poco complejas.

Pues bien, cosas que he aprendido que quizá os pueden ir bien son:

  1. Hay que habilitar los 'slow_query_logs' y tener indexados todos los campos que aparecen después de los WHERE's, eso mejora considerablemente el rendimiento.
  2. Hay que incrementar el innodb_log_file_size hasta un 25% del valor del innodb_buffer_pool_size; eso reduce considerablemente el tiempo de escritura.
  3. Usar JFS, como sistema de ficheros del directorio 'data' del MySQL. Es sustancialmente más rápido que el ext3. Ejemplos de rendimiento.
  4. Poner los logs en un disco dedicado, diferente del disco que tiene los datos.
  5. Usar el parámetro 'noatime' en la partición que contiene los de datos del MySQL.
  6. Deshabilitar la partición de SWAP, usar un 'innodb buffer spool' bien grande, siempre pensando en la RAM disponible.
  7. Montar los directorio temporal de MySQL sobre una partición RAM.
  8. Reducir el key_buffer size a 1-2Mb si no se usan tablas MyISAM.
  9. Usar el kernel 2.6 con SMP con procesador multi-núcleo, es especialmente importante para aplicaciones multithread como el MySQL.
  10. Usar RAID0 (stripped disks) y usar un servidor de replicación para las copias de seguridad. Mejora los tiempos de I/O del disco, como es lógico.

7 comentarios

Los comentarios del blog antiguo, tal como se escribieron. Los comentarios están cerrados.

  1. pau

    Oriol, fa temps que no toco aquest tema a fons pero pel que respecte
    al primer punt, tinc algunes anotacions que podries ser interessants.

    Quan creem la taula és interessant indexar els camps tenin en compte
    com es faran les cerces i és més important el com.

    Per exemple si tenim una taula que sempre segueix un patró de cerca del tipus

    select * from foo where ( a y )

    Sera molt més optim indexar els dos camps a i b conjuntament, que no pas per
    separats

    Si tenim també un patró de ceca del tipus

    select * from foo where ( a y )

    Pero ja tenim un index del tipus a,b; crear un index amb només a sera redundant

    De fet la meva recomanació es utilitzar la directiva EXPLAIN on es podra utilitzar
    el selct per saber com far el mapping de claus tenin en compte el where.

  2. pau

    Ups veig que m'ha polit els signes major i menor !!! les sentencies són

    select * from foo where ( a _less_ x and b _greater_ y )

    select * from foo where ( a _less_ x )

  3. albert

    ostres, que interessant! gracies!

  4. santi

    Hola, no acabo de entender porque
    deshabilitar la partición de swap
    afecta al rendimiento del mysql.

    ¿Algún sitio donde lo explican?

    Gracias por el post

    Santi.

  5. Oriol Rius

    Això es fa per evitar que la RAM s'acabi i es comenci a usar disc com a RAM, o sigui, SWAP això faria que el rendiment del MySQL baixés en picat!

    Pensa que la diferència del temps d'accés a la RAM comparat amb el disc dur és abismal i per tant, usar SWAP és bona idea perquè un sistema no caigui però és mala idea perquè un sistema sigui òptim en rendiment.

  6. santi

    Pero, ¿entonces que ocurre si el sistema se queda sin RAM?
    ¿el mysql se para?
    ¿el kernel empieza a matar procesos
    para recuperarse?

    Santi.

  7. Oriol Rius

    No vas malament, la qüesió és que es pot quedar fregit per manca de recursos. Les conseqüències exactes serien difícils de saber a priori.

    El que has de fer és dimencionar bé els recursos i el sistema perquè això no passi mai. Que abans de passar això comencis a descartar feina.

    També pots instal·lar un monit i quan els recursos es vagin acabant et pot avisar, o directament prendre accions més directes com tancar temporalment l'entrada de 'packets'/feina o el que sigui per parar l'allau de feina.