Hey peers.
We see an issue where when leaving Metabase on for 24 hours, our MySQL Database and the application that uses it crashes. Stops as the number of connections (2500) is maxed out. This has happened several times. We have verified that this is only caused when MetaBase is enabled. Turning Metabase off for a week we didnt see the issue come back. Turn it back on and boom same issue.
Please provide the error message in the logs. Also mention which version of Metabase you are running.
Im sorry error messages in what log?
We are running version (which didnt see to matter as we updated and still see the issue):
You’re on version v1.33.4.1
Built on 2019-10-14
Branch: enterprise-release-1.33.x
If you have the enterprise edition of Metabase, I think they have special Support which you can use.
I have been unsuccessful in reaching anyone at Metabase.
@sourceminer
There has been several database connection fixes in 1.33.5.1, so please try that.
Please post Diagnostic Info from Admin > Troubleshooting.
Metabase only opens 15 connections per database (if you have multiple setup in Admin > Databases, then that number will raise) unless manually configured to a different amount.
Which version of MySQL? Please post the output of this query (run from Metabase SQL query):
SHOW VARIABLES LIKE '%timeout%';
Our Version of MySQL is 5.7
I will tell you that on average we have about 950 average running processes
and 980 Connections.
@sourceminer
Where are you getting the 950/980 numbers from?
Did you try 1.33.5.1?
Are you seeing Broken pipe (Write failed) errors in the Metabase log sometimes or any other errors?
Does cards on the dashboard sometimes show the error There was a problem displaying this chart and if you refresh the browser, then it works?
Our primary application using that DB is doing all these connections (by design).
Yes I have installed the 1.33.5.1 version.
I have just started it, usually our app crashes in the middle of the night. I suspect due to the number of connections being consumed.
Okay, now that’s completely differently. I’m almost 100% sure that your MySQL is being bogged down by the scan that Metabase does (by default every night).
So perhaps because the MySQL is under heavy load from the Metabase connections (up to 15), which then slows down the responses on your app and your app perhaps just creates new connections for every query instead of using a queue.
You can change the scan process in Admin > Databases > (db) > enable “This is a large database…”, save, goto the tab Scheduling.
https://www.metabase.com/docs/latest/administration-guide/01-managing-databases.html#database-sync-and-analysis
With that amount of connections, you might want to look into running master/slave, where Metabase would just connect to the slave, which should be read-only.
Also, running data backup from a slave is also the best solution, when the master is under heavy load, since table locking while backup can block all writes for a long time. (that has nothing to do with Metabase, just a tip)
Or perhaps ProxySQL could be a solution.
OK great… and yes on the backup/slave scenario I have thought about this.
Also we already have this is a large database set.
@sourceminer It doesn’t make an effect just to set “This is a large database…” - you need to look in the Scheduling-tab and set it to scan at a time, when there’s no activity in your app or perhaps disable scanning (but then you’ll not get the full benefit of Metabase).
How many GB of data is your MySQL?
You might learn much more by looking in your MySQL log, since I have a feeling that it’s probably missing some indexes and maybe other optimizations too. Might want to look at https://github.com/major/MySQLTuner-perl
Our MySQL DB is 80GB.
And we had a crash again early in the morning. So I will take a look at that Scheduling-Tab