Optimiza y tunea 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. ¿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.
3. ¿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’ o ‘nano’. Ejemplo desde terminal: sudo nano /etc/mysql/my.cnf
4. ¿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
5. Leemos esta biblia de optimización de MySQL, para comprender el significado de cada parámetro.

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

Antes de empezar a hacer ningún 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:

Dirígete 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 cuáles son los parámetros 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 multiplicar 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 fácil optimizarlo…

¿Cuáles 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

Les recomiendo consultar esta página web: wiki.mikejung.biz

¿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 (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! Tener 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, sino, los cambios en el archivo no se verán reflejados.

Ejemplo desde terminar (Debian y familia):

sudo service mysql restart

Referencia: https://algoentremanos.com/optimiza-y-tunea-my-cnf-mysql-en-tu-servidor-vps/