Hacker News Reader

Comment by kscaldef | Hacker News Reader

I have to admit that I'm still a little puzzled here. Why does MySQL need to retrieve the actual rows from disk at all? Since the query is just returning count(*), can't it be answered entirely from the index? Does the index not reflect deletions or something like that?

View on HN

That's a good question, and one I'm not entirely sure about. My guess is that the index itself is also scattered across the disk, since it gets added to at the same time as the rows get added. So in the real query, where we are reading actual data, and not just counting the number of rows, there are actually two seek costs --- seeking to finding the correct part of the index to read, and then seeking to find the row it points to.

When you insert in sorted order into a b-tree (as you do with timestamps), later inserts will be more likely to be all in one block, and also more likely to be near the top of the tree.

Since b-trees have the guarantee that no root-to-leaf path is more than a constant factor longer than any other, you are probably hitting the very edge of that constant, and since disk seeks are the designated hitter of the performance team, you're getting hit pretty hard.

So, effectively, even your index is growing fragmented the more you use it.

To verify the fact that you're not hitting the data file though, maybe you could strace it and watch for reads on the index and the data file.

Typically, one expects a b-tree lookup to require at most 1 disk seek (for the leaf-nodes), and ideally your indices are fully in memory. So, color me skeptical that fragmentation of the index itself is to blame here.

Inserting in sorted order is not a typical case, and I strongly doubt your "at most 1 disk seek" claim but my memory is fuzzy so you may be correct.

You're definitely right that the cache should be hot though, so I am now also a skeptical hue.

One of the primary virtues of b-trees is that they make efficient use of disk. Even if the full data structure is too large to fit in memory, because of the large branching factor you can usually fit all the interior nodes in memory. So, in a lookup, only the final access of the leaf node requires a disk access. Of course, traversal may require additional seeks and reads.

Reply on news.ycombinator.com