search

andrewhancox / moodle-filter_translations

8 stars 8 forks source link

Slow page loads for admin users on site default language #132

Open rjnl opened 2 years ago

rjnl commented 2 years ago

We are noticing slow page loads for site admins, who can edit translations in site default language. For other users, those who cannot translate content or those with "edittranslations" permission, page loads are relatively fast.

On investigation we found that the query SELECT COUNT(?) FROM mdl_filter_translation_issues WHERE issue = $? AND targetlanguage = $? is what is slowing things. Our translation_issues table has over 1.5 million records. Our db is running on PostgresSQL and counting rows seem to be slow in PostgresSQL.

We don't face this issue on another site running on MySQL.

jgramp commented 2 years ago

It doesn't make sense to show the missing strings number for the default site language. I propose we only show it for non default languages (and also hide it for the child languages of the site default).

jgramp commented 2 years ago

Could we also set it once at the start of the session if it's 99+ and then only set it again if a bulk upload is done? (Rajneel was looking into this upload feature on another ticket).

jgramp commented 2 years ago

@rjnl could you resolve (by at least hiding for site languages) and send a pull request please? We'd like to get this resolved on our site asap as it's slowing down our work on the site.

andrewhancox commented 2 years ago

Adding an index to the table should make the query trivial and would be a lot less complex so I would suggest we do that first. In addition there needs to be a job to purge this table regularly - I would suggest weekly?

Sent from my iPhone

On 29 Jun 2022, at 07:18, Jessica Gramp @.***> wrote:

 @rjnl could you resolve (by at least hiding for site languages) and send a pull request please? We'd like to get this resolved on our site asap as it's slowing down our work on the site.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you are subscribed to this thread.

rjnl commented 2 years ago

@andrewhancox If this table can be purged, then would it be safe to delete/clear entries from the filter_translation_issues table??

We could first DELETE FROM filter_translation_issues WHERE targetlanguage="en_wp" AND issue=20 And then add the issue field to the index?

andrewhancox commented 2 years ago

A bit of background: As there is no direct relationship between a translation and the thing being translated logging is quite tricky. The issues are tracked based on the full url, 'best' hash, target language, context and generated hash - the last modified date is kept up to date but is debounced by the logdebounce setting - by default it will only be updated once per day to avoid hammering the table. The only use of this data is reporting on missing/stale translations so it can definitely be purged as and when required.

I think the index should probably be on issue and target language based on the query. I would suggest the scheduled task deletes all issues where the last modified date is over a week ago.

You could also cache the count but it's probably more hassle than it's worth - if the site wide count is going to permanently sit over 99 records (by a factor of 1M) then it may not be worth showing at all...

jgramp commented 2 years ago

Agreed. Let's scrap showing the count. Its just causing issues it seems.