[Question][MySQL] MySQL Performance

[jeffschaper]

Question

My company is running DevLake at scale with approximately 1,000 projects. The out-of-the-box MySQL database (running in Azure) seems to struggle when loading the Grafana dashboard despite a generous compute size of 16 cores and 64 GB of memory.
Is there a way to optimize performance of the MySQL database. Are there any slam-dunk server parameters I should change to optimize performance?

Screenshots

Additional context

The CPU and memory seem to spike when loading the Grafana dashboard.

[klesh]

Try increasing the memory and use ssd storage.

[kgalli]

I noticed that there is a lot of room to improve query performance by adding some indexes. You can easily login to the instance and enable slow query logging. I added a couple indexes myself to also improve the time spent on running the DORA calculations (from several hours down to minutes. I'm running 1.0.0 and will update to latest soonish. Once done I will check the performance again and if needed create a PR to suggest the indexes I added. In 1.0.0 I saw that a lot of foreign keys were simply missing e.g. for all those pull_requests_XXX tables which have the pull_request_id foreign key.

[jeffschaper]

@kgalli - Thanks for the reply! Indexing does seem like low-hanging fruit to improve performance.

My colleague and I analyzed the explain plan for a few of the queries and rewrote a couple of the common table expressions (CTE). We noticed the queries ran much faster (minutes to seconds) after making those optimizations. We're hoping to contribute that back to the project, but we're still validating the diff (since the SQL statements are embedded in the Grafana JSON, it's a little tricky to make sure the changes are being applied correctly).

Appreciate your reply! Thanks for offering support!