Início / dba / Perguntas / 332557
Accepted
aitap
Asked: 2023-10-27 03:42:40 +0800 CST

Como fazer com que o MariaDB local seja tão rápido quanto o PostgreSQL local?

  • 772

Nossa aplicação científica precisa armazenar e consultar parâmetros fundamentais para diversas moléculas. Existem 2 a 28 milhões de linhas por molécula, mas espera-se que o número de moléculas permaneça pequeno (atualmente 4). Aqui está a tabela que estamos usando:

CREATE TABLE `mol_trans` (
  `species_id` int(11) DEFAULT NULL,
  `wl_vac` double DEFAULT NULL,
  `upper_id` int(11) DEFAULT NULL,
  `lower_id` int(11) DEFAULT NULL,
  `prob` double DEFAULT NULL,
  `flag` tinyint(4) DEFAULT NULL,
  KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
 PARTITION BY LIST (`species_id`)
(PARTITION `CaO` VALUES IN (6115) ENGINE = InnoDB,
 PARTITION `CN3` VALUES IN (6121) ENGINE = InnoDB,
 PARTITION `CN2` VALUES IN (6119) ENGINE = InnoDB,
 PARTITION `AlO` VALUES IN (6109) ENGINE = InnoDB)

(As partições estão aqui para facilitar a eliminação de uma molécula inteira, se necessário, o que de outra forma seria um grande problema DELETE. Os problemas de desempenho existiam antes das partições serem adicionadas.)

Usarei 10.3.39-MariaDB-0+deb10u1 (conectando via soquete de domínio UNIX usando o cliente de linha de comando) para testes, mas temos visto os mesmos problemas no MySQL 5.6 e MariaDB 10.11 no Windows 10.

A consulta a seguir leva aproximadamente 45 segundos em minha máquina, medida usando time echo "$QUERY" | mysql $DATABASE >/dev/null:

select
  mtr.prob,
  mtr.lower_id,
  mtr.upper_id
from
  mol_trans mtr
where (
  mtr.species_id=6115
  and mtr.wl_vac > 766.0
  and mtr.wl_vac < 883.0
  and mtr.flag = 1
)
order by mtr.wl_vac;

A consulta produz 3024559 linhas e parece usar um índice:

+------+-------------+-------+------+---------------+--------------+---------+-------------+----------+-------------+
| id   | select_type | table | type | possible_keys | key          | key_len | ref         | rows     | Extra       |
+------+-------------+-------+------+---------------+--------------+---------+-------------+----------+-------------+
|    1 | SIMPLE      | mtr   | ref  | spid_flag_wl  | spid_flag_wl | 7       | const,const | 14158123 | Using where |
+------+-------------+-------+------+---------------+--------------+---------+-------------+----------+-------------+

Tentei converter o banco de dados para PostgreSQL e, embora não confie totalmente nos resultados da conversão, a mesma consulta retorna mais de 3 milhões de linhas em menos de 6 segundos na mesma máquina. Mas a API do conector MySQL/MariaDB C é o que nosso aplicativo já foi escrito e gostaríamos de manter a conveniência de atualizar o banco de dados de maneira centralizada.

A questão : Como acelero o MySQL para que a consulta demore menos para ser concluída, pelo menos no servidor local, mais próximo dos 6 segundos do PostgreSQL? Tentei ativar histogramas de 255 bytes e executar ANALYZE TABLE mol_trans PERSISTENT FOR ALL, mas isso piorou (até 2 minutos para executar a mesma consulta). Surpreendentemente, OPTIMIZE TABLE mol_transo tempo de consulta voltou para aproximadamente 40 segundos (recriando a tabela). Além disso, se eu fizer set profiling=one executar ANALYZEa consulta, a maior parte do tempo será gasto no envio de dados:

+------------------------+-----------+
| Status                 | Duration  |
+------------------------+-----------+
| Starting               |  0.000078 |
| Checking permissions   |  0.000005 |
| Opening tables         |  0.000021 |
| After opening tables   |  0.000004 |
| System lock            |  0.000004 |
| Table lock             |  0.000004 |
| Init                   |  0.000028 |
| Optimizing             |  0.000027 |
| Statistics             |  0.000088 |
| Preparing              |  0.000021 |
| Sorting result         |  0.000008 |
| Executing              |  0.000003 |
| Sending data           | 40.324591 |
| End of update loop     |  0.000032 |
| Query end              |  0.000002 |
| Commit                 |  0.000003 |
| Closing tables         |  0.000003 |
| Unlocking tables       |  0.000001 |
| Closing tables         |  0.000008 |
| Starting cleanup       |  0.000002 |
| Freeing items          |  0.000006 |
| Updating status        |  0.000011 |
| Reset for next command |  0.000002 |
+------------------------+-----------+

Ao conversar com um servidor remoto, posso ver os resultados da consulta aparecendo no Wireshark como texto logo após o envio da consulta (o terminal permanece em silêncio até que todo o resultado seja recebido). Existe uma maneira de acelerar o processo de formatação de texto? A documentação do MariaDB sugere que as instruções preparadas podem resultar no uso de protocolo binário, que é presumivelmente mais rápido de serializar. Ou é? Compilei um programa de teste que baixa os resultados da consulta usando mysql_store_resulte mysql_stmt_fetche parece que os dois métodos funcionam aproximadamente com a mesma rapidez.

Tenho outras opções?

query-performance

2 respostas

  1. Acredito que você mesmo encontrou a resposta para a lentidão:

    O que melhorou o desempenho foi criar a coluna id com o tipo INT UNSIGNED AUTO_INCREMENT e defini-la como chave primária. Com o índice spid_flag_wl(species_id, flag, wl_vac) recriado, a saída EXPLAIN agora parece um pouco diferente:

    id: 1 select_type: tabela SIMPLE: tipo mtr: intervalo Possible_keys: spid_flag_wl chave: spid_flag_wl key_len: 16 ref: NULL rows: 5487882 Extra: Usando condição de índice

    ... e recebo minhas 3024559 linhas em pouco mais de 6 segundos.

    Nos comentários que você mencionou:

    A CLI do PostgreSQL sobre soquete de domínio UNIX realmente leva menos de 6 segundos

    Os 6 segundos medidos em relação ao PostgreSQL provavelmente ainda serão a sobrecarga do kernel que transporta os dados através de um soquete de domínio UNIX de um processo para outro. Um banco de dados incorporado como SQLite é inerentemente mais rápido que um processo daemon.

    • 1
  2. Best Answer

    "Enviar dados" é uma pista falsa

    Pode ser visível naqueles sql/sql_select.ccconjuntos JOIN::exec_inner()antes stage_sending_data da chamada do_select()que realizam muito trabalho adicional além de ter o conjunto de resultados serializado e enviado ao usuário. Portanto, mesmo que a consulta gaste muito tempo "enviando dados", o problema ainda pode ser devido à maneira como a consulta é planejada e executada, e não à sobrecarga do protocolo.

    Há mais de uma maneira de usar um índice

    As duas consultas a seguir diferem apenas na FORCE INDEXdeclaração:

    SemFORCE INDEX ComFORCE INDEX 
    analyze select
     mtr.prob, mtr.lower_id, mtr.upper_id
    from mol_trans mtr
    where (
     mtr.species_id=6115
     and mtr.wl_vac > 766.0
     and mtr.wl_vac < 883.0
     and mtr.flag = 1
    )
    order by mtr.wl_vac
    		 
    analyze select
     mtr.prob, mtr.lower_id, mtr.upper_id
    from mol_trans mtr
    force index(spid_flag_wl)
    where (
     mtr.species_id=6115
     and mtr.wl_vac > 766.0
     and mtr.wl_vac < 883.0
     and mtr.flag = 1
    )
    order by mtr.wl_vac
    		 
               id: 1
      select_type: SIMPLE
            table: mtr
             type: ref
    possible_keys: spid_flag_wl
              key: spid_flag_wl
          key_len: 7
              ref: const,const
             rows: 14025100
           r_rows: 28417908.00
         filtered: 100.00
       r_filtered: 10.64
            Extra: Using where
    		 
               id: 1
      select_type: SIMPLE
            table: mtr
             type: range
    possible_keys: spid_flag_wl
              key: spid_flag_wl
          key_len: 16
              ref: NULL
             rows: 6260712
           r_rows: 3024559.00
         filtered: 100.00
       r_filtered: 100.00
            Extra: Using where
    1 minuto 48.719 segundos 11.086 segundos

    Por si só, o otimizador de consulta parece preferir usar o prefixo do spid_flag_wlíndice (veja: type=refe key_len=7) e depois filtrar as linhas por WHERE. Com FORCE INDEX, todo o índice é usado ( key_len=16, que parece corresponder a dois ints seguidos por um double; também, type=range). Usando apenas o prefixo do índice, o planejador de consulta espera encontrar 14 milhões de linhas, mas encontra o dobro e precisa extrair apenas cerca de 10% ( r_filtered) delas. Com o índice completo, não apenas o planejador de consultas encontra menos linhas do que o estimado, mas todas elas são aplicáveis.

    (Veja EXPLAIN e ANALYZE sobre como interpretar a saída de ANALYZE SELECT.)

    Use o FORCE, Lucas

    Infelizmente, nada ANALYZE TABLE(na minha experiência) ajudou o MariaDB a escolher usar o índice completo automaticamente, com base na distribuição dos valores-chave. Mas como o índice foi projetado especificamente para esta consulta, não há mal nenhum em usá-lo FORCE INDEXpara orientar o otimizador de consulta. Esta solução melhora o desempenho da consulta em tudo que tentei, desde MySQL 5.6 no Windows 10 até MariaDB-10.3.39-0+deb10u1 e 11.1.2 no GNU/Linux.

    Isso foi relatado como bug MDEV-32646 .

    Aumentar o pH sugerido (Quem precisa de ACID?)

    MyISAM é um mecanismo de armazenamento otimizado para ambientes com operações pesadas de leitura , que é exatamente o que este aplicativo científico faz. As gravações acontecem raramente, como um procedimento de manutenção, e a tabela é relativamente fácil de criar do zero se for danificada. Seguindo o excelente conselho de Vassilis Virvilis , tentei recriar a tabela com ENGINE=MyISAM. Obtive os resultados da consulta em 3.528s , o que supera todos os outros resultados obtidos com mecanismos de banco de dados cliente-servidor.

    • 1

relate perguntas