¿Cómo puedo usar el disco temporal de GaussDB(for MySQL)?

Riesgos

• Las sentencias SQL no se pueden ejecutar y no se devuelven resultados.
• Las sentencias SQL ocupan recursos de bloqueo durante mucho tiempo y bloquean otras sentencias SQL. Como resultado, el número de conexiones aumenta o incluso alcanza el límite superior, afectando a otros servicios.
• El número de archivos temporales en la caché de binlog alcanza el límite superior. La base de datos se descompone y tarda mucho tiempo en restaurarse, por lo que los servicios se interrumpen durante mucho tiempo.

Escenarios y solución de problemas

1. Creación explícita de tablas de disco temporales
   • Escenario

     Ejecute la sentencia create temporary table para crear explícitamente tablas de disco temporales. Las tablas temporales cuyo motor de almacenamiento es InnoDB se almacenan en caché en el grupo de búferes y se vacían en los discos por subprocesos sucios.

     En GaussDB(for MySQL), los datos de las tablas temporales de disco se almacenan en el espacio temporal de sesión (la ruta se especifica mediante el parámetro innodb_temp_tablespaces_dir), y los registros de deshacer se almacenan en el espacio temporal global (la ruta se especifica mediante el parámetro innodb_temp_data_file_path).

     Para evitar que las tablas de disco temporales ocupen demasiado espacio en disco, se recomienda eliminar las tablas de disco temporales innecesarias o desconectar las conexiones de base de datos innecesarias.

     

     • Espacio de tabla temporal de sesión: Se recupera cuando se libera la conexión de base de datos actual.
     • Espacio de tabla temporal global: solo se recupera después de reiniciar la base de datos.
   • Resolución de problemas
     1. Vea información sobre las tablas temporales que creó en InnoDB.

        mysql> select * from information_schema.innodb_temp_table_info;
        +----------------------+---------------+--------+------------+
        | TABLE_ID             | NAME          | N_COLS | SPACE      |
        +----------------------+---------------+--------+------------+
        | 18446744069414584311 | #sqle055_24_0 |      5 | 4294502266 |
        +----------------------+---------------+--------+------------+

     2. Compruebe el uso de los archivos de tablas temporales de InnoDB.

        En una tabla, la columna ID indica el ID de la sesión que está utilizando el archivo de tabla temporal. Si el valor es 0, no se utiliza el archivo ibt. La columna SIZE indica el tamaño del archivo ibt, que aumenta automáticamente en función del uso y se recupera cuando finaliza la sesión. Si el valor de la columna PURPOSE es INTRINSIC, la tabla es una tabla temporal implícita. Si el valor de la columna PURPOSE es USER, la tabla es una tabla temporal explícita.

        mysql> select * from information_schema.innodb_session_temp_tablespaces;
        +----+------------+----------------------------+-------+----------+-----------+
        | ID | SPACE      | PATH                       | SIZE  | STATE    | PURPOSE   |
        +----+------------+----------------------------+-------+----------+-----------+
        | 31 | 4294502265 | ./#innodb_temp/temp_9.ibt  | 81920 | ACTIVE   | INTRINSIC |
        | 36 | 4294502266 | ./#innodb_temp/temp_10.ibt | 98304 | ACTIVE   | USER      |
        | 34 | 4294502264 | ./#innodb_temp/temp_8.ibt  | 81920 | ACTIVE   | INTRINSIC |
        |  0 | 4294502257 | ./#innodb_temp/temp_1.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502258 | ./#innodb_temp/temp_2.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502259 | ./#innodb_temp/temp_3.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502260 | ./#innodb_temp/temp_4.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502261 | ./#innodb_temp/temp_5.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502262 | ./#innodb_temp/temp_6.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502263 | ./#innodb_temp/temp_7.ibt  | 81920 | INACTIVE | NONE      |
        +----+------------+----------------------------+-------+----------+-----------+

2. Consulta de tablas temporales de disco o archivos temporales creados implícitamente
   • Escenario

     Cuando se selecciona un plan de ejecución para una consulta, el optimizador de consultas puede usar tablas temporales. Se usan preferentemente tablas de memoria temporal. Cuando el tamaño de las tablas de memoria temporal excede el umbral (dependiendo del valor menor entre tmp_table_size y max_heap_table_size), se utilizan tablas de disco temporales.

     Las tablas temporales de disco son creadas implícitamente por consultas. Los datos entre las tablas que se crean implícita y explícitamente son los mismos y se almacenan en el espacio temporal de sesión. Si hay consultas complejas, incluidas, entre otras, palabras clave como UNION, GROUP BY y ORDER BY, en tablas más grandes, se pueden generar tablas de disco temporales. Además, cuando las consultas implican operaciones de ordenación, si el búfer de ordenación no puede almacenar todos los datos (el tamaño del búfer es especificado por sort_buffer_size), se pueden utilizar archivos de disco temporales para la ordenación auxiliar. En la mayoría de los escenarios, las tablas de disco temporales creadas implícitamente son la causa principal del espacio total en disco. Puede localizar consultas complejas o transacciones largas, optimizar sentencia de consulta, agregar índices adecuados y dividir transacciones largas para resolver estos problemas.

   • Resolución de problemas
     1. Compruebe si hay sentencias SQL que utilizan tablas temporales o clasificación de archivos.

        Si se muestra Using temporary en la columna Extra, se utilizan tablas temporales. Si se muestra Using filesort, se utiliza la clasificación de archivos.

        

     2. Consultar el uso de tablas temporales implícitas. El método es el mismo que las tablas temporales explícitas del disco.
3. Consulta de registros binarios generados para transacciones largas
   • Escenario

     Binlog es un archivo binario que registra los cambios en la base de datos, como DDL, DCL y DML (excluido SELECT). InnoDB almacena en caché los binlogs en la memoria antes de que se realice una transacción y escribe binlogs en los discos solo después de que se realice la transacción. El tamaño del archivo binlog para cada conexión en la memoria se especifica mediante el parámetro binlog_cache_size. Cuando el tamaño del archivo binlog registrado por una transacción excede el valor de este parámetro, el archivo binlog se escribe en un archivo de disco temporal. Las transacciones largas pueden causar grandes binlogs. Como resultado, el tamaño de los binlogs temporales en el disco es grande y el disco puede estar lleno. Se recomienda controlar el tamaño de la transacción, separar transacciones largas o cambiar correctamente el valor de binlog_cache_size.

   • Resolución de problemas
     1. Compruebe si binlog está habilitado.

        mysql> show variables like 'log_bin';
        +---------------+-------+
        | Variable_name | Value |
        +---------------+-------+
        | log_bin       | ON    |
        +---------------+-------+

     2. Vea el uso de la caché de binlog.

        Binlog_cache_disk_use indica el número de veces que los archivos temporales de disco se utilizan para almacenar en caché binlogs debido a la falta de memoria (especificada por binlog_cache_size). Si el valor de binlog_cache_size es grande, se invocan los archivos de disco temporales para almacenar en caché binlogs varias veces.

        mysql> show global status like '%binlog_cache%';
        +-----------------------+-----------+
        | Variable_name         | Value     |
        +-----------------------+-----------+
        | Binlog_cache_disk_use | 1335006   |
        | Binlog_cache_use      | 264240359 |
        +-----------------------+-----------+

4. Comprobación de archivos temporales generados por DDL
   • Escenario

     Durante las operaciones DDL en tablas, los archivos de disco temporales se generan en algunas fases.

     • A veces, es necesario volver a crear el espacio de tablas de la tabla original, lo que implica la recreación del índice de árbol B+ en la tabla. Si una tabla contiene una gran cantidad de datos, el búfer de ordenación no puede almacenar todos los datos. Es necesario crear un archivo temporal para ayudar a ordenar.
     • Aunque algunas sentencias DDL en línea admiten operaciones DML en la tabla original, la tabla original no se puede modificar directamente. La modificación debe registrarse en registros en línea y aplicarse a la nueva tabla una vez completadas las operaciones de DDL. Los registros en línea se almacenan preferentemente en la memoria. El tamaño de los registros en línea se especifica mediante el parámetro innodb_sort_buffer_size. Si el tamaño de los registros en línea excede el valor del parámetro, los registros en línea se almacenan temporalmente en un archivo temporal.
     • Cuando la sentencia OPTIMIZE TABLE se ejecuta en una tabla, los datos almacenados en el índice agrupado necesitan ser reorganizados, lo que puede generar archivos temporales.
   • Resolución de problemas
     • Ejecute el comando SHOW PROCESSLIST para comprobar si hay sentencias DDL que se han ejecutado durante mucho tiempo.
     • Asegúrese de que hay suficiente espacio antes de realizar DDL para tablas grandes.