BQ queries run much slower after 0.35 update

Running metabase on top of BigQuery,

Queries that worked on 0.34.4 and in the native BQ editor taking ~50 seconds take several minutes after the new update. It takes several minutes to output all ~1200 rows of the query, limiting it to a few rows seems to fix the slowdown issue but 0.34.4 had no issue spitting out a couple thousand rows.

Any known issues tied to this that I am missing?

Hi @brendan
Please post “Diagnostic Info” from Admin > Troubleshooting.
There was no release version 0.34.4
https://github.com/metabase/metabase/releases
When you check the log (Admin > Troubleshooting > Logs), what’s the query time - it’s the number right after [ASYNC: completed]

Sorry I meant 0.34.3. Diagnostic info:
{
“browser-info”: {
“language”: “en-US”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “1.8.0_252-heroku-b09”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_252-heroku”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “25.252-b09”,
“os.name”: “Linux”,
“os.version”: “4.4.0-1066-aws”,
“user.language”: “en”,
“user.timezone”: “Etc/UTC”
},
“metabase-info”: {
“databases”: [
“bigquery”
],
“hosting-env”: “heroku”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “11.7 (Ubuntu 11.7-2.pgdg16.04+1)”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.35.4”,
“date”: “2020-05-28”,
“branch”: “release-0.35.x”,
“hash”: “b3080fa”
},
“settings”: {
“report-timezone”: “UTC”
}
}
}

As I write this the query is taking over 11 minutes (according to the browser), when the same query in BQ takes 27 seconds. It is difficult to tell how long it takes in the logs because there are so many users at my org querying simultaneously + it takes so long that there is nothing being outputted to the logs.
Brendan

@brendan Okay, I haven’t seen this and it hasn’t been reported by others, but I’m guessing you’re probably hitting the limit of available connections to either the application database or your datasource, or perhaps webserver threads.
But without logs, it’s close to impossible to help. I’m sure Heroku has some interface, where you can see logs of the application.

Thanks @flamber. We dug into our logs a bit and it looks like it shows a cancelled ASYNC a few times until it eventually succeeds. So it runs about 30 seconds in the logs, fails and keeps trying until it succeeds.

Jun 09 14:21:48 cambly-metabase app/web.1: 06-09 21:21:48 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: canceled] 32.6 s (11 DB calls) App DB connections: 0/15 Jetty threads: 4/50 (1 idle, 0 queued) (106 total active threads) Queries in flight: 1 (0 queued)

@brendan That was helpful. Pretty sure you’re hitting the limit on Heroku, where they close the connection after 30 seconds.
https://github.com/metabase/metabase/issues/11463

1 Like