WORDPRESS

Ajuste de las variables del sistema MySQL para un alto rendimiento

Para la mayoría de los desarrolladores de aplicaciones, la base de datos es el altar del dios demonio y es mejor mantenerla fuera de su alcance. ¡Pero no tiene por qué ser así!

En igualdad de condiciones, la familiaridad de los desarrolladores con la base de datos subyacente determina su nivel de antigüedad. Pocas bases de datos y poca experiencia en codificación = desarrolladores junior; pocas bases de datos y buena experiencia en codificación = desarrolladores intermedios; buenas bases de datos y buena experiencia en codificación = desarrolladores avanzados.

Es una dura realidad que incluso los desarrolladores con 6 a 8 años de experiencia tienen problemas para explicar las complejidades de los optimizadores de consultas, y cuando se les pregunta sobre ajuste de la base de datos.

¿Por qué?

Sorprendentemente, la razón no es la pereza (aunque en cierto modo lo es).

El punto es que la base de datos en sí misma es una fuerza con la que lidiar. Incluso tradicionalmente, cuando solo había tipos relacionales de bases de datos con los que lidiar, dominarlos era un milagro y una carrera en sí misma; hoy en día, tenemos tantos tipos de bases de datos que es imposible esperar que un alma mortal domine todo.

Dicho esto, es muy probable que todavía se sienta cómodo con las bases de datos relacionales o que forme parte de un equipo cuyos productos se han estado ejecutando en bases de datos relacionales durante mucho tiempo. Nueve de cada 10 veces, estás usando MySQL (o MariaDB). Para estos casos, un poco de investigación puede generar grandes beneficios para mejorar el rendimiento de la aplicación, y vale la pena aprender cada detalle.

¿curioso? ¡Vamos a sumergirnos!

¿No tienes curiosidad? Bueno, sumérgete de todos modos, ¡porque tu carrera depende de ello! 😛

Optimización de la caché de consultas de MySQL

Casi todas las optimizaciones en informática se reducen al almacenamiento en caché. Por un lado, las CPU mantienen varios niveles de caché para acelerar sus cálculos y, por otro lado, las aplicaciones web utilizan activamente soluciones de almacenamiento en caché como Redis para ofrecer resultados precalculados a los usuarios en lugar de acceder a la base de datos cada vez.

Pero bueno, ¡incluso las bases de datos MySQL de mala calidad tienen su propio caché de consultas! Es decir, cada vez que consulta algo y los datos aún están obsoletos, MySQL entregará estos resultados almacenados en caché en lugar de ejecutar la consulta nuevamente, lo que hace que la aplicación sea ridículamente rápida.

Puede verificar si un caché de consulta está disponible en su base de datos ejecutando esta consulta en la consola de la base de datos (nota, disponible, no habilitada):

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Entonces puede ver que estoy ejecutando MariaDB y puedo activar el almacenamiento en caché de consultas. Si está utilizando una instalación estándar de MySQL, es muy poco probable que la apague.

Ahora veamos si realmente tengo activado el almacenamiento en caché de consultas:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+

Sí. Pero si no lo hace, puede activarlo diciendo:

MariaDB [(none)]> SET GLOBAL query_cache_type = ON;

Curiosamente, esta variable también acepta un tercer valor que significa «bajo demanda», lo que significa que MySQL solo almacenará en caché las consultas que le indiquemos, pero no lo discutiremos aquí.

¡Con esto, tiene el caché de consultas y da el primer paso hacia una configuración de MySQL más sólida! Digo el primer paso porque si bien activarlo es una mejora importante, necesitamos ajustar el caché de consultas para que se ajuste a nuestra configuración. Así que aprendamos a hacerlo.

Otra variable de interés aquí es query_cache_sizecuya función se explica por sí misma:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

Entonces, tengo un caché de consultas de unos 16 MB de tamaño. Tenga en cuenta que aunque el almacenamiento en caché de consultas está activado, pero este tamaño es cero, el almacenamiento en caché en realidad está desactivado. Es por eso que verificar solo una variable no es suficiente. Ahora, debe establecer el tamaño de la caché de consultas, pero ¿cuánto? Primero, tenga en cuenta que la función de caché de consultas en sí misma requiere 4 KB para almacenar sus metadatos, por lo que lo que elija debe estar por encima de eso.

Suponga que establece el tamaño de la caché de consultas en 500 KB:

MariaDB [(none)]> SET GLOBAL query_cache_size = 500000;

¿Es suficiente? Bueno, no, porque el rendimiento final del motor de consultas depende de más cosas:

  • primero, query_cache_size La variable debe ser lo suficientemente grande para contener los resultados de su consulta. Si es demasiado pequeño, nada se almacenará en caché.
  • Segundo, si query_cache_size Ajústelo demasiado alto y surgirán dos problemas: 1) El motor tendrá que hacer un trabajo adicional en esta enorme área de memoria para almacenar y ubicar los resultados de la consulta. 2) Si la mayoría de las consultas dan como resultado un tamaño mucho más pequeño, la memoria caché se fragmenta y se pierden los beneficios de usar la memoria caché.

¿Cómo sabes que el caché está fragmentado? Verifique el número total de bloques en el caché de la siguiente manera:

MariaDB [(none)]> show status like 'Qcache_total_blocks';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Qcache_total_blocks | 33    |
+---------------------+-------+

Si el número es grande, la memoria caché está fragmentada y debe actualizarse.

Por lo tanto, para evitar estos problemas, asegúrese de query_cache_size fue elegido sabiamente. Si está frustrado porque no le dejé números específicos aquí, me temo que así son las cosas una vez que deja el desarrollo y entra en ingeniería. Debe mirar la aplicación que está ejecutando y ver el tamaño de la consulta para obtener resultados de consulta importantes y luego establecer este número. Aun así, puede terminar cometiendo errores. 🙂

Subprocesos, grupos de subprocesos, esperas y tiempos de espera

Esta es probablemente la parte más interesante de cómo funciona MySQL, ¡hacerlo bien significa hacer que su aplicación sea varias veces más rápida!

hilo

MySQL es un servidor multiproceso.Esto significa que cada vez que se realiza una nueva conexión al servidor MySQL, abre un nuevo hilo con los datos de conexión y pasa su identificador al cliente (en caso de que se pregunte qué es un hilo, consulte Esta). Luego, el cliente envía todas las consultas y recibe los resultados a través de este hilo. Esto lleva a una pregunta natural: ¿cuántos subprocesos puede iniciar MySQL? La respuesta está en la siguiente sección.

Grupo de subprocesos

Ningún programa en un sistema informático puede abrir un número arbitrario de subprocesos. Hay dos razones: 1) Los subprocesos consumen memoria (RAM) y el sistema operativo no te vuelve loco y se come toda la memoria. 2) Administrar, digamos, un millón de subprocesos es una tarea enorme en sí misma, y ​​si el servidor MySQL pudiera crear tantos subprocesos, moriría tratando de manejar la sobrecarga.

Para evitar estos problemas, MySQL viene con un conjunto de subprocesos: inicialmente, un número fijo de subprocesos forman parte del conjunto. Una nueva solicitud de conexión hará que MySQL elija uno de los subprocesos y devuelva los datos de conexión, y si todos los subprocesos se agotan, la nueva conexión será naturalmente rechazada. Veamos qué tan grande es el grupo de subprocesos:

ariaDB [(none)]> show variables like 'thread_pool_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| thread_pool_size | 4     |
+------------------+-------+

Entonces, mi máquina permite hasta cuatro conexiones simultáneas. Curiosamente, el número 4 proviene del hecho de que tengo un procesador de cuatro núcleos, lo que significa que mi computadora solo puede ejecutar 4 tareas paralelas a la vez (estoy hablando de verdaderas tareas paralelas aquí, no de tareas simultáneas).Idealmente, este es el límite que debería empujar el valor. thread_pool_size, pero tiene beneficios para aumentarlo en máquinas más potentes. Si no desea tener todas las conexiones nuevas esperando y puede permitirse alguna penalización de rendimiento (nuevamente, este es el punto óptimo que puede juzgar según el rendimiento de su aplicación bajo carga), aumentarlo a 8 podría ser una buena idea.

Sin embargo, a menos que tenga una máquina de 32 núcleos, configurarlo por encima de 16 es una mala idea, ya que el rendimiento disminuirá significativamente. La madriguera del conejo de los grupos de subprocesos en MySQL es profunda, pero si está interesado, aquí está Discusión más detallada.

esperar y tiempo de espera

Una vez que se crea un hilo y se adjunta al cliente, será una pérdida de recursos si el cliente no envía ninguna consulta durante los próximos segundos (o minutos). Por lo tanto, MySQL finaliza la conexión después de un período de inactividad.Esto está hecho por wait_timeout Cambiando:

MariaDB [(none)]> show variables like 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

El valor resultante está en segundos. Así que sí, por defecto, MySQL está configurado para esperar más de 8 horas antes de cortar el cable. Esto puede estar bien si tiene consultas de larga duración y realmente quiere esperarlas (pero incluso entonces, ¡ocho horas es ridículo!) Pero apestará en la mayoría de los casos. Cuando se ejecuta una consulta, este valor se establece en 0 (lo que significa para siempre), pero generalmente se debe establecer en un valor muy bajo (como 5 segundos, tal vez incluso menos) para liberar la conexión para que la usen otros procesos.

Ajustar la tabla temporal

Comencemos con las tablas temporales en MySQL.

Supongamos que tenemos un MySQL con la siguiente estructura: TABLE A UNION (TABLE B INNER JOIN C). Es decir, nos interesa unir las tablas B y C y luego fusionar el resultado con la tabla A. Ahora, MySQL primero unirá las tablas B y C, pero antes de realizar la fusión, necesita almacenar estos datos en alguna parte. Aquí es donde entran las tablas temporales: MySQL las usa para almacenar datos temporalmente en medio de una consulta compleja, y esta tabla temporal se descarta una vez que finaliza la consulta.

La pregunta ahora es: ¿por qué deberíamos molestarnos con todo esto?

Solo porque una tabla temporal, solo un resultado de consulta, son los datos que MySQL usa en sus cálculos, y su velocidad de acceso (entre otras limitaciones) determinará qué tan rápido se ejecuta la consulta. Por ejemplo, almacenar una tabla temporal en la RAM será varias veces más rápido que almacenarla en el disco.

Hay dos variables que controlan este comportamiento:

MariaDB [(none)]> show variables like 'MariaDB [(none)]> show variables like 'tmp_table_size';  
+----------------+----------+

| Variable_name  | Value    |

+----------------+----------+

| tmp_table_size | 16777216 |

+----------------+----------+
';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+

MariaDB [(none)]> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

Primero, max_heap_table_sizeDíganos cuánta RAM puede usar una tabla MySQL («montón» aquí se refiere a las estructuras de datos utilizadas en la asignación y administración de RAM – lea más aquí), mientras que el segundo, tmp_table_size, muestra cuál es el tamaño máximo de la tabla temporal.En mi caso, ambos estaban configurados en 16 MB, aunque intenté que solo aumentara tmp_table_size no funcionará, MySQL seguirá siendo limitado max_table_heap_size.

Ahora viene el punto: si las tablas temporales que se crean son más grandes que los límites permitidos por estas variables, MySQL se verá obligado a escribirlas en el disco, lo que resultará en un rendimiento extremadamente bajo. Nuestro trabajo ahora es simple: hacer nuestro mejor esfuerzo para adivinar el tamaño de datos más preciso para la tabla temporal y ajustar esas variables a ese límite. Sin embargo, quiero advertirle que no sea ridículo: cuando la mayoría de sus tablas temporales tienen menos de 24 MB de tamaño, establecer este límite en 16 GB (suponiendo que tenga tanta RAM) es estúpido: simplemente está desperdiciando RAM posible. Ya utilizado por otra consulta o parte del sistema (como un caché).

En conclusión

Cuando la documentación de MySQL abarca miles de palabras, es imposible cubrir todas las variables del sistema, o incluso todas las variables importantes, en un artículo. Si bien hemos cubierto algunas variables comunes aquí, lo animo a mirar las variables del sistema para el motor que está usando (InnoDB o MiISAM).

Mi resultado más deseado de escribir este artículo es que te lleves tres cosas:

  1. MySQL es una pieza típica de software que funciona dentro de los límites establecidos por el sistema operativo. No es un programa arcano, Dios sabe qué, y es imposible de domesticar. Además, afortunadamente, no es tan difícil entender cómo está configurado y controlado por las variables del sistema.
  2. No existe una configuración única que pueda hacer que su instalación de MySQL sea más grande.No tiene más remedio que mirar el sistema en ejecución (recuerde, la optimización proviene de espalda La aplicación está en producción, no antes), tome sus mejores conjeturas y medidas, y acepte que nunca será perfecta.
  3. Ajustar las variables no es la única forma de optimizar MySQL: escribir consultas de manera eficiente es otra gran cosa, pero lo discutiré en otro artículo. Pero el punto es que, incluso si ha realizado un análisis divino y ajustado esos parámetros al máximo, todavía existe la posibilidad de que detenga las cosas.

¿Cuál es tu variable de sistema favorita para ajustar? 🙂

Artículo Recomendado:  Instalación de Apache 2.4.6 en Unix

Publicaciones relacionadas

Deja una respuesta

Tu dirección de correo electrónico no será publicada.