1

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.

16
  • Is this related to the default innodb_ibuf_max_size?
    – danblack
    Aug 2 at 1:08
  • @danblack sorry for the previous comment. I meant innodb_ibuf_accel_rate. Anyhow, disabling change buffer will reduce the speed of large INSERTs. It is good to have the change buffer, but I want to flush it right after the query.
    – Googlebot
    Aug 2 at 1:14
  • @danblack I believe innodb_ibuf_max_size is now innodb_change_buffer_max_size, as InnoDB changed the terminology, though Ibuf is still used in STATUS.
    – Googlebot
    Aug 2 at 1:18
  • Since the change buffer is probably dirty pages in the buffer pool. Does temporary dropping the global innodb_max_dirty_pages_pct force the flushing?
    – danblack
    Aug 2 at 1:19
  • 1
    In certain types of loads (I don't know which), secondary indexes are built (not rebuilt) after loading the data.
    – Rick James
    Aug 2 at 3:26

3 Answers 3

3

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.

8
  • The first misunderstanding, as discussed in the comments, was mere terminology. I meant update rather than rebuild entirely. The second misunderstanding is based on my experience. If I run the second query right after the first one, it would be much slower than executing it a few hours later. Please note that we are talking about an INSERT whose data is several times bigger than the change buffer size. After completing the query, InnoDB reads data from the disk into the change buffer to update/merge the secondary index. The problem might not be the way I expressed but there is something.
    – Googlebot
    Aug 2 at 20:55
  • Really? The default max change buffer size is 25% of your buffer pool. You INSERT data that is several times larger than this? Aug 3 at 18:13
  • I have change buffer size of 25GB (innodb_buffer_pool_size=50G, innodb_change_buffer_max_size=50). The size of ibd file for that simple table is 147GB for 1.5 billion rows. It's not a web server or something like that. I use MySQL for big data processing.
    – Googlebot
    Aug 3 at 19:07
  • But the idea is that the change buffer is a temporary holding space for a subset of index entries from recently inserted rows. Gradually the entries in the change buffer are merged into the tablespace. The change buffer does not have to hold all your indexes at all times. Imagine a huge warehouse, with a small table at the front where today's deliveries are dropped off. Warehouse workers move those items from the small table into their proper place in the warehouse, but the small table lets deliveries get done quickly. Aug 3 at 21:40
  • The question is: does InnoDB create dirty pages row by row? Or does it create the pages for the main data and, when the query is completed, start working on the secondary indexes (similar to MyISAM behavior for INSERTing into an empty table)? My experience suggests the latter. Otherwise, we should not have any I/O read activity after a large INSERT query.
    – Googlebot
    Aug 3 at 22:18
1

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.

3
  • Your answer is mostly in response to the comments rather than the original question. I have no problem with a lot of I/O activities for creating/updating the secondary index. My question is: How should check if the secondary index has been fully updated so that I can use it in the next query? If run the second query right after the first one, it would be slow, but if I wait for a while, it will be much faster since the required index for the JOIN is ready. The question is: how long should I wait?
    – Googlebot
    Aug 2 at 3:39
  • @Googlebot - As far as you can tell (without dumping the disk), the indexes are always up to date (within your current transaction). I added more.
    – Rick James
    Aug 2 at 3:49
  • When the secondary index is still being updated in the background, how can you use it? For small tables, it is not noticeable since the pages are still in the buffer, but for large tables, a majority of the dirty pages have already been flushed to the disk, and InnoDB needs to read them from the disk.
    – Googlebot
    Aug 2 at 3:54
0

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.

2
  • I disagree with the reference manual... Unless you recently did a big DELETE, using OPTIMIZE TABLE is mostly a waste of time on InnoDB tables.
    – Rick James
    Aug 2 at 17:55
  • Yeah it depends on individual experience Aug 3 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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