Sleep Metabase Application DB Connections When Not In Use?

Hey All,

New Metabase user here. Currently running Metabase on docker and it is going well. The application database is Postgres and the BI database is MySQL.

In monitoring the postgres DB, I see a consistent 15 connections 24/7. Is there a setting in Metabase or any way in Metabase that can drain these connections if not in use? For example, some parts of the day, nobody loads Metabase, no pulses are running, there’s no reason to have any connections. The reason this is important is because the Postgres DB is supposed to spin down after a period of inactivity. But the 15 connections are keeping it live.

And I can’t really schedule the the docker to shut down during those times just in case someone decides to use it at 2am.

I looked through the FAQ and found Control pool size, but it only addresses the max pool size, not necessarily closing the connections when idle.

Thanks!

Hi @wongchun
Please post “Diagnostic Info” from Admin > Troubleshooting.
Which version of Postgres?

We’re running Postgres 10.7, JDBC driver 42.2.8, Metabase v1.35.3 (10740ae)

@wongchun Okay, since you’re using Enterprise Edition, please use support email in the future.

So you’re only seeing this on the Postgres application database, but not on your MySQL datasource database?

Connections should be closed automatically:

  • when idle, after 3 hours
  • or when it’s excess connections (more than 1), and idle, then 15 minutes

Reference: https://github.com/metabase/metabase/blob/master/src/metabase/driver/sql_jdbc/connection.clj

Metabase will always keep at least one connection the it’s application database.
Unless minPoolSize=0, but I’m actually unsure what consequences it might have.

As for the 15 connections, that’s a configurable max limit, but setting this too low can result in the Metabase interface becoming slow if all connections are busy.
If you set it to 5 and you have 10 users actively browsing/download data, then some of the users might have to wait until there’s a free connection.

And unless you’ve changed scheduling of the sync+scan process, then that will run every hour for sync and daily for scans:
https://www.metabase.com/docs/latest/administration-guide/01-managing-databases.html#database-sync-and-analysis

If you prefer, then please use the support mail and you can provide more information, such as “Diagnostic Info” from Admin > Troubleshooting, how many active users you have, and where you’re hosting, so I can try to figure out what we could do.

Thanks for the troubleshooting @flamber. I’ve double checked some stuff. For the instance I was troubleshooting on, I forgot to reduce the hourly scan to a daily. I’ve done that now.

The datasource DB seems to be successfully spinning down when it’s not in use. I’ve set the daily scan to 6am.

For the application DB, on one instance it’s at 15 connections constantly, on another its 7 connections constantly. I’ll do a reboot on all my dockers at end of day and leave it alone overnight until my morning scan to see what the metrics show. Maybe that will reveal some more info. If it reduces itself to one, it’ll align with what you said about minPoolSize=1. Is there a way to override this setting just as a test? Perhaps as a docker parameter?

@wongchun

When Metabase is doing sync+scan on your datasources, then it will update the application database. There’s much more activity on the application database, but it’s short queries normally.

Currently the minPoolSize is 1 - don’t think you should change that.

You can change most of the c3po parameters in Docker with - example:

docker run ... -e JAVA_OPTS="-Dc3p0.maxIdleTime=900 -Dc3p0.maxIdleTimeExcessConnections=180" ...

c3po reference: https://www.mchange.com/projects/c3p0/