MariaDB Maxscale caching not working

Question

I have setup Maxscale (v6.2) and have connected to the Galera Cluster (3 nodes - MariaDB 10.5). I am trying to use the cache filter but it seems to not work. I have enabled general log for all the nodes and whenever I run the query I can see the queries are served from the nodes instead from the Maxscale cache.

Also I noticed that when I use mysqlslap with concurrency 10 I find that the general log file in each node shows 10 times connected and the actual query hit is 3. When I do similar operation using Haproxy then the general log shows 3 times connected and hit also 3. Not sure if there is anything to be setup properly for Maxscale.

Here is my maxscale.cnf

# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-25/

# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-25-mariadb-maxscale-configuration-guide/

[maxscale]
threads=auto
log_info=true

# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#

[server1]
type=server
address=1.1.1.1
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=1.1.1.2
port=3306
protocol=MariaDBBackend

[server3]
type=server
address=1.1.1.3
port=3306
protocol=MariaDBBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/maxscale-25-monitors/

[Galera-Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxscale
password=XXXXXXXX
monitor_interval=2000

#Galera router service
[Galera-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
password=XXXXXXXX
lazy_connect=true

#Galera cluster listener
[Galera-Listener]
type=listener
service=Galera-Service
protocol=MariaDBClient
address=0.0.0.0
port=3306

#cache
[Cache]
type=filter
module=cache
storage=storage_inmemory
soft_ttl=300s
hard_ttl=600s
cached_data=shared

Below image shows the maxscale log where the query is routed to the server instead of being served from cache.

Answer 1

Edit: You forgot to add filters=Cache to the service definition. This is why it looks like it's not working.

Filter by default apply only to the services that use them. To take them into use and combine them into a filter chain, add the filters parameter to the service and add the filters as the arugment to it, separated by the pipe character (|):

filter=FirstFilter|SecondFilter

---

The cache_data=thread_specific will allocate a separate cache for each worker thread in MaxScale. This means that the access to the cache is faster but it reduces the sharing that is done between the connections. On average you'll see as many requests as there are threads configured in MaxScale (in your case threads=auto means as many as there are CPU cores). If you want to further reduce the amount of queries that end up being sent to the database, you can use cache_data=shared.

The connection count is expected to be the same that it is without caching: the cache works only on the result level, not the connection level. To avoid connecting to the database when all results are served from the cache, use lazy_connect=true in the readwritesplit service. This will delay the connection creation until a request actually ends up being sent to the database.

There might still be some backend connections that will be created even if lazy_connect is enabled and all the requests are identical. This usually happens if a command that change the connection state is executed (e.g. SET NAMES utf8mb4). The cache doesn't store the result for these as they are not reads which means they are always retrieved from the database.