In 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:
https://mariadb.com/kb/en/mariadb/server-status-variables/#handler_icp_attempts
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.
https://mariadb.com/kb/en/mariadb/server-status-variables/#handler_icp_match
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | mysql [localhost] {msandbox} (test) > SELECT @@version, @@version_comment; +-----------------+-------------------+ | @@version | @@version_comment | +-----------------+-------------------+ | 10.1.20-MariaDB | MariaDB Server | +-----------------+-------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE t1G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `f1` int(11) DEFAULT NULL, `f2` int(11) DEFAULT NULL, `f3` int(11) DEFAULT NULL, KEY `idx_f1_f2` (`f1`,`f2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 3999996 | +----------+ 1 row in set (1.75 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM t1 LIMIT 12; +------+------+------+ | f1 | f2 | f3 | +------+------+------+ | 1 | 1 | 1 | | 1 | 2 | 1 | | 1 | 3 | 1 | | 1 | 4 | 1 | | 2 | 1 | 1 | | 2 | 2 | 1 | | 2 | 3 | 1 | | 2 | 4 | 1 | | 3 | 1 | 1 | | 3 | 2 | 1 | | 3 | 3 | 1 | | 3 | 4 | 1 | +------+------+------+ 12 rows in set (0.00 sec) |
It’s trivial, but it will work well for what we intend to show:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | mysql [localhost] {msandbox} (test) > FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3 and f2 < 4; +------+------+------+ | f1 | f2 | f3 | +------+------+------+ | 1 | 1 | 1 | | 1 | 2 | 1 | | 1 | 3 | 1 | | 2 | 1 | 1 | | 2 | 2 | 1 | | 2 | 3 | 1 | +------+------+------+ 6 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT'; +----------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +----------------------+----------------+ | HANDLER_ICP_ATTEMPTS | 8 | | HANDLER_ICP_MATCH | 6 | | ROWS_READ | 6 | | ROWS_SENT | 6 | +----------------------+----------------+ 4 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 AND f2 < 4; +------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+ | 1 | SIMPLE | t1 | range | idx_f1_f2 | idx_f1_f2 | 5 | NULL | 7 | Using index condition | +------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+ 1 row in set (0.01 sec) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | mysql [localhost] {msandbox} (test) > FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1; +------+------+------+ | f1 | f2 | f3 | +------+------+------+ | 1 | 1 | 1 | | 2 | 1 | 1 | +------+------+------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT'; +----------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +----------------------+----------------+ | HANDLER_ICP_ATTEMPTS | 8 | | HANDLER_ICP_MATCH | 2 | | ROWS_READ | 2 | | ROWS_SENT | 2 | +----------------------+----------------+ 4 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1; +------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+ | 1 | SIMPLE | t1 | range | idx_f1_f2 | idx_f1_f2 | 5 | NULL | 7 | Using index condition | +------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+ 1 row in set (0.00 sec) |
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):
1 2 | mysql [localhost] {msandbox} (test) > SET optimizer_switch='index_condition_pushdown=off'; Query OK, 0 rows affected (0.00 sec) |
Let’s run the queries again. For the first query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql [localhost] {msandbox} (test) > FLUSH STATUS; SELECT * FROM t1 WHERE f1 < 3 and f2 < 4; ... mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT'; +----------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +----------------------+----------------+ | HANDLER_ICP_ATTEMPTS | 0 | | HANDLER_ICP_MATCH | 0 | | ROWS_READ | 9 | | ROWS_SENT | 6 | +----------------------+----------------+ 4 rows in set (0.01 sec) mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 AND f2 < 4; +------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | t1 | range | idx_f1_f2 | idx_f1_f2 | 5 | NULL | 7 | Using where | +------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ 1 row in set (0.00 sec) |
As we can see by the handler counters, ICP is indeed not being used. Now the server is reading nine rows, as opposed to six when using ICP. Moreover, notice how we now see a Using where in the “Extra” column in the EXPLAIN output. This means that we are doing the filtering on the server layer; and we are using the first column of the composite index for the range scan (f1 < 3).
For the second query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql [localhost] {msandbox} (test) > FLUSH STATUS; SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1; ... mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT'; +----------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +----------------------+----------------+ | HANDLER_ICP_ATTEMPTS | 0 | | HANDLER_ICP_MATCH | 0 | | ROWS_READ | 9 | | ROWS_SENT | 2 | +----------------------+----------------+ 4 rows in set (0.01 sec) mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1; +------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | t1 | range | idx_f1_f2 | idx_f1_f2 | 5 | NULL | 7 | Using where | +------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ 1 row in set (0.00 sec) |
The server is also reading nine rows (because it’s also using only column f1 from the composite index, and we have the same condition on it), with the difference that it used to read only two while using ICP. We could say that this case was much better (and it was), but as with most of the time the answer to the bigger “how much better” question is “it depends”. As stated in the documentation, it has two factors:
- How many records are filtered out
- How expensive it is to read them
So it really depends on the size and kind of data set, and if it’s in memory or not. It’s better to benchmark the queries to have more information (like actual response times), but if it’s not possible we can get some valuable information by taking a look at these counters.
Lastly, I wanted to go back to the “attempts to match ratio” mentioned initially. As we saw in these examples, the first one had a 8:6 (or 4:3) ratio and the second a 8:2 (or 4:1) ratio, and the second one filtered more rows. Given this, I believe that the inverse will hold true: “The bigger the ratio of Handler_icp_attempts to Handler_icp_match, the better the filtering.”
Stay tuned for the next part, where we’ll see how to get this information from MySQL and Percona Server, too!
Hi Agustín,
Thanks for bringing attention to Index condition pushdown.
I would not say that the main advantage of Index Condition Pushdown (ICP) is that ‘the rows that were “saved” from being checked in the server layer’. The major benefit is that the storage engine may skip look-ups into the main table. In your example, a table look-up on primary key is needed to find the value of f3 since that column is not in the index. Using ICP, this look-up is avoided for rows where the condition on f2 is false.
Hi Øystein!
Indeed, I may have chosen incorrect wording while trying to be brief. Thanks! I’ll have it edited so it can point to your comment.