Prácticas recomendadas de escalado y rendimiento de Aurora MySQL

Aplique las prácticas recomendadas que se describen a continuación para mejorar el rendimiento y la escalabilidad de los clústeres de Aurora MySQL.

Utilización de clases de instancia T para el desarrollo y la prueba

Las instancias de Amazon Aurora MySQL que utilizan las clases de instancia de base de datos db.t2, db.t3 o db.t4g son más adecuadas para las aplicaciones que no admiten una carga de trabajo elevada durante un periodo de tiempo largo. Las instancias T se han diseñado para ofrecer un desempeño de referencia moderado y la capacidad de poder ampliarlo a un nivel considerablemente superior si así lo exige la carga de trabajo. Están pensadas para las cargas de trabajo que no utilizan toda la CPU con frecuencia o de forma continua, pero que de vez en cuando necesitan ampliar sus procesos. Recomendamos que las clases de instancia de base de datos T se utilicen solo para los servidores de desarrollo y de pruebas, o para otros servidores que no se utilicen para la producción. Para obtener más detalles sobre las clases de instancia T, consulte Instancias de rendimiento ampliable.

Si el clúster de Aurora tiene más de 40 TB, no utilice las clases de instancia T. Cuando la base de datos tiene un gran volumen de datos, la sobrecarga de memoria para administrar objetos de esquema puede exceder la capacidad de la instancia T.

No habilite el esquema de rendimiento de MySQL en instancias T de Amazon Aurora MySQL. Si el esquema de desempeño está habilitado, la instancia T podría quedarse sin memoria.

Cuando utilice una instancia T como una instancia de base de datos en un clúster de base de datos de Aurora MySQL, recomendamos lo siguiente:

Optimización de las consultas de combinación indexadas de Aurora MySQL con la captura previa de claves asíncronas

Amazon MySQL puede usar la característica de captura previa de claves asíncronas (AKP, por sus siglas en inglés) para mejorar el desempeño de las consultas que unen las tablas a través de los índices. Esta característica mejora el rendimiento al prever las filas necesarias para ejecutar consultas en las que una consulta JOIN requiere el uso del algoritmo de combinación Batched Key Access (BKA) y las características de optimización Multi-Range Read (MRR). Para obtener más información acerca de BKA y MRR, consulte Block Nested-Loop and Batched Key Access Joins y Multi-Range Read Optimization en la documentación de MySQL.

Para sacar máximo partido de la característica AKP, una consulta debe utilizar BKA y MRR. Por lo general, dicho tipo de consulta se produce cuando la cláusula JOIN de una consulta utiliza un índice secundario, pero requiere también algunas columnas del índice primario. Por ejemplo, puede usar la AKP cuando una cláusula JOIN represente a un equijoin en los valores de índice entre una tabla exterior pequeña y una interior grande, y el índice sea sumamente selectivo en la tabla grande. AKP trabaja conjuntamente con BKA y MRR para llevar a cabo una búsqueda del índice secundario al primario durante la evaluación de la cláusula JOIN. AKP identifica las filas necesarias para ejecutar la consulta durante la evaluación de la cláusula JOIN. Seguidamente, utiliza un subproceso en segundo plano para cargar de manera asíncrona las páginas que contienen dichas filas en la memoria antes de ejecutar la consulta.

AKP está disponible para la versión 2.10 y posteriores y la versión 3 de Aurora MySQL. Para obtener más información acerca de las versiones de Aurora MySQL, consulte Actualizaciones del motor de base de datos de Amazon Aurora MySQL.

Habilitación de la captura previa de clave asíncrona

Para habilitar la característica de AKP, establezca la configuración aurora_use_key_prefetch, una variable de MySQL Server, en on. De forma predeterminada, este valor se establece en on. No obstante, AKP no podrá habilitarse hasta que no active el algoritmo de combinación BKA y deshabilite la característica MRR basada en el costo. Para ello, debe ajustar los valores siguientes de optimizer_switch, una variable de MySQL Server:

En la actualidad, puede configurar estos valores únicamente en el nivel de sesión. El siguiente ejemplo ilustra cómo configurar estos valores a fin de habilitar AKP para la sesión actual ejecutando las instrucciones SET.

mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

Del mismo modo, puede usar las instrucciones SET para deshabilitar AKP y el algoritmo de combinación BKA, y volver a habilitar la característica MRR basada en el costo para la sesión actual, como se muestra en el ejemplo siguiente.

mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

Para obtener más información acerca de los cambios del optimizador batched_key_access y mrr_cost_based, consulte Switchable Optimizations en la documentación de MySQL.

Optimización de consultas para la captura previa de clave asíncrona

Puede confirmar si una consulta puede beneficiarse de la característica AKP. Para ello, utilice la instrucción EXPLAIN con el fin de perfilar la consulta antes de ejecutarla. La instrucción EXPLAIN ofrece información acerca del plan de ejecución que se va a utilizar para una consulta especificada

En el resultado de la instrucción EXPLAIN, la columna Extra describe información adicional que se incluye con el plan de ejecución. Si la característica AKP se aplica a una tabla que se ha utilizado en la consulta, esta tabla incluye uno de los siguientes valores:

En el siguiente ejemplo se muestra el uso de EXPLAIN para ver el plan de ejecución de una consulta que puede beneficiarse de AKP.

mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

Para obtener más información acerca del formato de salida EXPLAIN, consulte el tema sobre el Formato de salida de EXPLAIN ampliado en la documentación de MySQL.

Optimización de grandes consultas combinadas de Aurora MySQL con combinaciones hash

Si necesita unir una gran cantidad de datos mediante equijoin, una combinación hash puede mejorar el desempeño de las consultas. Puede habilitar las combinaciones hash para Aurora MySQL.

Una columna de combinación hash puede ser cualquier expresión compleja. En una columna de combinación hash puede realizar comparaciones entre distintos tipos de datos de las formas siguientes:

Las siguientes restricciones se aplican a las combinaciones hash de Aurora MySQL:

Habilitación de las combinaciones hash

Para habilitar combinaciones hash:

Las combinaciones hash están activadas de forma predeterminada en la versión 3 de Aurora MySQL y están desactivadas de forma predeterminada en la versión 2 de Aurora MySQL. En el siguiente ejemplo, se ilustra cómo habilitar las combinaciones hash para la versión 3 de Aurora MySQL. Puede emitir la declaración select @@optimizer_switch primero para ver qué otras configuraciones están presentes en la cadena del parámetro SET. La actualización de un ajuste en el parámetro optimizer_switch no borra ni modifica las demás configuraciones.

mysql> SET optimizer_switch='block_nested_loop=on';

Con esta configuración, el optimizador elige usar una combinación hash basada en el costo, las características de la consulta y la disponibilidad de los recursos. Si la estimación del costo es incorrecta, puede forzar al optimizador a elegir una combinación hash. Para ello, establezca hash_join_cost_based, una variable de MySQL Server, en off. En el siguiente ejemplo se ilustra cómo forzar al optimizador a elegir una combinación hash.

mysql> SET optimizer_switch='hash_join_cost_based=off';

Optimización de consultas para combinaciones hash

Para averiguar si una consulta puede beneficiarse de usar una combinación hash, utilice antes la instrucción EXPLAIN para perfilar la consulta. La instrucción EXPLAIN ofrece información acerca del plan de ejecución que se va a utilizar para una consulta especificada

En el resultado de la instrucción EXPLAIN, la columna Extra describe información adicional que se incluye con el plan de ejecución. Si se aplica una combinación hash a las tablas utilizadas en la consulta, esta columna incluye valores similares a los siguientes:

En el siguiente ejemplo se muestra el uso de EXPLAIN para ver el plan de ejecución de una consulta hash join (con combinación hash).

mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

En la salida, la Hash Join Inner table es la tabla utilizada para crear la tabla hash y la Hash Join Outer table es la tabla usada para sondear la tabla hash.

Para obtener más información acerca del formato de salida ampliado EXPLAIN, consulte Extended EXPLAIN Output Format (Formato de salida ampliado de EXPLAIN) en la documentación de producto de MySQL.

En Aurora MySQL 2.08 y versiones posteriores, puede usar sugerencias SQL para influir en si una consulta usa combinación hash o no, y las tablas que usar para los lados de compilación y sondeo de la combinación. Para obtener más información, consulte Sugerencias de Aurora MySQL.

Uso de Amazon Aurora para escalar las lecturas de una base de datos de MySQL

Puede usar Amazon Aurora con su instancia de base de datos MySQL para aprovechar las capacidades de escalado de lectura de Amazon Aurora y ampliar la carga de trabajo de lectura para su instancia de base de datos MySQL. Si desea usar Aurora para leer la escala de su instancia de base de datos de MySQL, cree un clúster de base de datos de Aurora MySQL y haga que sea una réplica de lectura de su instancia de base de datos de MySQL. A continuación, conéctese al clúster Aurora MySQL para procesar las consultas de lectura. La base de datos de origen puede ser una instancia de base de datos de RDS for MySQL o una base de datos de MySQL que se ejecute fuera de Amazon RDS. Para obtener más información, consulte Escalado de lecturas para su base de datos MySQL con Amazon Aurora.

Optimización de las operaciones de marca temporal

Cuando el valor de la variable de sistema time_zone se establece en SYSTEM, cada llamada a una función de MySQL que requiera un cálculo de zona horaria realiza una llamada a la biblioteca del sistema. Al ejecutar instrucciones SQL que devuelvan o modifiquen dichos valores de TIMESTAMP con una alta concurrencia, es posible que aumente la latencia, la contención de bloqueos y el uso de la CPU. Para obtener más información, consulte time_zone en la documentación de MySQL.

Para evitar este comportamiento, se recomienda cambiar el valor del parámetro time_zone del clúster de base de datos a UTC. Para obtener más información, consulte Modificación de los parámetros en un grupo de parámetros de clúster de base de datos en Amazon Aurora.

Si bien el parámetro time_zone es dinámico (no requiere el reinicio del servidor de base de datos), el nuevo valor solo se usará para las conexiones nuevas. Para asegurarse de que todas las conexiones se actualicen y utilicen el nuevo valor de time_zone, se recomienda que recicle las conexiones de la aplicación después de actualizar el parámetro del clúster de base de datos.

Errores de desbordamiento de ID de índice virtual

Aurora MySQL limita los valores de los ID de índice virtuales a 8 bits para evitar un problema provocado por el formato de deshacer de MySQL. Si un índice supera el límite de ID de índice virtual, es posible que el clúster no esté disponible. Cuando un índice se acerca al límite de ID de índice virtual o cuando intenta crear un índice por encima del límite de ID de índice virtual, es posible que RDS arroje un código de error 63955 o un código de advertencia 63955. Para solucionar un error de límite de ID de índice virtual, le recomendamos que vuelva a crear la base de datos con un volcado lógico y una restauración.

Para obtener más información sobre el volcado lógico y la restauración de Amazon Aurora MySQL, consulte Migrar bases de datos muy grandes a Amazon Aurora MySQL mediante MyDumper y MyLoader. Para obtener más información sobre cómo acceder a los registros de errores en Amazon Aurora, consulte Supervisión de archivos de registro de Amazon Aurora.