InnoDB Primary Key Index on Large Table

MySQL Tutorials - Herong's Tutorial Examples

∟InnoDB Storage Engine - Primary and Secondary Indexes

∟InnoDB Primary Key Index on Large Table

This section provides a tutorial example on how to create a large InnoDB table, and it index and data pages partially cached in the buffer.

In previous tutorials, we have looked at an InnoDB table with a single index page. Now let's build a large table with multiple index pages.

Since an index record is 14 bytes long, an index page will hold about 16384/14 = 1170 index records, able to point to 1170 data pages. If we continue with our "Head" table, a single index page can manage up to 1170*64 = 74880 rows.

1. Rebuild the "Head" table with 100,000 rows in a single transaction to reduce execution time.

mysql> delete from Head;
mysql> set autocommit = off;

mysql> CALL InsertHead(1, 100000);
Query OK, 1 row affected (2.87 sec)

mysql> commit;

2. Look at data and index page counts in the buffer.

mysql> select * from sys.schema_table_statistics_with_buffer
    -> where table_name='Head';
  | innodb_buffer_pages | io_read_requests | io_write_requests |
  +---------------------+------------------+-------------------+
  |                1566 |                0 |              5758 |

mysql> select * from information_schema.innodb_buffer_page
    -> where table_name rlike 'Head' and number_records <> 64;
  | page_number | page_type | number_records | data_size |
  +-------------+-----------+----------------+-----------+
  |           5 | INDEX     |             32 |      7520 |
  |           4 | INDEX     |              2 |        28 |
  |          37 | INDEX     |            560 |      7840 |
  |          38 | INDEX     |           1003 |     14042 |

3. Verify index and data page counts:

Number of data pages: 1563 - With 1562 pages holding 64 rows in each page, and 1 page holding 32 rows. This matches the calculation of 100,000/64 = 1562.5 data pages.
Number of index pages: 3 - With 2, 560 and 1003 records in 3 index pages. Total of 1565 records, pointing to 1563 data pages and 2 non-root index pages.

Since our data rows were inserted sequentially, The clustered index structure of the table should look like this:

             I (root)             - Root index page
----------------------------
|  | |  |  |  | |     \       \
|  | |  |  |  | |      I       I    - Non-root index pages
|  | |  |  |  | |    / | \    / \
D D D ... D D D   D ... D   D D   - Data pages

4. Verify number of pages to read when retrieving data.

mysql> show status like 'Innodb_buffer_pool_read_requests';
  | Innodb_buffer_pool_read_requests | 37191971 |

mysql> select * from Head where id=1;
  |  1 | Hello world! |

mysql> show status like 'Innodb_buffer_pool_read_requests';
  | Innodb_buffer_pool_read_requests | 37191973 |

mysql> select * from Head where id=100000;
  | 100000 | Hello world! |

mysql> show status like 'Innodb_buffer_pool_read_requests';
  | Innodb_buffer_pool_read_requests | 37191976 |

The output confirms that retrieving the first row reads 2 pages, and retrieving the last row reads 3 pages.

5. Check InnoDB buffer size to calculate the best chunk size of un-committed data.

mysql> show variables like 'innodb_%_size';
  | innodb_buffer_pool_size          | 134217728  |
  | innodb_page_size                 | 16384      |

mysql> select 134217728/16384 as pages;
  | 8192.0000 |

mysql> select 100000/64 as pages;
  | 1562.5000 |

mysql> select 100*1563/8192 as percent;
  | 19.0796 |

So the buffer can hold about 8,200 pages. If we set 100,000 rows as the chunk size for un-committed, it will require 1,563 pages, about 20% of the buffer, to store un-committed data. It should be a reasonable load for the buffer.

6. Add a new stored procedure to insert 100000 rows per transaction.

mysql> DELIMITER '/';
mysql> CREATE PROCEDURE LargeHead()
    -> BEGIN
    ->   DECLARE S INT;
    ->   DECLARE I INT;
    ->   SET S = 100000;
    ->   SET I = 0;
    ->   WHILE I < 20 DO
    ->     SELECT 'Inserting', I*S+1, (I+1)*S;
    ->     CALL InsertHead(I*S+1, (I+1)*S);
    ->     commit;
    ->     SET I = I + 1;
    ->   END WHILE;
    -> END/
mysql> DELIMITER ';'/

7. Insert 2,000,000 rows by calling LargeHead():

mysql> set autocommit = off;

mysql> CALL LargeHead();
  | Inserting |     1 |  100000 |
  1 row in set (0.00 sec)

  | Inserting | 100001 |  200000 |
  1 row in set (18.78 sec)

  | Inserting | 200001 |  300000 |
  1 row in set (22.72 sec)

  | Inserting | 300001 |  400000 |
  1 row in set (37.54 sec)

  ...

  | Inserting | 1900001 | 2000000 |
  1 row in set (8 min 24.19 sec)

  Query OK, 0 rows affected (8 min 51.63 sec)

mysql> set autocommit = on;

8. Look at total page counts in the buffer. I see only 7435 pages out of 31250 data pages plus some index pages in the buffer.

mysql> select 2000000/64 as data_pages;
  | 31250.0000 |

mysql> select * from sys.schema_table_statistics_with_buffer
    -> where table_name='Head';
  | innodb_buffer_pages | io_read_requests | io_write_requests |
  +---------------------+------------------+-------------------+
  |                7435 |            27498 |             31325 |

9. Look at index pages in the buffer. I see 24 index pages in the memory.

mysql> select * from information_schema.innodb_buffer_page
    -> where table_name rlike 'Head' and data_size/number_records = 14;

  | page_number | page_type | number_records | data_size |
  +-------------+-----------+----------------+-----------+
  |       29760 | INDEX     |           1120 |     15680 |
  |          62 | INDEX     |           1120 |     15680 |
  |          46 | INDEX     |           1120 |     15680 |
  |          54 | INDEX     |           1120 |     15680 |
  |          43 | INDEX     |           1120 |     15680 |
  |       29761 | INDEX     |            451 |      6314 |
  |          61 | INDEX     |           1120 |     15680 |
  |          45 | INDEX     |           1120 |     15680 |
  |          39 | INDEX     |           1120 |     15680 |
  |          50 | INDEX     |           1120 |     15680 |
  |           4 | INDEX     |             29 |       406 |
  |          41 | INDEX     |           1120 |     15680 |
  |          47 | INDEX     |           1120 |     15680 |
  |          49 | INDEX     |           1120 |     15680 |
  |          59 | INDEX     |           1120 |     15680 |
  |          37 | INDEX     |            560 |      7840 |
  |          63 | INDEX     |           1120 |     15680 |
  |          57 | INDEX     |           1120 |     15680 |
  |          55 | INDEX     |           1120 |     15680 |
  |          38 | INDEX     |           1120 |     15680 |
  |          51 | INDEX     |           1120 |     15680 |
  |          53 | INDEX     |           1120 |     15680 |
  |          58 | INDEX     |           1120 |     15680 |
  |          42 | INDEX     |           1120 |     15680 |
  +-------------+-----------+----------------+-----------+
  24 rows in set (0.03 sec)

10. Calculate total number of records in from index pages in the buffer. I see that 24 index pages in the buffer hold 24560 page addresses.

mysql> select count(*), sum(number_records) as total
    -> from information_schema.innodb_buffer_page
    -> where table_name rlike 'Head' and data_size/number_records = 14;
  | count(*) | total |
  +----------+-------+
  |       24 | 24560 |

11. Based on the following summary, there are still a lots of pages on the hard disk, not in the buffer. So it may take a longer time to retrieve a data row, if it is not in a data page in the buffer.

# of data rows:             2,000,000
# of data pages:            31,250 with 64 rows per page
# of index pages in buffer: 24 pages with 24,560 page addresses
# of data pages in buffer:  7,411 = 7435-24

# of total index pages:     > 28 = 31250/1120
Buffer size in pages:       8,192

Those 28 index pages should be organized in a Balanced-Tree in a shape like this, if it truly balanced. So retrieving a row requires reading maximum 5 pages: 4 index pages and 1 data page.

                        I
                I               I
            I       I       I       I
          I   I   I   I   I   I   I   I
         I I I I I I I I I I I I I . . .

 D D D D ............................... D D D D  - Data pages

12. Retrieve the first record. InnoDB reads 3 pages from the buffer, with < 0.01 sec. Note that "Innodb_buffer_pool_reads" is the number of reads from hard disk to buffer.

mysql> show status like 'Innodb_buffer_pool_read%';
  | Innodb_buffer_pool_read_requests | 58453716 |
  | Innodb_buffer_pool_reads         | 493319   |

mysql> select * from Head where id=1;
  |  1 | Hello world! |
  1 row in set (0.00 sec)

mysql> show status like 'Innodb_buffer_pool_read%';
  | Innodb_buffer_pool_read_requests | 58453719 |
  | Innodb_buffer_pool_reads         | 493319   |

12. Retrieve the record # 1000000. InnoDB reads 4 pages: 3 from the buffer and 1 from hard disk. This is why it takes a longer time: 0.03 sec.

mysql> show status like 'Innodb_buffer_pool_read%';
  | Innodb_buffer_pool_read_requests | 58453719 |
  | Innodb_buffer_pool_reads         | 493319   |

mysql> select * from Head where id=1000000;
  | 1000000 | Hello world! |
  1 row in set (0.03 sec)

mysql> show status like 'Innodb_buffer_pool_read%';
  | Innodb_buffer_pool_read_requests | 58453723 |
  | Innodb_buffer_pool_reads         | 493320   |

13. Retrieve the record # 1000000 again. InnoDB reads 4 pages. All of them are from the buffer. So this time it is much faster: < 0.01 sec.

mysql> show status like 'Innodb_buffer_pool_read%';
  | Innodb_buffer_pool_read_requests | 58453723 |
  | Innodb_buffer_pool_reads         | 493320   |

mysql> select * from Head where id=1000000;
  | 1000000 | Hello world! |
  1 row in set (0.00 sec)

mysql> show status like 'Innodb_buffer_pool_read%';
  | Innodb_buffer_pool_read_requests | 58453727 |
  | Innodb_buffer_pool_reads         | 493320   |

Conclusion: if an InnoDB is larger than the buffer, retrieving a data row may take a longer time, because of reading data pages from hard disk to the buffer. Retrieving the same data row again is faster, because related pages are already in the buffer.