Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-1887

Using cache causes slow read from mysql server

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.6
    • Fix Version/s: 2.2.10
    • Component/s: cache
    • Labels:
      None
    • Environment:
      mysql server: 5.7.17-11-log
    • Sprint:
      MXS-SPRINT-60

      Description

      I'm testing MaxScale for caching selects from mysql server. I've created simple application that periodically reads data from a huge test table with 2M rows and 512MB size using query

      SELECT /*TestCache*/ db_load_test.* FROM db_load_test
      

      The application directly connected to mysql server reads the entire table in 4 seconds.
      The application connected to MaxScale server reads the same data in 5-7 seconds.
      I configured caching as follows:

      [TestCache]
      type=filter
      module=cache
      soft_ttl=240
      hard_ttl=240
      selects=assume_cacheable
      rules=/etc/maxscale.cnf.d/TestCache.json
      debug=31
      [TestService]
      filters=TestCache
      

      TestCache.json:

      {
          "store": [
              {
                  "attribute": "query",
                  "op": "like",
                  "value": "/\\*TestCache\\*/"
              }
          ]
      }
      

      The application reads data from the cache pretty quickly: 1-2 seconds. But invalidating cache leads to very slow read from mysql backend server: about 2 minutes! It is actually performing read operation as I see this query in mysql process list.

      MaxScale> show filters
      Filter 0x1513910 (TestCache)
              Module:      cache
      {
        "rules": {
          "store": [
            {
              "attribute": "query",
              "op": "like",
              "value": "/\\*TestCache\\*/"
            }
          ]
        },
        "storage": {
          "lru": {
            "size": 502529185,
            "items": 5,
            "hits": 154,
            "misses": 6,
            "updates": 0,
            "deletes": 0,
            "evictions": 0
          },
          "real_storage": {
            "size": 502529185,
            "items": 5,
            "hits": 159,
            "misses": 0,
            "updates": 0,
            "deletes": 0
          }
        }
      }
      

        Attachments

          Activity

            People

            Assignee:
            johan.wikman Johan Wikman
            Reporter:
            konst Konstantin Pavelko
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.