MySQL es un motor de base de datos relacional muy conocido en la industria del desarrollo de software. Es considerado el motor de base de datos relacional más utilizado en todo el mundo. En esta publicación «Una breve cronología de MySQL» puedes conocer detalles de su historia, evolución , y cuales son las compañías que utilizan este software.
En esta publicación abordaremos dos conceptos importantes que puedes aplicar en tu proyecto, como son las recomendaciones que debemos seguir luego que instalamos MySQL en nuestro servidor; también mencionaremos algunas buenas prácticas en la configuración de nuestro servidor MySQL Server.
Es importante mencionar que cada aplicación web es única por lo tanto cada software puede requerir capacidades y configuraciones específicas para el servidor de base de datos.
¿Qué es my.cnf?
Es un archivo de configuración que se crea cuando instalas MySQL Server en tu servidor. La ubicación por defecto de este archivo depende del Sistema Operativo de tu servidor. Si estás trabajando con un Sistema Operativo como Debian,Ubuntu, la ubicación por defecto se encuentra en /etc/mysql/my.cnf
Este archivo «my.cnf» contiene las directivas de configuración que utiliza el motor de base de datos MySQL Server, por lo que debes tener mucho cuidado a la hora de modificar este fichero, sobre todo si estás en entorno de producción.
Con «my.conf» puedes optimizar el rendimiento de tu servidor de base de datos, incrementar el uso de recursos de cómputo, definir ubicación del almacenamiento de las bases de datos, entre otras cosas.
¿Quieres aprender a gestionar bases de datos MySQL de manera eficiente? Domina MySQL, uno de los sistemas de gestión de bases de datos más utilizados en el mundo. El siguiente curso te proporcionará las habilidades necesarias para convertirte en un experto en bases de datos. IR AL CURSO.
Antes de mencionar algunas directivas importantes de «my.cnf«, te comparto unas recomendaciones que debes tener en cuenta para configurar este archivo de configuración de servidor de base de datos MySQL.
Conocer las características de tu servidor
Si, lo primero que debes saber son las características y capacidades del servidor donde tienes instalado MySQL Server. La información mínima que debes conocer es:
- ¿Qué Sistema Operativo utiliza el servidor?
- ¿Cuál es la arquitectura del procesador que tiene el servidor?
- ¿Cuántos procesadores y de que velocidad tiene el servidor?
- ¿Cuánta memoria RAM tiene el servidor?
- ¿Qué tipo de disco duro tiene mi servidor, es SSD o HDD?
- ¿Cuál es la capacidad de almacenamiento de mi disco duro?
No copiar un archivo de configuración my.cnf de otro servidor
Como primera regla, asegúrate que las variables del sistema MySQL se agreguen después de la línea [mysqld] del archivo my.cnf.
Es una mala idea si intentas copiar un archivo de configuración my.cnf de otro servidor que suponga un rendimiento óptimo, debido a que las características de tu servidor no necesariamente sean las mismas que el otro servidor.
Asignación de Memoria RAM a consultas simples
sort_buffer_size
Si tu motor de base de datos MySQL solo atiende consultas simples, no es necesario aumentar el valor de la variable de configuraciónsort_buffer_size incluso así tengas más de 500 GB de RAM, debido a que el optimizador del motor de MySQL intentará calcular cuánto espacio necesita para atender la consulta que está utilizando un ORDER BY o GROUP BY, pero puede asignarse el máximo valor permitido para una consulta simple, consumiendo recursos de tu memoria de manera innecesaria.
Por lo tanto, podemos deducir que asignar un valor más grande de lo requerido a la directiva de configuración sort_buffer_size hará mas lenta la respuesta de la mayoría de consultas que recibe tu motor de la base de datos.
Lo ideal es asignar un mayor valor a esta directiva como una configuración de sesión, y solo para las sesiones que realmente necesitan un mayor tamaño de buffer.
El valor por defecto de sort_buffer_size es 256 KB. En algunas distribuciones Linux, se manejan umbrales de 256 KB hasta 2 MB, pero dependerá de la capacidad de su memoria. Se recomienda mantenerse dentro de ese rango de valores, salvo tenga necesidad de incrementar el valor para una sesión en particular.
mysql> show variables where variable_name='sort_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
La confiabilidad de tu base de datos es un muy importante
sync_binlog e innodb_flush_log_at_trx_commit
La confiabilidad es una característica importante que todo sistema de base de datos debe tener, por lo tanto, te sugiero configurar las directivas sync_binlog = 1 e innodb_flush_log_at_trx_commit = 1; para almacenar los eventos en el disco.
Si bien esta configuración puede incrementar el uso de almacenamiento de tu disco duro, hay alternativas para solucionar este problema. Esta funcionalidad te será de mucha ayuda para llevar un registro de los cambios en la base de datos.
mysql> show variables where variable_name='sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables where variable_name='innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.00 sec)
El almacenamiento temporal (buffers)
innodb_buffer_pool_size
MySQL utiliza esta variable de configuración para almacenar los indices y sus datos que tiene una base de datos, en la memoria RAM del servidor.
El valor por defecto es de 128 MB. Es oportuno que el valor sea entre el 50% y el 75% de la RAM disponible. No es necesario que sea más grande que la base de datos.
mysql> show variables where variable_name='innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
Configuración para altos volúmenes de escritura
innodb_io_capacity e innodb_io_capacity_max
Considere configurar estas dos variables del sistemainnodb_io_capacity e innodb_io_capacity_maxen caso su base de datos tenga un alto tráfico de escritura todo el tiempo y si su servidor utiliza discos duros de estado sólido (SSD).
Es importante que realice un seguimiento al rendimiento del disco antes y después de configurar estas directivas. Si tiene un servidor con un (01) disco SSD se recomienda los siguientes valores: innodb_io_capacity “4000” e innodb_io_capacity_max “8000″.
mysql> show variables where variable_name='innodb_io_capacity';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| innodb_io_capacity | 4000 |
+--------------------+-------+
mysql> show variables where variable_name='innodb_io_capacity_max';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity_max | 8000 |
+------------------------+-------+
innodb_autoinc_lock_mode
Esta variable de configuración define el modo de bloqueo que se utilizará para generar los valores numéricos que incrementan automáticamente (como aquellos campos auto_increment). Los valores permitidos son:
- 0 para el modo de bloqueo tradicional.
- 1 para el modo de bloqueo consecutivo. Valor por defecto.
- 2 para el modo de bloqueo intercalado.
La configuración de
innodb_autoinc_lock_modeen «2» mejora el rendimiento de MySQL al no depender del bloqueo AUTO-INC a nivel de tabla. Esto hace que las instrucciones de inserción de múltiples filas con clave primaria de incremento automático sean más óptimas, esto requiere binlog_format = ROW o MIXTO (ROW es el valor predeterminado en MySQL 5.7).
mysql> show variables where variable_name = 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 2 |
+--------------------------+-------+
MySQL 5.7 tiene valores de configuración significativamente mejores
innodb_buffer_pool_dump_at_shutdown e innodb_buffer_pool_load_startup
Ambas variables de configuración están habilitadas de forma predeterminada en la versión de MySQL 5.7. Estas variables ayudan a registrar las páginas almacenadas en caché en el InnoDB Buffer Pool cuando el servidor MySQL se apaga y acorta el proceso del reinicio del servidor de base de datos.
mysql> show variables where variable_name='Innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
mysql> show variables where variable_name='innodb_buffer_pool_dump_at_shutdown';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| innodb_buffer_pool_dump_at_shutdown | ON |
+-------------------------------------+-------+
innodb_file_per_table
Cuando innodb_file_per_table está habilitado, las tablas se crean en tablespaces independientes por archivo de forma predeterminada. Cuando está deshabilitado, las tablas se crean en el tablespace del sistema de forma predeterminada. Por lo tanto el valor por defecto «ON» es el correcto.
mysql> show variables where variable_name='Innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
innodb_stats_on_metadata
Tener deshabilitada esta variable de configuración mejorará la velocidad de acceso a los esquemas que tienen una gran cantidad de tablas e indices. Por lo tanto el valor predeterminado «OFF» es el adecuado.
mysql> show variables where variable_name = 'Innodb_stats_on_metadata';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | OFF |
+--------------------------+-------+
have_query_cache
Es una variable de configuración que indica si la caché de consultas está disponible. El valor por defecto es «YES«.
mysql> show variables where variable_name = 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
query_cache_type
Si el servidor se inicia con query_cache_type establecido en 0, no adquiere el resultado de la caché de consulta, lo que significa que la caché de consulta no se puede habilitar en tiempo de ejecución y hay una sobrecarga reducida en la ejecución de la consulta.
mysql> show variables where variable_name = 'query_cache_type';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | OFF |
+------------------+-------+
Referencias:
Quizás te pueda interesar:
¿Quieres aprender a gestionar bases de datos MySQL de manera eficiente? Domina MySQL, uno de los sistemas de gestión de bases de datos más utilizados en el mundo. El siguiente curso te proporcionará las habilidades necesarias para convertirte en un experto en bases de datos. IR AL CURSO.