15 consells útils per a l'optimització i l'optimització del rendiment de MySQL/MariaDB

MySQL és un potent sistema de gestió de bases de dades relacionals de codi obert o, en resum, RDBMS. Va ser llançat l'any 1995 (20 anys). Utilitza un llenguatge de consulta estructurat, que és probablement l'opció més popular per gestionar contingut dins d'una base de dades. L'última versió de MySQL és la 5.6.25 i es va publicar el 29 de maig de 2015.

Un fet interessant sobre MySQL és el fet que el nom prové de la filla My de Michael Widenius (creador de MySQL). Tot i que hi ha molts fets interessants sobre MySQL, aquest article pretén mostrar-vos algunes pràctiques útils per ajudar-vos a gestionar el vostre servidor MySQL.

L'abril de 2009 el projecte MySQL va ser comprat per Oracle. Com a resultat, es va crear una forquilla de comunitat MySQL anomenada MariaDB. El motiu principal per crear la bifurcació va ser mantenir el projecte lliure sota la Llicència Pública General.

Avui MySQL i MariaDB són un dels RDBMS més utilitzats (si no el més) utilitzat per a aplicacions web com WordPress, Joomla, Magento i altres.

Aquest article us mostrarà alguns consells bàsics però útils com optimitzar el rendiment de MySQL/MariaDB. Tingueu en compte que aquest article suposa que ja teniu instal·lat MySQL o MariaDB. Si encara us esteu preguntant com instal·lar-los al vostre sistema, podeu seguir les nostres guies exhaustives aquí:

  1. Instal·lació de LAMP a RHEL/CentOS 7
  2. Instal·lant LAMP a Fedora 22
  3. Configuració de LAMP a Ubuntu 15.04
  4. Instal·lant MariaDB a Debian 8
  5. Instal·leu MariaDB a Gentoo Linux
  6. Instal·leu MariaDB a Arch Linux

Important: abans de començar, no accepteu aquests suggeriments a cegues. Cada configuració de MySQL és única i requereix una reflexió addicional abans de fer cap canvi.

Coses que has de saber:

  1. El fitxer de configuració de MySQL/MariaDB es troba a /etc/my.cnf. Cada vegada que modifiqueu aquest fitxer haureu de reiniciar el servei MySQL perquè els nous canvis tinguin efecte.
  2. Per escriure aquest article s'ha utilitzat la versió 5.6 de MySQL com a plantilla.

1. Activeu fitxers per taula d'InnoDB

En primer lloc, és important explicar que InnoDB és un motor d'emmagatzematge. MySQL i MariaDB utilitzen InnoDB com a motor d'emmagatzematge predeterminat. En el passat, MySQL solia mantenir taules i índexs de bases de dades en un espai de taula del sistema. Aquest enfocament estava pensat per a servidors que únic objectiu és el processament de bases de dades i el seu disc d'emmagatzematge no s'utilitza per a cap altre propòsit.

L'InnoDB proporciona un enfocament més flexible i la informació de cada base de dades es guarda en un fitxer de dades .ibd. Cada fitxer .ibd representa un espai de taula propi. D'aquesta manera, les operacions de la base de dades com \TRUNCATE es poden completar més ràpidament i també podeu recuperar espai no utilitzat quan deixeu caure o trunqueu una taula de base de dades.

Un altre avantatge d'aquesta configuració és el fet que podeu conservar algunes de les taules de bases de dades en un dispositiu d'emmagatzematge independent. Això pot millorar molt la càrrega d'E/S als vostres discs.

L'innodb_file_per_table està habilitat per defecte a MySQL 5.6 i posterior. Ho podeu veure al fitxer /etc/my.cnf. La directiva té aquest aspecte:

innodb_file_per_table=1

2. Emmagatzema les dades de la base de dades MySQL en una partició separada

Nota: aquesta configuració només funciona amb MySQL, però no amb MariaDB.

De vegades, la lectura/escriptura del sistema operatiu pot alentir el rendiment del vostre servidor MySQL, especialment si es troba al mateix disc dur. En canvi, recomanaria utilitzar un disc dur separat (preferiblement SSD) per al servei MySQL.

Per completar-ho, haureu de connectar la nova unitat al vostre ordinador/servidor. Als efectes d'aquest article, suposaré que la unitat estarà a /dev/sdb.

El següent pas és preparar la nova unitat:

# fdisk /dev/sdb

Ara premeu \n per crear una nova partició. A continuació, premeu \p per fer que la nova partició sigui primària. Després d'això, configureu el número de partició de l'1 al 4. Després d'això, seleccionareu la mida de la partició. Premeu Intro aquí. Al següent pas, haureu de configurar la mida de la partició.

Si voleu utilitzar tot el disc, premeu Intro una vegada més. En cas contrari, podeu configurar manualment la mida de la nova partició. Quan estigui llest, premeu \w per escriure els canvis. Ara haurem de crear un sistema de fitxers per a la nostra nova partició. Això es pot fer fàcilment amb:

# mkfs.ext4 /dev/sdb1

Ara muntarem la nostra nova partició en una carpeta. He anomenat la meva carpeta \ssd i he creat al directori arrel:

# mkdir /ssd/

Estem preparats per muntar la nova partició que acabem de fer a la nova carpeta:

# mount /dev/sdb1  /ssd/

Podeu realitzar el muntatge a l'inici afegint la línia següent al fitxer /etc/fstab.

/dev/sdb1 /ssd ext3 defaults 0 0

Ara ja esteu preparat per moure MySQL al disc nou. Primer atureu el servei MySQL amb:

# service mysqld stop

Us recomanaria que atureu Apache/nginx també per evitar qualsevol intent d'escriure a les bases de dades:

# service httpd stop
# service nginx stop

Ara copieu tot el directori MySQL a la nova unitat:

# cp /var/lib/mysql /ssd/ -Rp

Això pot trigar una estona depenent del lloc de les vostres bases de dades MySQL. Un cop finalitzat aquest procés, canvieu el nom del directori MySQL:

# mv /var/lib/mysql /var/lib/mysql-backup

A continuació, crearem un enllaç simbòlic.

# ln -s /ssd/mysql /var/lib/mysql

Ara ja esteu preparat per iniciar el vostre servei web i MySQL:

# service mysqld start
# service httpd start
# service nginx start

En aquest punt, s'accedirà a les vostres bases de dades MySQL des de la nova unitat.