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

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: 
 

7d6794ca-e102-4e6c-872b-de5ed31352d0.png


Has this ever happened to you? Read on to learn about what causes this error, and how to resolve it. 
 

Why did this error happen?


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:

  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 a connection’s settings page in the Admin panel.
  3. Looker queued the query to give the database a chance to complete the queries that are already running.
    •  It's really important that Looker does this - by doing so, Looker protects databases and warehouses, so that the they don't get overloaded with requests. This ensures that the specific database or warehouse's resources are shared amongst all users, without the need to micromanage them. 
  4. After the query was queued for the specified amount of time in the Connection Pool Timeout setting (specified in the connection’s settings page), Looker cancelled the queued query.

How do I fix it?


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. 

  • Increase the specified number of connections in the Max Connections setting in a connection’s settings page in the Admin panel. This way, Looker will send more queries to the database before queueing queries in the connection pool.
  • Increase the amount of seconds in the Connection Pool Timeout  setting on the connection’s settings page. This way, Looker will wait longer before cancelling the query.
  • Leverage the System Activity History Explore to check the load of queries on the instance at the time of the error. Does the error happen on dashboards with a large number of tiles (>25)? Are there lots of queries running at the time the error is thrown?

For customer-hosted instances
 

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. 
  • Depending upon how your Looker is being leveraged, you may want to consider adjusting some of the other options respectively.
  • Keep in mind that clustered Looker instances have startup options for each node.

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.

0 0 5,152
0 REPLIES 0