ProxySQL Query Cache doesn't always respect the query rules for some reason

Question

I use ProxySQL (2.0.17) to cache all SELECT queries sent to MySQL. The mysql_query_rules table looks like this:

+---------+--------+----------+------------+--------+-------------+------------+------------+--------+------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest                 | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 1       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^[(]?SELECT (?!SQL_NO_CACHE) | NULL          | 0                    | CASELESS     | NULL    | NULL            | NULL                  | 300000    | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+

One simple rule (I tried ^SELECT .* as well) and 300 seconds to wait until a cached query is purged.

For some reason, 5% of each query to be cached are still sent to the backend. For instance, this one is the most popular query:

+-----------+------------+----------+----------------+--------------------+--------------------------+------------+------------+------------+-------------+----------+----------+-------------------+---------------+
| hostgroup | schemaname | username | client_address | digest             | digest_text              | count_star | first_seen | last_seen  | sum_time    | min_time | max_time | sum_rows_affected | sum_rows_sent |
+-----------+------------+----------+----------------+--------------------+--------------------------+------------+------------+------------+-------------+----------+----------+-------------------+---------------+
| 2         | ------     | ----     |                | 0xFB50749BCFE0DA3C | SELECT * FROM `language` | 12839      | 1621445210 | 1621455115 | 45069293213 | 31321    | 82235606 | 0                 | 56960         |
| -1        | ------     | ----     |                | 0xFB50749BCFE0DA3C | SELECT * FROM `language` | 326243     | 1621445210 | 1621455116 | 0           | 0        | 0        | 0                 | 0             |
+-----------+------------+----------+----------------+--------------------+--------------------------+------------+------------+------------+-------------+----------+----------+-------------------+---------------+

I can't get my head around this peculiarity. Whenever I update stats_mysql_query_digest, count_star on hostgroup 2 (backend) gets incremented without waiting 300 seconds for the query to be purged.

The query cache size is set to 512 Mb. At its peak, it takes up around 100 Mb.

Help?..

Answer 1

Cranking mysql-query_cache_size_MB up to 5120 MB (which is ridiculous, of course) seems to have resolved the problem to some extent. The frequency of backend requests for that query has dropped by 10 times (thanks to ProxySQL's Query Logging you can log just one query and analyze it). The cache_ttl value is still somewhat far from being respected but I guess this workaround is better than nothing at this point.

Answer 2

The issue is more likely that the result set of the query is large and exceeds mysql-threshold_resultset_size which is 4MB by default. I had to set this to 32MB before I saw 100% cache hit rate on a query with large results.

https://proxysql.com/documentation/query-cache/

Because mysql-threshold_resultset_size defines the maximum resultset size that can be buffered, it also defines the maximum resultset size that can be stored in Query Cache.

Answer 3

I learned Prepare Statements cache a differnt way if u see the amount of frontend request vs the backend u will see it cached https://proxysql.com/documentation/prepared-statements/

ScreenShot