Native queries not updating without manual refresh on 0.41

Hi there!
When I open a saved question or dashboard, it is not automatically updated like it always had before version 0.41. E.g. one question says "updated 21 hours ago". It seems, question results are cached and do not update without manual refresh or switching off caching. Has anyone encountered this?

Hi @pavelsson
Can you post "Diagnostic Info" from Admin > Troubleshooting?
And what are your caching settings set to? Admin > Settings > Caching

1 Like

Diagnostic info is below. Caching was set to enabled, with 3 minimal time, 5 multiplier and 204800 KB. I disabled caching and graphs started loading, but I don't know if this is an optimal solution.

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.71 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.12+7",
    "java.vendor": "Eclipse Foundation",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.12",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.12+7",
    "os.name": "Linux",
    "os.version": "4.19.0-17-amd64",
    "user.language": "en",
    "user.timezone": "Europe/Berlin"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "googleanalytics",
      "bigquery"
    ],
    "hosting-env": "unknown",
    "application-database": "mysql",
    "application-database-details": {
      "database": {
        "name": "MariaDB",
        "version": "10.5.11-MariaDB-1:10.5.11+maria~focal-log"
      },
      "jdbc-driver": {
        "name": "MariaDB Connector/J",
        "version": "2.6.2"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-10-06",
      "tag": "v0.41.0",
      "branch": "release-x.41.x",
      "hash": "c529fe2"
    },
    "settings": {
      "report-timezone": "Europe/Berlin"
    }
  }
}

@pavelsson If you only had the multiplier set to 5, then it should maximum stored the questions for question_running_time * 5.
It's a little tricky to fully understand what's going on without logs helping to hint what is being loaded from the cache.

Ok I see. Maybe it's possible to identify the source of the problem? I have several hypotheses:

  • New caching rules added with 0.41
  • We added some highly complex questions and Metabase cache crashes when trying to load them
  • Our servers started exhausting their resources

Could some reasons be identified or excluded?

@pavelsson

  1. 100% might be the new caching in 0.41, since there were a lot of changes. I'm going to try to reproduce, but as you problem know, testing cache stuff takes time.
  2. Okay, that should definitely not happen. I'm not sure what is crashing. Do you have stacktrace logs?
  3. Which servers? The Metabase app, the Metabase application database, or your database sources?

Regarding the issue, I'm curious how the cache behaves in case an query in dashboard fails or times out? How long will it be cached then? We had the nginx proxy timeout set to 60 sec. We have a lot heavy queries in the dashboard, so if all caches expire during first load of the dashboard not all queries finished in 60 seconds. Also the caches seems to be in place for other queries, which fail because of missing fields in some older dashboard, which is also a bit strange.

As for load on the server, mysql server is basically 100% on all cores when the dashboard is loading, which is normal, given the amount and complexity of the queries, just caches make user experience much better.

@a.gelenberg If there's null results or an error, then results are not cached.
I would probably also recommend that you read this: https://www.metabase.com/learn/administration/making-dashboards-faster

Is there any way, to manually set cache_ttl? Looks like it being set very high in the responses like "cache-ttl": 90000 , 367200, 183600 (which amount to up to 4 days caching). A daily refresh would suffice, but longer than that is not so good.

@a.gelenberg Please post "Diagnostic Info" from Admin > Troubleshooting. I don't know which cache_ttl you are referring to, there are so many places, so you need to provide more details.

@a.gelenberg is from the same company as me - you can use the diagnostic info I posted above. Regarding your earlier question, we don't have stacktrace logs. I was referring to our own servers which are hosting Metabase.

I am playing with the caching parameters and monitoring the issue - will get back here if I have more questions. Thanks a lot so far for your help!

@pavelsson Okay, it would have been helpful to know who's taking part of the thread.

There's an issue open for this now:
https://github.com/metabase/metabase/issues/18458 - upvote by clicking :+1: on the first post

2 Likes

Thanks a lot @flamber for testing on your end and identifying the issue!