Para uma tabela InnoDB com 500 milhões de linhas (em uma unidade NVMe separada), SELECT COUNT(*)
leva cerca de 3 minutos.
SHOW ENGINE INNODB STATUS\G
mostra ROW OPERATIONS
cerca de 2 milhões de leituras/s, o que é consistente com o tempo que a consulta leva.
Também mostra FILE I/O
cerca de 3.000 leituras/s. Isso é semelhante ao read from iostat
, que também mostra a velocidade de leitura de cerca de 50 MB/s.
O NVMe tem muito mais capacidade para ler os dados do disco.
Eu me pergunto qual é o gargalo aqui? Ainda I/O
é ou é o processamento do MySQL?
EXEMPLO
Fiz um teste reprodutível básico.
CREATE TABLE test
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
Number int(11) unsigned NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB
INSERT INTO test (Number) SELECT * FROM seq_1_to_500000000;
Query OK, 500000000 rows affected (20 min 2.846 sec)
Records: 500000000 Duplicates: 0 Warnings: 0
SELECT COUNT(*) FROM test;
+-----------+
| COUNT(*) |
+-----------+
| 500000000 |
+-----------+
1 row in set (1 min 20.234 sec)
Depois de reiniciarMySQL
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
query_cache_type=0
query_cache_size=0
na ausência de qualquer outra atividade, consegui
SELECT COUNT(*) FROM test;
+-----------+
| COUNT(*) |
+-----------+
| 500000000 |
+-----------+
1 row in set (1 min 13.245 sec)
A pergunta-chave: é o mais rápido que você pode executar essa consulta em um NVMe típico?
Configurações:
- Conjunto de buffers de 50 GB. O
ibd
arquivo tem 17,7 GB. - CPU é 16/32 núcleos/threads.
innodb_io_threads
não tem efeito. Eu tentei 4 (padrão) e 64 (máximo).
e
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+-----------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------+-------------+
| Innodb_buffer_pool_dump_status | |
| Innodb_buffer_pool_load_status | |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_load_incomplete | OFF |
| Innodb_buffer_pool_pages_data | 874527 |
| Innodb_buffer_pool_bytes_data | 14328250368 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 2351473 |
| Innodb_buffer_pool_pages_made_not_young | 0 |
| Innodb_buffer_pool_pages_made_young | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_old | 322843 |
| Innodb_buffer_pool_pages_total | 3226000 |
| Innodb_buffer_pool_pages_lru_flushed | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 682843 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 56901439 |
| Innodb_buffer_pool_reads | 874396 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 515 |
+-----------------------------------------+-------------+
23 rows in set (0.001 sec)
Informações adicionais sobre a mesa
SHOW TABLE STATUS LIKE 'test';
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| test | InnoDB | 10 | Dynamic | 499216334 | 25 | 12859736064 | 0 | 0 | 7340032 | 500000001 | 2022-07-29 00:10:49 | 2022-07-29 00:32:52 | NULL | utf8_general_ci | NULL | | | 0 | N |
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.001 sec)
EXPLAIN FORMAT=JSON SELECT COUNT(*) FROM test;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"table": {
"table_name": "test",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"rows": 499216334,
"filtered": 100,
"using_index": true
}
}
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
SHOW VARIABLES LIKE 'innodb_io%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 32000 |
| innodb_io_capacity_max | 64000 |
+------------------------+-------+
Questão simples; não é uma resposta simples. Tenha paciência comigo enquanto eu divagar...
SELECT COUNT(*) FROM tbl
usará o índice "menor". Por favor, forneçaSHOW CREATE TABLE
para que possamos discutir detalhes. Providencie tambémSHOW TABLE STATUS
para que possamos discutir os tamanhos.Dado que o escolhido
INDEX
é, digamos, 4GB em disco, divida isso por 16K para obter 256K blocos que precisam ser lidos. Alguns dos blocos podem já estar em cache. Infelizmente, esses números não correspondem a nada que você deu.Outra maneira de ver o esforço é executando isso antes e depois do SELECT:
Em seguida, subtraia os números. Alguns serão contagens de bytes; alguns com contagens de blocos. Há um fator de 16K entre os dois, deve ser óbvio para ver qual é qual. O em questão pode ser
Innodb_buffer_pool_pages_data
. Faça o processo uma segunda vez; veja como esse número muda.Até agora, não abordei sua pergunta diretamente. Vamos ver se consigo um pouco mais perto...
COUNT(*)
.]Taxa por segundo = RPS
Sugestões a serem consideradas para sua seção my.cnf [mysqld] para minimizar gargalos
Há provavelmente oportunidades adicionais a serem consideradas.