Consejos para optimizar el rendimiento de MySQL

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 las configuraciones de hardware. A continuación, se indican algunas consideraciones importantes sobre el hardware:

La potencia de procesamiento es uno de los factores más importantes de un sistema de base de datos eficaz. El número de conexiones, usuarios o hilos simultáneos determina el número de núcleos necesarios para procesar las solicitudes de la base de datos. La CPU asignada a la base de datos debe ser capaz de gestionar la carga de trabajo normal y la carga de trabajo máxima (extrema) para que las aplicaciones tengan un rendimiento óptimo.

En el caso de Cloud SQL, la solución MySQL totalmente gestionada de Google Cloud, 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 permite escalar el número de vCPUs de tu instancia, lo que facilita el cumplimiento de los requisitos de memoria y rendimiento de red de tu aplicación.

Un factor importante para determinar la cantidad de memoria que hay que asignar a una base de datos es asegurarse de que el conjunto de trabajo quepa 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 saber si se ha asignado suficiente memoria es comprobar el estado de lectura del disco en la base de datos. En condiciones normales de carga de trabajo, las lecturas de disco deberían ser mínimas o muy reducidas.

Si no se asigna suficiente memoria a la instancia, es posible que se produzcan errores de "Memoria agotada" que hagan que la instancia de la base de datos se reinicie y provoque periodos de inactividad de la base de datos o de la aplicación. 

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

El SSD ofrece un mayor rendimiento que el HDD. Por lo tanto, elige siempre SSD para un mejor rendimiento, sobre todo para cargas de trabajo de producción. 

Las operaciones de entrada/salida por segundo (IOPs) de lectura y escritura asignadas a la instancia dependen de la cantidad de almacenamiento asignado al crear la instancia. Cuanto mayor es el tamaño del disco, mayores son las IOPs de lectura y escritura. De ahí que sea aconsejable crear instancias con un tamaño de datos mayor para obtener 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 se configurará su base de datos una vez creada.

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, por lo que a los usuarios les resulta más fácil crear una instancia de base de datos lo más cerca posible de los usuarios finales.

Cloud SQL ofrece una forma sencilla de escalar o reducir verticalmente los recursos (CPU, memoria o almacenamiento) asignados a una instancia de base de datos. Esto puede ser útil para cargas de trabajo con requisitos de recursos variables. Por ejemplo, los usuarios pueden aumentar (escalar verticalmente) los recursos durante el periodo de mayor demanda de carga de trabajo y, después, reducirlos verticalmente cuando haya pasado la situación de máxima carga de trabajo.

En esta sección se incluyen las prácticas recomendadas de configuración de bases de datos MySQL para mejorar el rendimiento.

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. Más información sobre las versiones de MySQL compatibles con Cloud SQL.

En el caso de las instancias de MySQL, InnoDB es el único motor de almacenamiento compatible. El tamaño del grupo de búferes de Innodb es el primer parámetro que un usuario debe definir para obtener un rendimiento óptimo. El grupo de búferes es el área de memoria que se asigna para almacenar cachés de tablas, cachés de índices, datos modificados antes del vaciado y otras estructuras internas como el índice de hash adaptativo (AHI). 

Cloud SQL define un valor predeterminado de aproximadamente el 72 % de la memoria de la instancia para asignarlo al grupo de búferes de InnoDB, en función del tamaño de la instancia (los valores predeterminados varían con el tamaño de las instancias). Más información sobre la configuración del grupo de búferes en diferentes tamaños de instancias. Cloud SQL permite 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úferes debe ser suficiente para que en la instancia haya memoria libre disponible para el búfer de sesión, la caché de diccionario y las tablas performance_schema (si están habilitadas), aparte del grupo de búferes InnoDB.

Los usuarios pueden consultar las lecturas de disco que se producen desde la instancia para identificar cuántos datos se leen desde los discos en comparación con las lecturas realizadas desde el grupo de búferes. Si hay más lecturas de disco, aumentar el tamaño del grupo de búferes y la memoria de la instancia mejoraría el rendimiento de las consultas de lectura.

El archivo de registro de InnoDB o registro de operaciones de rehacer registra los cambios en los datos de la tabla. El tamaño del archivo de registro de InnoDB define el tamaño del único archivo de registro de operaciones de rehacer. 

En el caso de las cargas de trabajo con muchas operaciones de escritura, si el tamaño del registro de operaciones de rehacer 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_file_size), 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.

La marca innodb_flush_log_at_trx_commit controla la frecuencia con la que los datos de registro se vacían en el disco y si se vacían con cada confirmación de transacción o no. 

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 la instancia de Cloud SQL principal. Sin embargo, Cloud SQL sí 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 réplica en las réplicas. Consulta más información sobre innodb_flush_log_at_trx_commit.

El tamaño del búfer de registro de InnoDB es la cantidad del 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 superior a 16 MB, InnoDB necesita hacer una E/S de disco antes de confirmar la transacción, lo que afecta al rendimiento. Para evitar errores de E/S de disco, aumenta el valor de innodb_log_buffer_size.

Cloud SQL define un valor predeterminado de 16 MB para el tamaño del 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 resultó pequeño y que InnoDB tuvo que esperar a que se produjera el vaciado antes de confirmar la transacción. Si el valor de innodb_log_waits es superior a 0 y va en aumento, incrementa 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';

La capacidad de E/S de InnoDB define el número de IOPS disponibles para las tareas en segundo plano (como el vaciado de páginas del grupo de búferes y la fusión de datos del búfer de cambio).  

Cloud SQL define el valor predeterminado de 5000 para innodb_io_capacity y de 10.000 para innodb_io_capacity. 

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. El valor de los cambios aplicados se puede encontrar usando la siguiente consulta en el shell de MySQL:

mysql -e 'show engine InnoDB status \G;' | grep Ibuf

Los búferes de sesión son la memoria asignada a las sesiones individuales. Si tu aplicación o tus consultas incluyen muchos procesos de inserción, actualización, ordenación y unión, y necesitan búferes más grandes, puedes definir valores de búfer altos al ejecutar la consulta en una sesión concreta para evitar sobrecargas de rendimiento. Los usuarios pueden evitar la asignación excesiva de búferes a nivel general, que aumenta los valores de todas las conexiones y que, a su vez, aumenta el uso total de memoria de la instancia. Al cambiar el valor predeterminado de los siguientes búferes, se mejora el rendimiento de las consultas. Estos valores se pueden cambiar mediante marcas de bases de datos.

sort_buffer_size,

join_buffer_size,

tmp_table_size,

max_heap_table_size

Ten en cuenta que se trata de valores de búfer por sesión. El aumento de los límites puede afectar a todas las conexiones y, en última instancia, puede provocar un aumento del uso total de memoria.

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_open_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 tablas ocupa menos espacio y no utiliza descriptores de archivo. Si el número de instancias de tabla en la caché de objetos del diccionario supera el límite de table_definition_cache, se inicia un mecanismo de LRU que marca las instancias de tabla para su desalojo y, finalmente, las elimina de la caché de objetos del diccionario para dejar espacio a la nueva definición de tabla. Este proceso se realiza cada vez que se abre un nuevo espacio de tablas. Solo se cierran los espacios de tabla inactivos. Este proceso de desalojo podría ralentizar la apertura de tablas.

Table_open_cache define el número de tablas abiertas para todos los hilos. Para comprobar si necesitas aumentar la caché de tablas, consulta la variable de estado Opened_tables. Si el valor de Opened_tables es grande y no utilizas FLUSH TABLES a menudo, plantéate aumentar el valor de la variable table_open_cache.

Table_open_cache y Table_open_cache se pueden ajustar al número real de tablas de la instancia. Más información sobre el recomendador de gran número de tablas abiertas de Cloud SQL.

Nota: Cloud SQL proporciona flexibilidad para cambiar estos valores.

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.

Las claves principales incrementadas automáticamente, preferiblemente con valores enteros, son idóneas para los sistemas de procesamiento de transacciones online (OLTP).

La ausencia de claves principales también es una de las principales razones del retraso o demora en la réplica en las situaciones de réplica basada en filas.

La creación de índices ayuda a obtener los 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 índices o no se usan, el rendimiento de la base de datos también puede verse afectado.

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 diseño de hardware, bases de datos de MySQL o esquemas. Cambia los parámetros de uno en uno y compáralos con los resultados de la comparativa para ver si hay alguna mejora.

La agrupación de conexiones es una técnica para crear y gestionar un grupo de conexiones listas para usarse 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.

Se pueden usar réplicas de lectura (varias, en toda 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 esta. Por otra parte, permitirá que haya 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 que permite enrutar consultas de bases de datos, se puede usar para escalar horizontalmente la base de datos de Cloud SQL para MySQL.

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, etc. 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.

Si se llevan a cabo demasiados cambios (actualizar, eliminar, insertar, etc.) en los registros de una única transacción, esto ocupará los recursos (bloqueos, búferes) de demasiados registros. Ello podría desbordar los búferes de registro que dan lugar a E/S del disco. El resto de las consultas tendrán que esperar a que se liberen los recursos o los bloqueos. Esto hace que se incluyan demasiados datos en el grupo de búferes, lo que impide que este pueda usarse posteriormente. 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 de ejecución más rápida.

Optimiza siempre las consultas para obtener los mejores resultados, es decir, menos recursos y una ejecución más rápida. Consulta las recomendaciones para ajustar las consultas de MySQL.