Tunning de MariaDB/MySQL

Bases de datos 20 de jun. de 2024

Afinado de un Gestor de Bases de Datos

Optimizar la configuración de un motor de base de datos es algo imprescindible para obtener el mejor rendimiento, evitar bloqueos en transacciones e incluso evitar futuros errores por configuraciones erróneas.

Dependiendo de la versión y configuración actual de una base de datos o incluso si esta está montado en modo Clúster, el obtener unas métrica, examinarlas y decidir la mejor opción, puede ser una cosa que no esté al alcance del conocimientos de todos.

En GNU/Linux, con un simple script de Perl (el olvidado pero poderoso lenguaje de scripting con más módulos funcionales que python), podemos obtener un análisis profundo de nuestro gestor de bases de datos.

Os voy a hablar de mysqltuner, en otras referencias de internet, lo podéis encontrar como MySQLTuner-perl

MySQLTuner-perl

Como siempre, lo primero os dejo la referencia y acceso al repositorio GIT oficial de la herramienta que analizo en cada artículo:

En Debian, instalarlo es tan sencillo como salir a la terminal y escribir:

# Actualizar paqueteria
sudo apt update

# Instalar MySQLTuner-perl
sudo apt -y mysqltuner

Si usas una distribución en la que no dispones de la ultima versión, puedes descargarlo e instalarlo con los siguientes comandos:

# Crer directorio y cambiarse a él
mkdir -p ~/mysqltuner; cd ~/mysqltuner

# Descargar script en Perl
wget http://mysqltuner.pl/ -O mysqltuner.pl
chmod +x mysqltuner.pl

# Descargar CVEs y Diccionario claves
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

La última versión disponible en este momento de la publicación del artículo es la Version 2.5.3

¿Que permite hacer MySQLTuner?

El script perl, realiza un estudio de alto rendimiento de una base de datos MySQL o MariaDB, que muestra una instantánea del estado de salud del servidor a analizar.

En base de las estadísticas obtenidas se muestran una serie de recomendaciones específicas para cada parámetro de la configuración que aumenta la eficiencia y el rendimiento del servidor de base de datos.

El resultado del afinado final, que se genera de forma automatizada, tiene un nivel de tan alta calidad, que es similar a lo que le mostraría un experto administrador de bases de datos MySQL y/o MariaDB.

Características que revisa y analiza MySQLTuner

Uso de memoria: Calcula el uso de memoria de MySQL en el máximo de carga y hace recomendaciones para aumentar o disminuir el consumo de memoria de la base de datos.
Los datos por-hilo y de memoria intermedia del servidor («buffer») se calculan por separado para una instantánea precisa de la configuración del servidor.
Consultas lentas: Comentarios de la cantidad de consultas lentas en relación con las consultas totales.
También se analizan los límites de tiempo de consulta lenta y se hacen recomendaciones.
Conexiones : Se revisan recuentos de conexiones actuales y el histórico.
Clave de memoria intermedia («buffer»): Toma los datos de configuración y los compara con los índices reales que se encuentran en las tablas MyISAM.
Calcula las tasas de acierto de búsqueda en la memoria intermedia y sugiere ajustes en las variables.
Memoria intermedia de consultas : La tasa de acierto de la memoria de consultas y los porcentajes de uso se utilizan para hacer
para las variables de configuración de la memoria de consultas.
Ordenaciones y uniones: Las memorias intermedias por-hilo que afectan a las ordenaciones y las uniones son revisados junto con las estadísticas de las consultas ejecutadas en el servidor.
Tablas temporales: se hacen recomendaciones variable para reducir tablas temporales que se escriben en el disco.
Memoria intermedia de tablas: Compara las tablas abiertas totales con las tablas abiertas actualmente.
Calcula la tasa de éxito de la memoria intermedia de tablas a fin de hacer sugerencias.
Archivos abiertos: determina si el servidor se acercará o traspasará el límite de archivos abiertos establecido por el sistema operativo o el
MySQL en sí mismo.
Bloqueos de tablas: Busca bloqueos de tablas que obligan a las consultas a esperar y hace sugerencias para reducir los bloqueos que requieren una espera.
Memoria intermedia de hilos: Calcula cuántas veces la base de datos debe crear un nuevo hilo para responder a una consulta.
Conexiones abortadas: busca aplicaciones que no están cerrando las
a la bases de datos correctamente.
Tasas de lectura/escritura: Calcula el porcentaje de operaciones de
y escritura en su instalación de MySQL/MariaDB.

¿Que bases de datos y motores soporta?

Una de las mejores cosas de MySQLTuner-perl es que soporta bases de datos MySQL, MariaDB o Percona.

Además puede analizar y generar informes de mejora para clúster, en configuraciones de Galera Cluster, TokuDB, así como mejoras de Performance schema.

Otras métricas que muestra son para optimizar el sistema operativo Linux para su trabajo orientado a las bases de datos.

También puede analizar motores basados en InnoDB, MyISAM, Aria, etc.

MySQL: 8.0, 8.2, 8.3 (soporte completo)
MariaDB: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2 (soporte completo)
Galera replication - Galera Cluster (soporte completo)
Percona XtraDB cluster (soporte completo)
Mysql Replications (soporte parcial, sin entorno de test)

Ciberseguridad. Análisis de Vulnerabilidades

CVE's: Otro punto a tener en cuenta en esta herramienta es que puede utilizar los CVE de Mitre para analizar y mostrar las vulnerabilidades que se pueden aplicar al gestor de bases de datos que estés analizando.
Análisis de Contraseñas: Se le puede pasar un fichero con un "juego de passwords" para que analice si alguno de los usuarios de la bases de datos contiene "contraseñas de diccionario"

Uso básico de MySQLTuner

Cuando se realizan pruebas estadísticas sobre una base de datos se puede impactar en el rendimiento de la misma, por ello mientras se realicen los test con MySQLTuner se recomienda desactivar las estadísticas de metadados:

set global innodb_stats_on_metadata = 0;

Uso de Performance schema

Esta habilitado por defecto para usarse con MySQL y MariaDB.

Revisa si tu version de MariaDB es menor 10.6, porque en esas versiones está deshabilitado. Para activarlo en versiones menores a 10.6 y debes modificar tu fichero /etc/my.cnf, incluyendo las siguientes lineas.

Una vez finalizado el chequeo vueve a dejarlo como estaba.

[mysqld]
# -----------------------------------------
# MariaDB < 10.6 activar 'Performanche Schema'
performance_schema = on
performance-schema-consumer-events-statements-history-long = ON
performance-schema-consumer-events-statements-history = ON
performance-schema-consumer-events-statements-current = ON
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON
performance-schema-consumer-events-transactions-current=ON
performance-schema-consumer-events-transactions-history=ON
performance-schema-consumer-events-transactions-history-long=ON
performance-schema-consumer-events-waits-current=ON
performance-schema-consumer-events-waits-history=ON
performance-schema-consumer-events-waits-history-long=ON
performance-schema-instrument='%=ON'
max-digest-length=2048
performance-schema-max-digest-length=2018
# -----------------------------------------

Test mínimo a base de datos local y/o remota

# BBDD Local
./ mysqltuner.pl --host 127.0.0.1
./mysqltuner.pl --host localhost

# BBDD Remota (servidor por nombre DNS o IP)
./mysqltuner.pl --host servidor --user usuario_admin --pass clave_admin

Test con máximo de información

# Usar modo "verboso"
./mysqltuner.pl --verbose

# Forzar test específicos
./mysqltuner.pl --buffers --dbstat --idxstat --sysstat --pfstat --tbstat

Activar depuración

./mysqltuner.pl --debug

Generar informe

# Informe en pantalla y resultando en fichero
./mysqltuner.pl --outputfile /tmp/analisis_mysqltuner.txt

# Informe sin salida por pantalla (util para automatizaciones)
./mysqltuner.pl --silent --outputfile /tmp/analisis_mysqltuner.txt

Auto-actualización de MySQLTuner

# Chequear version y actualizar
./mysqltuner.pl --checkversion --updateversion

Chequeo de Vulnerabilidades

# Chequear vulnerabilidades CVE de Mitre
/mysqltuner.pl --cvefile=vulnerabilities.csv

Volcado completo de informe en directorio

mkdir -p /tmp/Analisis-mysqltuner
.\mysqltuner.pl --verbose --dumpdir=/tmp/Analisis-mysqltuner

Espero que esta pequeña, pero eficaz herramienta, os sea útil para mejorar el rendimiento de vuestros servidores de MariaDB o MySQL, así como los clústeres de bases de datos.

Recomiendo leer con calma los resultados y estudiar que hace cada unos de los valores que recomienda, y no aplicarlo nunca directamente en producción...