Optimiza y tunea my.cnf (Mysql o MariaDB) en tu servidor VPS

Imaginemos la situación. Acabas de mudar todas tus webs de un servidor compartido (Shared server) a un Servidor Virtual dedicado (VPS) y te encuentras que tienes que optimizar un monton de cosas, entre ellas tu base de datos Mysql o MariaDB que usabas en tus blogs de WordPress, en Drupal, Joomla o Moodle. ¿Por donde empezamos?

Preguntas que te tienes que hacer para optimizar tu base de datos

1. ¿Cuanta RAM tenemos en nuestro servidor? Factor clave y determinante para la optimización de nuestra base de datos Mysql
2. ¿Qué es lo que tengo que optimizar en Mysql? Principalmente el archivo my.cnf que encontraremos en /etc/mysql/my.cnf o /etc/my.cnf despendiendo del Sistema Operativo.
3. ¿Como lo edito? A través de terminal mediante una conexión ssh al servidor y gracias al editor de unix ‘vi’ o ‘vim’
4. ¿Que cantidad de memoria RAM puede consumir Mysql? En función de los parametros que configuremos en my.cnf. Podemos hacernos una idea con esta calculadora de memoria RAM para Mysql
5. Leemos esta biblia de optimización de MySQL, para comprender el significado de cada parametro.

Ejemplos de archivos de configuración my.cnf en función de la memoria RAM

Antes de empezar a hacer ningun cambio, sería mejor culturizarnos un poco. Normalmente, en nuestro servidor, podemos encontrar ejemplos de archivos my.cnf en función de la RAM que tengamos disponible:

Dirigete a: /usr/share/doc/ o /usr/share/ y busca el directorio de MariaDB o MySQL. En el vas a encontrar algo de documentación al respecto.

• my-small.cnf: para sistemas con 64MB de RAM (Como!!!)
• my-medium.cnf: para sistemas con 256MB de RAM
• my-large.cnf: para sistemas con 512MB de RAM
• my-huge.cnf: para sistemas con 1-2GB de RAM

Para empezar no esta mal y nos podemos hacer una idea de cuales son los parametros más importantes a ajustar. ¿Y si tu sistema tiene 4GB de RAM o más? Puedes seleccionar el archivo my-huge.cnf y empezar a multiplar algunos de sus valores por 2… Hay que ir probando.

Cómo comprobar el uso de memoria RAM de nuestro servidor

Mucho ojo conesto. En nuestro servidor VPS tenemos más cosas funcionando que «chupan» memoria RAM (Apache, PHP etc…). No podemos pensar que tenemos toda la memoria RAM disponible para el servidor de base de datos. Comprobamos el uso que nuestro VPS hace de la memoria con los siguientes comandos en el terminal:

• top
• ps aux
• free -m

Aspecto del achivo my.cnf de MySQL y MariaDB

Nos familiarizamos con el aspecto que tiene un archivo my.cnf

[mysqld]
safe-show-database
open_files_limit = 5000
tmp_table_size = 95M

max_heap_table_size = 95M

query_cache_limit=2M

query_cache_size=55M ## 64MB for every 1GB of RAM

query_cache_type=1

max_connections=100

collation_server=utf8_unicode_ci

character_set_server=utf8

delayed_insert_timeout=40

interactive_timeout=10

wait_timeout=30

connect_timeout=20

thread_cache_size=64

key_buffer=16M ## 32MB for every 1GB of RAM

key_buffer_size=40M

join_buffer=1M

max_connect_errors=20

max_allowed_packet=8M

table_cache=2048

table_definition_cache=2048

record_buffer=1M

sort_buffer_size=1M ## 1MB for every 1GB of RAM

read_buffer_size=1M ## 1MB for every 1GB of RAM

read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM

thread_concurrency=2 ## Number of CPUs x 2

myisam_sort_buffer_size=16M

innodb_file_per_table=1

innodb_buffer_pool_size=35M

La verdad es que no es facil optimizarlo… ¿Cuales son las variables más importantes a optimizar en una base datos para un servidor VPS?

Pues depende. Si estamos usando MyISAM o InnoDB. En el caso de MyISAM:

• max_connections
• wait_timeout
• thread_cache_size
• table_cache
• key_buffer_size
• query_cache_size
• tmp_table_size

En el caso de InnoDB:

• innodb_buffer_pool_size
• key_buffer_size
• innodb_log_file_size

Os recomeidno consultar esta página web: wiki.mikejung.biz

¿Donde encontramos más ayuda para tunear Mysql?

Afortunadamente hay varios programas que nos pueden ayudar a optimizar el archivo my.cnf de Mysql (lo mejor es usar los tres y comparar resultados):

• MySQLTuner
• Tuning Primer

Nos descargamos estos programas en nuestra carpeta /usr/local/sbin/, les damos los permisos para que se puedan ejecutar (entrando en el servidor como root) y nos preparamos para el despliegue de datos.

• Tutorial: Tuning MySQL Performance with MySQLTuner
• Significado de los datos obtenidos con mysqlreport
• Tuning Primer es el ofrece los datos más claros y los consejos más especificos

¡Suerte a todos! Tened en cuenta que hay que ejecutar estos programas entre 24 y 48 horas despues de haber hecho los primeros cambios en my.cnf. Dejamos pasar ese tiempo, los volvemos a ejecutar y volvemos a optimizar la bbdd editando my.cnf.

¡OJO! Después de editar el archivo hay que reiniciar Mysql a través del terminal o del panel WHM o similar, sino, los cambios en el archivo no se verán reflejados.