Prácticas recomendadas de SEQUENCE de base de datos

Creación de una secuencia

En DWS, el Global Transaction Manager (GTM) genera y mantiene la información global única sobre una transacción, como el ID de transacción global, la instantánea de transacción y la secuencia. La siguiente figura muestra el proceso de creación de una secuencia en DWS.

Figura 1 Creación de una secuencia

El proceso específico es el siguiente:

1. El CN que recibe el comando SQL solicita un UUID al GTM.
2. El GTM devuelve un UUID.
3. El CN vincula el UUID obtenido al sequenceName creado por el usuario.
4. El CN entrega la relación de vinculación a otros nodos, y otros nodos crean la tabla de metadatos de secuencia de forma sincrónica.
5. El CN envía el UUID y el startID de la secuencia al GTM para su almacenamiento permanente.

Por lo tanto, el mantenimiento y la solicitud de secuencias se completan realmente en el GTM. Al solicitar nextval, cada instancia obtiene un valor de secuencia del GTM mediante el UUID de la secuencia. La cantidad de valores solicitados se relaciona con el tamaño de la caché. Una instancia solo solicitará un nuevo valor de secuencia al GTM una vez que su caché se agote. Por lo tanto, ampliar la caché de la secuencia minimiza la frecuencia de comunicación entre el CN/DN y el GTM.

Dos métodos de creación de una secuencia

Método 1: Ejecutar la sentencia CREATE SEQUENCE para crear una secuencia y usar nextval para invocar la secuencia en la nueva tabla.

1
2
3
4
5

CREATE SEQUENCE seq_test increment by 1 minvalue 1 no maxvalue start with 1;
CREATE SEQUENCE

CREATE TABLE table_1(id int not null default nextval('seq_test'), name text);
CREATE TABLE

Método 2: Si se utiliza el tipo serial durante la creación de la tabla, se crea automáticamente una secuencia y el valor predeterminado de la columna se establece en nextval.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE mytable(a int, b serial) distribute by hash(a);
NOTICE:  CREATE TABLE will create implicit sequence "mytable_b_seq" for serial column "mytable.b"
CREATE TABLE

 \d+ mytable
                                            Table "dbadmin.mytable"
 Column |  Type   |                      Modifiers                      | Storage | Stats target | Description
--------+---------+-----------------------------------------------------+---------+--------------+-------------
 a      | integer |                                                     | plain   |              |
 b      | integer | not null default nextval('mytable_b_seq'::regclass) | plain   |              |
Has OIDs: no
Distribute By: HASH(a)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no

Uso común de secuencias en servicios

Las secuencias se utilizan comúnmente para generar claves primarias o columnas únicas durante la importación de datos, una práctica frecuente en los escenarios de migración de datos. Las diferentes herramientas de migración o escenarios de importación de servicios utilizan diferentes métodos de importación. Los métodos de importación comunes se clasifican en copy e insert. Para las secuencias, el procesamiento en los dos escenarios es ligeramente diferente.

• Escenario 1: Insert pushdown

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

  CREATE TABLE test1(a int, b serial) distribute by hash(a); NOTICE: CREATE TABLE will create implicit sequence "test1_b_seq" for serial column "test1.b" CREATE TABLE CREATE TABLE test2(a int) distribute by hash(a); CREATE TABLE EXPLAIN VERBOSE INSERT INTO test1(a) SELECT a FROM test2; QUERY PLAN ------------------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-memory | E-width | E-costs ----+-------------------------------------+--------+------------+----------+---------+--------- 1 | -> Streaming (type: GATHER) | 1 | | | 4 | 16.34 2 | -> Insert on dbadmin.test1 | 30 | | | 4 | 16.22 3 | -> Seq Scan on dbadmin.test2 | 30 | | 1MB | 4 | 14.21 RunTime Analyze Information ---------------------------------------------------- "dbadmin.test2" runtime: 9.586ms, sync stats Targetlist Information (identified by plan id) --------------------------------------------------------- 1 --Streaming (type: GATHER) Node/s: All datanodes 3 --Seq Scan on dbadmin.test2 Output: test2.a, nextval('test1_b_seq'::regclass) Distribute Key: test2.a ====== Query Summary ===== ------------------------------- System available mem: 1351680KB Query Max mem: 1351680KB Query estimated mem: 1024KB Parser runtime: 0.076 ms Planner runtime: 12.666 ms Unique SQL Id: 831364267 (26 rows)

  Durante una operación INSERT, nextval se ejecuta en los DN. Esto ocurre tanto si nextval se invoca con su valor predeterminado como si se invoca explícitamente. El plan de ejecución confirma que nextval opera en la capa de secuencia en los DN. En este escenario, los DN obtienen los valores de secuencia directamente del GTM y ejecutan la solicitud simultáneamente, lo que da como resultado un nivel de eficiencia relativamente alto.

  

Escenarios de optimización típicos relacionados con secuencias

Escenarios de servicios:

En un escenario de servicio, la herramienta de sincronización de datos CDM se utiliza para transferir datos e importar datos desde la base de datos de origen a la base de datos DWS de destino. A pesar de cambiar la simultaneidad de CDM de 1 a 5, la tasa de sincronización permanece sin cambios y hay una diferencia significativa entre la tasa de importación y el valor esperado. A excepción del copiado de datos, todos los demás servicios se ejecutan sin problemas y sin presentar inconvenientes de rendimiento o de recursos. Por lo tanto, es probable que exista un cuello de botella dentro del servicio. Se recomienda revisar la cola de trabajos específicamente para la operación COPY.

Como se muestra en la figura anterior, se ejecutan cinco trabajos de CDM de forma simultánea. Puede ver cinco sentencias COPY en la vista activa. Verifique la vista de espera basada en query_id correspondiente a las cinco sentencias COPY. De las cinco operaciones COPY, solo una solicita un valor de secuencia al GTM de forma concurrente, mientras que las demás esperan un bloqueo ligero. Como resultado, habilitar cinco trabajos simultáneos no mejora sustancialmente el rendimiento en comparación con la ejecución de un solo trabajo.

Causas:

El tipo serial se utiliza cuando se crea la tabla de destino. De forma predeterminada, la caché de la secuencia creada es 1. Como resultado, cuando los datos se copian de forma simultánea en la base de datos, el CN establece conexiones con el GTM con frecuencia y existe una contención de bloqueo ligero entre múltiples trabajos simultáneos, lo que da como resultado una baja eficiencia de sincronización de datos.

Soluciones:

En este escenario, aumente el valor de la caché de la secuencia para evitar cuellos de botella causados por el establecimiento frecuente de conexiones con el GTM. En este ejemplo de escenario de servicio, se sincronizan alrededor de 100,000 registros de datos cada vez. En función de la evaluación del servicio, cambie el valor de la caché a 10,000. (En la práctica, establezca un valor de caché adecuado en función de los servicios para garantizar un acceso rápido y evitar el desperdicio de números de secuencia).

En las versiones de clúster 8.2.1.100 y posteriores, puede utilizar ALTER SEQUENCE para cambiar el valor de la caché.

Los clústeres de DWS de la versión 8.2.1 o anterior no permiten la modificación de los valores de la caché a través de ALTER SEQUENCE. Para cambiar el valor de la caché de una secuencia existente, siga estos pasos (la tabla mytable se utiliza como ejemplo):

1. Elimine la asociación entre la secuencia actual y la tabla de destino.

   1 2	ALTER SEQUENCE mytable_b_seq owned by none; ALTER TABLE mytable alter b drop default;

2. Registre el valor de la secuencia actual como el valor de inicio de la nueva secuencia.

   1	SELECT nextval('mytable_b_seq');

   Elimine la secuencia.

   1	DROP SEQUENCE mytable_b_seq;

3. Cree una secuencia y vincúlela a la tabla de destino. Reemplace xxx con el valor de nextval obtenido en el paso anterior.

   1 2 3

   CREATE SEQUENCE mytable_b_seq START with xxx cache 10000 owned by mytable.b; ALTER SEQUENCE mytable_b_seq owner to u1;--u1 is the owner of the mytable table. The owner does not need to run this statement. ALTER TABLE mytable alter b set default nextval('mytable_b_seq');