Delay in query submission to Presto

Get Help
1

Hi
We are using Metabase on Presto and our version is 46.6.4.

We have been using Metabase and Presto for 3 years and recently our query responses are very slow and often fail to return result. When we monitor presto logs, we figured that there is a lag of 8-9minutes between submitting queries from Metabase and presto accepts it.

I would like to understand is there a server log or something that show or tracks query status, where query hangs or for what its waiting on.

We are planning to upgrade to latest version but not sure whether it will help or how it will help in resolving this issue.

Here is Diagnostic Info

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.20+8",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.20",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.20+8",
"os.name": "Linux",
"os.version": "5.15.120+",
"user.language": "en",
"user.timezone": "UTC"
},
"metabase-info": {
"databases": [
"presto-jdbc",
"postgres",
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.28-log"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.7.6"
}
},
"run-mode": "prod",
"version": {
"date": "2023-07-28",
"tag": "v0.46.6.4",
"branch": "release-x.46.6.x",
"hash": "7c60aca"
},
"settings": {
"report-timezone": null
}
}
}

2

Can you move to an updated version (48.4). You also need to upgrade the MySQL DB to v8

3

Thank you @Luiggi.
Sure will upgrade.
Are there any checks that can help before we upgrade versions?

4

@Luiggi,
We configured 3 databases in Metabase and primarily use 2 of them. During peak times, there might be around 200 to 300 queries running.
Recently we noticed that many of these queries are failing and I noticed these messages in my logs:

2024-02-07 06:22:57,041 DEBUG middleware.log :: e[32mPOST /api/dashboard/6227/dashcard/68398/card/84089/query 202 [ASYNC: canceled] 6.5 mins (12 DB calls) App DB connections: 15/15 Jetty threads: 10/50 (16 idle, 0 queued) (568 total active threads) Queries in flight: 50 (137 queued)e[0m"
timestamp: "2024-02-07T06:22:57.041637844Z

2024-02-05 09:28:37,356 DEBUG middleware.log :: e[32mGET /api/field/5811/remapping/5823 200 11.3 s (12 DB calls) App DB connections: 0/15 Jetty threads: 50/50 (0 idle, 102 queued) (189 total active threads) Queries in flight: 6 (0 queued); presto-jdbc DB 3 connections: 15/15 (1 threads blocked)

  1. Can you explain what these stats mean in the logs
  2. How these numbers impact when query rate is high
  3. Where can I actually see queued queries? I don't see them in Query Execution or Query tables.
  4. What configurational changes should I do?
    12 DB calls
    App DB connections: 15/15
    Jetty threads: 10/50 (16 idle, 0 queued) (568 total active threads)
    Queries in flight: 50 (137 queued)

Thanks
Rao.