Qual é o gargalo no SELECT das tabelas InnoDB?

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\Gmostra ROW OPERATIONScerca de 2 milhões de leituras/s, o que é consistente com o tempo que a consulta leva.

Também mostra FILE I/Ocerca 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 ibdarquivo tem 17,7 GB.
• CPU é 16/32 núcleos/threads.
• innodb_io_threadsnã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 |
+------------------------+-------+