What is the bottleneck in SELECT from InnoDB tables?

All we need is an easy explanation of the problem, so here it is.

For an InnoDB table with 500 million rows (on a separate NVMe drive), SELECT COUNT(*) takes about 3 min.

SHOW ENGINE INNODB STATUS\G shows ROW OPERATIONS of about 2M reads/s, which is consistent with the time the query takes.

It also shows FILE I/O of about 3,000 reads/s. This is similar to that read from iostat, which also shows the read speed of about 50MB/s.

The NVMe has a lot more capability for reading the data from the disk.

I wonder what is the bottleneck here? Is it still I/O, or is it MySQL processing?

EXAMPLE

I did a basic reproducible test.

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)

After restarting MySQL

innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
query_cache_type=0
query_cache_size=0

in the absence of any other activity, I got

SELECT COUNT(*) FROM test;
+-----------+
| COUNT(*)  |
+-----------+
| 500000000 |
+-----------+
1 row in set (1 min 13.245 sec)

The key question: Is it the fastest you can run this query on a typical NVMe?

Configurations:

  • 50GB buffer pool. The ibd file is 17.7GB.
  • CPU is 16/32 cores/threads.
  • innodb_io_threads has no effect. I tried 4 (default) and 64 (max).

and

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)

Additional information about the table

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 |
+------------------------+-------+

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section to minimize bottlenecks

Disable server_id with leading # to eliminate all REPL overhead
have_query_cache=0  # from YES to aliminate all QC overhead
innodb_flush_log_at_timeout=20  # from 1 for 20 second flush vs every second
innodb_lru_scan_depth=100  # from 128 for minimim scan depth
innodb_max_dirty_pages_pct_lwm-0.001  # from 0% to enable pre-flushing
innodb_max_dirty_pages_pct=0.002  # from 0% to tolerate some dirty pages
thread_cache_size=64  # from 8 
read_rnd_buffer_size=32K  # from 1G to reduce handler_read_rnd_next RPS
read_buffer_size=512K  # from 1G to reduce handler_read_next RPS
innodb_buffer_pool_size=32G  # from ~ 50G - loading less than 20G data/ndx
innodb_change_buffer_max_size=50  # from 0% for higher loading rows RPS
innodb_fast_shutdown=0  # to ensure dirty pages to media before shutdown
innodb_flushing_avg_loops=4  # from 30 to expedite dirty page reduction
innodb_read_io_threads=64  # from 4 per stackexchange Q 5666 Rolando's advice
innodb_write_io-threads=64  # from 4 per 9/12/2011 Rolando's advice

There are likely additional opportunities to be considered.

View profile for contact info and free Utility Scripts to assist with performance tuning, please.

Method 2

Simple question; not a simple answer. Bear with me while I ramble on…

SELECT COUNT(*) FROM tbl will use the "smallest" index. Please provide SHOW CREATE TABLE so we can discuss specifics. Also provide SHOW TABLE STATUS so we can discuss the sizes.

Given that the chosen INDEX is, say 4GB on disk, divide that by 16K to get 256K blocks that need to be read. Some of the blocks may already be in cache. Alas, these numbers don’t match anything you gave.

Another way to look at the effort is by running this both before and after the SELECT:

 SHOW GLOBAL STATUS LIKE 'InnoDB%';

Then subtract the numbers. Some will be byte counts; some with be block counts. There is a factor of 16K between the two, it should be obvious to see which is which. The one in question might be Innodb_buffer_pool_pages_data. Do the process a second time; see how that number changes.

So far, I have not addressed your question directly. Let’s see if I can some closer…

  • InnoDB uses one main thread for the query. [Caveat: the latest version tries to do some parallelism for COUNT(*).]
  • It uses a background thread to do the actual I/O, but that does not really matter, because…
  • It will read a block, count the rows, then move on to the next block.
  • There may be unrelated activity going on at the same time. For example, if you recently inserted lots of rows, the updating of the secondary indexes may be just now hitting the disk.
  • Since the COUNT must read each block into RAM in the buffer_pool, other blocks may need to be bumped out of that cache. This can lead to disk writes while you are doing an essentially read-only task.
  • If you run the query twice, the second time may run a lot faster than the first — this would be due to the first run filling the cache; the second one not doing any I/O! But…
  • If the index being used is bigger than will fit in cache, each run will do lots of I/O reads.
  • InnoDB is more designed for doing multiple actions from separate connections "simultaneously". For example, you might be able to do 10 counts at the ‘same time’ in, say, 6 seconds. Which do you care about? Latency — 3 sec for a single query versus Throughput — 10 queries in 6 seconds?
  • If those 10 queries are touching the same blocks, then caching avoids lots of I/O. If they are touching different blocks, then the disk drive may be the bottleneck.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply