Terminate queries on page refresh

Get Help
1

Hello, this is Heitor from Collact.

Metabase is our main dashboard and reporting solution and, as such, it's quite busy depending on the time and day. Today one user was searching through a lot of old questions and dashboards to find some pieces of information and his (fast) navigation, as you can imagine, resulted in hundreds of queries being executed in the database. Some of those were very slow resulting in degraded overall application performance for everyone.

For that reason we were wondering if there is any way to cancel a query on page refresh, maybe by detecting closed TCP connections. After all, if the user has left the page for any reason (refresh or not) then the query result is useless anyway.

I searched discourse and Github and found some information, but nothing that could precisely answer this point.

Thank you very much once again.
Heitor

1 Like
2

Same problem here!

3

Hi @heits
Metabase tries to cancel queries, when the user leave the question/dashboard. But this depends on the database type and several other factors.

Post "Diagnostic Info" from Admin > Troubleshooting.
And the logs showing the buildup.

Also worth reading: https://www.metabase.com/learn/administration/metabase-at-scale

4

Hey @flamber !
Here goes the Diagnostic

{
  "browser-info": {
    "language": "en-US",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.102 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "Java(TM) SE Runtime Environment",
    "java.runtime.version": "1.8.0_191-b12",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_191",
    "java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
    "java.vm.version": "25.191-b12",
    "os.name": "Linux",
    "os.version": "4.4.0-1111-aws",
    "user.language": "en",
    "user.timezone": "Etc/UTC"
  },
  "metabase-info": {
    "databases": [
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "12.7"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.18"
      }
    },
    "run-mode": "prod",
    "version": {
      "tag": "v0.40.7",
      "date": "2021-12-10",
      "branch": "release-x.40.x",
      "hash": "07fdc71"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

I don't know what "buildup logs" you refer to.

I checked the "metabase at scale" article some time ago but I couldn't find anything related to terminating those types of queries, where the user has already navigated away.

Thank you
Heitor

5

@heits I'm referring to all logs. Metabase should send a cancel query request, when browser connection is gone.

You should upgrade to a newer release: https://github.com/metabase/metabase/releases

And upgrade to Java 11: https://www.metabase.com/docs/latest/operations-guide/java-versions.html

6

Aah, ok! Server is quite busy and cancelling generates lots of stack traces and logs so I cannot attach it here.

However, I found that it is actually able to cancel queries on browser close. Out of curiosity: how can the server detect closed connections? I assumed that it was the problem because I have had problems with Tomcat detecting closed TCP connections in the past and Java not being able to tell a Socket was closed until trying to read from or write to it.

Funny that it didn't seem to be working yesterday night. Maybe users were opening several simultaneous tabs. Is there a way to configure query timeout and/or concurrent query limit per user? Or, even better, per role/permission?

I will try to upgrade to openjdk 11 and new Metabase today.

Thanks for your agility!
Heitor

7

@heits There's a connection open between the browser and Jetty (the internal webserver), so if that connection is dropped (browser away, closing browser, network problems, reverse-proxy terminating, etc), then the middleware layer, which keeps taps on Jetty and database connections, will then try to clean up.

Since database connections are per-instance, then any timeouts you add in Admin > Databases > (db) > JDBC connection string, will apply to everyone.
Try something like -c statement_timeout=20s or -c statement_timeout=1m

You might be interested in this:
https://github.com/metabase/metabase/issues/6004 - upvote by clicking :+1: on the first post

8

I will enforce a application-wide connection timeout but it would be great to see that by user. I just upvoted the issue you linked.

Thanks for all the explanation!
Heitor

9

@heits That's a lot trickier than it sounds and would either require that Metabase handles timeout, or would require separate connections for each user. Both would open up new possibilities besides what you're looking for, but my brain is slightly hurting thinking of the complexity right now (though I'm busy with many other things right now).

10

I can imagine. We have an API that runs queries and each query has a configurable timeout in milliseconds which can be passed to JDBC on execution. As the parameter is provided per execution you could store it per user or per role, i.e, leaving modelling the source of the timeout to the Metabase application. Just throwing some ideas that may help :slight_smile: