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 montón de cosas, entre ellas tu base de datos MySQL o MariaDB que usabas en tus blogs de WordPress, en Drupal, Joomla o Moodle. ¿Por dónde empezamos?

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

1. ¿Cuánta RAM tenemos en nuestro servidor? Factor clave y determinante para la optimización de nuestra base de datos MySQL.
2. ¿Tu servidor cuenta con discos SSD? Suelen ser más rápidos y más eficientes en operaciones de lectura/escritura.
3. ¿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 dependiendo del Sistema Operativo.
4. ¿Cómo lo edito? A través de terminal mediante una conexión SSH al servidor y gracias al editor de UNIX ‘vi’ o ‘vim’.
5. ¿Qué cantidad de memoria RAM puede consumir Mysql? En función de los parámetros que configuremos en my.cnf. Podemos hacernos una idea con esta calculadora de memoria RAM para MySQL.
6. Leemos esta biblia de optimización de MySQL, para comprender el significado de cada parámetro.
7. ¿Qué motor interno usa tu base de datos? Puedes que sigas usando MyISAM, pero deberías pasarte a InnoDB.
8. Optimizamos apache también: configuración de Apache en función de la RAM de tu servidor y optimiza PHP-FPM.

Por eso, antes de ponerte a optimizar parámetros de tu base de datos al tuntún, tenemos que conocer el gasto de RAM de tu servidor. Luego podemos usar MySQLTuner.

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

Mucho ojo con esto. 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

Para hacernos la vida más sencilla, vamos a usar un programa gratuito que puedes descargar en tu servidor: ps_mem. Puedes descargarlo directamente, o puedes instalar con DNF (en mi caso porque uso Rocky Linux):

dnf install ps_mem

En mi caso, lo he descargado, y ejecuto directamente ps_mem.py con el siguiente comando (después de haberle dado permisos de ejecución si no los tiene con chmod +x ps_mem.py):

./ps_mem.py 

Y obtienes un resultado como este (para un total de memoria en el servidor de 16 GB):

Private  +   Shared  =  RAM used       Program

  3.1 MiB +   2.5 MiB =   5.7 MiB       systemd (3)
  7.4 MiB +   3.7 MiB =  11.1 MiB       rsyslogd
 11.5 MiB +  10.5 KiB =  11.5 MiB       ossec-analysisd
 18.6 MiB + 438.5 KiB =  19.1 MiB       backup.pl
 18.9 MiB + 270.5 KiB =  19.2 MiB       shellserver.pl
  4.4 MiB +  15.3 MiB =  19.7 MiB       perl
 19.7 MiB + 403.5 KiB =  20.1 MiB       lfd - sleeping
  9.0 MiB +  15.8 MiB =  24.8 MiB       systemd-journald
 17.5 MiB +  15.3 MiB =  32.8 MiB       miniserv.pl (2)
 35.5 MiB +  16.5 KiB =  35.5 MiB       ossec-syscheckd
 51.6 MiB +   7.0 MiB =  58.5 MiB       httpd (5)
195.0 MiB +   5.5 KiB = 195.0 MiB       inotifywait
309.0 MiB +  73.5 KiB = 309.1 MiB       memcached
  1.7 GiB + 123.5 KiB =   1.7 GiB       mariadbd
  3.6 GiB +   1.0 GiB =   4.6 GiB       php-fpm (219)
---------------------------------
                                         7 GiB
=================================

Si te fijas, ves que MariaDB está gastando unos 1,7 GB de memoria RAM. El resto lo tenemos localizado en el sistema operativo (menos de 1 GB) y en PHP-FPM con 4.6 GB.

También puedes usar este script bash:

Creas un archivo ejecutable llamado uso_memoria.sh (vi uso_memoria.sh) y metes lo siguiente:

#!/bin/bash

# Obtener el directorio actual
CURRENT_DIR=$(pwd)

# Archivo de salida del informe en el directorio actual
REPORT_FILE="$CURRENT_DIR/memory_usage_report.txt"

echo "Generando informe de uso de memoria del sistema..."

# Fecha y hora del informe
echo "Informe de uso de memoria - $(date)" > $REPORT_FILE

# Información de uso de la memoria con el comando free
echo -e "\n=============================" >> $REPORT_FILE
echo -e "Uso de la memoria (free -h):" >> $REPORT_FILE
echo -e "=============================\n" >> $REPORT_FILE
free -h >> $REPORT_FILE

# Extraer información específica sobre la memoria
MEM_INFO=$(free -h)
MEM_TOTAL=$(echo "$MEM_INFO" | grep Mem | awk '{print $2}')
MEM_USED=$(echo "$MEM_INFO" | grep Mem | awk '{print $3}')
MEM_FREE=$(echo "$MEM_INFO" | grep Mem | awk '{print $4}')
SWAP_TOTAL=$(echo "$MEM_INFO" | grep Swap | awk '{print $2}')
SWAP_USED=$(echo "$MEM_INFO" | grep Swap | awk '{print $3}')
SWAP_FREE=$(echo "$MEM_INFO" | grep Swap | awk '{print $4}')

echo -e "\nMemoria RAM:" >> $REPORT_FILE
echo -e "-------------" >> $REPORT_FILE
echo -e "Total: $MEM_TOTAL" >> $REPORT_FILE
echo -e "Usada: $MEM_USED" >> $REPORT_FILE
echo -e "Libre: $MEM_FREE\n" >> $REPORT_FILE

echo -e "Memoria Swap:" >> $REPORT_FILE
echo -e "-------------" >> $REPORT_FILE
echo -e "Total: $SWAP_TOTAL" >> $REPORT_FILE
echo -e "Usada: $SWAP_USED" >> $REPORT_FILE
echo -e "Libre: $SWAP_FREE\n" >> $REPORT_FILE

# Información detallada del uso de la memoria con el comando vmstat
echo -e "\n=============================" >> $REPORT_FILE
echo -e "Estadísticas de la memoria (vmstat):" >> $REPORT_FILE
echo -e "=============================\n" >> $REPORT_FILE
vmstat -s >> $REPORT_FILE

# Información del uso de la memoria virtual (swap)
echo -e "\n=============================" >> $REPORT_FILE
echo -e "Uso de la memoria virtual (swap):" >> $REPORT_FILE
echo -e "=============================\n" >> $REPORT_FILE
swapon --show >> $REPORT_FILE

# Información de los procesos que más memoria consumen
echo -e "\n=============================" >> $REPORT_FILE
echo -e "Procesos que más memoria consumen:" >> $REPORT_FILE
echo -e "=============================\n" >> $REPORT_FILE
ps aux --sort=-%mem | head -n 10 >> $REPORT_FILE

# Información de los procesos que están usando swap
echo -e "\n=============================" >> $REPORT_FILE
echo -e "Procesos que están usando swap:" >> $REPORT_FILE
echo -e "=============================\n" >> $REPORT_FILE
echo -e "PID\tSwap Usage\tCommand" >> $REPORT_FILE
for proc in $(find /proc -maxdepth 1 -regex "^/proc/[0-9]+")
do
    pid=$(basename $proc)
    swap=$(grep VmSwap $proc/status 2>/dev/null | awk '{ print $2 }')
    comm=$(grep Name $proc/status 2>/dev/null | awk '{ print $2 }')
    if [[ ! -z "$swap" && "$swap" -gt 0 ]]; then
        echo -e "$pid\t$swap kB\t$comm" >> $REPORT_FILE
    fi
done

echo "Informe de uso de memoria del sistema guardado en $REPORT_FILE"

Le das permisos de escritura con chmod +x uso_memoria.sh, y lo ejecutas con ./uso_memoria.sh. Obtienes algo como esto (lo he cortado que es muy largo):

Informe de uso de memoria - Wed Jun 12 01:09:31 PM CEST 2024

=============================
Uso de la memoria (free -h):
=============================

               total        used        free      shared  buff/cache   available
Mem:            15Gi       9.9Gi       2.2Gi       1.0Gi       6.2Gi       5.5Gi
Swap:          8.0Gi       1.4Gi       6.6Gi

Memoria RAM:
-------------
Total: 15Gi
Usada: 9.9Gi
Libre: 2.2Gi

Memoria Swap:
-------------
Total: 8.0Gi
Usada: 1.4Gi
Libre: 6.6Gi

Con toda esta información, ya puedes tomar decisiones informadas para tunar los parámetros de tu base de datos.

¿Dónde encontramos más ayuda para tunear MySQL?

Afortunadamente, hay varios programas que nos pueden ayudar a optimizar el archivo my.cnf de MySQL, como por ejemplo MySQLTuner.

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.

Al final del informe, verás que el programa te indica por donde puedes empezar a tunear la base datos. Ahora solo tienes que cambiar los datos en el archivo my.cnf.

• Tutorial: Tuning MySQL Performance with MySQLTuner
• Significado de los datos obtenidos con mysqlreport

Pasamos tablas de MyISAM o InnoDB

¡DATO! Es posible que te salga en el informe que tienes tablas en formato MyISAM y que deberías pasarlas a InnoDB. Hazlo. Toma una copia de seguridad de tu base de datos. Este es un comando particular que me ha indicado MySQLturner para cambiar algunas tablas de una instalación de WordPress (puedes ejecutarlo en PHPmyAdmin, por ejemplo):

ALTER TABLE NOMBRETABLA.wp_term_taxonomy ENGINE=InnoDB;

¿Un comando más general? Puedes utilizar WP-CLI si usas WordPress y tienes instalado este programa de gestión de WordPress en línea de comandos (recuerda hacer una copia de seguridad de tu base de datos antes de usarlo):

wp db query "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') FROM information_schema.tables WHERE table_schema = 'nombre_de_tu_base_de_datos' AND ENGINE = 'MyISAM';" | wp db query

1. wp db query: Ejecuta una consulta SQL en la base de datos de WordPress.
2. SELECT CONCAT…: Construye un conjunto de comandos ALTER TABLE para cada tabla que tiene el motor de almacenamiento MyISAM.
3. table_schema = ‘nombre_de_tu_base_de_datos’: Reemplaza nombre_de_tu_base_de_datos con el nombre de tu base de datos.
4. AND ENGINE = ‘MyISAM’: Filtra las tablas que están utilizando el motor de almacenamiento MyISAM.
5. | wp db query: Pasa el resultado de la primera consulta como entrada a otra llamada de wp db query, que ejecutará las instrucciones ALTER TABLE.

Optimizamos los parámetros de my.cnf de MySQL y MariaDB

• ¡Dato! También puedes encontrar la configuración de MariaDB, por ejemplo en Rocky Linux 9, en /etc/my.cnf.d/mariadb-server.cnf. Da lo mismo. MariaDB visitará todos estos puntos para encontrar la configuración. Queda tu elección.

Nos familiarizamos con el aspecto que tiene un archivo my.cnf (este es el mío para un servidor de 16 GB de RAM, 6 núcleos, discos SSD, con LAMP, y con varias instalaciones de WordPress):

[mysqld]
# Directorio de datos de MySQL
datadir = /var/lib/mysql
# Socket de MySQL
socket = /var/lib/mysql/mysql.sock
# Archivo de log de errores
log-error = /var/log/mariadb/mariadb.log
# Archivo PID de MySQL
pid-file = /run/mariadb/mariadb.pid
# Permite tener un archivo de datos separado para cada tabla InnoDB
innodb_file_per_table = 1
# Tamaño máximo de un paquete o una columna
max_allowed_packet = 100M
# Caché de consultas desactivada para mejorar el rendimiento
query_cache_size = 0
query_cache_type = 0
# Tamaño de la caché de hilos
thread_cache_size = 768
# Tamaño del búfer de clasificación utilizado por MyISAM
myisam_sort_buffer_size = 16M
# Tamaño del búfer de lectura aleatoria
read_rnd_buffer_size = 4M
# Tamaño del búfer de lectura
read_buffer_size = 768K
# Tamaño del búfer de clasificación
sort_buffer_size = 4M
# Número máximo de tablas abiertas en caché
table_open_cache = 256
# Tamaño del búfer de claves para MyISAM
key_buffer_size = 128M
# Número máximo de conexiones simultáneas permitidas
max_connections = 140
# Tamaño del grupo de búfer de InnoDB
innodb_buffer_pool_size = 4G
# Número de definiciones de tablas que se pueden almacenar en caché
table_definition_cache = 1200
# Tamaño del archivo de registro de InnoDB
innodb_log_file_size = 1G
# Tamaño del búfer de registro de InnoDB
innodb_log_buffer_size = 512M
# Tamaño de la tabla temporal
tmp_table_size = 512M
# Tamaño máximo de la tabla en memoria
max_heap_table_size = 512M
# Dirección de enlace para conexiones de red
bind-address = localhost
# Deshabilitar enlaces simbólicos
symbolic-links = 0
# Deshabilitar la carga local de archivos
local-infile = 0
# Capacidad de I/O de InnoDB
innodb_io_capacity = 2000
# Capacidad máxima de I/O de InnoDB
innodb_io_capacity_max = 4000

La verdad es que no es fácil optimizarlo… ¿Cuáles son las variables más importantes a optimizar en una base datos para un servidor VPS? Pues depende. Sí, estamos usando MyISAM o InnoDB.

• Te recomiendo leer: Cómo optimizar el valor de innodb_buffer_pool_size en MySQL o MariaDB.

Recomendaciones de optimización para servidores con 4, 8, 16 y 32 GB de RAM

¡OJO! Estos valores están optimizados para servidores dedicados a MariaDB o MySQL. Es decir. Tienen disponibles toda la memoria RAM para ellos. ¿Tienes otros servicios en el mismo servidor como un servidor Apache y otros servicios? Debes tener en cuenta todo lo que te he contado sobre la memoria RAM. ¿Un ejemplo? Yo tengo 16 GB de RAM en mi servidor, pero muchos otros servicios activos. Si me voy a la columna InnoDB 16GB de la segunda tabla, tendría que poner en max_connections = 300. Es una barbaridad. Tengo puestos 140, la mitad. Lo mismo en innodb_buffer_pool_size = 8G. Tengo puestos 4G (la mitad). MySQLturner te orientará muy bien las primeras veces.

Primero los valores si tienes MyISAM, luego InnoDB:

Notas Adicionales:

1. max_connections: Aumenta con la cantidad de RAM disponible para manejar más conexiones simultáneas. Optimiza mediante prueba y error partiendo de estas recomendaciones.
2. wait_timeout: Piempo para desconectar conexiones inactivas.
3. thread_cache_size: Aumenta con la cantidad de RAM para reducir el costo de crear nuevas threads.
4. table_open_cache: Permite abrir más tablas simultáneamente con más RAM.
5. key_buffer_size (solo MyISAM): Incrementa con la RAM disponible para mejorar el rendimiento de los índices.
6. query_cache_size: Tamaño de la caché de consultas, puede ajustarse según el uso.
7. tmp_table_size: Permite tablas temporales más grandes en memoria con más RAM.
8. innodb_buffer_pool_size (solo InnoDB): Muy importante para el rendimiento de InnoDB, aumenta con la RAM disponible.
9. innodb_log_file_size (solo InnoDB): Mayor tamaño puede mejorar el rendimiento de las escrituras.
10. innodb_flush_log_at_trx_commit (solo InnoDB): Establecido en 1 para garantizar la seguridad de los datos.
11. innodb_flush_method (solo InnoDB): O_DIRECT evita la doble caché entre MySQL y el sistema operativo.

Conclusión

¡Suerte a todos! Tened en cuenta que hay que ejecutar estos programas entre 24 y 48 horas después 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, si no, los cambios en el archivo no se verán reflejados.

• Te puede interesar: Trucos y consejos para optimizar la memoria RAM en un servidor VPS con Virtualmin