MariaDB Handler_icp_% Counters: What They Are, and How To Use Them

Handler_icp_% countersIn this post we’ll see how MariaDB’s Handler_icp_% counters status counters (Handler_icp_attempts and Handler_icp_matches) measure ICP-related work done by the server and storage engine layers, and how to see if our queries are getting any gains by using them.

These counters (as seen in SHOW STATUS output) are MariaDB-specific. In a later post, we will see how we can get this information in MySQL and Percona Server. This investigation spun off from comments in Michael’s post about the new MariaDB dashboard in PMM. Comments are very useful, so keep them coming! 🙂

We can start by checking the corresponding documentation pages:

Description: Number of times pushed index condition was checked. The smaller the ratio of Handler_icp_attempts to Handler_icp_match the better the filtering. See Index Condition Pushdown.

Description: Number of times pushed index condition was matched. The smaller the ratio of Handler_icp_attempts to Handler_icp_match the better the filtering. See See Index Condition Pushdown.

As we’ll see below, “attempts” counts the number of times the server layer sent a WHERE clause down to the storage engine layer to check if it can be filtered out. “Match”, on the other hand, counts whether an attempt ended up in the row being returned (i.e., if the pushed WHERE clause was a complete match).

Now that we understand what they measure, let’s check how to use them for reviewing our queries. Before moving forward with the examples, here are a couple of points to keey in mind:

  • Even if the attempt was not successful, it doesn’t mean that it is bad. However, a high (attempts – match) number is good in this context, since this is a measure of the rows that were “saved” from being checked in the server layer after getting the complete row from the storage engine. (This is explained more thoroughly below in Øystein Grøvlen’s comment – check it out!) On the other hand, a low number is not bad – it just means that most (or all) attempts ended up being a match.
  • From the documentation links above, it is stated that “the smaller the ratio between attempts to match, the better the filtering.”, which I believe is the contrary.

Back to our examples, then. First, let’s review version, table structure and data set.

It’s trivial, but it will work well for what we intend to show:

In this scenario, the server sent a request to the storage engine to check on eight rows, from which six were a complete match. This is the case where a low attempts - match number is seen. The server scanned the index on the f1 column to decide which rows needed a “request for further check”, then the storage engine checked the WHERE condition on the f2 column with the pushed down (f2 < 4) clause.

Now let’s change the condition on f2:

In this scenario, the server also sent a request for eight rows, of which only two ended up being a match, due to the changed condition on f2. This is the case where a “high” attempts - match number is seen.

Great, we understand how to see the amount of rows sent between the server and storage engine layers. Now let’s move forward with the “how can I make sense of these numbers?” part. We can use the other counters included in the outputs that haven’t been mentioned yet (ROWS_READ and ROWS_SENT) and compare them when running the same queries with ICP disabled (which can be conveniently done with a simple SET):

Let’s run the queries again. For the first query: