CODEWITHSUNDEEP
mysqlperformancedatabase-performance
Filipp
Filipp

Reputation: 929

How to get statistics on time MySQL spent updating indexes during a new row insertion

I'm trying to figure out how multiple indexes are actually affecting insertion performance for MySQL InnoDB tables.

Is it possible to get information about index update times using performance_schema?

It seems like there are no instruments for stages that may reflect such information.

Upvotes: 3

Views: 400

Answers (1)

Rick James
Rick James

Reputation: 142366

Even if there is something in performance_schema, it would be incomplete.

Non-UNIQUE secondary indexes are handled thus:

  1. An INSERT starts.
  2. Any UNIQUE indexes (including the PRIMARY KEY) are immediately checked for "dup key".
  3. Other index changes are put into the "change buffer".
  4. The INSERT returns to the client.

The Change Buffer is a portion of the buffer_pool (default: 25%) where such index modifications are held. Eventually, they will be batched up for updating the actual blocks of the index's BTree.

In a good situation, many index updates will be combined into very few read-modify-write steps to update a block. In a poor case, each index update requires a separate read and write.

The I/O for the change buffer is done 'in the background' as is the eventual write of any changes to data blocks. These cannot be realistically monitored in any way -- especially if there are different clients with different queries contributing to the same index or data blocks being updated.

Oh, meanwhile, any index lookups need to look both in the on-disk (or cached in buffer_pool) blocks and the change buffer. This makes an index lookup faster or slower, depending on various things unrelated to the operation in hand.

Upvotes: 2

Related Questions