Recentemente, mudamos uma de nossas tabelas para innodb e agora estamos experimentando tempos de execução de UPDATE muito lentos. Uma atualização que costumava levar de 0,010 a 0,030 segundos agora pode levar mais de 70 segundos. Algumas consultas são descartadas porque não podem adquirir um bloqueio dentro do limite padrão de 50 segundos (sei que podemos aumentar esse limite).
A tabela em questão possui apenas um índice, a própria chave primária que é um valor mediumint. A tabela tem cerca de 1 milhão de linhas. Todos os UPDATEs neste contexto envolvem uma única linha. Normalmente, 4-5 colunas dessa linha são afetadas em cada consulta.
O my.cnf atual é colado abaixo. Você vê algo que poderia causar um desempenho ruim de UPDATE para o innodb?
[mysqld]
set-variable=local-infile=0
datadir=/db/mysql/data
socket=/var/lib/mysql/mysql.sock
#log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-locking
key_buffer = 1G
query_cache_size = 256M
thread_cache_size = 128
table_cache = 2048
max_connections = 400
query_cache_limit = 1024M
log_slow_queries = /var/log/mysql-slow.log
long_query_time = 1
skip-bdb
skip-locking
skip-name-resolve
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
#innodb_log_file_size=250M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
ATUALIZAÇÃO:
innodb_log_file_size: 5242880
have_innodb: SIM A
cláusula "WHERE" sempre procura apenas uma coluna, que é a chave primária.
ATUALIZAÇÃO - 26 de julho de 2012: Atualizamos
nosso banco de dados para mysql 5.5. Agora as atualizações do innodb são bem rápidas, menos de 0,010 segundos em nosso caso específico. E a variância é bem baixa. Minha opinião sobre isso é: o innodb deve ser usado com muito cuidado no mysql 5.0.
PERSPECTIVA #1
Quando você atualiza por chave primária apenas no InnoDB, há uma ocasião rara, mas possível, em que o índice clusterizado (também conhecido como gen_clust_index ) pode ser bloqueado.
Certa vez, respondi a três postagens de um indivíduo sobre esse assunto
Por favor, leia-os cuidadosamente. O autor da pergunta encontrou sua própria solução alternativa com base no comportamento de bloqueio de índice clusterizado do InnoDB. Infelizmente, ele não postou qual era a solução alternativa.
Além disso, quando você perceber que as consultas estão lentas, faça login no mysql e execute
SHOW ENGINE INNODB STATUS\Ge comece a procurar bloqueios no índice clusterizado.PERSPECTIVA #2
Vejo que você comentou innodb_log_file_size . Você tem 5 MB, o padrão. Como innodb_buffer_pool_size está definido como 1G, innodb_log_file_size precisa estar em 256M. Clique aqui para configurar innodb_log_file_size para 256M .
PERSPECTIVA #3
Vejo que você não está usando innodb_file_per_table . Você pode querer usá-lo para fazer atualizações de tabela especificamente para uma tabela com um milhão de linhas. Clique aqui para ver como limpar a infraestrutura InnoDB para usar innodb_file_per_table .
Você está atualizando o valor da chave primária?
Alguma das colunas são chaves estrangeiras para outras tabelas? Como o InnoDB também atualiza as chaves estrangeiras na mesma transação por meio do CASCADE.
Você pode aumentar o innodb_buffer_pool_size para mais de 1 GB para que toda a tabela caiba na memória?
Quantas outras tabelas estão no schmea - pois pode haver problemas ao redimensionar o tablespace para realizar a atualização
Você pode querer tentar usar o innodb_file_per_table onde um arquivo tablespace é criado para cada tabela. Depois de alterar esse parâmetro, você precisa reiniciar o servidor e criar seu banco de dados novamente para que ele entre em vigor.