Início / dba / Perguntas / 21075
Accepted
32bitfloat
Asked: 2012-07-17 02:23:49 +0800 CST

maneira de evitar que as consultas esperem pelo bloqueio no nível da tabela

  • 772

Encontramos um problema após mover o banco de dados de nosso cliente para um servidor extra. Isso deveria ter tido efeitos positivos no desempenho do site, mas há um problema com o bloqueio de tabelas no MyISAM. (Já ouvi falar do uso do InnoDB em vez do MyISAM, mas não podemos alterar o mecanismo em um futuro próximo).
Podemos identificá-lo em uma consulta de atualização que é realizada quando um moderador ativa um comentário no site de artigos. Este é o processo:

  • a consulta de atualização é processada SET status = 1 WHERE id = 5(o índice é definido)
  • os arquivos em cache da página são excluídos

Neste ponto, a página inteira fica lenta. O próprio banco de dados está ocupado por minutos. Eu busquei a lista de processos algumas vezes e vi cerca de 60 entradas de diferentes consultas de seleção, que estavam todas no estado aguardando o bloqueio de nível de tabela .

1. Não entendo por que essa atualização na tabela article_commentspode afetar as instruções select para que articlea tabela aguarde o bloqueio no nível da tabela. Na processlist quase todas as consultas em espera eram desta tabela. Eu li sobre o fato de que atualizações/inserções são preferidas a seleções e que isso pode causar tais problemas, mas a tabela de artigos em si não é atualizada quando os comentários são ativados, então as seleções não devem esperar. Eu entendi errado isso?
2. Existe algo além de mudar para o InnoDB para evitar esse comportamento ou pelo menos para obter um equilíbrio melhor? Estou muito irritado com o fato de que esse problema não apareceu antes de mover o banco de dados para o novo servidor. Acho que há alguma configuração errada, mas não sei como identificar.

mysql performance

2 respostas

  1. Best Answer

    O MyISAM Storage Engine é furiosamente notório por realizar bloqueios de tabela completos para qualquer DML (INSERTs, UPDATEs, DELETEs). O InnoDB definitivamente resolveria esse problema a longo prazo.

    Eu escrevi sobre prós e contras de usar MyISAM vs InnoDB

    Com relação à sua pergunta atual, aqui está um cenário possível:

    • articlee article_commentssão ambas tabelas MyISAM
    • article_commentstem um ou mais índices com statuscomo uma coluna
    • As atualizações da página de índice article_commentssão armazenadas em cache no MyISAM Key Buffer (dimensionado por key_buffer_size ), fazendo com que as páginas de índice antigas saiam do MyISAM Key Buffer
    • Você tem consultas SELECT que executam JOINs entre articleearticle_comments

    No meu cenário sugerido, SELECTs na articletabela podem ser impedidos de permitir gravações por causa de ter que esperar para article_commentsestar livre de qualquer DML (neste caso, um UPDATE)

    • 8

  2. Neste ponto, a página inteira fica lenta. O próprio banco de dados está ocupado por minutos.

    Cheira como se você tivesse um grande Query_cache?

    mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 | -- Not over 50M
| query_cache_type             | DEMAND   | -- Only if using SQL_CACHE
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

    Para sistemas de produção com muitas gravações, você também pode desativar o query_cache.

    Todas as entradas no query_cache para a tabela fornecida são limpas quando ocorre qualquer gravação nessa tabela. Quanto maior o QC, mais lenta é essa tarefa.

    MyISAM usa bloqueios de "nível de tabela". Leituras e gravações não podem ocorrer ao mesmo tempo (na mesma tabela). Bruto, mas eficaz.

    • 8

relate perguntas