Início / dba / Perguntas / 264153
Accepted
a1an
Asked: 2020-04-03 05:37:36 +0800 CST

Por que uma consulta se torna extremamente lenta independentemente da quantidade de dados?

  • 772

Eu tenho uma consulta em execução geralmente em poucos segundos/minutos que se torna extremamente lenta depois de um tempo (cerca de uma semana) levando dias! executar. A consulta fica apenas em 'Enviando dados' e o uso da CPU é 100%. O servidor é um Mariadb 10.4 e o sistema está executando muitas outras consultas complexas sem problemas, apenas essa consulta específica parece atingir algumas limitações do servidor ou um bug de desempenho.

A quantidade de dados não parece ser relevante, pois a consulta é executada em diferentes bancos de dados que são criados e excluídos para cada projeto específico com diferentes quantidades de registros, mas o problema ocorre mesmo para projetos menores.

Uma reinicialização do servidor faz com que a consulta seja executada rapidamente novamente por um tempo, mas o problema ocorre repetidamente. O problema não parece ocorrer antes que o servidor atinja sua quantidade máxima permitida de RAM, mesmo que ainda haja RAM livre no servidor para usar (reduzi o tamanho do buffer especificamente para testá-lo). Uma vez que o problema se manifesta, isso acontece com os mecanismos InnoDB e MyISAM. Como a consulta é executada muito rapidamente após a reinicialização do servidor, não parece ser um problema de índices ausentes ou algo semelhante. Alguma dica do que pode causar o comportamento e como investigar/resolver?

Segue a consulta:

CREATE TABLE counts_otus (
    _sample_id INT,
    _region_sample_id INT,
    sequencesPerOtu INT,
    PRIMARY KEY (_region_sample_id),
    INDEX (_sample_id)
) ENGINE=InnoDB AS 
    SELECT _sample_map._sample_id, _sample_map._region_sample_id, (
            SELECT COUNT(*) 
              FROM cluster AS otu 
             WHERE otu._cluster_sample_id = _sample_map._region_sample_id
    ) + (
            SELECT count(*) 
              FROM cluster AS otu 
        INNER JOIN cluster AS mem 
                ON otu._region_sample_id = mem._cluster_sample_id
             WHERE otu._cluster_sample_id = _sample_map._region_sample_id
    ) + 1 AS sequencesPerOtu
      FROM Region
INNER JOIN _sample_map USING (primaryAccession)
INNER JOIN sample USING (_sample_id)
     WHERE regionTag is NULL
       AND sampleTag is NULL
       AND sample_type <> 'otumap'
;

Os planos de consulta são realmente diferentes, o que pode ser determinante para acertar o problema: O plano ao executar rápido é

+------+--------------------+-------------+------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+-------+--------------------------+
| id   | select_type        | table       | type | possible_keys                                   | key            | key_len | ref                                                                    | rows  | Extra                    |
+------+--------------------+-------------+------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+-------+--------------------------+
|    1 | PRIMARY            | sample      | ALL  | PRIMARY,id_sample_type                          | NULL           | NULL    | NULL                                                                   | 10    | Using where              |
|    1 | PRIMARY            | _sample_map | ref  | fk_sset_seqent,fk_sset_sample,fk_sset_smapleTag | fk_sset_sample | 4       | silvangs_slv_main_pid23875_rid26315.sample._sample_id                  | 52186 | Using where              |
|    1 | PRIMARY            | Region      | ref  | PRIMARY,fk_rgnTag                               | fk_rgnTag      | 100     | const,silvangs_slv_main_pid23875_rid26315._sample_map.primaryAccession | 1     | Using where; Using index |
|    3 | DEPENDENT SUBQUERY | otu         | ref  | PRIMARY,id_cluster                              | id_cluster     | 4       | silvangs_slv_main_pid23875_rid26315._sample_map._region_sample_id      | 1     | Using index              |
|    3 | DEPENDENT SUBQUERY | mem         | ref  | id_cluster                                      | id_cluster     | 4       | silvangs_slv_main_pid23875_rid26315.otu._region_sample_id              | 1     | Using index              |
|    2 | DEPENDENT SUBQUERY | otu         | ref  | id_cluster                                      | id_cluster     | 4       | silvangs_slv_main_pid23875_rid26315._sample_map._region_sample_id      | 1     | Using index              |
+------+--------------------+-------------+------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+-------+--------------------------+

O plano ao ser executado extremamente lento (eliminou a consulta em execução e levou a explicação de sua seleção logo em seguida:

+------+--------------------+-------------+--------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+--------+--------------------------+
| id   | select_type        | table       | type   | possible_keys                                   | key            | key_len | ref                                                                    | rows   | Extra                    |
+------+--------------------+-------------+--------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+--------+--------------------------+
|    1 | PRIMARY            | sample      | ALL    | PRIMARY,id_sample_type                          | NULL           | NULL    | NULL                                                                   | 10     | Using where              |
|    1 | PRIMARY            | _sample_map | ref    | fk_sset_seqent,fk_sset_sample,fk_sset_smapleTag | fk_sset_sample | 4       | silvangs_slv_main_pid23875_rid26315.sample._sample_id                  | 41361  | Using where              |
|    1 | PRIMARY            | Region      | ref    | PRIMARY,fk_rgnTag                               | fk_rgnTag      | 100     | const,silvangs_slv_main_pid23875_rid26315._sample_map.primaryAccession | 1      | Using where; Using index |
|    3 | DEPENDENT SUBQUERY | mem         | index  | id_cluster                                      | id_cluster     | 4       | NULL                                                                   | 738041 | Using index              |
|    3 | DEPENDENT SUBQUERY | otu         | eq_ref | PRIMARY,id_cluster                              | PRIMARY        | 4       | silvangs_slv_main_pid23875_rid26315.mem._cluster_sample_id             | 1      | Using where              |
|    2 | DEPENDENT SUBQUERY | otu         | ref    | id_cluster                                      | id_cluster     | 4       | silvangs_slv_main_pid23875_rid26315._sample_map._region_sample_id      | 57226  | Using index              |
+------+--------------------+-------------+--------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+--------+--------------------------+

Portanto, não existem apenas os tipos de junção "ref" ao executar lentamente, mas também "index" e "eq_ref", que devem ser melhores até onde posso dizer, mas acabam presos por dias.

A pergunta foi postada originalmente no stackoverflow, onde recebi a sugestão de que seria mais adequado aqui no dba, aqui o link para a pergunta: https://stackoverflow.com/questions/60952661/why-does-a-query-becomes- extremamente-lento-independentemente-da-quantidade-de-dados

mysql query-performance

2 respostas

  1. Best Answer

    De acordo com a documentação do mysql ( https://dev.mysql.com/doc/refman/5.7/en/controlling-query-plan-evaluation.html ) parece que o plano de consulta errado pode realmente fazer a diferença em tais ordens de magnitude como segundos vs dias, então presumo que o problema esteja no plano de consulta errado sendo escolhido pelo otimizador. Por que isso está acontecendo regularmente depois de algum tempo o servidor está rodando (e a memória disponível para seus buffers está totalmente alocada) permanece um mistério, porém a solução parece estar em dar dicas ao otimizador para evitar a ordem errada de junções e usar o índice referenciado na boa consulta. Isso é obtido alterando a consulta da seguinte forma:

    DROP TABLE IF EXISTS test_counts_otus;
CREATE TABLE test_counts_otus (
    _sample_id INT,
    _region_sample_id INT,
    sequencesPerOtu INT,
    PRIMARY KEY (_region_sample_id),
    INDEX (_sample_id)
) ENGINE=InnoDB AS 
    SELECT _sample_map._sample_id, _sample_map._region_sample_id, (
            SELECT COUNT(*) 
              FROM cluster AS otu FORCE INDEX ( id_cluster )
             WHERE otu._cluster_sample_id = _sample_map._region_sample_id
    ) + (
            SELECT count(*) 
              FROM cluster AS otu FORCE INDEX ( id_cluster )
     STRAIGHT_JOIN cluster AS mem FORCE INDEX ( id_cluster )
                ON otu._region_sample_id = mem._cluster_sample_id
             WHERE otu._cluster_sample_id = _sample_map._region_sample_id
    ) + 1 AS sequencesPerOtu
      FROM Region
INNER JOIN _sample_map USING (primaryAccession)
INNER JOIN sample USING (_sample_id)
     WHERE regionTag is NULL
       AND sampleTag is NULL
       AND sample_type <> 'otumap'
;

    A consulta fixa usa índices forçados e STRAIGHT_JOIN conforme documentado em https://mariadb.com/kb/en/index-hints-how-to-force-query-plans/

    • 0

  2. Este índice de "cobertura" no otu pode fazer o truque:

    INDEX(_cluster_sample_id, _region_sample_id)  -- in this order
    • 0

relate perguntas