A shared characteristic in most (if not all) databases, be them traditional relational databases like Oracle, MySQL, and PostgreSQL or some kind of NoSQL-style database like MongoDB, is the use of a caching mechanism to keep (a copy of) part of the data in memory.

The reasoning behind it is very simple: accessing data from memory remains many times faster than retrieving data from disk. And if a thousand users are trying to access a given bit of data simultaneously, having to fetch it from disk for each request would be extremely inefficient.

Note that the caching structure doesn’t have to be as big as your dataset to work well. In most cases, if it is big enough to store the “hot” part of the dataset, or the data most often accessed, that’s a win. Considering the example from above, even if that bit of data being requested by the users is not found in memory and needs to be retrieved from disk, that slow operation will have to be performed only once for that batch of requests.

So, how do you know if your hot data is in memory? How do you know if your MySQL database caching is operating efficiently?

By comparing the amount of data pages (the “bits of data”) being served directly from memory to the amount of data pages having to be fetched from disk: the more the database needs to read from disk to complete queries, the less efficient the caching structure is operating. Note that unoptimized queries contribute to this by going over/processing more data than necessary, loading them into the caching structure, and often pushing hot data out of it.

Finding that ratio is not always easy; ideally, the database should expose these metrics. MySQL does. Its main data caching structure for the standard InnoDB storage engine is called Buffer Pool. The two status variables (or status counters in this case) that expose the Buffer Pool efficiency are (quoting the MySQL manual):

  • Innodb_buffer_pool_read_requests: The number of logical read requests.
  • Innodb_buffer_pool_reads: The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

For years, MySQL DBAs have been tracking those metrics on monitoring tools or even capturing them manually during key moments. Two tools in the Percona Toolkit can help with that:

  • pt-stalk can be used to capture status variables (among various other important metrics for analyzing server performance). For example:

  • pt-mext can be used to plot a collection of status variables captured by pt-stalk in a way that highlights how the status variables changed over time for a collection of samples. For example:

then:

Roughly, it shows an average of 89% of pages read from the Buffer Pool – or 11% of pages being fetched from disk.

Is there an easy way to get that information? There is if you have Percona Monitoring and Management (PMM). In fact, there used to be a graph to display this on PMM v.1. It has since been removed from there on PMM2, so I created a feature request to bring it back: https://jira.percona.com/browse/PMM-11923

One thing that didn’t work well at the time was mixing read requests with write requests and then having a third data point for reads from disk, like follows:

Innodb_buffer_pool_reads is a percentage of Innodb_buffer_pool_read_requests. Thus, it is best represented by a bars-style graph, decoupled from write requests:

Should I aim for a 100% hit ratio?

The second reason we had that graph removed from PMM was to avoid passing the wrong impression: that you should be looking at increasing your Buffer Pool size if you were seeing any reads from disk showing up there with the aim of reaching a 100% hit ratio.

Remember, you want the Buffer Pool to be big enough to hold all your hot data – not pages that are loaded due to unoptimized queries. Those need to be traced and fixed, and PMM can help you do that as well.

Comparing the Reads from disk from above with the CPU Usage graph from the Node Summary dashboard, we can see a period when pages were being read from disk and iowait was extremely high:

MySQL Data Caching

We can then check the MySQL Handlers graph on the MySQL Instance Summary dashboard in search of possible index scans (read_next) and full-table scans (read_rnd_next),

and even the MySQL Temporary Objects graph on the same dashboard to search for on-disk temporary tables. But the place where we will find the queries we are looking for, the queries we need to improve, is in the Query Analytics (QAN) dashboard – a subject for another day!


I strongly believe there’s an important place for this graph on “data caching efficiency” for MySQL in PMM. It shouldn’t be looked at in an isolated way, and we shouldn’t be aiming for an all-green graph. Still, we can get important information about a server’s efficiency and workload by having a quick glance at this graph.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments