Query timeout issue

[howinator]

Metabase version: 0.35.4
Athena Driver version: v1.0.0

We have an Athena database which isn't optimized, so queries to it often time out after 5 minutes with the following message on the front end:

I checked in the Athena console and these queries are timing out at ~298s (~5 minutes).

In the metabase logs I see the following:

Caused by: com.simba.athena.dsi.exceptions.OperationCanceledException: [Simba][AthenaJDBC](100081) Query execution got cancelled for this query

I've tried setting the MB_DB_CONNECTION_TIMEOUT_MS env var to 1200000 (20 minutes), but it had no effect. A cursory google search showed that this was something that came up with the Druid driver, and it was changed.

If I'm not missing an existing configuration parameter, would it be possible to get something similar for this Athena driver? Thanks so much in advance!

[dacort]

Hi @howinator, thanks for filing the issue. Timeouts are always so much fun. :)

I believe Athena's default timeout is 30 minutes.
MB_DB_CONNECTION_TIMEOUT_MS is for the initial DB connection tests, so won't impact a running query.

Then there's also frontend timeouts, which this may be (see metabase/metabase#12423). Where are you running Metabase and is there a load balancer in front of it?

[howinator]

Hey @dacort thanks for the response. There is a LB in front of it, but I configured it to have a longer timeout to no effect. I'm not familiar with the architecture of Metabase, but should I expect a OperationCanceledException in the logs if it's a front-end timeout?

[orenmazor]

some of my users started hitting this as well. I'm running a default elastic beanstalk setup right now while we're figuring out if this is the right solution. I'd love to be able to bump the timeout to 30m

I get that this will result on hitting that EB instance heavier, and I'm very ok with that. thats a moneyscale issue in my books.

[dacort]

Thanks @howinator and @orenmazor - I'll try to dig into this this week and figure out the right solution.

[orenmazor]

I’d love to help but my java will only hinder your efforts. Happy to help test tho

…

On Mon, Jun 29, 2020 at 9:27 PM, Damon P. Cortesi ***@***.***> wrote: Thanks ***@***.***(https://github.com/howinator) and ***@***.***(https://github.com/orenmazor) - I'll try to dig into this this week and figure out the right solution. — You are receiving this because you were mentioned. Reply to this email directly, [view it on GitHub](#46 (comment)), or [unsubscribe](https://github.com/notifications/unsubscribe-auth/AAC52FYMTN3CDV2HHAC5WFTRZE5RXANCNFSM4OIXN7VA).

[dacort]

I'm also running beanstalk and tried changing the "idle timeout" setting on the load balancer to 600 seconds and that seemed to work, although I didn't test it with the Athena driver directly but another long-running query.

I should note that if you haven't, you should also consider enabling caching on the Metabase side so these long-running queries will be cached for some period.

[orenmazor]

@dacort I've enabled caching. I'm also playing around with actually pulling those cached queries somehow into being visible in the main metabase landing page. kind of like "here are the last 20 queries people ran". but that's more of a metabase discussion and less of the athena driver :)

[orenmazor]

I set the idle timeout on the elb to a higher amount and still seeing the same timeout issue, btw.

[orenmazor]

so this has cropped up further on my priority list and now i have to dig at this.

I started looking at logs, and one thing I noticed is that athena is showing queries getting cancelled at 60s, and the logs are showing the same error as above. so it looks like an active kill :(

[orenmazor]

update! figured it out.

the issue isn't the ELB. it's nginx as the proxy on the EB box. you need to adjust the timeout settings with whichever way you prefer to manage your host config.

I adjusted my elasticbeanstalk folder to just add these to the http config:

        client_header_timeout   1800;
        client_body_timeout     1800;
        send_timeout            1800;
        proxy_connect_timeout   1800;
        proxy_read_timeout      1800;
        proxy_send_timeout      1800;

[dacort]

Ah thanks @orenmazor! The default Metabase EB config has the timeouts set at 600s as an FYI.

[dacort]

Going to close this one as I believe it's due to load balancer configuration/timeouts.

@howinator Feel free to reopen if you feel otherwise!

[jensenity]

I'm running metabase on k8s with nginx, does this mean I have to configure a longer nginx client timeout to avoid this error?