Cómo planificar y optimizar Amazon Aurora con compatibilidad de MySQL para cargas de trabajo consolidadas

Amazon Aurora con compatibilidad de MySQL es una opción popular para los clientes que buscan consolidar las cargas de trabajo de bases de datos. Aurora MySQL es un motor de base de datos relacional que combina la velocidad y confiabilidad de las bases de datos comerciales de alta gama con la simplicidad y rentabilidad de las bases de datos de código abierto. También ofrece hasta cinco veces el rendimiento de la edición estándar de la comunidad de MySQL.

En algunas orientaciones para ayudarle a optimizar Amazon Aurora para grandes cargas de trabajo de bases de datos consolidadas. También contesta preguntas comunes, como “¿Cuánto puedo consolidar?” y “¿Qué tan grande puede llegar a ser mi conjunto de datos?” Aunque estas preguntas son simples, no siempre tienen respuestas simples. Las respuestas dependen mucho de su conjunto de datos y patrones de carga de trabajo.

Consolidación de bases de datos definida

Para casos de uso de consolidación, nos enfocaremos en las siguientes dimensiones y luego discutcon mayor detalle cómo opera Aurora MySQL en su contexto:

1. El tamaño de las tablas. Un resultado común de la consolidación son tablas más grandes. Si estás en el área de tecnología publicitaria, internet de las cosas (Internet of Things, IoT) o aplicaciones de consumo, normalmente divides la base de datos de una aplicación homogénea grande en muchos fragmentos, cada uno con un subconjunto de los datos. Con Aurora, es posible que no se acabe con el particionamiento (sharding) por completo, pero se consolida en menos fragmentos para reducir la sobrecarga operativa.
2. El número de tablas. Un mayor número de tablas también es un resultado de consolidación. Este resultado es común en aplicaciones de Software como Servicio (Software as a Service, SaaS) en donde se requiere aislamiento de usuarios, donde cada usuario suele tener su propia base de datos, o conjunto de tablas. Varios usuarios de este tipo se agrupan en menos clústeres de Aurora de mayor tamaño para reducir el costo operativo por usuario.
3. La utilización de la base de datos. La utilización de la carga de trabajo de la base de datos consolidada aumenta a través de una serie de métricas, incluyendo un mayor número de conexiones simultáneas.

En la práctica, encuentras aumentos en varias de estas dimensiones en un mismo proyecto. La siguiente guía debería ayudarle a optimizar su carga de trabajo en estas dimensiones.

Cómo optimizar para tablas grandes

Amazon Aurora almacena datos usando páginas de 16 KiB. Las páginas se agrupan en espacios de tablas(tablespaces), que actúan como contenedores para tablas y los índices asociados. Por defecto, Aurora utiliza un espacio de tabla separado para cada tabla, o para cada partición de una tabla si la tabla está particionada. La mayor parte de lo que contiene dicho espacio de tabla son las siguientes estructuras de datos:

• El índice agrupado(clustered index) que contiene los registros de tabla, ordenados por la clave primaria o clave única de la tabla. Si no hay ninguna clave disponible, se utiliza un identificador(ID) de fila interna que aumenta monótonamente para identificar y ordenar los registros.
• Los índices secundarios de la tabla.
• Valores almacenados externamente para campos de longitud variable (BLOB, TEXT, VARCHAR, VARBINARY, etc.) que no caben en el registro de índice agrupado.

La arquitectura de tabla anterior significa que no hay un número máximo fijo de filas que pueda almacenar en una tabla dada. El número máximo de fila depende de una serie de factores:

• El número máximo de valores únicos admitidos por su clave primaria o clave única. Por ejemplo, si usa un tipo de datos INT sin firmar(unsigned) para la clave pri(una opción popular), su tabla admite un máximo de 2 ³² o un poco más de 4.29 mil millones de filas.
• El número y tamaño de tus índices secundarios.
• La cantidad de datos de longitud variable que se almacenan directamente en los registros de índice agrupados o en páginas externas.
• Con qué eficacia se utilizan las páginas de datos.

Su diseño de esquema y los patrones de consulta son factores más importantes que el número de filas para determinar cuánto puede consolidar efectivamente una tabla en la práctica. A medida que aumenta el número de filas en una tabla, también lo hace el tamaño del índice agrupado y los índices secundarios y el tiempo que lleva recorrer estos índices. Por lo tanto, el rendimiento de su consulta disminuye con el tamaño de la tabla. Veamos algunas de las mejores prácticas y formas de mitigar las disminuciones del rendimiento con mayor profundidad.

Diseñar esquemas para tablas con gran número de registros

La densidad de registros por página, en contexto de sus patrones de consulta y esquema, se vuelve cada vez más relevante en tamaños de tablas grandes. La longitud máxima de fila en Aurora MySQL, excluyendo los datos de longitud variable, es un poco menos de 8 KB de tamaño (la mitad de la página de datos). La base de datos administra las páginas para mantener la eficiencia del almacenamiento, sin sacrificar el rendimiento. Al igual que InnoDB en la Edición de MySQL de la Comunidad (MySQL community edition), Aurora MySQL deja una parte de la página sin utilizar para acomodar escrituras futuras y reducir el número de divisiones de página. También intenta fusionar páginas si la relacion de llenado cae después del 50 por ciento. Debido a que las páginas nunca están completamente llenas, siempre hay una cierta cantidad de sobrecarga de almacenamiento.

El diseño óptimo del esquema es la mejor manera de garantizar que la sobrecarga útil no se conviertan en excesivos desperdicios de almacenamiento. Dicho desperdicio se traduce en una mayor utilización de recursos y latencia, lo que lleva a una carga de trabajo más allá de lo que consideraría un rendimiento aceptable.

Hay dos pautas generales para el diseño efectivo del esquema:

1. Toda la información en una fila de la tabla debe ser igualmente valiosa. Es decir, deberas consultar y manipular datos en todos los campos de la fila con igual frecuencia. Almacenar datos más calientes y fríos en la misma fila genera ineficiencias.
2. Elija siempre el tipo de dato más pequeño que pueda para representar los valores almacenados en una columna determinada. Los ahorros a nivel de fila pueden parecer mínimos, pero el efecto se magnifica potencialmente en miles de millones de filas, volviéndose significativo.

Si su esquema contiene campos de longitud variable, Aurora MySQL intenta almacenar tantos datos de longitud variable en el registro de índice agrupado como pueda contener, y el resto almacenado en páginas externas. Los registros de datos grandes dan como resultado una menor densidad de registros en las páginas de datos, lo que provoca una disminución del rendimiento de las consultas. Pero es posible que aún desee registros grandes si sus consultas afectan predominantemente a todos los datos del registro (lecturas y escrituras), incluidos los campos de longitud variable. Si ese no es el caso, podría resultarle beneficioso almacenar campos tan grandes en tablas separadas. Mejor aún, puede almacenarlos fuera de la base de datos completamente en un almacén de objetos, como Amazon S3.

Los índices son efectivos para aumentar el rendimiento de las consultas. Sin embargo, tienen un costo adicional, consumen almacenamiento adicional y espacio de memoria y reducen el rendimiento de escritura. Los registros de índice secundario no apuntan directamente a las coordenadas de almacenamiento físico de una fila. En cambio, apuntan a un valor de clave primaria para esa fila. En consecuencia, cada registro de índice secundario contiene una copia del valor de clave primaria para la fila subyacente.

Por lo tanto, las claves primarias compuestas también causan registros de índice más grandes y, finalmente, reducen la eficiencia de almacenamiento y de operaciones de E/S(I/O). Use solo los índices que necesite y recuerde que la selectividad del índice se mueve de izquierda a derecha en índices compuestos. Es posible que tenga la oportunidad de reducir el número de índices, reemplazándolos por menos índices compuestos, si sus patrones de consulta permiten seguir esa regla de selectividad.

Finalmente, al usar un diseño de esquema efectivo, puede tener tablas con un mayor número de filas antes de encontrar un rendimiento inaceptable. Pero ¿cuál es ese número máximo de fila? en la práctica, depende de sus datos y de cómo interactúe con ellos.

Tablas de consulta con gran número de registros

Las particiones (y subparticiones) son una herramienta para mitigar la disminucion de rendimiento en tablas grandes. Debido a que cada partición se almacena en un espacio de tabla separado de forma predeterminada, contiene el índice agrupado, índices secundarios y páginas externas para ese subconjunto específico de datos, según lo determinado por la expresión de partición. Puede tener hasta 8,192 particiones y subparticiones por tabla. Sin embargo, un gran número de particiones crea problemas de rendimiento propios. Estos incluyen una mayor utilización de la memoria y problemas de rendimiento para consultas que utilizan una gran cantidad de particiones.

Como las estructuras de índice en las particiones son más pequeñas, son más rápidas de atravesar. Si sus patrones de consulta son selectivos para una sola partición, o un conjunto muy pequeño de particiones (una optimización llamada recorte de particiones(partition pruning)), es posible que vea beneficios de rendimiento. Sin embargo, las consultas que no son selectivas para algunas particiones específicas, por ejemplo, consultas con predicados que no incluyen las columnas particionadas, podrían ser más lentas. Este efecto se produce porque con las particiones, el motor tiene que atravesar múltiples índices más pequeños en lugar de uno solo más grande. Por lo tanto, el impacto en el rendimiento de las tablas particionadas grandes depende de la eficacia con la que pueda aprovechar el recorte o selección de particiones en su carga de trabajo.

Con tablas grandes, tener estadísticas precisas es importante para el optimizador de consultas. Las estadísticas precisas aseguran que el optimizador de consultas utilice los índices más selectivos con la cardinalidad correcta, mejorando así el rendimiento de las consultas. Por defecto, Aurora MySQL toma muestras de 20 páginas de índice aleatorias para estimar estadísticas y cardinalidad. Sin embargo, este número puede ser insuficiente cuando se trata de tablas muy grandes, o tablas con distribución de valores no uniformes dentro de las columnas. También por defecto, las estadísticas son persistentes en disco y se recalculan automáticamente después de que la tabla sufre cambios sustanciales. Un ejemplo es una operación de lenguaje de manipulación de datos (DML) que afecta a más del 10 por ciento de las filas.

Como los cambios de esta magnitud son menos frecuentes con tablas muy grandes, las estadísticas pueden llegar a ser menos precisas con el tiempo. A su vez, el rendimiento de las consultas afectadas se degrada progresivamente con el tiempo, o incluso abruptamente, a medida que se alcanza un punto crítico y el optimizador de consultas cambia el plan de ejecución. Si sospecha que esto es un problema, revise el plan de ejecución de la consulta utilizando la instrucción EXPLAIN para identificar los cambios del comportamiento esperado.

También le recomendamos que establezca un rendimiento base de referencia para las consultas claves de la carga de trabajo y supervise su rendimiento a lo largo del tiempo. El registro de consultas lentas(slow query log) es efectivo para registrar consultas que cruzan un cierto limite, pero menos efectivo para capturar una degradación lenta a lo largo del tiempo. Para monitorear el rendimiento de las consultas de forma continua, en la versión compatible con MySQL 5.6 puede usar el Esquema de Rendimiento de MySQL(MySQL Performance Schema). Sin embargo, tenga en cuenta que habilitarlo aumenta el consumo de memoria y también podría degradar el rendimiento general del sistema.

Existen dos mecanismos para mejorar la precisión de las estadísticas:

1. Monitorear de la antigüedad de la tabla y las estadísticas de índice para las tablas relevantes usando el Esquema del Sistema de MySQL(MySQL system schema) (INNODB_TABLE_STATS e INNODB_INDEX_STATS) y luego ejecutando ANALYZE TABLE para actualizar las estadísticas según corresponda.
2.  para sus instancias de base de datos y aumentar el número de páginas a las cuales se le están tomando muestras para aumentar la precisión (consulte la tabla siguiente). Sin embargo, el aumento de páginas para tomar muestras también aumenta la duración que se necesita para calcular las estadísticas.

Manejo de cambios frecuentes en su esquema de datos de tablas grandes

¿Qué tan grandes pueden ser sus tablas antes de que las operaciones del lenguaje de definición de datos (DDL) se vuelvan problemáticas? El tamaño es un factor, pero qué tan activa es su tabla podría importar más. Si su tabla mantiene miles de escrituras por Handling frequent changes to your data schema of large tables segundo, incluso tablas relativamente pequeñas con unos pocos millones de registros o menos pueden resultar problemáticas para ejecutar operaciones DDL.

Si su carga de trabajo, o las actualizaciones de su carga de trabajo, dependen de operaciones DDL frecuentes y cambios de esquema, esas acciones pueden limitar su capacidad de usar tablas muy grandes. Este comportamiento es similar al funcionamiento en la Edición de MySQL de la Comunidad (MySQL community edition). Las operaciones DDL fuera de línea copian sus datos a un nuevo espacio de tabla en el esquema correcto; por lo tanto, necesita tener disponible la capacidad de espacio adecuada. También bloquean la tabla por la duración de la operación, lo que es perjudicial para las cargas de trabajo normales. Las operaciones DDL en línea, cuando se pueden realizar, cambian los datos de la tabla directamente. Sin embargo, almacenan en segmentos nuevas escrituras en la tabla en un espacio temporal, bloqueando la tabla solo cuando esas escrituras se fusionan. Con cargas de trabajo que generan un gran número de escrituras en tablas sometidas a operaciones DDL en línea de larga duración, la cantidad de cambios a fusionar es relativamente grande. Este tamaño contribuye a que la fase de fusión de bloqueo tome más tiempo. En un caso extremo, el espacio temporal podría agotarse antes de que se complete la operación de cambio de la tabla, evitando que se complete la operación DDL en línea.

Aurora MySQL también soporta Fast DDL, que permite agregar una columna con valores nulos al final de una tabla como una operación casi instantánea. Esta característica ayuda a mitigar algunos de los problemas de DDL descritos anteriormente. Para operaciones DDL que no pueden ser manejadas eficientemente por operaciones DDL regulares o DDL rápidas, puede considerar usar la Herramienta de Cambio de Esquema en Línea de Percona (Percona Online Schema Change Tool) para ejecutar la operación. Si la herramienta se aplica a su caso de uso, puede realizar operaciones DDL de una manera menos disruptiva, pero la operación lleva más tiempo en tablas muy grandes.

Cómo optimizar para un gran número de tablas

Las cargas de trabajo consolidadas también pueden dar como resultado un gran número de tablas almacenadas en su clúster de Aurora. En la práctica, el número de tablas que se pueden consolidar razonablemente en un solo clúster de Aurora depende de su carga de trabajo y de patrones de acceso.

A diferencia de la Edición de MySQL de la Comunidad, donde las características del sistema de archivos limitan la escalabilidad de la base de datos en términos de tamaño y número de tablas, Aurora MySQL utiliza un servicio de almacenamiento estructurado de registros distribuidos de propósito especifico. Por lo tanto, muchas de las razones por las que usa configuraciones de espacio de tabla personalizadas en MySQL para mitigar las limitaciones heredadas del sistema de archivos no se aplican a Aurora. No hay impacto relacionado con el sistema de archivos por tener muchas tablas, ya sea operacionalmente o desde una perspectiva de recuperación de fallas. Aunque puede desactivar la opción inndb_file_per_table usando grupos de parámetros del clúster de base de datos personalizados de Aurora, no la recomendamos, porque ya no afecta el rendimiento ni el tiempo de recuperación.

Sin embargo, un gran número de tablas en Aurora sí afecta la utilización de la memoria. El consumo de memoria en un clúster Aurora con parámetros predeterminados es el siguiente.

Optimización de las memorias caché relacionadas con tablas cuando se utilizan grandes cantidades de tablas

Dos memorias caché son particularmente relevantes para cargas de trabajo con un gran número de tablas. Su configuración incorrecta puede potencialmente causar problemas de rendimiento y estabilidad.

La caché de tabla (o caché de tabla abierta) almacena estructuras de control para tablas abiertas como resultado de la actividad del usuario. Una tabla se abre independientemente por cada sesión, por lo que la caché puede contener varios elementos para la misma tabla si hay varias sesiones simultáneas accediendo a ella. En Aurora, hemos aumentado el tamaño predeterminado de esta caché hasta 6,000 tablas abiertas para instancias de base de datos en la clase . Sin embargo, este es un límite suave(soft limit). Esta caché puede convertirse en un consumidor de memoria significativo en función del número de tablas (y particiones en esas tablas) involucradas en sus sentencias SQL. Este efecto se amplifica por el número de sesiones simultáneas que su carga de trabajo esté ejecutando en la base de datos.

La caché de definición de tabla almacena la definición de una tabla (esquema, metadatos) para tablas de acceso común en la memoria. Cuantas más tablas activas tengas, mejor será tener sus definiciones en caché. Con Aurora, hemos aumentado el tamaño predeterminado de esa caché para almacenar hasta 20,000 definiciones para instancias de base de datos en la clase r4. Por lo tanto, esta caché puede convertirse en un consumidor de memoria significativo. Con cargas de trabajo que utilizan cientos de miles de tablas, también hay un incentivo para aumentar el tamaño de esta caché más allá del valor predeterminado, si la mayoría de esas tablas están activas. Este tamaño de caché también es un límite suave. El motor de base de datos MySQL no desaloja las definiciones de tabla para las tablas relacionadas con claves foráneas padre-hijo. Por lo tanto, el tamaño total de la caché puede exceder el límite de tamaño de caché.

Por lo tanto, la utilización eficiente de la memoria prácticamente puede limitar el número de tablas que puede operar en un clúster de Aurora con instancias de un tamaño determinado. Para mitigar esa limitación de memorias caché, es posible que tenga que usar una clase de instancia de base de datos más grande para acomodarlas. Alternativamente, es posible que necesite reducir la cantidad de memoria asignada a la caché de consultas o al grupo de búferes para compensar. Esta reducción a su vez podría afectar el rendimiento de la carga de trabajo de otras maneras, ya que menos de sus conjuntos de datos de trabajo caben en la memoria. Pero es difícil identificar un número exacto de tablas que sean “demasiadas”. Los siguientes ejemplos ilustran mejor algunos de estos efectos.

El siguiente gráfico muestra la memoria libre reportada a través de métricas de Monitoreo Mejorado(Enhanced Monitoring) de una prueba de procesamiento de transacciones en línea(Online Transaction Processing, OLTP) de lectura y escritura de sysbench en una base de datos con 1,000 tablas, usando solo 40 conexiones simultáneas. Esta carga de trabajo se ejecutó por solo 10 minutos en una instancia de base de datos db.r4.2xlarge compatible con Aurora MySQL 5.6 con 61 GB de memoria, una opción de clase de instancia popular.

Para esta prueba, el siguiente comando prepara la base de datos y tablas antes de que se ejecute la prueba:

sysbench oltp_read_write --table-size=1000 --tables=1000 --threads=40 --time=600 --max-requests=0 --db-driver=mysql --mysql-host=<aurora_db_cluster_endpoint> --mysql-db=sbtest --mysql-user=<user> --mysql-password=<password> prepare

La memoria libre del sistema cae repentinamente de más de 12.2 GB a 5 GB cuando comienza la prueba, y los recursos de CPU se agotan casi por completo a lo largo de la prueba. A diferencia de la Edición de MySQL de la Comunidad, Aurora preasigna el grupo de memoria(Buffer Pool), y en la prueba anterior ya estaba alojado. Se realizó la prueba con un número comparativamente pequeño de tablas activas (1,000) y conexiones concurrentes totales (40). El consumo de memoria se debe predominantemente a la caché de tabla y al efecto de amplificación que tiene cada conexión cuando están involucradas grandes cantidades de tablas activas.

El parámetro table_open_cache en el grupo de parámetros de la instancia de base de datos controla el tamaño de la caché de la tabla. Por defecto, este parámetro se establece mediante la siguiente fórmula:

lesser of (<DB instance class memory in bytes>/1,179,121) or 6,000

A modo de comparación, la tabla siguiente representa una prueba similar. La única diferencia es que la prueba está accediendo sólo a 500 tablas. Aquí, la memoria libre del sistema también cae repentinamente de más de 12.2 GB a aproximadamente 2.5 GB cuando comienza la prueba.

El siguiente ejemplo ilustra el efecto de la caché de definición de tabla cuando se utilizan grandes números de tablas. La carga de trabajo de ejemplo en esta prueba crea 100,000 tablas simples, cada una con una clave primaria de tipo entero autoincremental, una columna de tipo fecha y hora, una columna de tipo punto flotante y dos columnas de tipo cadena corta. La prueba inserta una fila en cada tabla simple usando una sola conexión, ejecutándose en una instancia de base de datos db.r4.large compatible con Aurora MySQL 5.7 con 15.25 GB de memoria. No se están ejecutando otras actividades en el clúster Aurora, y el clúster comienza vacío.

Se puede ver cómo disminuye la memoria libre a medida que aumenta la carga de trabajo. La memoria libre se estabiliza a medida que se alcanza el límite de caché, consumiendo ~700 MB adicionales de memoria en general, predominantemente para la caché de definición de tabla.

El parámetro table_definition_cache en el grupo de parámetros de la instancia de base de datos controla el tamaño de la caché de definición de tabla. Por defecto, este parámetro se establece mediante la siguiente fórmula:

lesser of (<DB instance class memory in bytes>/393,040) or 20,000

En conclusión, el número de tablas que puedes consolidar en la práctica depende de varios factores. Estos factores son: cuántas de las tablas se utilizan activamente, la cantidad de memoria disponible y la cantidad de conexiones simultáneas que necesita soportar.

Cómo optimizar para una mayor utilización de los recursos de la base de datos

Anteriormente en esta publicación, se discutalgunas de las formas en que las tablas más grandes o un mayor número de tablas impactan en la utilización de los recursos del servidor como el CPU o la memoria. Pero la consolidación de la carga de trabajo en sí misma provoca una mayor utilización. Si se reduce el número de particiones de la base de datos, se podrían establecer más conexiones simultáneas para cada partición de base de datos restante. Cada base de datos consolidada hace más trabajo; el volumen de consultas de lectura y escritura aumenta.

Amazon Aurora para MySQL viene con agrupación de conexiones(connection pooling) de servidores internos y multiplexación de subprocesos, lo que ayuda a reducir la contención y mejorara la escalabilidad cuando se trata de miles de conexiones simultáneas. Puede configurar cada instancia de base de datos Aurora para permitir hasta 16,000 conexiones simultáneas. Pero su carga de trabajo y la selección de clase de instancia de base de datos podrían limitar el máximo a un número menor que ese.

Cada conexión, sesión, y subproceso consume una cantidad variable de memoria para varios búferes, cachés y otras estructuras de memoria basadas en la instrucción SQL específica que se está ejecutando actualmente. Este consumo no es determinista, y compite por la misma cantidad de memoria disponible que las otras estructuras discutidas anteriormente al inicio de esta publicación. Para comprender las mejores prácticas para una administración y escalamiento eficiente de las conexiones, el documento técnico de Amazon Aurora MySQL DBA Handbook for Connection Management es un gran recurso. Contiene consejos útiles para ayudarle a optimizar la utilización de las conexiones para cargas de trabajo más grandes y de mayor rendimiento.

Resumen

Una serie de factores están involucrados cuando considera a Amazon Aurora con compatibilidad de MySQL como una solución para consolidar múltiples cargas de trabajo de bases de datos. Aunque no es una lista exhaustiva, los temas anteriores enumeran un conjunto común de consideraciones que vemos al ayudar a nuestros clientes a implementar tales consolidaciones. Cada carga de trabajo es diferente, por lo que los límites prácticos para la consolidación son diferentes en cada caso.

Como práctica recomendada, debe probar minuciosamente los cambios de configuración de los valores predeterminados a escala de producción e implementarlos solo si ve un impacto positivo cuantificable en el rendimiento y la confiabilidad. Esta mejor práctica es especialmente cierta cuando se traen configuraciones de la Edición de MySQL de la Comunidad, porque Aurora podría no comportarse de la misma manera. Para obtener más detalles sobre cómo ejecutar un proyecto de consolidación de cargas de trabajo, consulte la publicación Reducir el consumo de recursos consolidando su sistema particionado en Aurora en el blog de la base de datos de AWS.

Este artículo fue traducido del Blog de AWS en Inglés.