Uniphix
Active member
Hello,
Our production site uses XenForo as a core framework for our Group Development Platform similar to the concept of a Learning Management System. And I have created a Translator Addon that works with Bing and Google.
Now I know my addon is not the cause of the problem because when I point out the issue in the Deferred it'll paint a clear picture of what is happening.
1) In the Deferred for rebuilding the Phrases takes an awful long time, now the remaking of the mapping of the phrases for each language is not taking that long although keep in mind with this results from using the same query that it has to use.
10:14:38 SELECT * FROM xf_phrase WHERE language_id = 0 ORDER BY CONVERT(title USING utf8) LIMIT 10000000000 137238 row(s) returned 0.578 sec / 24.266 sec
As you can see in blue that there are 137,238 rows in the master phrases, this results in our system that uses the master phrases to store the original language, then we have 1 language (Spanish) that translates each phrase in the system as well. But lets focus on the green and red, green is the time it took to execute that query, and the red determines how long that query took to return the results. This is a lot of records to have to pull.
2) The problem for this is that when building the phrase map for a specific language and in this case the first thing it does is rebuilds the Master Phrases Map for a Language Tree, then it will proceed to the custom phrases for another language, so in the function
As you can see the it will grab all phrases for the language in this case first one it does is 0, the result above is the time it took to process this. After it finishes the mapping of the master phrases, it then will process to the child phrases, therefore the child of master phrase is 1 and 2, 1 being English and 2 being Spanish in our system. It will then do the execution of the query above in English
10:28:13 SELECT * FROM xf_phrase WHERE language_id = 1 ORDER BY CONVERT(title USING utf8) LIMIT 10000000000 0 row(s) returned 0.078 sec / 0.000 sec
As you can see english was 0 results which was expected, when Master Phrases to me in concept is the English version of the wording, we don't need to make the system work harder. This is expected.
Lets now do Spanish
10:30:03 SELECT * FROM xf_phrase WHERE language_id = 2 ORDER BY CONVERT(title USING utf8) LIMIT 10000000000 137139 row(s) returned 0.547 sec / 25.859 sec
Less results but took again almost 26 seconds to totally execute this query.
Because of this it takes almost 1 hour and 20 minutes to rebuild the phrases ALONE.
Solution
Because I cannot wait for the team to fix the issue, maybe my fixes will be encouraged to be add and reviewed on your end. It could save you guys time and feel free to use it. We cannot choose another core framework when we have put 2 years of development with over 35,000 revisions to our own systems using XenForo. Our system is almost as big as XenForo itself and will continue to grow as we expand.
Solution 1)
Like the import system you guys have it so that it determines the MAX user_id, or whatever your doing. In this case we can do MAX(title) and give us the LAST known Title.
MySQL does have support of title > ?.
Old Function
NEW function
This could help speed up the process of all the phrase rebuilds, but I have noticed the same issue in Templates, Admin Templates re-parsing.
I have done some changes to help speed up the process of rebuilding phrases, templates, admin templates and email templates.
It would be very much appreciative if we get some optimization when it comes to rebuilding the caches.
Last and finally this problem is throughout the entire 1.2+, 1.3+ series
Our production site uses XenForo as a core framework for our Group Development Platform similar to the concept of a Learning Management System. And I have created a Translator Addon that works with Bing and Google.
Now I know my addon is not the cause of the problem because when I point out the issue in the Deferred it'll paint a clear picture of what is happening.
1) In the Deferred for rebuilding the Phrases takes an awful long time, now the remaking of the mapping of the phrases for each language is not taking that long although keep in mind with this results from using the same query that it has to use.
10:14:38 SELECT * FROM xf_phrase WHERE language_id = 0 ORDER BY CONVERT(title USING utf8) LIMIT 10000000000 137238 row(s) returned 0.578 sec / 24.266 sec
As you can see in blue that there are 137,238 rows in the master phrases, this results in our system that uses the master phrases to store the original language, then we have 1 language (Spanish) that translates each phrase in the system as well. But lets focus on the green and red, green is the time it took to execute that query, and the red determines how long that query took to return the results. This is a lot of records to have to pull.
2) The problem for this is that when building the phrase map for a specific language and in this case the first thing it does is rebuilds the Master Phrases Map for a Language Tree, then it will proceed to the custom phrases for another language, so in the function
PHP:
XenForo_Phrase::_buildPhraseMapForLanguageTreeAs you can see the it will grab all phrases for the language in this case first one it does is 0, the result above is the time it took to process this. After it finishes the mapping of the master phrases, it then will process to the child phrases, therefore the child of master phrase is 1 and 2, 1 being English and 2 being Spanish in our system. It will then do the execution of the query above in English
10:28:13 SELECT * FROM xf_phrase WHERE language_id = 1 ORDER BY CONVERT(title USING utf8) LIMIT 10000000000 0 row(s) returned 0.078 sec / 0.000 sec
As you can see english was 0 results which was expected, when Master Phrases to me in concept is the English version of the wording, we don't need to make the system work harder. This is expected.
Lets now do Spanish
10:30:03 SELECT * FROM xf_phrase WHERE language_id = 2 ORDER BY CONVERT(title USING utf8) LIMIT 10000000000 137139 row(s) returned 0.547 sec / 25.859 sec
Less results but took again almost 26 seconds to totally execute this query.
Because of this it takes almost 1 hour and 20 minutes to rebuild the phrases ALONE.
Solution
Because I cannot wait for the team to fix the issue, maybe my fixes will be encouraged to be add and reviewed on your end. It could save you guys time and feel free to use it. We cannot choose another core framework when we have put 2 years of development with over 35,000 revisions to our own systems using XenForo. Our system is almost as big as XenForo itself and will continue to grow as we expand.
Solution 1)
Like the import system you guys have it so that it determines the MAX user_id, or whatever your doing. In this case we can do MAX(title) and give us the LAST known Title.
MySQL does have support of title > ?.
Old Function
PHP:
public function compileAllPhrases($maxExecution = 0, $startLanguage = 0, $startPhrase = 0)
{
$db = $this->_getDb();
$languages = $this->_getLanguageModel()->getAllLanguages();
$languageIds = array_merge(array(0), array_keys($languages));
sort($languageIds);
$lastLanguage = 0;
$startTime = microtime(true);
$complete = true;
XenForo_Db::beginTransaction($db);
foreach ($languageIds AS $languageId)
{
if ($languageId < $startLanguage)
{
continue;
}
$lastLanguage = $languageId;
$lastPhrase = 0;
$phrases = $this->getAllPhrasesInLanguage($languageId);
foreach ($phrases AS $phrase)
{
$lastPhrase++;
if ($languageId == $startLanguage && $lastPhrase < $startPhrase)
{
continue;
}
$this->compileNamedPhraseInLanguageTree($phrase['title'], $phrase['language_id']);
if ($maxExecution && (microtime(true) - $startTime) > $maxExecution)
{
$complete = false;
break 2;
}
}
}
if ($complete)
{
$compiledRemove = $db->fetchAll("
SELECT c.title, c.language_id
FROM xf_phrase_compiled AS c
LEFT JOIN xf_phrase_map AS m ON (c.title = m.title AND c.language_id = m.language_id)
WHERE m.title IS NULL
");
foreach ($compiledRemove AS $remove)
{
$db->delete('xf_phrase_compiled',
"language_id = " . $db->quote($remove['language_id']) . " AND title = " . $db->quote($remove['title'])
);
}
$this->_getLanguageModel()->rebuildLanguageCaches();
}
XenForo_Db::commit($db);
if ($complete)
{
return true;
}
else
{
return array($lastLanguage, $lastPhrase + 1);
}
}NEW function
PHP:
public function compileAllPhrases($maxExecution = 0, $startLanguage = 0, $startPhrase = '')
{
$db = $this->_getDb();
$languages = $this->_getLanguageModel()->getAllLanguages();
$languageIds = array_merge(array(0), array_keys($languages));
sort($languageIds);
$lastLanguage = 0;
$startTime = microtime(true);
$complete = true;
XenForo_Db::beginTransaction($db);
foreach ($languageIds AS $languageId)
{
if ($languageId < $startLanguage)
{
continue;
}
$lastLanguage = $languageId;
$lastPhrase = '';
$phrases = $this->fetchAllKeyed('
SELECT *
FROM xf_phrase
WHERE language_id = ? AND title > ?
ORDER BY CONVERT(title USING utf8)
LIMIT 100
', 'title', array($languageId, $startPhrase));
foreach ($phrases AS $phrase)
{
$lastPhrase = $phrase['title'];
$this->compileNamedPhraseInLanguageTree($phrase['title'], $phrase['language_id']);
if ($maxExecution && (microtime(true) - $startTime) > $maxExecution)
{
$complete = false;
break 2;
}
}
}
if ($complete)
{
$compiledRemove = $db->fetchAll("
SELECT c.title, c.language_id
FROM xf_phrase_compiled AS c
LEFT JOIN xf_phrase_map AS m ON (c.title = m.title AND c.language_id = m.language_id)
WHERE m.title IS NULL
");
foreach ($compiledRemove AS $remove)
{
$db->delete('xf_phrase_compiled',
"language_id = " . $db->quote($remove['language_id']) . " AND title = " . $db->quote($remove['title'])
);
}
$this->_getLanguageModel()->rebuildLanguageCaches();
}
XenForo_Db::commit($db);
if ($complete)
{
return true;
}
else
{
return array($lastLanguage, $lastPhrase);
}
}This could help speed up the process of all the phrase rebuilds, but I have noticed the same issue in Templates, Admin Templates re-parsing.
I have done some changes to help speed up the process of rebuilding phrases, templates, admin templates and email templates.
It would be very much appreciative if we get some optimization when it comes to rebuilding the caches.
Last and finally this problem is throughout the entire 1.2+, 1.3+ series
Upvote
1
