Como usar discos temporários do TaurusDB?

Riscos

• As instruções SQL falham ao serem executadas e nenhum resultado é retornado.
• Instruções SQL ocupam recursos de bloqueio por um longo tempo e bloqueiam outras instruções SQL. Como resultado, o número de conexões aumenta ou até atinge o limite superior, afetando outros serviços.
• Há muitos arquivos temporários no cache binlog, o que pode causar a quebra do banco de dados. Isso leva muito tempo para restaurar, então os serviços são interrompidos por um longo tempo.

Cenários e solução de problemas

1. Criação explícita de tabelas de disco temporárias
   • Cenário

     Você pode executar a instrução create temporary table para criar explicitamente tabelas de disco temporárias. As tabelas temporárias cujo mecanismo de armazenamento é o InnoDB são armazenadas em cache no pool de buffer e liberadas para os discos por threads sujas.

     Em TaurusDB, os dados nas tabelas temporárias do disco são armazenados no tablespace temporário da sessão (o caminho é especificado pelo parâmetro innodb_temp_tablespaces_dir) e os logs de desfazer são armazenados no tablespace temporário global (o caminho é especificado pelo parâmetro innodb_temp_data_file_path).

     Para evitar que tabelas de disco temporárias ocupem muito espaço em disco, é aconselhável excluir tabelas de disco temporárias desnecessárias ou desconectar conexões de banco de dados desnecessárias.

     

     • Tablespace temporário da sessão: ele é recuperado quando a conexão de banco de dados atual é liberada.
     • Tablespace temporário global: ele é recuperado somente depois que o banco de dados é reiniciado.
   • Solução de problemas
     1. Visualize informações sobre as tabelas temporárias que você criou no InnoDB.

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

     2. Verifique o uso de arquivos de tabela temporários do InnoDB.

        Em uma tabela, a coluna ID indica o ID da sessão que está usando o arquivo de tabela temporária. Se o valor for 0, o arquivo ibt não será usado. A coluna SIZE indica o tamanho do arquivo ibt, que aumenta automaticamente com base no uso e é recuperado quando a sessão termina. Se o valor da coluna PURPOSE for INTRINSIC, a tabela é uma tabela temporária implícita. Se o valor da coluna PURPOSE for USER, a tabela é uma tabela temporária 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 tabelas temporárias de disco ou arquivos temporários criados implicitamente
   • Cenário

     Ao selecionar um plano de execução para uma consulta, o otimizador de consulta pode usar tabelas temporárias. Tabelas de memória temporárias são usadas preferencialmente. Quando o tamanho das tabelas de memória temporária excede um determinado limite (tmp_table_size ou max_heap_table_size, o que for menor), as tabelas de disco temporário são usadas.

     Tabelas temporárias de disco são criadas implicitamente por consultas. Os dados entre tabelas que são criadas implícita e explicitamente são os mesmos e armazenados no espaço de tabela temporário de sessão. Se houver consultas complexas, incluindo, mas não limitadas a palavras-chave como UNION, GROUP BY e ORDER BY, em tabelas maiores, tabelas de disco temporárias podem ser geradas. Além disso, quando as consultas envolvem operações de classificação, se o buffer de classificação não puder armazenar todos os dados (o tamanho do buffer é especificado por sort_buffer_size), os arquivos de disco temporários podem ser usados para classificação auxiliar. Na maioria dos cenários, tabelas de disco temporárias implicitamente criadas são a principal razão pela qual os discos ficam cheios. Se o disco estiver ficando cheio, você poderá localizar consultas complexas ou transações longas, otimizar as instruções de consulta, adicionar índices adequados e dividir transações longas para resolver o problema.

   • Solução de problemas
     1. Verifique se há instruções SQL usando tabelas temporárias ou classificação de arquivos.

        Se Using temporary for exibido na coluna Extra, tabelas temporárias serão usadas. Se Using filesort for exibido, a classificação de arquivos será usada.

        

     2. Consulte o uso de tabelas temporárias implícitas. O método é o mesmo das tabelas temporárias de disco explícito.
3. Consulta de binlogs gerados para transações longas
   • Cenário

     Um binlog é um binário que registra alterações no banco de dados, como DDL, DCL e DML (excluindo SELECT). O InnoDB armazena binlogs na memória antes que as transações sejam confirmadas e grava binlogs em discos somente depois que as transações são confirmadas. O tamanho do arquivo binlog para cada conexão na memória é especificado pelo parâmetro binlog_cache_size. Quando o tamanho do arquivo binlog gravado por uma transação excede o valor desse parâmetro, o arquivo binlog é gravado em um arquivo de disco temporário. Transações longas podem causar grandes binlogs. Como resultado, o tamanho dos binlogs temporários no disco é grande e o disco pode estar cheio. É aconselhável controlar o tamanho da transação, dividir transações longas ou alterar binlog_cache_size para um valor mais apropriado.

   • Solução de problemas
     1. Verifique se o binlog está ativado.

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

     2. Veja o uso do cache do binlog.

        Binlog_cache_disk_use indica o número de vezes que os arquivos de disco temporários são usados para armazenar em cache de binlogs devido à memória insuficiente (especificado por binlog_cache_size). Se o valor de binlog_cache_size for grande, os arquivos temporários de disco serão chamados para armazenar binlogs em cache várias vezes.

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

4. Verificação de arquivos temporários gerados por DDLs
   • Cenário

     Durante as operações DDL em tabelas, arquivos temporários de disco são gerados em algumas fases.

     • Às vezes, você precisa recriar o espaço de tabela da tabela original, o que envolve a recriação do índice de árvore B+ na tabela. Se uma tabela contiver uma grande quantidade de dados, o buffer de classificação não poderá armazenar todos os dados. Você precisa criar um arquivo temporário para ajudar com a classificação.
     • Embora algumas instruções DDL on-line suportem operações DML na tabela original, a tabela original não pode ser modificada diretamente. A modificação deve ser registrada em logs on-line e aplicada à nova tabela após a conclusão das operações DDL. Os logs on-line são preferencialmente armazenados na memória. O tamanho dos logs on-line é especificado pelo parâmetro innodb_sort_buffer_size. Se o tamanho dos logs on-line exceder o valor do parâmetro, os logs on-line serão armazenados temporariamente em um arquivo temporário.
     • Quando a instrução OPTIMIZE TABLE é executada em uma tabela, os dados armazenados no índice clusterizado precisam ser reorganizados, o que pode gerar arquivos temporários.
   • Solução de problemas
     • Execute o comando SHOW PROCESSLIST para verificar se há instruções DDL que estão demorando muito para serem executadas.
     • Certifique-se de que há espaço suficiente antes de executar DDLs para tabelas grandes.