En cargas de trabajo de MySQL con alta concurrencia, los puntos de serialización en la capa de servicio y en la capa del motor (como la contención de bloqueos transaccionales) pueden degradar el rendimiento. AliSQL ofrece la función de cola de instrucciones, la cual utiliza un mecanismo de colas basado en buckets para reducir la sobrecarga por contención y mejorar el rendimiento de la instancia. Esta función asigna al mismo bucket las instrucciones que probablemente generen contención, como aquellas que operan sobre la misma fila, con el fin de optimizar la ejecución concurrente.
Contexto
En la capa de servicio y en la capa del motor de MySQL, la ejecución concurrente de instrucciones involucra varios puntos de serialización que fácilmente causan contención. Por ejemplo, la contención de bloqueos transaccionales es común durante la ejecución de instrucciones DML. En el motor InnoDB, la granularidad más fina de un bloqueo transaccional es el bloqueo a nivel de fila. Cuando varias instrucciones operan de forma concurrente sobre la misma fila, se produce una contención severa que reduce drásticamente el rendimiento del sistema conforme aumenta la concurrencia. Para resolver este problema, AliSQL proporciona la función de cola de instrucciones, que mejora el rendimiento de la instancia al reducir la sobrecarga por contención.
Requisitos previos
Esta función está disponible para instancias de ApsaraDB RDS for MySQL con las siguientes versiones:
MySQL 8.4 en Basic Edition o High-availability Edition
MySQL 8.0 en Basic Edition o High-availability Edition (versión secundaria del motor 20191115 o posterior)
MySQL 5.7 en Basic Edition o High-availability Edition (versión secundaria del motor 20200630 o posterior)
Variables de configuración
AliSQL proporciona dos variables para definir la cantidad y el tamaño de los buckets en la cola de instrucciones. Puede modify these variables en la consola de ApsaraDB RDS.
-
ccl_queue_bucket_count: especifica la cantidad de buckets.
Valores válidos: de 1 a 64
Valor predeterminado: 4
-
ccl_queue_bucket_size: especifica la cantidad de instrucciones concurrentes permitidas por bucket.
Valores válidos: de 1 a 4096
Valor predeterminado: 64
Sintaxis
AliSQL admite dos tipos de hints:
-
ccl_queue_value: aplica un hash a un valor específico para asignar la instrucción a un bucket.Sintaxis:
/*+ ccl_queue_value([int | string]) */Ejemplo:
update /*+ ccl_queue_value(1) */ t set c=c+1 where id = 1; update /*+ ccl_queue_value('xyz') */ t set c=c+1 where name = 'xyz'; -
ccl_queue_field: aplica un hash al valor de un campo específico de la cláusula WHERE para asignar un bucket.
Sintaxis:
/*+ ccl_queue_field(string) */Ejemplo:
update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';NotaAmbos hints son sensibles a la posición y deben colocarse inmediatamente después de la palabra clave UPDATE.
El hint
ccl_queue_fieldsolo permite especificar un campo a la vez. El formato/*+ ccl_queue_field(id name) */constituye un error de sintaxis e impide que la cola de instrucciones surta efecto. Si utiliza hints duplicados, como/*+ ccl_queue_field(id) ccl_queue_field(name) */, solo se toma en cuenta el primero.El campo especificado en el hint
ccl_queue_fielddebe aparecer en la cláusula WHERE.Para el hint
ccl_queue_field, la cláusula WHERE solo admite operaciones binarias sobre campos sin procesar (sin aplicar funciones ni cálculos al campo). El valor del lado derecho de la operación binaria debe ser un número o una cadena.
API
AliSQL proporciona dos funciones para monitorear la cola de instrucciones:
-
dbms_ccl.show_ccl_queue(): consulta el estado actual de la cola de instrucciones.call dbms_ccl.show_ccl_queue();A continuación se muestra el resultado devuelto:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 1 | QUEUE | 64 | 1 | 0 | 0 | | 2 | QUEUE | 64 | 40744 | 65 | 6 | | 3 | QUEUE | 64 | 0 | 0 | 0 | | 4 | QUEUE | 64 | 0 | 0 | 0 | +------+-------+-------------------+---------+---------+---------+ 4 rows in set (0.01 sec)La siguiente tabla describe los parámetros.
Parámetro
Descripción
CONCURRENCY_COUNT
La cantidad máxima de instrucciones concurrentes permitidas.
MATCHED
La cantidad total de instrucciones que coincidieron con la regla.
RUNNING
La cantidad de instrucciones que se están ejecutando actualmente.
WAITING
La cantidad de instrucciones que se encuentran en espera.
-
dbms_ccl.flush_ccl_queue(): borra los datos almacenados en memoria.call dbms_ccl.flush_ccl_queue(); call dbms_ccl.show_ccl_queue();A continuación se muestra el resultado devuelto:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 1 | QUEUE | 64 | 0 | 0 | 0 | | 2 | QUEUE | 64 | 0 | 0 | 0 | | 3 | QUEUE | 64 | 0 | 0 | 0 | | 4 | QUEUE | 64 | 0 | 0 | 0 | +------+-------+-------------------+---------+---------+---------+ 4 rows in set (0.00 sec)
Prácticas recomendadas
Prueba de la función
Para evitar modificaciones extensas en el código de su aplicación, puede usar la cola de instrucciones junto con un statement outline y así ajustar la aplicación en línea de manera rápida y sencilla. El siguiente ejemplo utiliza el caso de prueba update_non_index de SysBench para demostrar este proceso.
-
Entorno de prueba
-
Esquema de la tabla
CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 MAX_ROWS=1000000; -
Instrucción de prueba
UPDATE sbtest1 SET c='xyz' WHERE id=0; -
Script de prueba
./sysbench \ --mysql-host={$ip} \ --mysql-port={$port} \ --mysql-db=test \ --test=./sysbench/share/sysbench/update_non_index.lua \ --oltp-tables-count=1 \ --oltp_table_size=1 \ --num-threads=128 \ --mysql-user=u0
-
-
Procedimiento
-
Agregue un statement outline en línea.
CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1, ' /*+ ccl_queue_field(id) */ ', "UPDATE sbtest1 SET c='xyz' WHERE id=0");A continuación se muestra el resultado devuelto:
Query OK, 0 rows affected (0.01 sec) -
Consulte el statement outline.
call dbms_outln.show_outline();A continuación se muestra el resultado devuelto:
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+ | ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT | +------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+ | 1 | test | 7b945614749e541e0600753367884acff5df7e7ee2f5fb0af5ea58897910f023 | OPTIMIZER | | 1 | /*+ ccl_queue_field(id) */ | 0 | 0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? | +------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+ 1 row in set (0.00 sec) -
Compruebe que el statement outline haya surtido efecto.
explain UPDATE sbtest1 SET c='xyz' WHERE id=0;A continuación se muestra el resultado devuelto:
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | UPDATE | sbtest1 | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)show warnings;A continuación se muestra el resultado devuelto:
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | update /*+ ccl_queue_field(id) */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xyz' where (`test`.`sbtest1`.`id` = 0) | +-------+------+-----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -
Revise el estado de la cola de instrucciones.
call dbms_ccl.show_ccl_queue();A continuación se muestra el resultado devuelto:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 1 | QUEUE | 64 | 0 | 0 | 0 | | 2 | QUEUE | 64 | 0 | 0 | 0 | | 3 | QUEUE | 64 | 0 | 0 | 0 | | 4 | QUEUE | 64 | 0 | 0 | 0 | +------+-------+-------------------+---------+---------+---------+ 4 rows in set (0.00 sec) -
Inicie la prueba.
sysbench \ --mysql-host={$ip} \ --mysql-port={$port} \ --mysql-db=test \ --test=./sysbench/share/sysbench/update_non_index.lua \ --oltp-tables-count=1 \ --oltp_table_size=1 \ --num-threads=128 \ --mysql-user=u0 -
Compruebe los resultados de la prueba.
call dbms_ccl.show_ccl_queue();A continuación se muestra el resultado devuelto:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 1 | QUEUE | 64 | 10996 | 63 | 4 | | 2 | QUEUE | 64 | 0 | 0 | 0 | | 3 | QUEUE | 64 | 0 | 0 | 0 | | 4 | QUEUE | 64 | 0 | 0 | 0 | +------+-------+-------------------+---------+---------+---------+ 4 rows in set (0.03 sec)call dbms_outln.show_outline();A continuación se muestra el resultado devuelto:
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+ | ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT | +------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+ | 1 | test | xxxxxxxxx | OPTIMIZER | | 1 | /*+ ccl_queue_field(id) */ | 115795 | 0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? | +------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+ 1 row in set (0.00 sec)NotaLos resultados muestran que el statement outline coincidió con la regla 115,795 veces. El estado de la cola de instrucciones indica 10,996 coincidencias, con 63 instrucciones en ejecución y 4 en espera.
-
Prueba de rendimiento
-
Entorno de prueba
Servidor de aplicaciones: una instancia de ECS
Especificaciones de la instancia RDS: 8 vCPU, 16 GB de memoria y un ESSD
Edición de la instancia: High-availability Edition, que utiliza replicación asíncrona.
-
Caso de prueba
El siguiente caso de prueba de SysBench ejecuta actualizaciones concurrentes sobre el registro con id=1:
pathtest = string.match(test, "(.*/)") if pathtest then dofile(pathtest .. "oltp_common.lua") else require("oltp_common") end function thread_init() drv = sysbench.sql.driver() con = drv:connect() end function event() local val_name val_name = "'sdnjkmoklvnseajinvijsfdnvkjsnfjvn".. sb_rand_uniform(1, 4096) .. "'" query = "UPDATE sbtest1 SET c=" .. val_name .. " WHERE id=1" rs = db_query(query) end -
Resultados de la prueba
Habilitar la función de cola de instrucciones incrementa significativamente las QPS en cargas de trabajo con alta concurrencia. A mayor concurrencia, mayor es la mejora.
NotaSin la función de cola de instrucciones, una prueba de estrés con 4,096 hilos provoca un cambio entre el nodo principal y el secundario, lo que resulta en 0 QPS.