MariaDB Maxscale caching not working



  • 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



  • 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 https://mariadb.com/kb/en/mariadb-maxscale-62-cache/#cached_data 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 https://mariadb.com/kb/en/mariadb-maxscale-62-mariadb-maxscale-configuration-guide/#threads 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 https://mariadb.com/kb/en/mariadb-maxscale-62-readwritesplit/#lazy_connect 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2