What does the "Looker is having trouble connecting to your database" error mean?

[jonathon1]

Knowledge Drop

Last tested: Nov 18, 2020
 

Looker's connection pool timeout is the only known cause of this error.

Why did this happen?

Basically, the following scenario happened:

1. Looker tried to send a new query to a database. This query could have been triggered by a dashboard, Look, explore, SQL Runner, etc.
2. Looker noticed that it had already sent a number of unreturned queries to the database greater than or equal to the "Max Connections" setting in the Connections page.
3. Looker queued the query to give the database a chance to complete the already running queries. - It's really important that Looker does this. Looker protects our databases and warehouses, so that the they don't get overloaded with requests. This ensures that the specific database's or warehouse's resources are shared amongst all of us, without having the need to micro manage them. "Very cool. Thanks Looker!"
4. After the amount of time specified in the "Connection Pool Timeout" setting in the Connections page, Looker cancelled the queued query.

How do I fix it?

It's recommended to check the concurrency limits (pools and threads) of your database or warehouse. After which, consider the following options:

• Increasing "Max Connections" on the Connection page in Looker's UI. This way, Looker will send more queries to the database before queueing queries in the connection pool.
• Increasing the "Connection Pool Timeout" on the Connection page in Looker's UI. This way, Looker will wait longer before running the query in database.
• Increasing the database's concurrency limit and/or resource allocation. If the database can handle more queries, then Looker won't need to wait as long to send the database a new query.
• Checking the load of queries on the instance at the time of the error. Does the error happen on dashboards with lots of tiles (>25)? Are there lots of queries running at the time the error is thrown? If more queries are being run than expected, try and reduce query concurrency by either trimming dashboards or trimming schedules.

Customer-Hosted

NOTE: If Looker hosts, then your instance's elasticity (based on your license) will automatically adjust the following startup flags - so you can skip this section. If you and your company host your Looker instance, then you may also want to consider the following options:

There are whole lot of helpful startup flags to help us fine tune the Looker instance's interaction with your database.

• Below are the most common flags for this connection pool error:
  • --per-user-query-limit=<i>
  • --per-user-query-timeout=<i>
• Depending upon how your Looker is being leveraged, you may want to consider adjusting some of the other flags respectively.
• Clustered Lookers will have startup flags for each node.

Remember that adjusting these is a balancing act between the database's or warehouse's concurrency limit and the connection settings and startup flags in Looker. It's also worth noting that after these individual systems grow and scale sometimes, we have to think "out of the box!" Hah! Based on your data ecosystem's architecture this can sometimes mean configuring a new project with a new database or warehouse, so that, we grow the pools and threads limit to meet the resource needs of our diversified data workflows. All in all, there are several very helpful layers that ultimately help us achieve a balanced data harmony.

This content is subject to limited support.