Topic: Merit Stats pages Cloud Flare error (Read 131 times)

The stats in question have post titles, so one of the tables queried will have to be the table containing the titles of every post. I would suspect your database would only contain post titles of posts merited. Depending on how the tables are setup, the edit history of posts may also be queried.

Even if the queries were made more efficient, they are still resource-intensive and would be a potential means an attacker someone could DDoS the forum by repeatedly visiting these stats pages.

It may be a question of simply revising the existing indexes on the tables that are joined to return the query. On SQL server, without using anything too flashy, the query for any of those stats takes 2 second on my environment. I’d expect the server being used to be way faster, and although there may be lock issues (more so as tables grow), dirty reads can do the trick for these cases.

I can’t recall encountering the timeout error before pulling those stats, but it does happen now on some of the topic and reply stats.

If correctly indexed, the join with the merit table should reduce the access plan a hell of a lot, being of a much smaller cardinality.

It does take quite a long time to load the page and it does look quite big. I'd suggest the same of doing it once a day with the addition of checking and doing it when the servers aren't busy.

---

If the slowdown is for realtime data collection on the merit number (as some of those threads have a lot of pages) they could see the number just be added to when people get merit so they can still show realtime stats based off that - dunno if there's a table that has threadNo, meritTotal (though the time delay could also be caused by a bad join collecting thread titles).

I recently tried to view some of the merit stats reports and received a Cloud Flare error message when trying to view some of the reports, specifically the top merited topics and replies, recent and all time.

The error message said the web server timed out, and gave this information:

I have noticed these reports can oftentimes take several seconds to load when they actually load.

Based on the above, I strongly suspect that when someone tries to navigate to one of these pages, a report will be generated that is up-to-date as of when the report is pulled, and the report will only be displayed to that specific person. So if two people go to the top merited topics, all time page, 5 minutes apart, two reports will be generated (that are identical, most likely), with each report being displayed to one user.

This is not ideal because the all-time reports need to query every merit transaction, which after 4 years is a lot. I would propose that as an alternative, with the exception of 'recent merits' and 'merit sources', that a user going to any of the merit stats pages will return a static page, and the static page can have a report that is updated every x amount of time. The frequency of how often these static pages should update should depend on how often the output of the reports has historically changed, particularly the rankings. I might suggest the reports be updated no less frequently than once per day.