Some context first. We're using latest MB version. We mainly consume a MySQL database hosted on AWS Aurora.
Since we activated the model caching we started to see issues and bottlenecks like waits and lock table related to the model cache table creation process during each refresh.
I raise our cluster size without effect.
Now I start to think if this issues aren't related to the models by them selves. We have some nested models like Orders, Customers and Customer Orders based on the first two. Is this not generating issues ?
And if not idk if someone still encounter such issues.
The cache is refreshed every hour. We have +40 models and some of them they're not so small tbh.
I achieve to mitigate our past errors by raising our size cluster a lot. I have error like tmp table full or timeout connexion (I read on github that the timeout was hardcode to avoid some issues, is that still true ? how many time? )
But I have questions on how the cache work behind the scene?
When a question is turn to a model the cache is put on a queue waiting for the next schedule time (we see it in the cache tool menu)
Then if the cache model table doesn't exist, it create the cache model table
Does it run the query behind the scene, get the result, purge the table the table and insert the table ? or maybe he work differently?
How the model execution are decided ? if I have a model A using a model B. Does Metabase run models accordingly to this dependencies or not? Is this possible to go in a race condition situation ?
If I refresh a model A using a model B does that model A cache used the cache from the model B ? or he's just taking the model B as a code snippet and execute the whole query ?
