Tuning a MySQL server

Translation of the original in Catalan. Read it in: CatalàCastellano

This last week I've had some performance problems with a MySQL server, in fact, there was in particular a table that had up to 53 million records, even though its space wasn't that big either, around 4Gb, read and write access to the table was very slow, above all for somewhat complex filtering functions.

Well then, things I've learned that maybe can be useful to you are:

  1. You have to enable the 'slow_query_logs' and have indexed all the fields that appear after the WHERE's, that considerably improves performance.
  2. You have to increase the innodb_log_file_size up to 25% of the value of the innodb_buffer_pool_size; that considerably reduces the write time.
  3. Use JFS, as the file system of the MySQL 'data' directory. It's substantially faster than ext3. Performance examples.
  4. Put the logs on a dedicated disk, different from the disk that has the data.
  5. Use the 'noatime' parameter on the partition that contains the MySQL data ones.
  6. Disable the SWAP partition, use a really big 'innodb buffer spool', always thinking about the available RAM.
  7. Mount the MySQL temporary directory on a RAM partition.
  8. Reduce the key_buffer size to 1-2Mb if MyISAM tables aren't used.
  9. Use the 2.6 kernel with SMP with a multi-core processor, it's especially important for multithread applications like MySQL.
  10. Use RAID0 (stripped disks) and use a replication server for the backups. It improves the disk I/O times, as is logical.

7 comments

Comments from the old blog, as they were written. Comments are closed.

  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.