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_trans
o tempo de consulta voltou para aproximadamente 40 segundos (recriando a tabela). Além disso, se eu fizer set profiling=on
e executar ANALYZE
a 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_result
e mysql_stmt_fetch
e parece que os dois métodos funcionam aproximadamente com a mesma rapidez.
Tenho outras opções?