1

I'm trying to show that when selecting from a table with an appropriate index the number of rows has almost no effect on the query latency. So I created a table:

CREATE TABLE `perf_test` (
  `first` int NOT NULL,
  `second` int NOT NULL,
  `third` int NOT NULL,
  `fourth` int NOT NULL,
  `fifth` int NOT NULL,
  KEY `yyy` (`first`)
) 

I ran two tests: one where the table has 1M rows, 100 of which match the query; the other where the table has 10M rows, 100 of which match the query. I issued the query SELECT * FROM perf_test WHERE first = 1 10 times.

The average run time for 1M rows was 0.007 seconds, for 10M rows it was 0.057, so an order of magnitude greater. Why would that be?

MySQL version is 8.0.19 and I'm running this on Windows 10, using MySQL command line client.

Edit (adding info from SHOW SESSION STATUS LIKE 'Handler%'; as per @RickJames suggestion):

mysql> select count(*) from perf_test where first = 1;        
+----------+                                                  
| count(*) |                                                  
+----------+                                                  
|      100 |                                                  
+----------+                                                  
1 row in set (0.00 sec)                                       
                                                              
mysql> SHOW SESSION STATUS LIKE 'Handler%';                   
+----------------------------+-------+                        
| Variable_name              | Value |                        
+----------------------------+-------+                        
| Handler_commit             | 19    |                        
| Handler_delete             | 11    |                        
| Handler_discover           | 0     |                        
| Handler_external_lock      | 34    |                        
| Handler_mrr_init           | 0     |                        
| Handler_prepare            | 4     |                        
| Handler_read_first         | 0     |                        
| Handler_read_key           | 10    |                        
| Handler_read_last          | 0     |                        
| Handler_read_next          | 1044  |                        
| Handler_read_prev          | 0     |                        
| Handler_read_rnd           | 0     |                        
| Handler_read_rnd_next      | 0     |                        
| Handler_rollback           | 0     |                        
| Handler_savepoint          | 0     |                        
| Handler_savepoint_rollback | 0     |                        
| Handler_update             | 0     |                        
| Handler_write              | 0     |                        
+----------------------------+-------+                        
18 rows in set (0.00 sec)                                     
                                                              
mysql> select count(*) from perf_test_10m where first = 1;    
+----------+                                                  
| count(*) |                                                  
+----------+                                                  
|      100 |                                                  
+----------+                                                  
1 row in set (0.00 sec)                                       
                                                              
mysql> SHOW SESSION STATUS LIKE 'Handler%';                   
+----------------------------+-------+                        
| Variable_name              | Value |                        
+----------------------------+-------+                        
| Handler_commit             | 20    |                        
| Handler_delete             | 11    |                        
| Handler_discover           | 0     |                        
| Handler_external_lock      | 36    |                        
| Handler_mrr_init           | 0     |                        
| Handler_prepare            | 4     |                        
| Handler_read_first         | 0     |                        
| Handler_read_key           | 11    |                        
| Handler_read_last          | 0     |                        
| Handler_read_next          | 1144  |                        
| Handler_read_prev          | 0     |                        
| Handler_read_rnd           | 0     |                        
| Handler_read_rnd_next      | 0     |                        
| Handler_rollback           | 0     |                        
| Handler_savepoint          | 0     |                        
| Handler_savepoint_rollback | 0     |                        
| Handler_update             | 0     |                        
| Handler_write              | 0     |                        
+----------------------------+-------+                        
18 rows in set (0.00 sec)                                     
7
  • I'm no expert in mysql but some thoughts: a) your timings are quite small so it's hard to conclude too much, b) assuming that's not a clustered key then MySql still has to do key lookups to get all the other columns, meaning there's more disc access in the 10M row scenario. If you change your query to select first from ... you're likely to see less time increase as it can take the data directly from the index. I'd expect the extra time for the select * ... would not increase very much with more rows in the table.
    – Rory
    Jun 30, 2021 at 12:25
  • I don't understand, why is there more disk access for 10M rows? I thought the index points to the relevant row and then that's read into memory.
    – Johnny
    Jun 30, 2021 at 12:37
  • Sorry, I was being a bit lazy explaining what I meant. DBs usually read from disc in pages. The index entries identify the desired pages & location within the page. Each page will hold many rows but the more data you have the fewer rows of interest will be on each page, depending on the distribution of that data.. If all your 100 rows are next to each other in both 1M and 10M scenarios then disc access is probably approx the same. If your rows are evenly distributed amongst the other data then in the 10M scenario you'll need to read more pages.
    – Rory
    Jun 30, 2021 at 12:46
  • Also the index size in the 10M scenario means it's a bit bigger and hence a bit slower, but I'd guess that doesn't make much difference.
    – Rory
    Jun 30, 2021 at 12:47
  • In SQL Server you can ask for statistics on logical reads, query plan, etc to get more info about what's actually going on. That's much better than guessing and timing with the clock, e.g. since server might be doing lots of other things, memory load & caching is different, etc. Not sure how that works for MySql though...
    – Rory
    Jun 30, 2021 at 12:49

1 Answer 1

3

The problem

A table that is bigger than the buffer_pool is likely to be slow. How much slower depends on the query, other activity, the selectivity of the WHERE, etc, etc. There are applications that have tables that are 100 times the buffer_pool, yet still run nicely.

innodb_buffer_pool_size is only 8M. This is extremely tiny. Change it to 70% of available RAM. (8M is a very old default; did you copy my.cnf from somewhere?)

Your 10MB table is too big to be cached in the buffer pool. So there was a lot of I/O going on -- hence it was slower.

After increasing the buffer_pool, your test should show no 'significant' difference in timing between a small table and a bigger table.

Further discussion

I would expect you to get the 'same' time for 10M rows as 1M rows. Since you did not, let me list some potential flaws in the test.

When using ENGINE=InnoDB, it is wise to explicitly include a PRIMARY KEY. (It won't impact the test you are doing, but it avoids quibbles about its absence.)

In typical timing tests, one finds that the first run takes a certain amount of time, then all other tests take a similar time, but different than the first. If you find the 'first' to be faster (or slower) then the rest, then I will elaborate further.

If the "Query cache" is turned on, the timings are bogus. One way to be sure it is not messing up the conclusion: add SQL_NO_CACHE. (The QC is off for MySQL 8.0, so this should not be an issue.)

Another approach is to show the number of "Handler" actions. I find this to be useful in comparing two potential queries, even if they involve so few rows that the timings are suspect. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

I would expect the Handler counts to be identical for your two tables, thereby indicating that they are identical in effort.

If the table (or the relevant index) is bigger than innodb_buffer_pool_size, there may be more I/O. I doubt if this is the case for your "small" tables, but it is worth checking. (Note: The Handler counting does not discover this difference.)

Do EXPLAIN SELECT ... for each. I don't expect any substantive difference.

Do EXPLAIN FORMAT=JSON SELECT ... for each. I don't expect any substantive difference.

6
  • There does seem to be a difference in Handler_read_next (see my edit).
    – Johnny
    Jul 1, 2021 at 16:19
  • @Johnny - 1144-1044 = 100, which makes sense. Since the Handler numbers are counters that keep growing, please rerun the test with a "flush": FLUSH STATUS; SELECT ... ; SHOW ... ; You probably had other things contributing to the "1044".
    – Rick James
    Jul 1, 2021 at 17:35
  • You're right, now the values are exactly the same. So what could it be?
    – Johnny
    Jul 4, 2021 at 7:44
  • So changing innodb_buffer_pool_size changed the picture completely. The latency values are pretty close now. As to why innodb_buffer_pool_size was so small in the first place: it's the default value for installation on Windows. Thanks for your help!
    – Johnny
    Jul 4, 2021 at 10:35
  • @Johnny - When the buffer_pool is "big enough", the first query will need I/O to read blocks into it. Subsequent references to the rows in those blocks don't need to do I/O, hence are faster. If it is "too small", blocks will be bumped out and need to be reread from disk. (The Handler counts count "rows", not "I/Os"; that takes a different technique.)
    – Rick James
    Jul 4, 2021 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.