Consejos de optimización del rendimiento para MySQL
La optimización del rendimiento es un aspecto fundamental a la hora de gestionar cualquier base de datos. La optimización del rendimiento se puede realizar en cada paso del proceso de gestión de bases de datos, desde la selección de los componentes de hardware y software para alojar los servidores de bases de datos hasta el diseño del modelo y las configuraciones de esquemas. En este documento se explican consejos de optimización del rendimiento para bases de datos MySQL en la nube, en concreto, Cloud SQL para MySQL, incluidas las prácticas recomendadas para crear instancias de bases de datos y optimizar las que ya tengo.
Artículos relacionados
Consideraciones sobre el hardware
Las configuraciones de hardware son un factor importante para el rendimiento de las bases de datos. Es importante conocer bien el número de usuarios activos y simultáneos de una aplicación, el tamaño de la base de datos y los índices, y la latencia esperada de la aplicación o del servicio antes de definir el de hardware. A continuación, se indican algunas consideraciones importantes sobre el hardware:
Unidad central de procesamiento (CPU)
La potencia de procesamiento es uno de los factores más importantes en el rendimiento de un sistema de base de datos. El número de conexiones, usuarios o hilos simultáneos determina el número de núcleos necesarios para procesar solicitudes de bases de datos. La CPU asignada a la base de datos debe ser capaz de gestionar la carga de trabajo normal (y de carga de trabajo máxima) para que las aplicaciones tengan un rendimiento óptimo.
En el caso de Cloud SQL, la solución MySQL totalmente gestionada de MySQL, la CPU se asigna en forma de CPU virtual (vCPU). El número de vCPUs asignadas a una base de datos determina en última instancia la cantidad de memoria y el rendimiento de red de una instancia de base de datos, ya que cada vCPU tiene una cantidad máxima de memoria asignada e incluso el rendimiento de red varía. según el número de vCPUs. Cloud SQL es flexible para escalar el número de vCPUs a tu instancia, de modo que puedas cumplir los requisitos de memoria y rendimiento de red de tu aplicación fácilmente.
Memoria
Un factor importante para determinar la cantidad de memoria que asignar a una base de datos es asegurarte de que el conjunto de trabajo encaja en el grupo de búferes. Un conjunto de trabajo son los datos que la base de datos utiliza de forma activa en cualquier momento. La memoria asignada debería ser suficiente para almacenar este conjunto de trabajo o datos a los que se accede con frecuencia, que suelen estar compuestos por datos de bases de datos, índices, búferes de sesión, caché de diccionario y tablas de hash. Una forma de comprobar si se ha asignado suficiente memoria es comprobar si el disco lee el estado en la base de datos. En condiciones óptimas, las lecturas de disco deberían ser menores o muy mínimas.
Si no se asigna suficiente memoria a la instancia, se pueden encontrar problemas con el estado Sin memoria que provocarán que la instancia de la base de datos se reinicie y dirija a la base de datos o el periodo de inactividad de la aplicación.
Almacenamiento
El almacenamiento de las bases de datos es otro componente que desempeña un papel importante a la hora de optimizar el rendimiento. Cloud SQL ofrece dos tipos de almacenamiento
- SSD (predeterminado)
- HDD
Por su parte, SSD ofrece un mayor rendimiento que HDD. Por eso, siempre han elegido el SSD para mejorar el rendimiento, sobre todo para las cargas de trabajo de producción.
Las operaciones de entrada/salida por segundo (IOP) que se leen y escriben a la instancia dependen de la cantidad de almacenamiento asignado al crear la instancia. Cuanto mayor es el tamaño del disco, más alta es la EPI de lectura y escritura. Por lo tanto, se recomienda crear instancias con un tamaño de datos mayor para un mejor rendimiento de IOPS. En la siguiente captura de pantalla de la consola de Google Cloud, se muestra el resumen de los recursos (incluida la capacidad máxima) asignados a la instancia de la base de datos en el momento de la creación. Esto ayuda a los usuarios a confirmar y entender exactamente cómo la base de datos se configurará una vez que se cree.
Zona o país
Una de las formas de reducir la latencia de red es elegir la región de instancia que esté más cerca de la aplicación o del servicio. Cloud SQL para MySQL está disponible en todas las regiones de Google Cloud, lo que facilita a los usuarios crear una instancia de base de datos lo más cerca posible de los usuarios finales.
Escala elástica
Cloud SQL ofrece una forma sencilla de escalar verticalmente o reducir los recursos (CPU, memoria o almacenamiento) asignados a una instancia de base de datos. Esto puede ser útil para cargas de trabajo con distintos requisitos de recursos. Por ejemplo, los usuarios pueden aumentar los recursos en vertical durante el periodo en el que se incrementa la carga de trabajo y, después, reducirlos cuando la carga máxima es alta.
Configuraciones de MySQL
En esta sección se incluyen las prácticas recomendadas de configuración de bases de datos MySQL para mejorar el rendimiento.
Versión
Elige la última versión de MySQL al crear una base de datos. Las últimas versiones incluyen correcciones de errores y optimizaciones para mejorar el rendimiento en comparación con las versiones anteriores. Cloud SQL proporciona la última versión de MySQL disponible en el mercado y la convierte en la versión predeterminada al crear una nueva base de datos. Obtén más información sobre las versiones de MySQL admitidas en Cloud SQL.
Tamaño del grupo de búfer de InnoDB
En el caso de las instancias de MySQL, InnoDB es el único motor de almacenamiento compatible. El tamaño del grupo de búfer de Innodb es el primer parámetro que un usuario querría definir para obtener un rendimiento óptimo. El grupo de búferes es el área de memoria que se asigna a las cachés de las tablas de tablas, las cachés de índice, los datos modificados antes de borrarlos y otras estructuras internas como el índice de hash adaptativo (AHI).
Cloud SQL define el valor predeterminado de alrededor del 72 % de la memoria de la instancia que se va a asignar al grupo de búfer InnoDB, según el tamaño de la instancia (los valores predeterminados varían con los tamaños de instancia). Consulta más información sobre la configuración del grupo de búferes en diferentes tamaños de instancia. Cloud SQL ofrece la flexibilidad de modificar el tamaño del grupo de búferes en función de las necesidades de tus aplicaciones con marcas de bases de datos.
El tamaño del grupo de búfer debe ser suficiente para que en la instancia haya suficiente memoria libre disponible para el búfer de sesión, la caché de diccionario, las tablas rendimiento_esquema (si están habilitadas) aparte del grupo de búfer InnoDB.
Los usuarios pueden consultar las lecturas de discos de la instancia para identificar cuántos datos se están leyendo en los discos en comparación con las lecturas satisfechas del grupo de búferes. Si hay más lecturas de disco, aumentar el tamaño del grupo de búfer y la memoria de la instancia mejorarían el rendimiento de las consultas de lectura.
Rehacer el tamaño del archivo de registro InnoDB
El archivo de registro de InnoDB o rehacer el registro registran los cambios de datos en los datos de la tabla. El tamaño del archivo de registro de InnoDB define el tamaño de rehacer el único archivo el registro.
En el caso de las cargas de trabajo de escritura pesadas, si el tamaño de registro es mayor, hay más espacio para las operaciones de escritura sin tener que hacer actividades de vaciado de puntos de control frecuentes y ahorrar E/S de disco, lo que mejora el rendimiento de la escritura. El tamaño total del registro de rehacer, que se puede calcular como innodb_log_file_size*innodb_log_files_in_group, debería ser suficiente para cubrir al menos 1- 2 horas de escritura de datos durante los concurridos periodos de acceso a las bases de datos.
Cloud SQL define un valor predeterminado de 512 MB. Cloud SQL también permite aumentar el tamaño del archivo de registro InnoDB mediante marcas de bases de datos.
NOTA: Si aumentas el valor del tamaño del archivo de registro InnoDB, aumentas el tiempo de recuperación de fallos.
Durabilidad
La etiqueta innodb_flush_log_at_trx_commit controla la frecuencia con la que los datos de registro se eliminan del disco y si se borran de cada confirmación de transacción.
Puedes mejorar el rendimiento de la escritura en las réplicas de lectura cambiando los valores de innodb_flush_log_at_trx_commit a 0 o 2.
Cloud SQL no permite cambiar la configuración de durabilidad en CloudSQL principal. Sin embargo, CloudSQL permite cambiar la marca en las réplicas de lectura. Al reducir la durabilidad de las réplicas de lectura, se mejora el rendimiento de escritura en las réplicas. De esta forma, se soluciona el retraso de replicación en las réplicas. Consulta más información sobre innodb_flush_log_at_trx_commit.
Tamaño del búfer de registro de InnoDB
El tamaño del búfer de registro de InnoDB es la cantidad de búfer que usa InnoDB para escribir en el archivo de registro (registro de rehacer).
Si las transacciones (inserciones, actualizaciones o eliminaciones) de la base de datos son grandes y el búfer utilizado es de más de 16 MB, InnoDB se necesita hacer un PI de disco antes de confirmar la transacción, lo que afecta al rendimiento. Para evitar el PI de disco, aumenta el valor de innodb_log_buffer_size.
CloudSQL define un valor predeterminado de 16 MB para el tamaño de búfer de registro de InnoDB. La variable de estado de MySQL innodb_log_waits muestra el número de veces que innodb_log_buffer_size era pequeño y que InnoDB tenía que esperar a que el color se borrara antes de confirmar la transacción. Si el valor de innodb_log_waits es superior a 0 y aumenta, después aumenta el valor de innodb_log_buffer_size mediante marcas de bases de datos para mejorar el rendimiento. Para identificar el valor de innodb_log_buffer_size e innodb_log_waits, ejecuta las siguientes consultas en el shell de MySQL (CLI). ). Estas consultas muestran el valor de las variables de estado y las variables globales en MySQL.
MOSTRAR VARIABLES GLOBALES COMO 'innodb_log_buffer_size';
MOSTRAR ESTADO MUNDIAL COMO 'innodb_log_waits';
Capacidad de E/S de InnoDB
La capacidad de E/S de InnoDB define el número de IOPS disponibles para las tareas en segundo plano (como vaciado de páginas del grupo de búferes y combinación de datos del búfer de cambio).
Cloud SQL define el valor predeterminado de 5000 para innodb_io_capacity y 10.000 para innodb_io_capacity_max.
Esta opción predeterminada es ideal para la mayoría de las cargas de trabajo, pero si dicha carga presenta grandes cambios o no se ha aplicado en la instancia, es alta y, si tienes suficientes PIs disponibles en ella, aumenta la innodb_io_capacity y la innodb_io_capacity_máx.. El valor de los cambios aplicados se puede encontrar usando la siguiente consulta en el shell de MySQL:
mysql -e "muestra el estado del motor InnoDB \G;' | Grep Ibuf
Búferes de sesión
Los búferes de sesión son la memoria asignada a las sesiones individuales. Si tu aplicación o consultas incluyen muchos procesos de inserción, actualización, ordenación, unión y necesidad de búferes superiores, y definir valores de búfer altos al ejecutar la consulta en una sesión concreta evitarás que se produzcan sobrecargas de rendimiento. Los usuarios pueden evitar la asignación excesiva del búfer a nivel mundial, lo que aumenta el valor de todas las conexiones y, a su vez, aumenta el uso total de la memoria de la instancia. Al cambiar el valor predeterminado de estos búferes, se mejora el rendimiento de las consultas. Estos valores se pueden cambiar mediante marcas de bases de datos.
Estos valores son para cada búfer de sesión. El aumento de los límites puede afectar a todas las conexiones y, en última instancia, provocar un aumento del uso general de la memoria.
Table_open_cache y Table_Definition_cache
Si tienes demasiadas tablas (en exceso de miles) en la instancia de base de datos (en una o en varias bases de datos), aumenta los valores de table_open_cache y table_Definition_cache para mejorar la velocidad de apertura de las tablas.
Table_Definition_cache acelera la apertura de tablas y solo tiene una entrada por tabla. La caché de definición de tabla ocupa menos espacio y no utiliza descriptores de archivos. Si el número de instancias de la tabla en la caché de objetos del diccionario supera el límite de la tabla_definición_tabla, se inicia un mecanismo de LRU para marcar las instancias de tabla de desalojamiento y, con el tiempo, los elimina de la caché de objetos del diccionario para dejar espacio a la nueva definición de la tabla. Este proceso se realiza cada vez que se abre un espacio de tabla nuevo. Solo se cierran los espacios de tabla inactivos. Este proceso de desahucio podría ralentizar la apertura de tablas.
Table_open_cache define el número de tablas abiertas para todas las conversaciones. Para comprobar si necesitas aumentar la caché de la tabla, consulta la variable de estado Opened_tables. Si el valor de Opened_tables es grande y no utilizas FLUSHTABLES y, a continuación, plantéate aumentar el valor de las table_open_cache.
Caché de tabla abierta_{1/} yalmacenamiento_de_tabla_de_tabla se puede establecer como el número real de tablas en la instancia, Más información sobre el recomendador de Cloud SQL de número alto de tablas abiertas
Nota: Cloud SQL proporciona flexibilidad para cambiar estos valores.
Recomendaciones de esquemas
Definir siempre las claves principales
Al definir claves principales en la tabla, se organizan físicamente los datos para que la búsqueda, la extracción y la clasificación de registros sean más rápidas y, por tanto, mejora el rendimiento.
Lo ideal es que las claves principales incrementadas automáticamente con valores enteros sean ideales para los sistemas de procesamiento online (OLTP).
La ausencia de claves principales también es una de las principales razones por las que se produce un retraso en la replicación en las situaciones de replicación basadas en filas.
Creación de índices
La creación de índices ayuda a obtener datos más rápidamente y, por lo tanto, mejora el rendimiento de las consultas de lectura. Crea índices para las columnas utilizadas en las cláusulas WHERE, ORDER BY y GROUP BY de las consultas.
NOTA: Si hay demasiados o no se usan los índices, el rendimiento de la base de datos también puede verse afectado.
Prácticas recomendadas para optimizar el rendimiento
Ejecutar comparativas
Ejecuta pruebas de rendimiento o comparativas para ver si la configuración es óptima o para mejorarla aún más ajustando las configuraciones de hardware, de base de datos MySQL o de diseño de esquema. Cambia los parámetros de uno en uno y compáralos con los resultados de la comparativa para ver si hay alguna mejora.
Grupo de conexiones
La agrupación de conexiones es una técnica para crear y gestionar un grupo de conexiones listas para usar en cualquier proceso que las necesite. La agrupación de conexiones puede aumentar significativamente el rendimiento de la aplicación y reducir el uso general de los recursos. Consulta información detallada sobre cómo gestionar las conexiones desde la aplicación, incluidos el tiempo de espera y el recuento de conexiones.
Distribuye la carga de trabajo de lectura a las réplicas de lectura
Se pueden usar réplicas de lectura (varias en la zona) para descargar la carga de trabajo de lectura de la instancia principal. Esto reduce la sobrecarga o la carga de la instancia principal y, a su vez, mejora el rendimiento de la instancia principal. Además, hay más recursos disponibles para las consultas de lectura en la réplica de lectura.
ProxySQL, un proxy de código abierto de MySQL de alto rendimiento capaz de enrutar consultas de bases de datos, se puede usar para escalar horizontalmente la base de datos de Cloud SQL para MySQL.
Evitar consultas de larga duración
Se sabe que las consultas que llevan activas varios minutos u horas provocan una disminución del rendimiento.
- Los registros de deshacer se usan para almacenar la versión antigua de las filas modificadas y restaurar la transacción, así como para proporcionar la lectura coherente (captura de datos) de una transacción. Estos registros de deshacer se almacenan en forma de listas vinculadas, con versiones recientes que apuntan a versiones anteriores, que dirigen a otras más antiguas, entre otras. Las transacciones de larga duración tienden a retrasar la purga de los registros de deshacer y, por tanto, aumentan la lista de registros de deshacer. InnoDB tiene que hacer un barrido del gran volumen de registros de deshacer y una larga lista vinculada, lo que reduce el rendimiento.
- Las consultas de larga duración también consumen recursos (como memoria, búferes o bloqueos), que no se liberan mucho tiempo y afectan a las demás consultas por la falta de recursos.
Evitar grandes transacciones
Demasiados cambios (actualizar, eliminar e insertar) en una sola transacción contienen recursos (bloqueos, búferes) y demasiados registros. Puede desbordar los búferes de registro que generan PIs de disco. Las consultas restantes tendrán que esperar a que se liberen los recursos o los bloqueos. Esto provoca que se incluyan demasiados datos en el grupo de búferes, lo que impide el uso posterior del grupo de búferes. La restauración de estas transacciones de gran tamaño también afecta al rendimiento de la base de datos. Para superar esta situación, la práctica recomendada es dividir las transacciones grandes en transacciones pequeñas y más rápidas.
Optimizar consultas
Optimiza siempre las consultas para obtener los mejores resultados, es decir, menos recursos y más rápido. Consulta las recomendaciones para ajustar las consultas de MySQL.
Herramientas para ajustar el rendimiento
Supervisión
Cloud SQL ofrece paneles de control predefinidos para varios productos de Google Cloud, incluido un panel de control de monitorización de Cloud SQL predeterminado. Los usuarios pueden usar este panel para monitorizar el estado general de sus instancias principal y de réplica. Los usuarios también pueden crear sus propios paneles personalizados para mostrar métricas de su interés. Si usas estos paneles y métricas, puedes identificar y abordar diversos cuellos de botella de rendimiento, como usar mucha CPU o un uso elevado de la memoria, siguiendo las recomendaciones mencionadas anteriormente. Las alertas también se pueden configurar en función de estas métricas.
Marca de consulta lenta
Se puede habilitar la marca de consultas lentas en la instancia de Cloud SQL para MySQL para identificar las consultas que duran más que el valor de long_query_time para su ejecución. Estas consultas lentas se pueden analizar y ajustar para mejorar el rendimiento. Aprende a habilitar y comprobar las consultas lentas de las instancias de Cloud SQL.
Esquema de rendimiento
El esquema de rendimiento permite monitorizar el bajo nivel de la instancia de MySQL. El esquema de rendimiento se puede habilitar en una instancia de Cloud SQL para MySQL con memoria de más de 15 Gb. Los informes del esquema Sys proporcionan varios informes para identificar los cuellos de botella, las esperas, la ausencia de índices, el uso de la memoria, etc.
Información útil sobre las consultas
Query Insights es una función nativa de Cloud SQL que permite analizar y analizar las consultas para mejorar su rendimiento. Las estadísticas de las consultas cuentan con funciones de monitorización intuitivas y proporcionan información de diagnóstico que puede ayudarte a identificar los motivos del problema de rendimiento.
Recomendaciones de rendimiento
El recomendador de número de tablas de Cloud SQL es también una función nativa de Cloud SQL que ofrece un rendimiento determinado.recomendaciones a los usuarios de Cloud SQL para mejorar el rendimiento de las bases de datos existentes, ofrecer sugerencias para definir la configuración con el fin de mejorar el rendimiento y reducir el coste de las instancias. Consulta las recomendaciones de Cloud SQL para obtener más información.
Productos y servicios relacionados
Google Cloud ofrece una base de datos MySQL gestionada que se adapta a las necesidades de tu negocio, desde la retirada de tu centro de datos on‑premise, la ejecución de aplicaciones de software como servicio o la migración de sistemas empresariales fundamentales.
