Os índices múltiplos na tabela MYSQL são motivo para UPDATES e INSERTS lentos?

Dec 10 2020

O desempenho do meu site (pilha LAMP) degradou significativamente nos últimos dias, apesar de nenhuma atualização de código. Parece que apenas inserções, atualizações e exclusões de uma tabela MySQL específica causam o problema. Qualquer página que atualiza, insere ou exclui uma entrada da "tabela" de empregos leva cerca de 10 segundos para carregar. (EG UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)

SELECT as consultas parecem executar como antes, embora pareçam ser mais lentas se uma atualização estiver ocorrendo ao mesmo tempo.

A tabela tem cerca de 180.000 entradas. Notei na visão PHPMyAdmin, que além do índice "normal" no campo primário, existe um índice no campo "entry_date" (veja a imagem). Isso poderia ser um problema neste caso? Não tenho ideia de por que um índice nesse campo teria sido criado.

Se não, o que mais poderia ser a origem do problema? Verifiquei o espaço no disco, que parece OK. (7 GB disponíveis) de acordo com df.

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) NOT NULL DEFAULT '0', 
    `entry_date` date NOT NULL DEFAULT '0000-00-00', 
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00', 
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `description` text COLLATE latin1_german2_ci NOT NULL, 
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `status` tinyint(4) NOT NULL DEFAULT '0', 
    `cat_id` int(4) NOT NULL DEFAULT '0', 
    `price` decimal(4,2) NOT NULL DEFAULT '1.00', 
    `emailcount` smallint(5) NOT NULL DEFAULT '-1', 
    `emailcount2` int(11) NOT NULL DEFAULT '-1', 
    `recemailcount` int(11) NOT NULL DEFAULT '-1', 
    `archive` tinyint(4) NOT NULL DEFAULT '0', 
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL, 
    PRIMARY KEY (`job_id`), 
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

ATUALIZAÇÃO - Em primeiro lugar, obrigado a todos os contribuidores. Eu agradeço muito. Portanto, nos últimos dias, o problema parou, o que tornou ainda mais difícil tentar descobrir qual era o problema. Mas agora parece ter voltado mais uma vez. Deixe-me começar fornecendo o tipo de máquina hospedado na nuvem do google: g1-small (1 vCPU, 1,7 GB de memória). Vou continuar atualizando isso com as informações adicionais que foram solicitadas por aqueles que comentaram.

Respostas

6 ypercubeᵀᴹ Dec 10 2020 at 19:22

Para responder à pergunta específica:

Não , um índice extra em (entry_date)ou dois não prejudicaria o desempenho de atualização de uma titlecoluna diferente .

Além disso:

Não , a versão do MySQL, 5.6, não é muito antiga, mesmo que alguns recursos modernos (como funções de janela) estejam ausentes. Você deve ter um desempenho decente com hardware decente.

Podemos apenas especular sobre o problema em questão, mas o que poderia estar errado ou explicá-lo:

hardware antigo e ineficiente: verifique as especificações do disco, meça seu desempenho.
índice / tabela fragmentada. Verifique a documentação do MySQL e as perguntas / respostas antigas aqui sobre como desfragmentar tabelas MyISAM (OPTIMIZE TABLE).

Por último, mas não menos importante:

Sua mesa está usando o motor MyISAM , o que não é novidade. InnODB o substituiu como o mecanismo padrão no MySQL anos atrás. Não há desenvolvimento ativo deste motor. Ele carece de vários recursos em comparação ao InnoDB (transações, restrições de chave estrangeira, etc) e desempenho na maioria das cargas de trabalho . Eu sugiro fortemente que você mude sua tabela (após testar, é claro, se seus aplicativos e procedimentos não quebram) para usar o InnoDB.
É importante para sua pergunta, o InnoDB pode executar SELECTe UPDATEao mesmo tempo (normalmente). MyISAM bloqueia completamente a mesa; a atualização ou seleção deve terminar antes que a seleção ou atualização possa começar.
Ao mudar de MyISAM para InnoDB, certifique-se de ajustar key_buffer_sizee innodb_buffer_pool_size.

3 J.D. Dec 10 2020 at 19:20

Os índices afetam levemente o desempenho das operações INSERT e DELETE, mas geralmente isso é insignificante, especialmente se seus índices forem bem planejados e você não exagerar colocando 30 índices na mesma tabela, cada um com 30 combinações diferentes de campos. (Geralmente, eu fico com uma diretriz 5 por 5 - 5 índices no máximo, 5 campos por índice no máximo. Claro que isso é apenas uma diretriz e não uma regra rígida).

Dito isso, os índices são usados para aumentar o desempenho das consultas UPDATE e SELECT porque o índice é usado para localizar as linhas para UPDATE ou SELECT.

É duvidoso que a mudança drástica que você está vendo esteja relacionada ao índice entry_date que você descobriu (você sabe se isso foi adicionado recentemente ou já existia antes da mudança de desempenho?).

Duas coisas que você deve examinar são:

Se os índices anteriores para suas consultas ainda estão sendo usados, especialmente para consultas UPDATE e SELECT (e se eles estão sendo pesquisados ou se uma operação de varredura está ocorrendo agora). Isso pode mudar devido a mudanças nas estatísticas da tabela de dados ao longo do tempo. Você pode usar a instrução ANALYZE para verificar as estatísticas de uma tabela.
Outra coisa que você pode examinar é a fragmentação do índice , que é uma ocorrência natural ao longo do tempo. Isso acontece à medida que mais dados são adicionados a uma tabela. (Geralmente, isso não deve ser uma preocupação em uma pequena mesa como a sua, mas vale a pena verificar isso de qualquer maneira.)

Continuarei atualizando minha resposta com mais coisas para examinar enquanto penso nelas. Executar um EXPLAIN em suas consultas também pode ajudar a ter uma ideia sobre o problema. É tentar separar as instruções UPDATE e INSERT que estão rodando lentamente e tentar entender se elas estão sendo afetadas pela mesma causa raiz (já que, novamente, elas agem de forma um tanto inversa aos índices, INSERT são insignificantemente mais lentas, mas UPDATEs geralmente se beneficiam do índice correto e deve ser mais rápido.)