Início / dba / Perguntas / 10370
Accepted
Alan
Asked: 2012-01-08 18:07:54 +0800 CST

Atualizações simultâneas do MySQL travam com o InnoDB (no Amazon RDS)

  • 772

Estou enfrentando um problema em que várias atualizações do MySQL executadas ao mesmo tempo travam e levam vários minutos para serem concluídas. Estou usando o InnoDB, então estou confuso sobre por que isso pode estar acontecendo, já que cada atualização está atualizando apenas 1 linha. Também estou usando uma instância RDS m2.4xlarge (a maior que existe).

Atualização travada

Aqui está o que estou fazendo: tenho uma tabela com cerca de 100 milhões de linhas, com "visualizações" sendo uma coluna (que é indexada) e desejo atualizar as visualizações em cerca de 1 milhão de linhas. Em vários servidores diferentes, tenho um loop como este, onde cada servidor possui seu próprio conjunto de linhas a serem atualizadas (pseudo código):

mysql("set autocommit=0");
mysql("start transaction");

foreach($rows as $row) {
    mysql("update table set views=views+1 where id=$row[id]");
}

mysql("commit");

Isso percorre todas as linhas que precisam ser atualizadas. Funciona perfeitamente quando o número de servidores é pequeno, como cerca de 4, mas quando aumenta para mais de 10, as atualizações começam a travar no estado "Atualizando" de uma só vez. Nada diz que está esperando um bloqueio, é apenas "Atualizando". Isso acontece por cerca de 5 minutos, onde ele finalmente fará as atualizações e continuará no loop e, eventualmente, acontecerá novamente.

Não estou procurando maneiras alternativas de fazer as atualizações. Ter coisas como uma tabela tmp e

update table,tmp_table set table.views = table.views+tmp_table.views where
  table.id = tmp_table.id

bloqueie todas as linhas que estão sendo atualizadas até que todas terminem (o que pode levar horas), o que não funcionará para mim. Eles DEVEM estar nesses loops terríveis.

Estou me perguntando por que eles podem estar travando no estado "Atualizando" e o que posso fazer para evitar isso.

tldr; Ter mais de 10 loops de "atualização" eventualmente bloqueará todas as atualizações sendo feitas, ao mesmo tempo, por um motivo desconhecido até que eles decidam finalmente fazer atualizações e continuar pelos loops, apenas para que isso aconteça novamente segundos depois.

MOSTRAR VARIÁVEIS: http://pastebin.com/NdmAeJrz

MOSTRAR STATUS INNODB DO MOTOR: http://pastebin.com/Ubwu4F1h

mysql innodb

3 respostas

  1. Best Answer

    Não estou procurando maneiras alternativas de fazer as atualizações. Ter coisas como uma tabela tmp [irá] bloquear todas as linhas que estão sendo atualizadas até que todas terminem (o que pode levar horas), o que não funcionará para mim. Eles DEVEM estar nesses loops terríveis.

    Discordo.

    A força de um RDBMS está na execução de operações definidas como "atualizar todas essas linhas, por favor". Diante disso, sua intuição deve lhe dizer que esses "loops horríveis" não são o melhor caminho a percorrer, exceto em circunstâncias muito raras.

    Vamos dar uma olhada em sua lógica de atualização atual e entender o que ela está fazendo.

    Em primeiro lugar, a set autocommit=0linha em seu script é desnecessária . Como você abre explicitamente uma transação imediatamente depois disso com start transaction, autocommit fica desabilitado automaticamente até que você finalize a transação com COMMITou ROLLBACK.

    Agora, para a essência da lógica: você envolveu todas essas atualizações individuais dentro do loop em uma grande transação. Se sua intenção por trás das atualizações iterativas era reduzir o bloqueio e aumentar a simultaneidade, a transação agrupada anula essa intenção. O MySQL deve manter bloqueios em todas as linhas que atualiza até que a transação seja confirmada, para que possa reverter todas de uma vez se a transação falhar ou for cancelada. Além disso, em vez de saber com antecedência que está prestes a bloquear esse intervalo de linhas (o que permitiria ao MySQL emitir bloqueios com a granularidade apropriada) , o mecanismo é forçado a emitir um grande número de bloqueios em nível de linha rapidamente. Dado que você está atualizando 1 milhão de linhas, isso é um fardo enorme para o mecanismo.

    Proponho duas soluções:

    1. Ative autocommite remova o wrapper de transação. O MySQL poderá liberar todos os bloqueios de linha logo após terminar de atualizar a linha. Ainda é forçado a emitir e liberar um grande número de bloqueios em um curto período de tempo, então duvido que essa seja uma correção apropriada para você. Além disso, se ocorrer algum erro no meio do loop, nada será revertido, pois o trabalho não está vinculado à transação.

    2. Agrupe suas atualizações em uma tabela temporária. Você mencionou e depois descartou esta solução, mas aposto que terá o melhor desempenho. Você já experimentou? Eu testaria primeiro a atualização completa de um milhão de linhas. Se isso demorar muito, agrupe o trabalho em partes progressivamente menores até encontrar o ponto ideal: os lotes são grandes o suficiente para fazer o trabalho total rapidamente, mas nenhum lote individual bloqueia outros processos por muito tempo. Essa é uma técnica comum que os DBAs usam quando precisam modificar um grande número de linhas durante as operações ao vivo. Lembre-se, já que seu objetivo é maximizar sua simultaneidade, continue autocommite não envolva nenhum desses trabalhos em uma transação massiva para que o MySQL libere seus bloqueios o mais rápido possível.

      Observe que, à medida que os lotes se tornam progressivamente menores, essa solução acaba se aproximando da primeira. É por isso que estou confiante de que esta solução terá um desempenho melhor: quando o mecanismo de banco de dados pode agrupar seu trabalho em blocos, ele voa.

    • 11

  2. Há sempre a ameaça iminente de deadlock, mesmo com o InnoDB. Nesse caso específico, posso ver linhas mesmo no InnoDB executando de cabeça em situações de impasse porque você está atualizando dados por meio da PRIMARY KEY das tabelas de visualizações. Isso iniciará o bloqueio agressivo no índice clusterizado.

    Você pode ver isso bloqueado usandoSHOW ENGINE INNODB STATUS\G

    Respondi a três perguntas muito difíceis sobre uma questão semelhante.

    As consultas SELECT/UPDATE podem executar bloqueios no gen_clust_index , também conhecido como Clustered Index ao atualizar por meio da PRIMARY KEY.

    Aqui estão três perguntas do DBA Stack Exchanges que examinei agressivamente com @RedBlueThing , a pessoa que fez essas perguntas. @RedBlueThing encontrou soluções alternativas para suas perguntas.

    Em todas essas três questões, um bloqueio de linha envolveu um bloqueio correspondente no índice clusterizado da mesma tabela. Chaves vizinhas de linhas bloqueadas estavam envolvidas e, portanto, contribuíram para os problemas.

    MORAL DA HISTÓRIA: Deadlocks com InnoDB ainda são uma possibilidade. A configuração de um algoritmo adequado para bloqueios de nível de linha individuais e atualização individual das linhas em questão é muito mais segura do que a atualização em massa por meio de vários bloqueios de nível de linha a qualquer momento.

    Certifique-se de usar autocommit=1ao atualizar fortemente uma tabela dessa maneira. Mesmo assim, atualizar uma linha no InnoDB fará com que todos os tipos de dados MVCC sejam envoltos em torno do conteúdo anterior da linha para permitir transações simultâneas. Dada a natureza do UPDATE, muitos dados MVCC serão gerados.

    • 1

  3. Olhando para o seu status innodb, vejo que o último impasse com a tabela de visualizações também se deve a esta consulta:

    update low_priority reddit_new 
join images_new on images_new.hash = reddit_new.hash 
set reddit_new.score = images_new.views 
where date > date(now() - interval 1 day)

    Está reddit_new.dateindexado? As colunas de hash de ambas as tabelas são indexadas?

    • 0

relate perguntas