Author: Jonathon Miller-Girvetz @jonathon1
The "Looker is having trouble connecting to your database" error can occur when you least expect it. For example, while running an Explore - BAM! There it is:
Has this ever happened to you? Read on to learn about what causes this error, and how to resolve it.
Looker's connection pool timeout is the only known cause of the "Looker is having trouble connecting to your database" error.
Basically, the following occurred:
We recommended checking the various concurrency limits (pools and threads) in your database or data warehouse, and if suitable, increasing them. If the database can handle more queries, then Looker won't need to wait as long to send the database a new query. After confirming your database can accommodate more queries, consider the following settings in Looker:
These settings can impact database performance. Be sure to increase the settings only as far as your database resources can allow.
If Looker hosts your instance, then your instance's elasticity (based on your license) will automatically adjust the startup flags mentioned below, so you can skip this section.
If your organization hosts its own Looker instance, then you may also want to consider Looker’s startup options.
There are a few startup options that can fine tune Looker’s interaction with your database:
These settings can impact database performance. Be sure to increase the settings only as far as your database resources can allow.
--per-user-query-limit=<i>
- This setting limits the number of concurrent queries per user. You can increase the number of concurrent queries per user by adjusting the maximum specified. The default is 15 concurrent queries per user.
--per-user-query-timeout=<i>
- This setting specifies the maximum time Looker will wait to run a query (per user). You can increase the amount of time by adjusting the maximum specified. The default is 600 seconds per user. Adjusting these settings is a balancing act between the database 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, we sometimes have to think "out of the box!"
Based on your data ecosystem's architecture, this can sometimes mean configuring a new project with a new database or warehouse in order to meet the resource needs of more diversified data workflows. All in all, the options and settings in Looker and in your database are helpful layers that can achieve a balanced data harmony.