MySQL y MariaDB son los sistemas de administración de bases de datos relacionales (RDMS) más utilizados cuando se trata de alojamiento de sitios web y sistemas CMS como Joomla, WordPress, Drupal y Typo 3. En este artículo, explicaré cómo acelerar y optimizar su Servidor de base de datos MySQL y MariaDB.
Almacenar datos MySQL en particiones separadas
Cuando se trata del punto de optimización y seguridad, siempre es la mejor idea almacenar los datos de la base de datos en un volumen separado. Los volúmenes son específicamente para volúmenes de almacenamiento rápido como SSD, NVMe. Incluso si su sistema falla, tendrá su base de datos segura. Como el volumen de la partición se compone de volúmenes de almacenamiento rápidos, el rendimiento será más rápido.
Establecer el número máximo de conexiones MySQL
MySQL/MariaDB utiliza una instrucción max_connections que especifica cuántas conexiones simultáneas se permiten actualmente en el servidor. Demasiadas conexiones dan como resultado un alto consumo de memoria, así como una alta carga de CPU. Para sitios web pequeños, las conexiones se pueden especificar en 100-200 y los más grandes pueden necesitar 500-800 y más. Las conexiones_máximas se puede cambiar dinámicamente usando la consulta SQL. En este ejemplo, he establecido el valor en 200.
$ mysql -u root -p
mysql> set global max_connections=200;
Salida:
Habilitar el registro de consultas lentas de MySQL
El registro de consultas que tardan mucho tiempo en ejecutarse facilita la solución de problemas de la base de datos. El registro de consultas lentas se puede habilitar agregando las siguientes líneas en el archivo de configuración de MySQL/MariaDB.
slow-query-log=1 slow-query-log-file= /var/lib/mysql/mysql-slow-query.log long-query-time=1
Donde la primera variable habilita el registro de consultas lentas
La segunda variable define el directorio del archivo de registro
La tercera variable define el tiempo para completar una consulta MySQL
Reinicie el servicio mysql/mariadb y controle el registro
$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log
Establecer el paquete máximo permitido por MySQL
Los datos se dividen en paquetes en MySQL. Max_allowed_packet define el tamaño máximo de los paquetes que se pueden enviar. Configurar max_allowed_packet demasiado bajo puede hacer que la consulta sea demasiado lenta. Se recomienda establecer el valor del paquete al tamaño del paquete más grande.
Configuración de la capacidad de la mesa temporal
Tmp_table_size es el espacio máximo utilizado para la tabla de memoria integrada. Si el tamaño de la tabla supera el límite especificado, se convertirá en una tabla MyISAM en el disco. En MySQL/MariaDB, puede agregar las siguientes variables en el archivo de configuración para configurar el tamaño de la tabla temporal. Se recomienda establecer este valor en el servidor 64M por GB de memoria.
[mysqld] tmp_table_size=64M
Reinicie el servicio mysql
$ systemctl restart mysql
$ systemctl restart mariadb
Configura la capacidad máxima de la tabla de memoria.
Max_heap_table_size es la variable utilizada en MySQL para configurar la capacidad máxima de la tabla de memoria. El tamaño de la capacidad máxima de la tabla de memoria debe ser el mismo que la capacidad de la tabla temporal para evitar escrituras en el disco. Se recomienda establecer este valor en el servidor en 64 M por GB de memoria. Agregue la siguiente línea en el archivo de configuración de MySQL y reinicie el servicio.
[mysqld] max_heap_table_size=64M
Para aplicar los cambios, reinicie el servidor de la base de datos.
$ systemctl restart mysql
$ systemctl restart mariadb
Deshabilitar la búsqueda inversa de DNS para MySQL
Cuando se recibe una nueva conexión, MySQL/MariaDB realizará una búsqueda de DNS para resolver la dirección IP del usuario. Esto puede causar un retraso cuando la configuración de DNS no es válida o si hay un problema con el servidor DNS. Para deshabilitar la búsqueda de DNS, agregue la siguiente línea en el archivo de configuración de MySQL y reinicie el servicio de MySQL.
[mysqld] skip-name-resolve
Reinicie el servicio:
$ systemctl restart mysql
$ systemctl restart mariadb
Evitar el uso de intercambio en MySQL
El kernel de Linux mueve parte de la memoria a una partición especial del disco llamada espacio de "intercambio" cuando el sistema se queda sin memoria física. En esta condición, el sistema escribe información en el disco en lugar de liberar memoria. Como la memoria del sistema es más rápida que el almacenamiento en disco, se recomienda desactivar el intercambio. El intercambio se puede deshabilitar usando el siguiente comando.
$ sysctl -w vm.swappiness=0
Salida:
Aumentar el tamaño del grupo de búfer de InnoDB
MySQL/MariaDB tiene un motor InnoDB que tiene un grupo de búfer para almacenar en caché e indexar datos en la memoria. El grupo de búfer ayuda a que las consultas de MySQL/MariaDB se ejecuten comparativamente más rápido. Elegir el tamaño adecuado del grupo de búfer de InnoDB requiere cierto conocimiento de la memoria del sistema. La mejor idea es establecer el valor del tamaño del grupo de búfer de InnoDB en el 80 % de la RAM.
Ejemplo.
- Memoria del sistema =4 GB
- Tamaño del grupo de búfer =3,2 GB
Agregue la siguiente línea en el archivo de configuración de MySQL y reinicie el servicio
[mysqld] Innodb_buffer_pool_size 3.2G
Reinicie la base de datos:
$ systemctl restart mysql
$ systemctl restart mariadb
Tratar con el tamaño de caché de consultas
La directiva de caché de consultas en MySQL/MariaDB se utiliza para almacenar en caché todas las consultas que se repiten con los mismos datos. Se recomienda establecer el valor en 64 MB y aumentar el tiempo para sitios web pequeños. No se recomienda aumentar el valor del tamaño de la caché de consultas a GB, ya que puede degradar el rendimiento de la base de datos. Agregue la siguiente línea en el archivo my.cnf.
[mysqld] query_cache_size=64M
Comprobar conexiones inactivas
Los recursos son consumidos por conexiones inactivas, por lo que debe finalizarse o actualizarse si es posible. Estas conexiones permanecen en el estado de "reposo" y pueden permanecer durante un largo período de tiempo. Verifique las conexiones inactivas usando el siguiente comando.
$ mysqladmin processlist -u root -p | grep “Sleep”
La consulta mostrará una lista de los procesos que están en estado de suspensión. Generalmente en PHP, el evento puede ocurrir cuando se usa mysql_pconnect. Esto abre la conexión MySQL, ejecuta las consultas, elimina las autenticaciones y deja la conexión abierta. Usando wait_timeout directiva, las conexiones inactivas pueden ser interrumpidas. El valor predeterminado para wait_timeout es 28800 segundos que se puede reducir a un rango de tiempo mínimo como 60 segundos. Agregue la siguiente línea en el archivo my.cnf
[mysqld] wait_timeout=60
Optimizar y reparar la base de datos MySQL
Si el servidor se apaga inesperadamente, existe la posibilidad de que las tablas en MySQL/MariaDB se bloqueen. Hay otras razones posibles para que la tabla de la base de datos se bloquee, como acceder a la base de datos mientras se ejecuta el proceso de copia, el sistema de archivos se bloquea repentinamente. En esta situación, tenemos una herramienta especial llamada “mysqlcheck ” que verifica, repara y optimiza todas las tablas en las bases de datos.
Use el siguiente comando para realizar las actividades de reparación y optimización.
Para todas las bases de datos:
$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
Para base de datos específica:
$ mysqlcheck -u root -p --auto-repair --check --optimize dbname
Reemplace dbname con el nombre de su base de datos
- Comprobar el rendimiento de MySQL/MariaDB mediante herramientas de prueba
Se recomienda comprobar periódicamente el rendimiento de la base de datos MySQL/MariaDB. Esto facilitará la obtención del informe de rendimiento y el punto de mejora. Hay muchas herramientas disponibles entre las cuales mysqltuner es la mejor.
Ejecute el siguiente comando para descargar la herramienta
$ wget https://github.com/major/MySQLTuner-perl/tarball/master
Descomprimir el archivo
$ tar xvzf master
Vaya al directorio del proyecto y ejecute el siguiente script.
$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl
Salida:
Conclusión
En este artículo, hemos aprendido cómo optimizar MySQL/MariaDB usando diferentes técnicas. Gracias por leer.