my.cnf : optimització de mysql drupalcèntrica

Per començar cal dir que no hi ha una fòrmula universal, que sigui la millor per tothom (si no, ja no hi hauria un fitxer de configuració per personalitzar certs paràmtres). Les webs poden tenir usos molt diferents, que fa que hi hagi variables més o menys adequades. Per exemple, no és el mateix una web amb moltes connexions concentrades en breus periodes de temps, que si estan més distribuides. Ni connexions que entren i surten ràpidament (bots, consultes puntuals...) que connexions persistents (clients d'una botiga de comerç electrònic), etc.

La base de dades mysql, que drupal usa per defecte, te un fitxer per configurar alguns paràmetres: my.cnf que habitualment a linux es troba al directori de configuracions /etc/my.cnf

En aquest article provaré de recopilar algunes de les recomanacions i configuracions tipus que es poden trobar per Internet. Una bona idea és usar un dels arxius de configuració subministrats per mysql (els podeu trobar a /usr/share/mysql/) i anar variant els paràmetres segons el vostre context i els resultats que aneu observant. Us adjunto el fitxers.

Apunts previs:

• Per coneixer l'estat del vostre mysql, podeu usar l'ordre: "show status" +info
• Per coneixer les variables que s'estan usant: "show variables"
• Per canviar una variable podeu usar l'ordre "SET", per exemple: "SET GLOBAL thread_cache_size=96". + info
  • Moltes variables es poden canviar dinàmicament (sense reiniciar el servidor)
  • No oblideu igualment, quan trobeu el valor amb el que us trobeu a gust, modificar el fitxer my.cnf perquè mysql agafi bé el paràmetre el proper cop que arrenqui.
• Hi ha un script: http://day32.com/MySQL/ que fa una diagnosi de la configuració actual del mysqld i suggereix millores
  Per usar-lo cal posar-lo al directori /etc i executar-lo amb l'ordre "sh /etc/tuning-primer.sh" (adjunto el fitxer).

Paràmetres

thread_cache_size

• La mida del cache pels threads oberts...
• Quan una web te moltes connexions curtes és útil. Millora el consum de CPU de mysql
• Per mesurar l'ús de la cache de threads, podeu dividir el paràmetre threads_created / connections
• Un valor excessivament alt pot donar problemes de memòria
• Si els threads_created creixen ràpidament, ens indica la seva conveniència. En un funcionament normal, no haurien de creixer massa.

table_cache

• Un dels paràmetres més importants
• Si la opened_tables és massa alt, pot indicar-ne un valor massa baix
• La seva conveniència depén del nombre de taules i connexions
• Consumeix memòria i recursos.

key_buffer_size

• Un altre dels paràmetres importants
• Els valors de key_reads/key_read_request per sota de l'1% son "normals", i ens indiquen el percentatge d'us de la cache (en aquest cas, un 99%)

max_connections

• La memòria disponible hi està relacionada segons aquesta fòrmula:
  memory = key_buffer + (sort_buffer_size + read_buffer_size) * max_connections
  • Exemple: 64 + (1 + 1) * 200 = 464 MB
• La variable max_used_connections ens donarà una bona referència. Si el seu valor és baix, podem baixar max_connections deixant un marge de seguretat.

query_cache_size

• Si l'ús de la BBDD és molt accentuat en la lectura i no hi ha caches a nivell d'aplicacions pot ser convenient.
• Cal vigilar molt, perquè valors excessivament alts poden donar molt mal resultat per la saturació dels recursos. El millor és monitoritzar els canvis a veure com reacciona la BBDD.
• Hi ha certs usos en que l'ús de la cache és tant baix que es pot prescindir de la seva activació

log-slow-queries i log-queries-not-using-indexes

Si estem interessats en registrar les consultes lentes o que no facin servir els índexos, activarem aquests dos paràmetres:

• Primer ubiquem el fitxer:
  • Creem el directori /var/log/mysql: mkdir /var/log/mysql
  • Creem el fitxer log-slow-queries.log: touch /var/log/mysql/log-slow-queries.log
  • Modifiquem l'usuari i el grup per posar-lo a mysql: chown mysql.mysql -R /var/log/mysql
• Li treiem el permís de lectura i execució als usuaris que no siguin mysql, root o estiguin als seus grups, seguint les indicacions de seguretat que ens fan els nois de mysql. Això es pot fer amb laa comanda chmod -R o-r /var/log/mysql i  chmod -R o-x /var/log/mysql
• afegim al my.cnf dugues línies:
  • log-slow-queries=/var/log/mysql/log-slow-queries.log
  • log-queries-not-using-indexes
• Si no volem que el fitxer log creixi sense aturador, podem posar al cron l'ordre mysqladmin fluch-logs:
  • mv -f /var/log/mysql/log-slow-queries.log /var/log/mysql/log-slow-queries.vell
  • mysqladmin flush-logs

Aquests logs ens podran ajudar a optimitzar el codi, i si no podem fer-ho, a configurar paràmtres com el join_buffer_size, que ens ajuda a millorar el rendiment de consultes amb un join sense indexar.

Referències

• [HowTo] Optimising MYSQL (2005-2006)