10 consejos de rendimiento esenciales para MySQL

por Baron Schwartz

Al igual que con todas las bases de datos relacionales, MySQL puede llegar a ser una bestia complicada, una que puede detenerse en cualquier momento, dejando sus aplicaciones en la estacada y su negocio en la línea.

La verdad es que los errores comunes subyacen a la mayoría de los problemas de rendimiento de MySQL. Para asegurarse de que su servidor MySQL funcione a la máxima velocidad, proporcionando un rendimiento estable y consistente, es importante eliminar estos errores, que a menudo quedan ocultos por alguna sutileza en su carga de trabajo o una trampa de configuración.

Afortunadamente, muchos problemas de rendimiento de MySQL resultan tener soluciones similares, lo que hace que la resolución de problemas y el ajuste de MySQL sean una tarea manejable.

Aquí hay 10 consejos para obtener un gran rendimiento de MySQL.https://imasdk.googleapis.com/js/core/bridge3.439.0_en.html#goog_1414877874Volumen 0% 

Consejo de rendimiento de MySQL n° 1: perfile tu carga de trabajo

La mejor manera de comprender cómo su servidor gasta su tiempo es perfilar la carga de trabajo del servidor. Al perfilar su carga de trabajo, puede exponer las consultas más costosas para un mayor ajuste. Aquí, el tiempo es la métrica más importante porque cuando emite una consulta contra el servidor, no le importa nada excepto la rapidez con la que se completa.

La mejor manera de perfilar su carga de trabajo es con una herramienta como el analizador de consultas de MySQL Enterprise Monitor o pt-query-digest del Percona Toolkit. Estas herramientas capturan las consultas que ejecuta el servidor y devuelven una tabla de tareas ordenadas por orden decreciente de tiempo de respuesta, lo que hace que las tareas más costosas y que consuman más tiempo a la parte superior de forma instantánea puedan ver dónde concentrar sus esfuerzos.

Las herramientas de creación de perfiles de carga de trabajo agrupan consultas similares, lo que le permite ver las consultas que son lentas, así como las consultas que son rápidas pero que se ejecutan muchas veces.

Consejo de rendimiento de MySQL n° 2: comprender los cuatro recursos fundamentales

Para funcionar, un servidor de base de datos necesita cuatro recursos fundamentales: CPU, memoria, disco y red. Si alguno de estos es débil, errático o sobrecargado, es muy probable que el servidor de la base de datos funcione mal.

Comprender los recursos fundamentales es importante en dos áreas particulares: elección de hardware y resolución de problemas.

Al elegir hardware para MySQL, asegúrese de que todos los componentes funcionen bien. Igual de importante, equilibrelos razonablemente bien entre sí. A menudo, las organizaciones seleccionarán servidores con CPU y discos rápidos, pero que carecen de memoria. En algunos casos, agregar memoria es una forma económica de aumentar el rendimiento en órdenes de magnitud, especialmente en cargas de trabajo que están vinculadas al disco. Esto puede parecer contradictorio, pero en muchos casos los discos se sobreutilizan porque no hay suficiente memoria para contener el conjunto de datos de trabajo del servidor.

Otro buen ejemplo de este equilibrio se refiere a las CPU. En la mayoría de los casos, MySQL funcionará bien con CPU rápidas porque cada consulta se ejecuta en un solo hilo y no se puede paralelizar entre las CPU.

Cuando se trata de la resolución de problemas, verifique el rendimiento y la utilización de los cuatro recursos, con un ojo atento para determinar si están funcionando mal o simplemente se les pide que hagan demasiado trabajo. Este conocimiento puede ayudar a resolver problemas rápidamente.

Consejo de rendimiento de MySQL n° 3: no use MySQL como cola

Las colas y los patrones de acceso similares a colas pueden colarse en su aplicación sin que usted lo sepa. Por ejemplo, si establece el estado de un elemento para que un proceso de trabajo en particular pueda reclamarlo antes de actuar sobre él, entonces, sin saberlo, está creando una cola. Marcar correos electrónicos como no enviados, enviarlos y luego marcarlos como enviados es un ejemplo común.

Las colas causan problemas por dos razones principales: serializan su carga de trabajo, lo que impide que las tareas se realicen en paralelo y, a menudo, dan como resultado una tabla que contiene trabajo en proceso, así como datos históricos de trabajos que se procesaron hace mucho tiempo. Ambos agregan latencia a la aplicación y se cargan en MySQL.

Consejo de rendimiento de MySQL n° 4: filtrar los resultados por los más baratos primero

Una excelente manera de optimizar MySQL es hacer primero un trabajo barato e impreciso, luego el trabajo duro y preciso en el conjunto de datos resultante más pequeño.

Por ejemplo, suponga que está buscando algo dentro de un radio determinado de un punto geográfico. La primera herramienta en la caja de herramientas de muchos programadores es la fórmula del círculo máximo (Haversine) para calcular la distancia a lo largo de la superficie de una esfera. El problema con esta técnica es que la fórmula requiere muchas operaciones trigonométricas, que consumen mucha CPU. Los cálculos de círculo máximo tienden a ejecutarse lentamente y hacen que la utilización de la CPU de la máquina se dispare.

Antes de aplicar la fórmula del círculo máximo, reduzca sus registros a un pequeño subconjunto del total y recorte el conjunto resultante a un círculo preciso. Un cuadrado que contiene el círculo (de forma precisa o imprecisa) es una forma fácil de hacer esto. De esa manera, el mundo fuera de la plaza nunca se verá afectado por todas esas costosas funciones de activación.

Consejo de rendimiento de MySQL n° 5: conozca las dos trampas mortales de escalabilidad

La escalabilidad no es tan vaga como puede creer. De hecho, existen definiciones matemáticas precisas de escalabilidad que se expresan como ecuaciones. Estas ecuaciones destacan por qué los sistemas no se escalan tan bien como deberían.

Tomemos como ejemplo la Ley de escalabilidad universal, una definición que es útil para expresar y cuantificar las características de escalabilidad de un sistema. Explica los problemas de escala en términos de dos costos fundamentales: serialización y diafonía.

Los procesos paralelos que deben detenerse para que se realice algo serializado están inherentemente limitados en su escalabilidad. Asimismo, si los procesos paralelos necesitan charlar entre ellos todo el tiempo para coordinar su trabajo, se limitan entre sí.

Evite la serialización y la diafonía, y su aplicación escalará mucho mejor. ¿En qué se traduce esto dentro de MySQL? Varía, pero algunos ejemplos serían evitar bloqueos exclusivos en filas. Las colas, el punto 3 anterior, tienden a escalar mal por esta razón.

Consejo de rendimiento de MySQL n° 6: no se centre demasiado en la configuración

Los administradores de bases de datos tienden a dedicar una gran cantidad de tiempo a ajustar las configuraciones. El resultado no suele ser una gran mejora y, a veces, incluso puede ser muy perjudicial. He visto muchos servidores “optimizados” que fallan constantemente, se quedan sin memoria y funcionan mal cuando la carga de trabajo se vuelve un poco más intensa.

Los valores predeterminados que se envían con MySQL son de talla única y están muy desactualizados, pero no es necesario configurar todo. Es mejor tener los fundamentos correctos y cambiar otras configuraciones solo si es necesario. En la mayoría de los casos, puede obtener el 95 por ciento del rendimiento máximo del servidor configurando aproximadamente 10 opciones correctamente. Las pocas situaciones en las que esto no se aplica serán casos extremos exclusivos de sus circunstancias.

En la mayoría de los casos, no se recomiendan las herramientas de “ajuste” del servidor porque tienden a brindar pautas que no tienen sentido para casos específicos. Algunos incluso tienen codificados consejos peligrosos e inexactos, como índices de aciertos de caché y fórmulas de consumo de memoria. Estos nunca fueron correctos, y se han vuelto aún menos correctos con el paso del tiempo.

Consejo de rendimiento de MySQL n° 7: tenga cuidado con las consultas de paginación

Las aplicaciones que se paginan tienden a poner de rodillas al servidor. Al mostrarle una página de resultados, con un enlace para ir a la página siguiente, estas aplicaciones generalmente se agrupan y ordenan de maneras que no pueden usar índices, y emplean un LIMITy offsetque hacen que el servidor haga mucho trabajo generando, luego descartando filas.

Las optimizaciones a menudo se pueden encontrar en la propia interfaz de usuario. En lugar de mostrar el número exacto de páginas en los resultados y los enlaces a cada página individualmente, puede mostrar un enlace a la página siguiente. También puede evitar que las personas vayan a páginas demasiado alejadas de la primera página.

En el lado de la consulta, en lugar de usar LIMITcon offset, puede seleccionar una fila más de la que necesita, y cuando el usuario hace clic en el enlace “página siguiente”, puede designar esa fila final como el punto de partida para el siguiente conjunto de resultados. Por ejemplo, si el usuario vio una página con las filas 101 a 120, también seleccionaría la fila 121; para representar la página siguiente, debe consultar al servidor las filas mayores o iguales que 121, límite 21.

Consejo de rendimiento de MySQL n° 8: guarde las estadísticas con entusiasmo, alerta de mala gana

El monitoreo y las alertas son esenciales, pero ¿qué sucede con el sistema de monitoreo típico? Comienza a enviar falsos positivos y los administradores del sistema configuran reglas de filtrado de correo electrónico para detener el ruido. Pronto su sistema de monitoreo es completamente inútil.

Me gusta pensar en el monitoreo de dos maneras: capturando métricas y alertando. Es muy importante capturar y guardar todas las métricas que puedas porque te alegrará tenerlas cuando intentes averiguar qué cambió en el sistema. Algún día, surgirá un problema extraño y le encantará la capacidad de señalar un gráfico y mostrar un cambio en la carga de trabajo del servidor.

Por el contrario, hay una tendencia a alertar demasiado. La gente suele alertar sobre cosas como el índice de aciertos del búfer o el número de tablas temporales creadas por segundo. El problema es que no existe un buen umbral para tal proporción. El umbral correcto no solo es diferente de un servidor a otro, sino de una hora a otra a medida que cambia su carga de trabajo.

Como resultado, alerta con moderación y solo en condiciones que indiquen un problema definitivo y procesable. Una tasa baja de aciertos en el búfer no es procesable ni indica un problema real, pero un servidor que no responde a un intento de conexión es un problema real que debe resolverse.

Consejo de rendimiento de MySQL n° 9: aprenda las tres reglas de indexación

La indexación es probablemente el tema más incomprendido en las bases de datos porque hay muchas formas de confundirse sobre cómo funcionan los índices y cómo los usa el servidor. Se necesita mucho esfuerzo para comprender realmente lo que está sucediendo.

Los índices, cuando se diseñan correctamente, cumplen tres propósitos importantes en un servidor de base de datos:

1. Los índices permiten que el servidor encuentre grupos de filas adyacentes en lugar de filas individuales. Mucha gente piensa que el propósito de un índice es encontrar filas individuales, pero encontrar filas individuales conduce a operaciones de disco aleatorias, lo cual es lento. Es mucho mejor encontrar grupos de filas, todas o la mayoría de las cuales son interesantes, que buscar filas una a la vez.
2. Los índices permiten que el servidor evite la clasificación leyendo las filas en el orden deseado. La clasificación es costosa. Leer filas en el orden deseado es mucho más rápido.
3. Los índices permiten que el servidor satisfaga consultas completas solo desde el índice, evitando la necesidad de acceder a la tabla. Esto se conoce de forma diversa como índice de cobertura o consulta de solo índice.

Si puede diseñar sus índices y consultas para aprovechar estas tres oportunidades, puede hacer que sus consultas sean varios órdenes de magnitud más rápidas.

Consejo de rendimiento de MySQL n° 10: aproveche la experiencia de sus pares

No intentes hacerlo solo. Si está desconcertado por un problema y está haciendo lo que le parece lógico y sensato, eso es genial. Esto funcionará aproximadamente 19 de cada 20. La otra vez, te hundirás en una madriguera que será muy costosa y te llevará mucho tiempo, precisamente porque la solución que estás probando parece tener mucho sentido.

Cree una red de recursos relacionados con MySQL, y esto va más allá de los conjuntos de herramientas y las guías de resolución de problemas. Hay algunas personas extremadamente conocedoras que acechan en listas de correo, foros, sitios web de preguntas y respuestas, etc. Las conferencias, ferias comerciales y eventos de grupos de usuarios locales brindan valiosas oportunidades para obtener información y establecer relaciones con colegas que pueden ayudarlo en caso de necesidad.

Para aquellos que buscan herramientas para complementar estos consejos, pueden consultar el  Asistente de configuración de Percona para MySQL , el Asesor de consultas de Percona para MySQL y los complementos de monitoreo de Percona . (Nota: Deberá crear una cuenta de Percona para acceder a esos dos primeros enlaces. Es gratis). El asistente de configuración puede ayudarlo a generar un archivo my.cnf de referencia para un nuevo servidor que sea superior a los archivos de muestra que se envían con el servidor. El asesor de consultas analizará su SQL para ayudar a detectar patrones potencialmente malos, como consultas de paginación (No. 7). Los complementos de monitoreo de Percona son un conjunto de complementos de monitoreo y gráficos para ayudarlo a guardar estadísticas con entusiasmo y alertar de mala gana (No. 8). Todas estas herramientas están disponibles gratuitamente.

Fuente: https://www.infoworld.com/article/3210905/10-essential-performance-tips-for-mysql.html