Configuración y Ajuste de MySQL

Introducción

En este laboratorio, aprenderá los fundamentos de la configuración del servidor MySQL y la optimización del rendimiento. Comenzará inspeccionando la configuración actual del servidor, luego modificará un parámetro clave de rendimiento, el innodb_buffer_pool_size, para ver cómo se aplican los cambios.

El laboratorio lo guiará a través de la edición del archivo de configuración de MySQL, el reinicio del servidor para aplicar los cambios y la verificación de que la nueva configuración esté activa. Finalmente, aprenderá un método básico para analizar el rendimiento de las consultas utilizando el profiler integrado de MySQL. Esto proporcionará una base para optimizar su base de datos para diferentes cargas de trabajo.

Ver la Configuración Actual de MySQL

Antes de realizar cualquier cambio, es crucial comprender la configuración actual de su servidor MySQL. En este paso, se conectará a MySQL e inspeccionará el valor de una variable crítica de rendimiento, innodb_buffer_pool_size.

Primero, abra la terminal desde su escritorio.

Conéctese al servidor MySQL como usuario root. En este entorno de laboratorio, puede usar sudo para conectarse sin contraseña.

sudo mysql -u root

Una vez conectado, verá el prompt de MySQL (mysql>).

Las variables del sistema controlan el comportamiento del servidor MySQL. La variable innodb_buffer_pool_size determina la cantidad de memoria asignada para almacenar en caché datos e índices para tablas InnoDB. Un buffer pool de tamaño adecuado es esencial para un buen rendimiento.

Utilice el comando SHOW VARIABLES con una cláusula LIKE para encontrar el valor actual de esta variable.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Verá una salida similar a la siguiente, que muestra el valor predeterminado en bytes. Anote este valor, ya que lo cambiará en el siguiente paso.

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+--------------------------+-----------+
1 row in set (0.01 sec)

Ahora que ha verificado la configuración actual, puede salir del shell de MySQL.

exit

Modificar el Archivo de Configuración de MySQL

La configuración de MySQL se puede cambiar temporalmente para la sesión actual o de forma permanente en un archivo de configuración. Para que un cambio persista después de reiniciar el servidor, debe editar el archivo de configuración. En este paso, modificará /etc/mysql/my.cnf para aumentar el innodb_buffer_pool_size.

Abra el archivo de configuración de MySQL utilizando el editor de texto nano con privilegios de sudo.

sudo nano /etc/mysql/my.cnf

Desplácese hacia abajo para encontrar la sección [mysqld]. Esta sección contiene configuraciones específicas para el demonio del servidor MySQL. Agregue la siguiente línea debajo del encabezado [mysqld] para establecer el tamaño del buffer pool en 256 megabytes.

innodb_buffer_pool_size=256M

Su sección [mysqld] ahora debería verse algo así:

[mysqld]
innodb_strict_mode=OFF
init_connect='SET NAMES utf8'
character-set-server = utf8
collation-server=utf8_general_ci
innodb_buffer_pool_size=256M

Ahora, guarde el archivo y salga de nano. Presione Ctrl+X, escriba Y para confirmar los cambios y presione Enter para escribir en el archivo.

Los cambios en el archivo de configuración solo surtirán efecto después de reiniciar el servidor MySQL. Utilice el comando service para reiniciarlo.

sudo service mysql restart

Ahora ha actualizado permanentemente la configuración. En el siguiente paso, verificará que el cambio esté activo.

Verificar el Cambio de Configuración

Después de modificar el archivo de configuración y reiniciar el servidor, debe verificar que la nueva configuración se haya aplicado correctamente. En este paso, se volverá a conectar a MySQL y verificará nuevamente la variable innodb_buffer_pool_size.

Conéctese al servidor MySQL.

sudo mysql -u root

Ahora, ejecute nuevamente el comando SHOW VARIABLES para ver el nuevo valor.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

La salida ahora debería mostrar el nuevo valor en bytes. MySQL convierte automáticamente 256M (256 megabytes) a 268435456 bytes.

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+--------------------------+-----------+
1 row in set (0.00 sec)

Comparar este valor con el que anotó en el Paso 1 confirma que su cambio de configuración fue exitoso y ahora está activo.

Ahora puede salir del shell de MySQL.

exit

Analizar el Rendimiento de Consultas

La optimización de las variables del servidor se realiza para mejorar el rendimiento de las consultas. Si bien un análisis profundo es complejo, puede utilizar el generador de perfiles de consultas integrado de MySQL para obtener una medición básica del tiempo de ejecución de las consultas. En este paso, creará una tabla de prueba, insertará datos y analizará una consulta simple.

Primero, conéctese al servidor MySQL.

sudo mysql -u root

Cree una nueva base de datos llamada testdb y cambie a ella.

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

A continuación, cree una tabla llamada employees para almacenar datos de ejemplo.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary INT
);

Inserte algunos registros en la tabla employees.

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 60000),
('Bob', 'Engineering', 80000),
('Charlie', 'Sales', 65000),
('David', 'Marketing', 55000);

Ahora, habilite el generador de perfiles de consultas para su sesión. Esto registrará datos de rendimiento para las consultas posteriores.

SET profiling = 1;

Ejecute una consulta que desee analizar. Por ejemplo, busquemos todos los empleados del departamento de 'Sales'.

SELECT * FROM employees WHERE department = 'Sales';

Para ver los resultados de rendimiento, utilice el comando SHOW PROFILES. Esto lista las consultas que ha ejecutado desde que habilitó el generador de perfiles y sus duraciones.

SHOW PROFILES;

La salida será similar a esta, mostrando la duración de cada consulta en segundos.

+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00038500 | SELECT * FROM employees WHERE department = 'Sales'    |
+----------+------------+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Esta columna Duration le proporciona una línea base para el rendimiento de las consultas. En un escenario del mundo real, utilizaría esta herramienta para comparar la velocidad de las consultas antes y después de los cambios de configuración en conjuntos de datos mucho más grandes.

Ha utilizado con éxito el generador de perfiles para analizar una consulta. Ahora puede salir del shell de MySQL.

exit;

Resumen

En este laboratorio, ha aprendido el proceso básico de configuración y ajuste de un servidor MySQL. Ha practicado cómo ver la configuración actual del servidor inspeccionando variables del sistema como innodb_buffer_pool_size.

Ha adquirido experiencia práctica modificando el archivo de configuración de MySQL (my.cnf) para realizar cambios permanentes, reiniciando el servidor para aplicarlos y verificando que la nueva configuración esté activa. Finalmente, se le introdujo a una técnica básica de análisis de rendimiento utilizando el generador de perfiles de consultas integrado de MySQL para medir la duración de las consultas.

Estas habilidades fundamentales son esenciales para cualquier desarrollador o administrador responsable del mantenimiento de una base de datos MySQL saludable y de alto rendimiento.