Eu estava tentando executar um bastante grande INSERT...SELECTno MySQL com JDBC e recebi a seguinte exceção:
Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
Como não estou retornando um objeto ResultSet, pensei que o espaço de heap Java não deveria ser um problema. No entanto, tentei aumentar de qualquer maneira e não adiantou. Tentei então executar a instrução no MySQL Workbench e obtive essencialmente a mesma coisa:
Error Code 5: Out of memory (Needed 1073741816 bytes)
Devo ter bastante RAM para concluir essas operações (o suficiente para caber em toda a tabela da qual estou selecionando), mas acho que há várias configurações que preciso ajustar para aproveitar toda a minha memória. Estou executando uma instância extragrande dupla de alta memória do Amazon EC2 com uma AMI do Windows Server 2008. Tentei mexer no arquivo my.ini para usar configurações melhores, mas, pelo que sei, posso ter piorado as coisas. Aqui está um dump desse arquivo:
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=1024M
table_cache=256
tmp_table_size=25G
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_repair_threads = 2
myisam_sort_buffer_size=10G
key_buffer_size=5000M
bulk_insert_buffer_size = 4000M
read_buffer_size=8000M
read_rnd_buffer_size=8000M
sort_buffer_size=1G
innodb_additional_mem_pool_size=26M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=13M
innodb_buffer_pool_size=23G
innodb_log_file_size=622M
innodb_thread_concurrency=18
innodb_file_per_table=TRUE
join_buffer_size=4G
max_heap_table_size = 10G
Então, isso é apenas uma questão de alterar as configurações acima para funcionar melhor no meu ambiente? Se sim, quais configurações devo usar? Eu sou o único que usa essa instância; Eu o uso para meu projeto de hobby pessoal que envolve análise estatística de grandes conjuntos de dados. Como tal, estou livre para deixá-lo consumir todos os recursos disponíveis para minhas próprias consultas.
Se não for uma questão de alterar essas configurações, qual é o problema? Obrigado por qualquer ajuda que você possa oferecer sobre como configurar melhor tudo.
Dado que esta é uma instalação do Windows, o @DTest ainda forneceu a direção inicial adequada.
Aplique a seguinte fórmula:
A maioria das pessoas usa isso:
Eu prefiro isso:
Essas variáveis são as que você precisa ajustar até que a fórmula produza 80% de RAM instalada ou menos.
Eu tentaria diminuir seus tamanhos de buffer. Torná-los tão grandes quanto você vai causar problemas. Quanta memória você tem disponível para executar estes valores:
Alguns dos tamanhos de buffer são alocados por thread, por exemplo, myisam_sort_buffer_size de 10G aloca 10G para cada thread.
Eu primeiro reduziria esses valores drasticamente e, em seguida, investigaria quais valores você realmente precisa para ter essa quantidade de RAM alocada (se houver).
Uma maneira rápida de determinar quanta memória o MySQL pensa que poderia alocar é a seguinte:
wget mysqltuner.pl
perl mysqltuner.pl
Quando você executa este script, ele lhe dirá qual porcentagem da RAM instalada o MySQL pensa que pode alocar com segurança. Se a resposta for superior a 100%, você definitivamente precisa diminuir o tamanho do buffer. Os principais a serem focados são:
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections key_buffer_size
(não é realmente eficaz após 4G)
@DTest já definiu a direção para você na resposta dele, então +1 para a resposta dele. O script perl lhe dirá o que acontece se você não o definir ou se alterar qualquer valor. Aqui está um exemplo:
Um cliente meu tem
read_buffer_size=128K
read_rnd_buffer_size=256K
sort_buffer_size=2M
join_buffer_size=128K
max_connections=1050
Aqui está a saída do mysqltuner.pl:
-------- Estatísticas Gerais ---------------------------------------- ----------
[--] Verificação de versão ignorada para script MySQLTuner
[OK] Atualmente executando o MySQL suportado versão 5.0.51a-community-log
[!!] Mudar para SO de 64 bits - o MySQL não pode usar atualmente toda a sua RAM
-------- Estatísticas do mecanismo de armazenamento --------------------------------------- ----
[--] Status: +Arquivo -BDB +Federado +InnoDB -ISAM -NDBCluster
[--] Dados em tabelas MyISAM: 319M (Tabelas: 108)
[--] Dados em tabelas InnoDB: 2M (Tabelas: 5)
[!!] Total de tabelas fragmentadas: 22
-------- Métricas de desempenho ---------------------------------------- ---------
[--] Até: 52d 23h 15m 57s (72M q [15.875 qps], 241K conn, TX: 2B, RX: 1B)
[--] Leituras / Gravações: 59% / 41%
[--] Total de buffers: 34,0M global + 2,7M por thread (máximo de 1050 threads)
[!!] Alocação > 2 GB de RAM em sistemas de 32 bits pode causar instabilidade do sistema
[!!] Uso máximo de memória possível: 2,8 G (72% da RAM instalada)
[OK] Consultas lentas: 0% (54/72M)
[OK] Maior uso de conexões disponíveis: 6% (65/1050)
[OK] Tamanho do buffer de chave / índices MyISAM totais: 8,0M/ 82,1M
[OK] Taxa de acerto do buffer de chave: 100,0% (4B em cache / 1M de leituras)
[!!] O cache de consulta está desabilitado
[OK] Classificações que exigem tabelas temporárias: 0% (0 classificações temporárias / classificações de 948K)
[OK] Tabelas temporárias criadas no disco: 3% (11K no disco / 380K no total)
[!!] O cache de thread está desabilitado
[!!] Taxa de acerto do cache da tabela: 0% (64 aberto / 32K aberto)
[OK] Abrir arquivo limite usado: 2% (125/5K)
[OK] Bloqueios de tabela adquiridos imediatamente: 99% (30M imediatos / 30M bloqueios)
[OK] Tamanho de dados InnoDB / buffer pool: 2,7M/8,0M
-------- Recomendações ----------------------------------------- ------------
Recomendações gerais:
Execute OPTIMIZE TABLE para desfragmentar tabelas para melhor desempenho
Habilite o log de consulta lenta para solucionar problemas de consultas incorretas
Defina thread_cache_size como 4 como um valor inicial
Aumente table_cache gradualmente para evitar limites de descritor de arquivo
Variáveis para ajustar:
query_cache_size (>= 8M)
thread_cache_size (começar em 4)
table_cache (> 64)
Observe nas métricas de desempenho
[--] Total de buffers: 34,0 M globais + 2,7 M por thread (máximo de 1050 threads)
que o MySQL pode alocar até 72% da RAM instalada com base nas configurações em /etc/my.cnf.
O 34M é baseado em innodb_buffer_pool_size e key_buffer_size combinados
Os 2,7 milhões por thread foram baseados em read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size.
Múltiplos de 2,7 milhões são baseados em max_connections.
Portanto, você deve ajustar esses parâmetros até que o relatório de métricas de desempenho diga que você tem menos de 100% (de preferência menos de 80%) de RAM instalada.
Você não disse quanta memória RAM você tem? Eu suponho que seja pelo menos 32 GB.
Bom para tanta RAM.
Muito grande. É ineficiente quando é grande. Recomendar não mais do que 50M.
Pode haver um limite rígido de 4G (ainda) no Windows, tinha um limite rígido de 4G. Seu 5G pode ter se transformado em 1G. De qualquer forma, se todas as suas tabelas são InnoDB, por que desperdiçar a memória ram. Defina-o para 50M.
Como a mensagem de erro tinha exatamente 1G, cheira a
sort_buffer_size. 32M pode ser razoável.