5

I have some confusion about how the Innodb buffer pool works.

Let's say I have table with 15 records, if I execute: select ... between 1 to 10 then that result will cached in buffer(?). My question is: if i execute select .... between 1 to 15 next time, then how it will fetch the records. Is it select 10 records from the buffer and 5 records from disk?

5

Your question, "How MySQL select request works internally" is a very broad topic. However, I can more or less give a not-completely-accurate, but simple overview to "what happens when you select records 1 to 10 and then, sequentially, 1-15" regarding caching.

First thing you must understand is that, functionally, MySQL can be divided into 2 separate layer, the "frontend" or SQL layer, and the ENGINE layer, which communicate just by a low-level (remember I am simplifying things) row-based api. You can have InnoDB or other pluggable engines to store rows (let's focus on only that one). Caching can happen at SQL or Engine level:

SQL Layer

At SQL layer there are several caching mechanisms, let's focus on query results caching, known as Query Cache. It allows to allocate certain amount of space in memory to return query results without going to the engine. It is a very high level method of caching, has to be consistent with the actual data stored, and its implementation is very simplistic, which has lead to many practical problems:

  • It requires a global lock for writes, which kills all parallelism and creates high contention under medium to high load, even for low number of writes
  • It can slow down misses
  • Evictions can also be harmful, and all results are invalidated as soon as a table is written, even if results would not be affected
  • If the same results are returned, but the query is slightly different, those are considered as 2 different queries, storing the same queries twice
  • It does not support partitioned tables, and clustering solutions such as Galera do not support it
  • Not all queries are cachable (e.g. undeterministic queries), and there will be a finite number of space for them

In your case, having executed 2 different queries, the query cache would be useless, and you would have 2 misses, following by 2 separate inserts of the results (assuming they are cachable, etc.)

Many people recommend disabling the query cache and using the extra memory for other buffers. The exact impact would depend on your particular workload (you should monitor hits/misses/evictions and query latency). If you still need query caching, you can opt to have something like memcache, closer to the application layer. And even MySQL integrates a plugin to run memcache as a frontend for InnoDB, as part of the server.

Separating the caching, you can control yourself the TTL of the cached results. There are many other mechanisms that can handle that, such as ProxySQL or implementing your own on the application.

InnoDB

InnoDB implements it's own complex caching system for faster row retrieval inside the buffer pool (its page cache), a much lower-level cache. In general, InnoDB tries to avoid (overpass) the filesystem cache and implement its own row cache system. The default page size is 16KB (although that can be changed) and it has the same structure on disk than in memory. A page should be large enough to store 2 data rows or more (a row can be larger than 8K, for variable-size fields, but that is another story). When a page is accessed on disk, it gets copied to the buffer pool. In fact, due to some optimizations, in many cases, not only a single page gets read, but multiple contiguous ones to reduce IO operations.

So when you read the first 10 rows on an empty buffer pool, maybe only those pages are are loaded into memory with the rows you want and probably some more; and in many cases, the full extend and the next one. All of this is subject to tuning and depends on the kind of access patterns you have.

When you read again, you will probably find the same rows already in memory, and thus you have reduced the disk I/O. If you are missing some pages, it will require a (or several) disk IO operation. This is normally seen when you run the same query twice and get a faster response. Although row caching is only a part of the speed up- you also have thread caching, table caching, dictionary caching, index caching ...(at both SQL and engine level).

However, it can happen that by the time you have done the second query, the rows have been evicted- the eviction follow a relatively complex algorithm and it is also highly tunable, but it is -simplifying it- a LRU (Least recently used) implementation, which means that if the other, most recently accessed data gets into memory, it can evict rarely-used data.

Here you have a step-by-step summary of how InnoDB buffer pool works:

Other engines can do things differently, for example, MyISAM and TokuDB have both index caching, but they both relay more heavily on the filesystem cache for data.

  • I got what you are telling, now my doubt is how query will know those 10 rows already exist in buffer, will it maintain any dictionary? – Sudeep shetty Dec 30 '16 at 11:52
  • Not sure exactly what you mean- pages are organized in B+trees and linked to each other and have a space id and offset (identifiers). InnoDB also has its own data dictionary (databases, tables, etc) and several lists controling what is on the buffer pool (LRU). – jynus Dec 30 '16 at 15:34
  • You can see an overview of innodb internals at: percona.com/live/mysql-conference-2013/sessions/… – jynus Dec 30 '16 at 15:44
0

Short answer... Only 1 disk read for both queries. (Not 2, not 15.)

Medium answer...

Probably your simple example involved some disk hits to initially "open" the table, then one disk hit to read the first "block", which will have the first several rows.

InnoDB caches all data and indexes through its buffer_pool. When you fetched rows 1..10, they may (or may not) have all been in one block, necessitating 1 disk hit. And that block(s) may (or may not) have included 11..15 -- no more disk hits.

A simple Rule of Thumb is that 100 'rows' are fetched when you fetch one block. A block is always 16KB, so the actual number of rows depends on size of the rows, and (it may seem) the phase of the moon.

  • Nobody uses blocks in InnoDB context, we use page. It is not always 16K, it can vary in size (32K to 4K), and 100 rows per page can be very off- it can contain 200, or just one row. Also, I would expect whole extents to be fetched at the same time (around 1MB in size) + read ahead to kick in, unless optimized for fast random access. – jynus Dec 30 '16 at 15:56

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.