Cómo comprobar qué instrucciones usan consultas paralelas para Aurora MySQL
En el funcionamiento habitual, no es necesario realizar acciones especiales para sacar partido de las consultas en paralelo. Cuando una consulta cumple los requisitos esenciales para consultas en paralelo, el optimizador de consultas decide automáticamente si usar las consultas en paralelo para cada consulta específica.
Si ejecuta experimentos en un entorno de desarrollo o de pruebas, es posible que observe que las consultas paralelas no se utilizan porque las tablas son demasiado pequeñas en número de filas o volumen de datos general. Los datos de la tabla también podrían encontrarse completamente en el grupo de búfer, especialmente en el caso de tablas que haya creado recientemente para realizar experimentos.
Cuando monitorea o ajusta el rendimiento de clústeres, asegúrese de decidir si se utilizan las consultas paralelas en los contextos adecuados. Podría ajustar el esquema de la base de datos, la configuración, las consultas SQL o incluso la topología de clústeres y la configuración de conexión de aplicación para aprovechar esta característica.
Para comprobar si una consulta utiliza consultas paralelas, compruebe el plan de consulta (también conocido como el "plan de explicación") ejecutando la instrucción EXPLAINEXPLAIN para consultas en paralelo, consulte Constructos de SQL para consultas paralelas en Aurora MySQL.
En el siguiente ejemplo se muestra la diferencia entre un plan de consultas tradicional y un plan de consultas en paralelo. Este plan de explicación procede de la consulta 3 de la comparativa TPC-H. En muchas de las consultas de ejemplo de esta sección se usan las tablas del conjunto de datos de TPC-H. Puede obtener las definiciones de tabla, consultas y el programa dbgen que genera datos de ejemplo desde el sitio web de TPC-H
EXPLAIN SELECT l_orderkey, sum(l_extendedprice * (1 - l_discount)) AS revenue, o_orderdate, o_shippriority FROM customer, orders, lineitem WHERE c_mktsegment = 'AUTOMOBILE' AND c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate < date '1995-03-13' AND l_shipdate > date '1995-03-13' GROUP BY l_orderkey, o_orderdate, o_shippriority ORDER BY revenue DESC, o_orderdate LIMIT 10;
De forma predeterminada, es posible que la consulta tenga un plan como el siguiente. Si no ve la combinación hash utilizada en el plan de consulta, asegúrese de que la optimización esté activada primero.
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| 1 | SIMPLE | customer | NULL | ALL | NULL | NULL | NULL | NULL | 1480234 | 10.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 14875240 | 3.33 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 59270573 | 3.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
En la versión 3 de Aurora MySQL, puede activar la combinación hash en el nivel de sesión mediante la siguiente instrucción.
SET optimizer_switch='block_nested_loop=on';
Para la versión 2.09 y versiones posteriores de Aurora MySQL, establezca el parámetro de base de datos o el parámetro de clúster de base de datos aurora_disable_hash_join en 0 (desactivado). Si aurora_disable_hash_join está desactivado, se establece el valor de optimizer_switch en hash_join=on.
Después de activar la combinación hash, intente ejecutar la instrucción EXPLAIN de nuevo. Para obtener información acerca de cómo utilizar combinaciones hash de manera eficaz, consulte Optimización de grandes consultas combinadas de Aurora MySQL con combinaciones hash.
Con las combinaciones hash activadas pero las consultas paralelas desactivadas, es posible que la consulta tenga un plan como el siguiente, que usa combinaciones hash, pero no consultas paralelas.
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
| id | select_type | table |...| rows | Extra |
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
| 1 | SIMPLE | customer |...| 5798330 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | orders |...| 154545408 | Using where; Using join buffer (Hash Join Outer table orders) |
| 1 | SIMPLE | lineitem |...| 606119300 | Using where; Using join buffer (Hash Join Outer table lineitem) |
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
Después de que las consultas paralelas estén activadas, dos pasos de este plan de consulta pueden usar la optimización de consultas paralelas, como se muestra en la columna Extra de la salida de EXPLAIN. El procesamiento con uso intensivo de E/S y de CPU para estos pasos se baja a la capa de almacenamiento.
+----+...+--------------------------------------------------------------------------------------------------------------------------------+
| id |...| Extra |
+----+...+--------------------------------------------------------------------------------------------------------------------------------+
| 1 |...| Using where; Using index; Using temporary; Using filesort |
| 1 |...| Using where; Using join buffer (Hash Join Outer table orders); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra) |
| 1 |...| Using where; Using join buffer (Hash Join Outer table lineitem); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra) |
+----+...+--------------------------------------------------------------------------------------------------------------------------------+
Para obtener información sobre cómo interpretar las salidas de EXPLAIN para una consulta en paralelo y las partes de las instrucciones de SQL a las que las consultas en paralelo pueden aplicarse, consulte Constructos de SQL para consultas paralelas en Aurora MySQL.
En la siguiente salida de ejemplo se muestran los resultados de ejecutar la consulta anterior en una instancia de db.r4.2xlarge con un grupo de búfer frío. La consulta se ejecuta notablemente más rápido al usar las consultas en paralelo.
nota
Dado que los tiempos dependen de muchos factores del entorno, sus resultados podrían ser diferentes. Realice siempre sus propias pruebas de rendimiento para confirmar los resultados con su propio entorno, carga de trabajo, etc.
-- Without parallel query
+------------+-------------+-------------+----------------+
| l_orderkey | revenue | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
| 92511430 | 514726.4896 | 1995-03-06 | 0 |
.
.
| 28840519 | 454748.2485 | 1995-03-08 | 0 |
+------------+-------------+-------------+----------------+
10 rows in set (24 min 49.99 sec)
-- With parallel query
+------------+-------------+-------------+----------------+
| l_orderkey | revenue | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
| 92511430 | 514726.4896 | 1995-03-06 | 0 |
.
.
| 28840519 | 454748.2485 | 1995-03-08 | 0 |
+------------+-------------+-------------+----------------+
10 rows in set (1 min 49.91 sec)
Muchas de las consultas de ejemplo de esta sección usan tablas de este conjunto de datos de TPC-H, en particular la tabla PART, que tiene 20 millones de filas y la siguiente definición.
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| p_partkey | int(11) | NO | PRI | NULL | |
| p_name | varchar(55) | NO | | NULL | |
| p_mfgr | char(25) | NO | | NULL | |
| p_brand | char(10) | NO | | NULL | |
| p_type | varchar(25) | NO | | NULL | |
| p_size | int(11) | NO | | NULL | |
| p_container | char(10) | NO | | NULL | |
| p_retailprice | decimal(15,2) | NO | | NULL | |
| p_comment | varchar(23) | NO | | NULL | |
+---------------+---------------+------+-----+---------+-------+
Experimente con su carga de trabajo para averiguar si las instrucciones de SQL individuales pueden sacar partido de las consultas en paralelo. A continuación, use las siguientes técnicas de monitoreo para ayudar a comprobar la frecuencia con la que se usa la consulta paralela en las cargas de trabajo reales a lo largo del tiempo. En el caso de cargas de trabajo reales, se aplican otros factores adicionales, como los límites de simultaneidad.
