tiki-freetag_translate.php times out

VERIFIED FIXED in 1.5

Status

support.mozilla.org
General
VERIFIED FIXED
8 years ago
8 years ago

People

(Reporter: cilias, Assigned: paulc)

Tracking

unspecified

Firefox Tracking Flags

(Not tracked)

Details

(Whiteboard: tiki_bug, tiki_upstreamed, URL)

Attachments

(1 attachment)

(Reporter)

Description

8 years ago
Now that the new start page links to <https://support.mozilla.com/tiki-browse_freetags.php?locale=en-US>, it is more important to make sure that freetags are assigned a language, or else users will see articles on that page that are not in their language.
(I'll also tell localizers not to use English freetags.)

Right now, we can't assign freetags to languages because https://support.mozilla.com/tiki-freetag_translate.php?locale=en-US times out.
(Assignee)

Comment 1

8 years ago
The solution to most of these timeouts is really about not listing _all_ of the content in a certain element E.g. we had issues with listing all pages in pagination, listing all polls/kb articles in a select box, etc.

James: I'm hoping maybe we can discuss what we should do with these types of problems. Is there a better approach than the hack-ish "don't put <select> in template"? I suppose replacing that with search would be the way to go, for this bug.
Listing every wiki page/poll in a select box and then running the urlt output filter is the particularly bad combination. It matches every <option value=".*">. I don't know if it applies here, but a little rough math:

12 (entries in urlt_regex_out) * 5500 (wiki pages) = 66000 calls to preg_replace().

Explains time-outs in a lot of places.
(Assignee)

Comment 3

8 years ago
Depending on what we decide to do for bug 526246, we can do something similar here.
(Assignee)

Comment 4

8 years ago
Created attachment 410581 [details] [diff] [review]
v1

Here's a first attempt:
* adds pagination
* optimizes the SQL query by
** removing a (seemingly) unnecessary join -- I tested and saw the same results on the page
** removing filtering out NULL tagIds, since they can't be NULL in the db (I did a SELECT for null tagIds to check, just in case)

However, when the offset is larger (e.g. I tested with 200), the page load is still slow and likely to time out, and it's all because of the query. It seems that all columns being joined are being indexed, so I'm not sure how to optimize this further - other than just creating a table with all this data already joined ...

Anyhow, improvements on my local, from 9 *minutes* to 20s on the first page, and, unfortunately, still up to 3m on offset=400. If we can't improve this query time, maybe we can retrieve only the data we need in a different way.

I also did a:
---
ALTER TABLE `tiki_objects` DROP INDEX `itemId` 
---
Since tiki-objects apparently has two indexes on the same pair of columns. This didn't seem to help though.

I've had to deal with this same query in bug 473000 and was stumped, basically because there's a lot of data here and it's not stored in a way that makes it easy to retrieve without many joins.

After some playing around, it looks like removing the join with tiki-objects also preserves the results, but it doesn't seem to speed up the query, so I didn't include it in the patch.
Attachment #410581 - Flags: review?(james)
Comment on attachment 410581 [details] [diff] [review]
v1

I did not drop the index, but it went from not loading at all to loading, albeit slowly on my VM. WFM.
Attachment #410581 - Flags: review?(james) → review+
Assignee: nobody → paulc
(Assignee)

Comment 6

8 years ago
r526582
This should improve page load time quite a bit. We try more improvements in the future, but freetags are tough because of so much data. Maybe a good solution would be to clean up unused tags?
Status: NEW → RESOLVED
Last Resolved: 8 years ago
Resolution: --- → FIXED
(Assignee)

Comment 7

8 years ago
I meant r56282. Also s/We try/We can try
(Assignee)

Updated

8 years ago
Target Milestone: --- → 1.5
(Assignee)

Updated

8 years ago
Whiteboard: tiki_bug
Verified FIXED -- at least, I can load https://support-stage.mozilla.org/tiki-freetag_translate.php?locale=en-US without issue now :-)

(I checked out the pagination, too.)
Status: RESOLVED → VERIFIED
I reviewed the patch. I don't think it solved the problem.

First off, removing the join causes no translation to be loaded. You might as well just load the tags. From any object, you need to join the translation table twice to obtain all other objects in the set. There is no way around it with the table design. I don't remember the exact impact on that page, nor can I explain why it made no difference on rendering. It may be due to the regular data pattern with most data being on English or unassociated.

As for pagination, it's a good thing, but filtering is good too. Initially, that page was designed to list the tags per page as a base filter. The global listing was mainly meant for testing due to the very large volumes of data that would follow.

Also, tiki's query function has parameters for pagination.
Whiteboard: tiki_bug → tiki_bug, tiki_discuss
James/Paul: any comments here for LPH?
If this has been fixed upstream another way then I'm happy to take that fix.
It's not an upstream fix. The page has always supported the type and objId parameters to filter for the tags present on a single page or object.
Yes, but if you can't load the page in the first place to set those filters, they don't help. That's what the goal of this bug was to fix. If the page can't load in 30 seconds, it's essentially broken. If that has been fixed upstream, then it's fine.
The page never offered filters to select. Instead, the page was linked from using the appropriate filters. When listing tags and one of them was in a foreign language, it would propose to translate the tags and list them for the current object.

As far as I know, there were never direct links to the entire tag translation page.

I upstreamed the pagination. But that's only part of the problem.

I could not upstream directly because the pagination links did not preserve the object filters or the selected languages.
Additional testing indicated that the pagination could not be used this way. Postprocessing of the results assembled the data. Pagination caused some to be missing. Partially rewrote the logic to make sure all the data is present.
Whiteboard: tiki_bug, tiki_discuss → tiki_bug, tiki_upstreamed
You need to log in before you can comment on or make changes to this bug.