Fun MySQL fact of the day: change buffering

Yesterday, we considered the consequences of random-order inserts on a PRIMARY index. Unfortunately, this same problem exists in secondary indices, and usually to an even greater effect since the data are rarely ordered at insertion time.

For example, let's assume a secondary index of (user_id,thing_id,created,id). In this index, all "things" for a specific "user" are logically ordered in order of a user_id, then by a user's thing_id, then the date it was created, and then, as you recall, a secondary index always ends with the primary key linking back to the associated record in the PRIMARY index. In a busy system, unless we're bulk-loading data, the likelihood of inserts being ordered by user_id is slim at best if not completely coincidental.

For the sake of argument, let's say this index record consists of 4 8byte fields for a total of 32bytes. Ignoring some details, this would allow us to store something like 512 records per page. Now, if each user_id had 10,000 thing_ids, the index records would be spread across no less than 20 pages in 1 single extent, which can be read all in 1 read operation. However, due to unordered inserts causing page splitting, it's more likely to be 40 half-filled pages across 40 extents, needing in the order of 40 reads. Not only is that 40x more reads, that's probably an average case, not even a worst case (you'd have to measure to be sure).

Thankfully, InnoDB has got your back in a feature called the "change buffer", which, for non-unique secondary indices, "buffers" inserts, updates, and deletes until they are read on demand, at which point the buffered page(s) are merged into the pages as they're read from disk. This means a few things, but at the top of the list is that reading (including both DML and queries) from a non-unique secondary index on your master database is a sure-fire way to defeat most of the gains you get from change buffering. This would lead to more page fragmentation, which would lead to more IO, which eventually leads to lower performance.

So, keep your reads on your replicas and only ever use your master for operations that use either the PRIMARY index or unique secondary indices.