La configuración por defecto de MySQL y MariaDB está pensada para arrancar en cualquier máquina, no para aprovechar la tuya: por ejemplo, reserva solo 128 MB para la caché de InnoDB. En este tutorial ajustarás los parámetros que más influyen en el rendimiento en un servidor Ubuntu 24.04 dedicado a la base de datos, en un archivo propio que no se pierde al actualizar, y comprobarás el efecto de cada cambio con métricas del propio servidor.
Requisitos previos
Para seguir esta guía necesitas:
- Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath, con almacenamiento SSD o NVMe.
- MySQL 8.0 o MariaDB 10.11 instalado desde los repositorios de Ubuntu, con tablas InnoDB.
- Un usuario no root con privilegios
sudo. - Una carga de trabajo real o representativa. Ajustar sin tráfico que medir es adivinar.
Los ejemplos usan como referencia un servidor con 8 GB de RAM y 4 vCPU dedicado a MySQL. Escala los valores a tu máquina. Si el servidor comparte RAM con una aplicación web, reserva primero la memoria que esa aplicación necesita.
Paso 1: Medir el punto de partida
Antes de cambiar nada, anota los valores actuales para poder comparar. Entra en la consola:
sudo mysql
Consulta el tamaño del buffer pool y cuántas lecturas tienen que ir a disco porque la página no estaba en memoria:
SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;
SHOW GLOBAL STATUS WHERE Variable_name IN
('Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
'Max_used_connections', 'Threads_created', 'Created_tmp_disk_tables', 'Created_tmp_tables',
'Uptime');
+----------------+
| buffer_pool_mb |
+----------------+
| 128.00000000 |
+----------------+
+----------------------------------+------------+
| Variable_name | Value |
+----------------------------------+------------+
| Created_tmp_disk_tables | 1840 |
| Created_tmp_tables | 25310 |
| Innodb_buffer_pool_read_requests | 918234551 |
| Innodb_buffer_pool_reads | 4120933 |
| Max_used_connections | 38 |
| Threads_created | 212 |
| Uptime | 604800 |
+----------------------------------+------------+
Qué mirar:
- Aciertos del buffer pool:
1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests). En el ejemplo es 99,55 %. En un servidor bien dimensionado debería estar por encima del 99,9 %. Max_used_connections: el pico real de conexiones desde el arranque. Sirve para dimensionarmax_connections.Created_tmp_disk_tablesfrente aCreated_tmp_tables: qué parte de las tablas temporales acabó en disco.
Estos contadores se acumulan desde el arranque (Uptime, en segundos), así que tómalos después de que el servidor lleve un tiempo recibiendo tráfico normal.
Paso 2: Crear un archivo de configuración propio
No edites /etc/mysql/my.cnf ni el mysqld.cnf del paquete: una actualización puede preguntarte si sobrescribirlos. En su lugar, crea un archivo que se lea el último (por eso el prefijo zz-). MySQL carga los archivos de /etc/mysql/mysql.conf.d/ por orden alfabético y, si una directiva aparece dos veces, gana la última.
Para MySQL 8.0:
sudo nano /etc/mysql/mysql.conf.d/zz-tuning.cnf
Para MariaDB, el directorio equivalente es /etc/mysql/mariadb.conf.d/:
sudo nano /etc/mysql/mariadb.conf.d/zz-tuning.cnf
En los pasos siguientes irás completando este archivo. Todas las directivas van bajo la sección [mysqld].
Paso 3: Dimensionar el buffer pool de InnoDB
innodb_buffer_pool_size es el parámetro más importante: es la caché donde InnoDB guarda datos e índices. Si tus datos activos caben en él, la mayoría de las lecturas no tocan el disco. En un servidor dedicado, usa entre el 60 % y el 75 % de la RAM.
Para saber cuánto ocupan tus datos e índices InnoDB:
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS innodb_gb
FROM information_schema.tables WHERE engine = 'InnoDB';
Si el resultado es menor que el 70 % de la RAM, no hace falta que el buffer pool sea mucho mayor que los datos más un margen de crecimiento. Para el servidor de 8 GB de ejemplo:
[mysqld]
innodb_buffer_pool_size = 5G
MySQL divide automáticamente el buffer pool en varias instancias cuando supera 1 GB, así que no necesitas ajustar innodb_buffer_pool_instances.
Notaen MySQL 8.0 puedes probar el tamaño en caliente con
SET GLOBAL innodb_buffer_pool_size = 5368709120;sin reiniciar. El cambio se pierde al reiniciar si no lo guardas en el archivo.
Paso 4: Ajustar el redo log y el vaciado a disco
El redo log registra los cambios antes de escribirlos en los archivos de datos. Si es pequeño, InnoDB tiene que vaciar páginas a disco con mucha frecuencia en cargas con muchas escrituras. En MySQL 8.0.30 y posteriores (Ubuntu 24.04 trae 8.0.4x) se controla con un único parámetro:
innodb_redo_log_capacity = 2G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_redo_log_capacity: el valor por defecto es 100 MB. Entre 1 y 2 GB es razonable para una carga con escrituras frecuentes; a cambio, la recuperación tras un corte tarda algo más.innodb_flush_method = O_DIRECT: evita que los datos se guarden dos veces en memoria (en el buffer pool y en la caché de páginas del sistema). Es la opción recomendada en Linux.innodb_io_capacity: las operaciones por segundo que InnoDB usa para las escrituras en segundo plano. El valor por defecto (200) está pensado para discos mecánicos; en SSD o NVMe, 1000 es un punto de partida prudente.
En MariaDB 10.11 el redo log se configura con innodb_log_file_size en lugar de innodb_redo_log_capacity:
innodb_log_file_size = 2G
Deja innodb_flush_log_at_trx_commit en su valor por defecto (1) salvo que aceptes perder hasta un segundo de transacciones confirmadas si el servidor se cae. Con 2 las escrituras son más rápidas, pero ya no es una base de datos completamente duradera.
Paso 5: Dimensionar las conexiones y las tablas temporales
Cada conexión reserva memoria propia para ordenaciones, joins y tablas temporales, así que un max_connections demasiado alto puede agotar la RAM en un pico de tráfico. Usa como referencia el Max_used_connections del paso 1 con un margen de un 50 %:
max_connections = 200
thread_cache_size = 32
table_open_cache = 4000
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size: reutiliza hilos en lugar de crear uno por conexión. SiThreads_createdsigue creciendo rápido con el servidor ya caliente, súbelo.table_open_cache: número de tablas abiertas que se mantienen en caché. SiOpened_tablescrece continuamente, súbelo.tmp_table_sizeymax_heap_table_size: tamaño máximo de una tabla temporal en memoria (se aplica el menor de los dos, por eso van a la par). Súbelos solo siCreated_tmp_disk_tableses una proporción alta deCreated_tmp_tables.
No subas por defecto sort_buffer_size, join_buffer_size ni read_buffer_size: se reservan por conexión y valores altos suelen empeorar el rendimiento. Si una consulta concreta los necesita, es mejor añadir un índice.
Notasi estás en MySQL 5.7 o en MariaDB, verás recomendaciones sobre
query_cache_size. La caché de consultas no existe en MySQL 8.0 y en MariaDB viene desactivada por defecto porque se convierte en un cuello de botella con varias CPU. Déjala así.
Paso 6: Activar el log de consultas lentas
Ningún parámetro compensa una consulta sin índice. El log de consultas lentas te dice cuáles son:
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = OFF
Con long_query_time = 1 se registran las consultas que tardan más de un segundo. Puedes bajarlo a 0.5 una vez eliminadas las más lentas.
Paso 7: Aplicar y verificar la configuración
El archivo completo para el servidor de ejemplo con MySQL 8.0 queda así:
[mysqld]
# Memoria
innodb_buffer_pool_size = 5G
# Redo log y E/S
innodb_redo_log_capacity = 2G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
# Conexiones y tablas
max_connections = 200
thread_cache_size = 32
table_open_cache = 4000
tmp_table_size = 64M
max_heap_table_size = 64M
# Consultas lentas
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = OFF
Reinicia el servicio (mariadb en lugar de mysql si usas MariaDB) y comprueba que ha arrancado:
sudo systemctl restart mysql
sudo systemctl status mysql --no-pager
Si no arranca, el motivo aparece en el log de errores:
sudo tail -n 30 /var/log/mysql/error.log
Confirma que los valores se han aplicado:
sudo mysql -e "SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS bp_gb, @@innodb_redo_log_capacity/1024/1024 AS redo_mb, @@max_connections, @@slow_query_log;"
+--------+-----------+-------------------+------------------+
| bp_gb | redo_mb | @@max_connections | @@slow_query_log |
+--------+-----------+-------------------+------------------+
| 5.0000 | 2048.0000 | 200 | 1 |
+--------+-----------+-------------------+------------------+
Paso 8: Analizar las consultas lentas
Tras unas horas con tráfico real, resume el log agrupando las consultas similares y ordenándolas por tiempo total:
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
Count: 1520 Time=2.31s (3511s) Lock=0.00s (0s) Rows=20.0 (30400), miapp_user[miapp_user]@[10.0.0.5]
SELECT * FROM orders WHERE customer_email = 'S' ORDER BY created_at DESC LIMIT N
Toma la consulta más costosa y revisa su plan de ejecución:
EXPLAIN SELECT * FROM miapp.orders WHERE customer_email = '[email protected]' ORDER BY created_at DESC LIMIT 20;
Si la columna type muestra ALL (recorrido completo de la tabla) y key es NULL, falta un índice. En este ejemplo:
CREATE INDEX idx_orders_email_created ON miapp.orders (customer_email, created_at);
Vuelve a ejecutar el EXPLAIN para confirmar que ahora usa el índice (key muestra idx_orders_email_created).
Para una segunda opinión sobre la configuración, mysqltuner (disponible en los repositorios de Ubuntu) revisa los contadores del servidor y sugiere ajustes. Úsalo cuando el servidor lleve al menos 24 horas en marcha, y toma sus recomendaciones como puntos a investigar, no como cambios a aplicar sin más:
sudo apt install mysqltuner
sudo mysqltuner
Paso 9: Comparar con el punto de partida
Pasados unos días con la nueva configuración, repite las consultas del paso 1 y compara:
- La tasa de aciertos del buffer pool debería estar por encima del 99,9 %. Si no, y la RAM lo permite, súbelo.
Max_used_connectionsdebe quedar holgadamente por debajo demax_connections.- Revisa la memoria del sistema con
free -h: si el servidor empieza a usar swap, reduce el buffer pool. Una base de datos que hace swap es mucho más lenta que una con menos caché.
Cambia un parámetro cada vez y mide antes del siguiente. Si cambias varios a la vez, no sabrás cuál ha tenido efecto.
Solución de problemas
MySQL no arranca tras el cambio. Revisa /var/log/mysql/error.log. Lo más habitual es una directiva mal escrita (unknown variable) o una que no existe en tu servidor, como innodb_redo_log_capacity en MariaDB.
El proceso muere con Out of memory en dmesg. El buffer pool más la memoria por conexión supera la RAM disponible. Reduce innodb_buffer_pool_size o max_connections.
Too many connections. Antes de subir max_connections, comprueba si la aplicación deja conexiones abiertas o no usa un pool de conexiones. SHOW PROCESSLIST; muestra quién las ocupa.
Conclusión
Has pasado de la configuración genérica a una ajustada a tu RAM y tu almacenamiento, en un archivo propio que sobrevive a las actualizaciones, y tienes el log de consultas lentas para encontrar los problemas que ningún parámetro arregla. Como siguientes pasos, puedes analizar el log con más detalle con pt-query-digest (paquete percona-toolkit), vigilar las métricas de InnoDB con tu sistema de monitorización, y separar lecturas en una réplica si el servidor principal se queda corto.