Este mês estou trabalhando no ClickTracker 2.0 e importando os dados do banco antigo para o novo me deparei com o erro acima em uma das minhas querys do processo de migração:

# código levemente adaptado para facilitar a didática
INSERT INTO clicktracker_dev.keywords (id, keyword)
SELECT NULL id, DISTINCT(keyword) keyword FROM clicktracker_v1.logs;


Procurando no Google por uma solução acabei descobrindo que precisava alterar nas configurações do MySQL o valor da variável innodb_buffer_pool_size lá no arquivo /etc/my.cnf. O valor que estava por padrão era 2MB e eu modifiquei para 16MB - maior que isso eu imagino que vai deixar passar o que pode dar mais gargalo ainda para o servidor…

A parte que eu ainda queria entender era a relação do tamanho do buffer do innodb com essa query que insere numa tabela o resultado de uma pesquisa feita em outra.

Então continuando a busca para entender isso cheguei nessa contribuição do forum do MySQL:

If you have a small buffer pool, then the InnoDB lock table may indeed grow so big that it does not fit in the buffer pool. The default size of the buffer pool is just 8 MB.

Fix: modify my.cnf and increase innodb_buffer_pool_size. But do not make it bigger than about 70 % of your computer’s RAM.

Eu de fato só aumentei para 16MB, o que é bem menos de 10% do total de RAM que tenho no meu dedicado virtual.

Um artigo no MySQL Performance Blog (que eu assino o feed) também explica como ocorre o lock em tabelas innodb:

in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set.

Isto explicado e as alterações realizadas, basta reiniciar o mysqld e executar novamente a query. Com o conforto de um dedicado o processamento é prazerosamente rápido e você só se chateia com o tempo que perdeu procurando uma solução. :)