How to improve the performmance of queries.

Nodeloc

Hi ,

After following @rafaucau 's suggestion, I installed Clockwork to monitor the performance of SQL queries in my Flarum. I was astonished to find out that the request for the main topic list was executing over 180 queries. This is truly unacceptable. Upon further investigation, I noticed numerous individual queries within, such as poll queries for each topic, terms queries, and badge queries. I understand that these are caused by the extensions I installed, but now that I need these extensions, how can I optimize the program to avoid so many queries?

Nodeloc

I tried to optimize some functions, removing unnecessary queries, and now it's much better. But still not enough.

Before

After

I do these changes:

Remove fof/terms ( because a loop queries).
Modify fof/polls , Remove badge in discussion list( because a loop queries).
Modify user basge , Removed unnecessary queries(there is a loop queries too).
Remove PayToRead.(Loop queries in it)

If there's a better way, please let me know.

Thanks.

GreXXL

Nodeloc would be awesome to see some of your findings either as opened issues to those extensions - or even better PRs improving it 🙂

You mentioned it not being enough what is still causing bottlenecks?

Gorm

Nodeloc i check your queries on image. It's general select and most of them must be cached (database query cache). Do you have any real impact in time (ms) with your optimisation? Or its only counts of queries differrence?

clarkwinkelmann

It's true that FoF Terms isn't well optimized, but it should be noted that those queries, one for each visible user, are only slowing down moderators who can see the user terms status. Regular visitors should not be impacted.

rob006

Gorm It's general select and most of them must be cached (database query cache).

Query cache is no longer a thing - MySQL removed cache support completely and in MariaDB it is disabled by default and probably not available in most environments.

N+1 query is quite common problem for Flarum, because there is no easy and convenient way to detect and investigate these issues while developing extensions (Clockwork is extremely inconvenient IMO). I think we need some better dev tools (like some kind of debug toolbar with query stats) to deal with this at ecosystem level.

luceos

rob006 N+1 query is quite common problem for Flarum, because there is no easy and convenient way to detect and investigate these issues while developing extensions (Clockwork is extremely inconvenient IMO). I think we need some better dev tools (like some kind of debug toolbar with query stats) to deal with this at ecosystem level.

Flarum can actually report it, but we haven't implemented this in such a way that it becomes usable for the general public yet.

Reference: https://laravel.com/docs/10.x/eloquent-relationships#preventing-lazy-loading

Nodeloc

rob006 Yes, I believe that in extension development, we need to consider more efficiency-related matters. It's possible to increase redundant data appropriately to reduce relational queries.