1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Lack of Interest Compilation of Phrases, Templates, Admin Templates etc. in Deferred

Discussion in 'Closed Suggestions' started by Uniphix, Jul 23, 2014.

  1. Uniphix

    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

    PHP:
    XenForo_Phrase::_buildPhraseMapForLanguageTree
    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
    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
     
    Xon likes this.
  2. Uniphix

    Uniphix Active Member

    Okay I just realized that the sorting for rebuilding should be based on the phrase_id, and sorted by then and then add the next by 1. Using title we would need to find out what the next title in the phrase we would be doing otherwise it will never rebuild it...
     
    Xon likes this.
  3. Uniphix

    Uniphix Active Member

    $startPhrase, $startTemplate, in both Admin Templates, Phrases and Templates really not sure why you guys aren't doing something like this

    Code:
    SELECT phrase_id, title, language_id
                    FROM xf_phrase
                    WHERE language_id = ? AND phrase_id >= ?
                    ORDER BY phrase_id ASC
                    LIMIT 100
    
     
    Xon likes this.
  4. Uniphix

    Uniphix Active Member

    The following code changes I have done are:

    In XenForo_Model_AdminTemplate::compileAllParsedAdminTemplates and XenForo_Model_AdminTemplate::reparseAllAdminTemplates

    PHP:
    /**
        * Reparses all admin templates
        *
        * @param integer $maxExecution The approx maximum length of time this function will run for
        * @param integer $startTemplateId The number of the template to start with in that style
        *
        * @return boolean|int True if completed successfully, otherwise int of restart template counter
        */
        
    public function reparseAllAdminTemplates($maxExecution 0$startTemplateId 0)
        {
            
    $db $this->_getDb();
            
    $startTime microtime(true);
            
    $complete true;
            
    $nextTemplateId 0;
            
    XenForo_Db::beginTransaction($db);
            
    $templates $db->fetchAll('
                SELECT *
                FROM xf_admin_template
                WHERE template_id >= ?
                ORDER BY template_id ASC
                LIMIT 100'
    $startTemplateId);
            foreach (
    $templates AS $template)
            {
                
    $nextTemplateId $template['template_id'] + 1;
                
    $this->reparseTemplate($templatefalse)->getMergedData();
                if (
    $maxExecution && (microtime(true) - $startTime) > $maxExecution)
                {
                    
    $complete false;
                    break;
                }
            }
         
            
    // more to compile
            
    if( $nextTemplateId ) {
                
    $completed false;
            }
            
    XenForo_Db::commit($db);
            if (
    $complete)
            {
                return 
    true;
            }
            else
            {
                return 
    $nextTemplateId;
            }
        }

        
    /**
        * Compiles all admin templates
        *
        * @param integer $maxExecution The approx maximum length of time this function will run for
        * @param integer $startTemplateId The number of the template to start with in that style
        * @param array $priority List of templates that have priority to be compiled on the first run (eg, cache rebuild and page container)
        *
        * @return boolean|int True if completed successfully, otherwise int of restart template counter
        */
        
    public function compileAllParsedAdminTemplates($maxExecution 0$startTemplateId 0, array $priority = array())
        {
            
    $db $this->_getDb();

            
    $startTime microtime(true);
            
    $nextTemplateId 0;
            
    $completed true;

            
    XenForo_Db::beginTransaction($db);

            if (
    $startTemplateId == 0)
            {
                
    $priorityTemplates $db->fetchAll('
                    SELECT *
                    FROM xf_admin_template
                    WHERE
                        title IN(' 
    $db->quote($priority) . ')');
             
                foreach (
    $priorityTemplates AS $template)
                {
                    
    $template $this->reparseTemplate($templatefalse)->getMergedData();
                    
    $this->compileParsedAdminTemplate(
                        
    $template['template_id'], unserialize($template['template_parsed']), $template['title']
                    );
                }
            }
         
            
    // priority templates are already recompiled, why recompile them again?
            
    $templates $db->fetchAll('
                SELECT *
                FROM xf_admin_template
                WHERE template_id >= ?
                ORDER BY template_id ASC
                LIMIT 100'
    $startTemplateId);

            foreach (
    $templates AS $template)
            {
                
    $nextTemplateId $template['template_id'] + 1;

                
    $this->compileParsedAdminTemplate(
                    
    $template['template_id'], unserialize($template['template_parsed']), $template['title']
                );

                if (
    $maxExecution && (microtime(true) - $startTime) > $maxExecution)
                {
                    
    $completed false;
                    break;
                }
            }
         
            if( 
    $nextTemplateId ) {
                
    $completed false;
            }

            if (
    $completed)
            {
                
    $compiledRemove $db->fetchAll("
                    SELECT DISTINCT c.title
                    FROM xf_admin_template_compiled AS c
                    LEFT JOIN xf_admin_template AS m ON (c.title = m.title)
                    WHERE m.title IS NULL
                "
    );
                foreach (
    $compiledRemove AS $remove)
                {
                    
    $db->delete('xf_admin_template_compiled',
                        
    "title = " $db->quote($remove['title'])
                    );
                }

                
    $this->updateAdminStyleLastModifiedDate();
            }

            
    XenForo_Db::commit($db);

            if (
    $completed)
            {
                return 
    true;
            }
            else
            {
                return 
    $nextTemplateId;
            }
        }
     
    Xon likes this.
  5. Uniphix

    Uniphix Active Member

    In XenForo_Model_Template::reparseAllTemplates and XenForo_Model_Template::compileAllTemplates

    PHP:
    /**
        * Reparses all templates.
        *
        * @param integer $maxExecution The approx maximum length of time this function will run for
        * @param integer $startStyleId The ID of the style to start with
        * @param integer $startTemplateId The number of the template to start with in that style
        *
        * @return boolean|array True if completed successful, otherwise array of where to restart (values start style ID, start template counter)
        */
        
    public function reparseAllTemplates($maxExecution 0$startStyleId 0$startTemplateId 0)
        {
            
    $db $this->_getDb();
            
    $styles $this->getModelFromCache('XenForo_Model_Style')->getAllStyles();
            
    $styleIds array_merge(array(0), array_keys($styles));
            
    sort($styleIds);

            
    $lastStyleId 0;
            
    $startTime microtime(true);
            
    $completed true;
            
    XenForo_Db::beginTransaction($db);
            foreach (
    $styleIds AS $styleId)
            {
                if (
    $styleId $startStyleId)
                {
                    continue;
                }
                
    $lastStyleId $styleId;
                
    $nextTemplateId 0;
                
    $templates $db->fetchAll('
                    SELECT template_id, title, style_id
                    FROM xf_template
                    WHERE style_id = ? AND template_id >= ?
                    ORDER BY template_id ASC
                    LIMIT 100'
    , array($styleId$startTemplateId));
                foreach (
    $templates AS $template)
                {
                    
    $nextTemplateId $template['template_id'] + 1;
                    
    $this->reparseTemplate($templatefalse);
                    if (
    $maxExecution && (microtime(true) - $startTime) > $maxExecution)
                    {
                        
    $completed false;
                        break 
    2;
                    }
                }
             
                
    // we have more data to work with
                
    if( $nextTemplateId ) {
                    
    $completed false;
                    break;
                }
            }
            
    XenForo_Db::commit($db);
            if (
    $completed)
            {
                return 
    true;
            }
            else
            {
                return array(
    $lastStyleId$nextTemplateId);
            }
        }

        
    /**
        * Recompiles all templates.
        *
        * @param integer $maxExecution The approx maximum length of time this function will run for
        * @param integer $startStyleId The ID of the style to start with
        * @param integer $startTemplateId The number of the template to start with in that style
        *
        * @return boolean|array True if completed successfull, otherwise array of where to restart (values start style ID, start template counter)
        */
        
    public function compileAllTemplates($maxExecution 0$startStyleId 0$startTemplateId 0)
        {
            
    $db $this->_getDb();
            
    $styles $this->getModelFromCache('XenForo_Model_Style')->getAllStyles();
            
    $styleIds array_merge(array(0), array_keys($styles));
            
    sort($styleIds);

            
    $lastStyleId 0;
            
    $startTime microtime(true);
            
    $completed true;
            
    XenForo_Db::beginTransaction($db);
            foreach (
    $styleIds AS $styleId)
            {
                if (
    $styleId $startStyleId)
                {
                    continue;
                }
                
    $lastStyleId $styleId;
                
    $nextTemplateId 0;
                
    $templates $db->fetchAll('
                    SELECT template_id, title, style_id
                    FROM xf_template
                    WHERE style_id = ? AND template_id >= ?
                    ORDER BY template_id ASC
                    LIMIT 100'
    , array($styleId$startTemplateId));
                foreach (
    $templates AS $template)
                {
                    
    $nextTemplateId $template['template_id'] + 1;
                    
    $this->compileNamedTemplateInStyleTree($template['title'], $template['style_id']);
                    if (
    $maxExecution && (microtime(true) - $startTime) > $maxExecution)
                    {
                        
    $completed false;
                        break 
    2;
                    }
                }
             
                
    // we have more data to work with
                
    if( $nextTemplateId ) {
                    
    $completed false;
                    break;
                }
            }
            if (
    $completed)
            {
                
    $compiledRemove $db->fetchAll("
                    SELECT DISTINCT c.title, c.style_id
                    FROM xf_template_compiled AS c
                    LEFT JOIN xf_template_map AS m ON (c.title = m.title AND c.style_id = m.style_id)
                    WHERE m.title IS NULL
                "
    );
                foreach (
    $compiledRemove AS $remove)
                {
                    
    $db->delete('xf_template_compiled',
                        
    "style_id = " $db->quote($remove['style_id']) . " AND title = " $db->quote($remove['title'])
                    );
                    if (
    XenForo_Application::get('options')->templateFiles)
                    {
                        
    XenForo_Template_FileHandler::delete($remove['title'], $remove['style_id'], null);
                    }
                }
                
    $this->getModelFromCache('XenForo_Model_Style')->updateAllStylesLastModifiedDate();
                
    $this->getModelFromCache('XenForo_Model_AdminTemplate')->updateAdminStyleLastModifiedDate();
            }
            
    XenForo_Db::commit($db);
            if (
    $completed)
            {
                return 
    true;
            }
            else
            {
                return array(
    $lastStyleId$nextTemplateId);
            }
        }
     
    Xon likes this.
  6. Uniphix

    Uniphix Active Member

    In XenForo_Model_Phrase::compileAllPhrases

    PHP:
    public function compileAllPhrases($maxExecution 0$startLanguageId 0$startPhraseId 0)
        {
            
    $db $this->_getDb();
            
    $languages $this->_getLanguageModel()->getAllLanguages();
            
    $languageIds array_merge(array(0), array_keys($languages));
            
    sort($languageIds);

            
    $nextPhraseId 0;
            
    $lastLanguageId 0;
            
    $startTime microtime(true);
            
    $completed true;
            
    XenForo_Db::beginTransaction($db);
            foreach (
    $languageIds AS $languageId)
            {
                if (
    $languageId $startLanguageId)
                {
                    continue;
                }
                
    $lastLanguageId $languageId;
                
    $nextPhraseId 0;
                
    $phrases $db->fetchAll('
                    SELECT phrase_id, title, language_id
                    FROM xf_phrase
                    WHERE language_id = ? AND phrase_id >= ?
                    ORDER BY phrase_id ASC
                    LIMIT 100
                '
    , array($languageId$startPhraseId));
                foreach (
    $phrases AS $phrase)
                {
                    
    $nextPhraseId $phrase['phrase_id'] + 1;
                    
    $this->compileNamedPhraseInLanguageTree($phrase['title'], $phrase['language_id']);
                    if (
    $maxExecution && (microtime(true) - $startTime) > $maxExecution)
                    {
                        
    $completed false;
                        break 
    2;
                    }
                }
             
                
    // more work needed
                
    if( $nextPhraseId ) {
                    
    $completed false;
                    break;
                }
            }
            if (
    $completed)
            {
                
    $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 (
    $completed)
            {
                return 
    true;
            }
            else
            {
                return array(
    $lastLanguageId$nextPhraseId);
            }
        }
    I have tested, and tried it on a fresh install of XenForo and it works well. It reduced our site from 1 hour and 20 minutes of "Phrases time alone" to less than 3 minutes (that is 238,000 phrases) compiled. The remaining 20 minutes was rebuilding/reparsing of the templates and admin templates.

    The entire processed after the changes I've done took only 15 minutes.
     
    Xon likes this.
  7. Mike

    Mike XenForo Developer Staff Member

    The suggestions here aren't necessarily unreasonable, though I haven't looked in detail to the actual code. That said, I would note that you have a huge amount of data compared to what is generally the expected situation - probably 20 times more than a base install and likely at least 10 times more than a normal fully translated board. I will consider changes, but when the data goes orders of magnitude beyond the expected situation, different approaches may need to be undertaken to fit your needs specifically. (Most of the speed complaints regarding rebuilds are during the template stage.)

    I'm actually a bit surprised at how long these queries are taking on your site as well - 26 seconds to send that data? If you have, say, a 100mbit connection to the DB server, that should still be able to transport 12.5MB in a second. With the data going over the binary protocol, there shouldn't be a huge amount of overhead, so I'd be surprised if there is actually 12.5MB there, though obviously I don't know the size of your custom phrases. (The content of the default phrase rows looks to be around 560KB put together.) There are still some other factors to consider in the total time, but I'd be concerned about that transfer speed from the MySQL server. (Minor point but I am also surprised that it took 0.07 seconds to determine that there were 0 rows as that's an index lookup, but I don't know if that's just a one off slow result.)
     
    Uniphix likes this.
  8. Uniphix

    Uniphix Active Member

    Hi Mike,

    First of all thank you for looking into my code changes, you will notice what I did and why I did what I did. I took the concept of how the Import System functions and how it is able to insert hundreds of records quickly. Anyway that being said, If you look at the most recent post which I would suggest, it lays out the changes I've done to not only the Phrases but to the Templates and Admin Templates as well. I did a test on our production site of just adding a simple AddOn to it and the entire process took almost 15 minutes, Phrases took 3 minutes, rebuilding of the templates took 5 minutes and admin templates taking 4 minutes where email templates finally and lastly took only a minute. Then I reverted my changes to the production site and did the entire process again. The phrases alone took 1 hour and 10 minutes to finally finish rebuilding, and the templates took another 20 minutes alone. The Admin Templates on the other hand was faster because it doesn't incorporate 'styles' .

    On Our system for production we have an Addon we developed on that addon alone we have 556 templates, where XenForo has about
    434 templates. So the templates we have are twice the amount than what XenForo is shipped with. Our system will continue to grow and change and we have only been up since March of 2012, which is when the development process took place. Since then I have done almost 30,000 revisions, thousands of changes to templates, styles, phrases. We went from a few MB database to almost 1 GB of database space, which is all from other parts of our system.

    I have tested my code above and it works really well, I've done hard-code changes, and was even able to provide how much of a percentage it is being from completion.

    Again thanks for taking a look into this and I wasn't sure if this was more of a suggestion or a bug, on my end it is a bug because it slows down our production rather than keeping it a steady pace. Hate having to hotfix our development and production website lol.
     

Share This Page