DSZ's questions

Eu tenho um servidor MySQL Percona 8, rodando via Docker, atuando como armazenamento de dados de apoio para um serviço muito utilizado. A cada hora, será executado um script que lê um valor de uma coluna virtual de aproximadamente 1,7 milhão de linhas (estimativa de tamanho da tabela de 2,3 GiB) e insere esse valor específico e os dados-chave associados em outra tabela da qual o sistema, de outra forma, apenas lê. A coluna virtual é uma pesquisa JSON json_extract(jsonData, '$.root.interestingValue')e possui o GENERATEDsinalizador. A ideia por trás disso é colocar menos pressão sobre a tabela que é considerada ativa e atualizada regularmente, quando os usuários estão interessados ​​apenas em alguns valores específicos e não é necessário recuperar o valor mais recente absoluto.

A consulta é a seguinte (com tabelas/colunas renomeadas)

CREATE TEMPORARY TABLE t1_cache_temp
SELECT
  t2.id as uid,
  t3.displayText as dt,
  t2.virtualColumn as interestingValue
FROM liveTable t2
JOIN otherLiveTable t3 on t2.id = t3.id;

TRUNCATE TABLE t1_cache;

INSERT INTO t1_cache
SELECT uid, dt, interestingValue FROM t1_cache_temp;

DROP TEMPORARY TABLE t1_cache_temp;

O tempo total gasto por este script é de 47 segundos.

Embora a leitura da tabela InnoDB e a gravação na tabela temporária sejam adequadas, a gravação na outra tabela InnoDB faz com que todas as outras operações no banco de dados sejam interrompidas após os primeiros segundos. Eu reduzi a INSERT INTO t1_cache...instrução executando cada instrução individualmente.

Também tentei escrever em tabelas novas e completamente não utilizadas (também usando InnoDB), o que dá o mesmo resultado. No momento da realização do último teste, apenas 2% do Máximo de Conexões permitidas estão em uso. Além disso, apenas 48% do buffer pool do InnoDB estava em uso.

Se eu mudar a mesa para MyISAM, tudo funciona normalmente, sem interrupções ou travamentos. Além disso, se eu remover a tabela temporária, o mesmo problema ocorrerá, independentemente do mecanismo InnoDB ou MyISAM.

Embora usar MyISAM seja provavelmente a melhor opção, o que poderia estar causando isso? Há algo que eu possa fazer sobre isso no nível da configuração?

Esta é mais uma pergunta do tipo "por favor, confirme/corrija meu entendimento".

Histórico Eu me conecto ao MySQL usando JDBC, o pool de conexões tem cerca de 250 conexões, a maioria das quais parece conectada persistentemente mesmo após o encerramento da consulta - todas usam a mesma conta de usuário. O procedimento armazenado para o qual desejo usar uma tabela temporária é chamado pelo código Java e executa uma transformação em uma coluna JSON para convertê-la em uma linha para uma tabela diferente. A transformação é relativamente cara, executada potencialmente em milhares de linhas, por isso não desejo realizar a transformação mais de uma vez. Para contornar isso, minha solução proposta é transformar os dados em uma tabela temporária, realizar minhas consultas estatísticas em torno dos dados agora transformados e, em seguida, inserir esse atacado na tabela de destino.

Pergunta Eu li que as tabelas temporárias estão vinculadas ao User . Estou certo ao teorizar que, como as conexões aparentemente nunca são encerradas e todas usam a mesma conta de usuário, a tabela temporária criada no procedimento seria compartilhada por todas as conexões e consultas (e assim os dados poderiam ser acessados ​​por outras conexões executando o mesmo procedimento ao mesmo tempo)?

Limitações O que eu gostaria de fazer é transmitir os dados originais ou realizar a transformação e transmitir esses dados de volta para Java. No entanto, estamos lidando com cerca de algumas centenas de megabytes de dados de cada vez e a transferência de dados de volta para Java parece bloquear o banco de dados até que a transmissão seja concluída. (Não sei por que, se alguém tiver uma recomendação para corrigir isso, por favor me avise)

Então, fico com a única outra solução que consegui pensar: não transmitir os dados de origem, mas realizar a transformação, coletar os dados agregados necessários e, em seguida, inserir os dados transformados no destino sem que eles saiam do banco de dados servidor.

desde já, obrigado

Desculpe pelo título longo, não tinha certeza de como diferenciá-lo dos resultados da pesquisa automática.

Eu tenho um arquivo de despejo grande (~ 44 GB) e estou tentando importá-lo para o MySQL em um servidor diferente.

Ambos os servidores estão rodando 5.7, embora um seja 5.7.20 (fonte) e o outro seja 5.7.42 (destino), no Ubuntu 22.04 LTS. Eu, como uma salva de palmas, instalei o servidor MySQL 8 mais recente para o sistema operacional como o servidor de banco de dados de destino, que produziu os mesmos resultados.

Toda tentativa de restaurar o banco de dados resulta em um erro de sintaxe, mas nem sempre no mesmo lugar, e às vezes antes ou depois de onde a execução anterior chegou.

Por exemplo; na execução 1 pode ocorrer um erro na linha 26880, depois na execução 2 ocorrerá um erro na linha 14230 (antes da execução 1) e na execução 3 talvez na linha 390290 (mais tarde que ambas). Os números das linhas aqui são arbitrários, mas sobem e descem e geralmente nunca ficam no mesmo lugar duas vezes.

• max_allowed_packettem no máximo 1 GB
• innodb_buffer_pool_sizeestá definido para 128 GB (que é aproximadamente 65% da RAM disponível)

Todo o resto é padrão. Não há erros listados no log de erros.

Tentei outros backups, que produziram o mesmo resultado.

O backup não está compactado.

O que eu fiz errado?