Forcing InnoDB to build the secondary index

Question

When INSERTing, InnoDB defers the building of secondary indexes. For example,

CREATE TABLE item_creators
(
item_id int(11) unsigned NOT NULL,
creator_id int(11) unsigned NOT NULL,
INDEX(creator_id),
PRIMARY KEY(item_id)
) ENGINE=InnoDB

After the first query,

INSERT INTO item_creators (item_id,creator_id)
    SELECT item_id,creator_id FROM t1;

SELECT a.item_id, b.creator FROM
    item_creators a JOIN creators ON a.creator_id=b.creator_id;

InnoDB starts to rebuild the secondary index, INDEX(creator_id) in background. Without this index, the second query is quite slow.

• First, how can we force InnoDB to rebuild the secondary index after the first query?
• Second, how can we check if the required index has been completed before running the second query?

Note that it's about very large tables (hundreds of millions of rows) where the index building takes time.

Answer 1

I think you have some misunderstandings. When you insert a row, InnoDB doesn't rebuild the whole index. It just adds an entry to the existing index. It would be incredibly bad for performance if it had to rebuild the index on a large table after every INSERT.

Another misunderstanding is about the deferred index update. The index is still usable during this time. InnoDB knows how to check entries that are in the change buffer as well as the index. If a query reads a value from the change buffer, this causes it to be merged into the index immediately. If a query reads other values that are already merged into the index, the index helps that query.

You might be trying to solve a problem that does not exist.

As for the question you asked, how to tell if the index is fully merged, it's tricky to do this. You can query to see how much of the buffer pool is allocated to the change buffer (this is documented on this manual page: https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html)

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100))
       AS change_buffer_page_percentage;

+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                2064 |        8191 |                       25.1984 |
+---------------------+-------------+-------------------------------+

This shows after I inserted a couple of million rows into a test table, about 25% of my buffer pool is occupied by change buffer content waiting to be merged. But it does not tell me which table or index, so it could be changes for other table(s) that are accounting for this.

Over time, the change buffer pages will grow and shrink, as index changes are merged and other INSERT/UPDATE/DELETE traffic comes in.

Answer 2

The I/O activity after a large INSERT probably comes from the delayed "change buffer" activity.

This where updates to non-unique secondary indexes are being updated.

Think of it this way. When a row is updated/inserted/deleted,

• The top priority (after transaction stuff, etc) is to get the data into the table.
• Updates to secondary indexes need read-update-write to index BTree blocks, but, instead, a small record of what needs to be done is stored in a portion of the buffer pool called the "change buffer".
• As time permits (that is, in the background), these updates are sorted (they are probably kept in a 'priority queue' so they are both accessible and ordered), the read-update-write action taken.
• By batching the updates, fewer reads need to happen. (That is, read one 16KB index block; insert/delete several 'rows' before writing.)
• The resulting block is now just like any regular block, so it sits as a "dirty" block waiting to be flushed to disk.
• And even later, that index block will actually be written to the disk.

So, yes, a lot of I/O activity. But it exists to make the system more efficient and it leads to less I/O.

I think there are only two settings to control the change buffer:

• Percentage of buffer_pool -- 96% of users leave innodb_change_buffer_max_size at the default of "25".
• Which operations to handle -- 94% of users have innodb_change_buffering = all (the default)

There is no need to "wait" for it to finish; all of this is handled transparentlyc in the background.

Answer 3

Perform OPTIMIZE TABLE TBL_NAME on few interval as suggested in MySQL manual.

Once your data reaches a stable size, or a growing table has increased by tens or some hundreds of megabytes, consider using the OPTIMIZE TABLE statement to reorganize the table and compact any wasted space. The reorganized tables require less disk I/O to perform full table scans. This is a straightforward technique that can improve performance when other techniques such as improving index usage or tuning application code are not practical

Additionally, if the combination of item_id & creator_id are gone be unique then create a composite primary key.