0

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.enter image description here

2
  • 1
    Have you confirmed that you need that extra level of caching? Or is this "premature optimization"?
    – Rick James
    Jan 17, 2022 at 19:54
  • @RickJames Yes I need the caching.
    – Suraj
    Jan 18, 2022 at 5:39

1 Answer 1

2

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.

5
  • I tried with the suggested configuration changes but still there is not much change. When I am using 100 concurrency I can see 50 connections in each of 2 nodes and 1 node is not receiving any. Also I assume that if it takes for example 7 secs for 100 concurrency then subsequent queries should take much less as they are cached but I do not see that. Is there are anything I am missing.
    – Suraj
    Jan 30, 2022 at 12:43
  • You might want to add log_info=true under the [maxscale] section in maxscale.cnf to see how the query routing is done. You should be able to see when caching is used and when it isn't. Note that only queries that are identical can be cached: if you use something like sysbench which generates unique queries, the caching rate will be slower.
    – markusjm
    Jan 31, 2022 at 13:07
  • I enabled the log and have also attached the log image in the question.
    – Suraj
    Jan 31, 2022 at 16:01
  • I think I realized why it wasn't working: you didn't add filters=Cache to the service definition. I've updated the answer with the real reason why it works.
    – markusjm
    Jan 31, 2022 at 17:22
  • thank you very much. I had missed to add the filter. Now it is working as expected.
    – Suraj
    Feb 1, 2022 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.