Full recache of huge number of products and PHP max_execution_time 3600s - how!?!?!?!!?! (PARTIALLY SOLVED)

[AndreyPopov]

now I have 6500 products in database.

after Purge All Cache full recache of all for one UA is 8-12 hours

one run recache script from cli (or web) recache only near 1000 (one thousand) product links.

I must re-run recache script each hour by cron (or by hand)

but ............. after restart recache script again and again build urls list and start recache from beginning

recache already cached urls is near 10 mins for 1000 urls.

5000 already cached urls is near 40-50 mins.

last 1500 urls script recache with iteration 200-300 urls for run.

total: 5hours for first 5000 urls and 5-8 hours for last 1500 urls.

is the way ONCE build urls list, save it? remember last cached urls and restart again from next url?


P.S. solution found.

 

[AndreyPopov]

some sleepless nights
and now testing algorithm that remember last cached item and new recache start from it.

 

[AndreyPopov]

first step

add some variables for use

admin/model/extension/module/lscache.php

        // lscache crawler product list recache variabels
        $this->db->query(" insert into " . DB_PREFIX . "setting (store_id, code, `key`, value, serialized) values ('0', 'module_lscache', 'module_lscache_product_list_recache_status', 'empty', '0')") ;
        $this->db->query(" insert into " . DB_PREFIX . "setting (store_id, code, `key`, value, serialized) values ('0', 'module_lscache', 'module_lscache_product_list_last_recached', '0', '0')") ;
        $this->db->query(" insert into " . DB_PREFIX . "setting (store_id, code, `key`, value, serialized) values ('0', 'module_lscache', 'module_lscache_product_list_recache_total', '0', '0')") ;

admin/controller/extension/module/lscache.php

            // add variables for recache product list
            
            if(!isset($oldSetting["module_lscache_product_list_recache_status"])){
                $oldSetting["module_lscache_product_list_recache_status"] = 'empty';
            }
            
            if(!isset($oldSetting["module_lscache_product_list_last_recached"])){
                $oldSetting["module_lscache_product_list_last_recached"] = '0';
            }
            
            if(!isset($oldSetting["module_lscache_product_list_recache_total"])){
                $oldSetting["module_lscache_product_list_recache_total"] = '0';
            }
            
            // end add

 

[AndreyPopov]

second step

to catalog/controller/extension/module/ lscache.php
add function that build full Product Urls List for recache and store it in database

    protected function BuildListOfProductUrls($categoryPath1) {
        
        $UrlsCount1 = 0;
        $this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "lscache_product_urls_list` ( `url_list_id` int(11) NOT NULL AUTO_INCREMENT, `lscache_product_url` varchar(255) NOT NULL, `recache_status` tinyint(1) NOT NULL, PRIMARY KEY (`url_list_id`)) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;");
        foreach ($this->model_catalog_product->getProducts() as $result) {
            foreach ($this->model_catalog_product->getCategories($result['product_id']) as $category) {
                if(isset( $categoryPath1[$category['category_id']] )){
                    $this->db->query("INSERT INTO " . DB_PREFIX . "lscache_product_urls_list SET lscache_product_url = '" . 'path=' . $categoryPath1[$category['category_id']] . '&product_id=' . $result['product_id'] . "' ");
                    $UrlsCount1++;
                }
            }

            $this->db->query("INSERT INTO " . DB_PREFIX . "lscache_product_urls_list SET lscache_product_url = '" . 'manufacturer_id=' . $result['manufacturer_id'] . '&product_id=' . $result['product_id'] . "' ");
            $UrlsCount1++;

            $this->db->query("INSERT INTO " . DB_PREFIX . "lscache_product_urls_list SET lscache_product_url = '" . 'product_id=' . $result['product_id'] . "' ");
            $UrlsCount1++;
        }
        
        $this->db->query("DELETE FROM " . DB_PREFIX . "setting WHERE store_id = '0' AND `code` = 'module_lscache' AND `key` = 'module_lscache_product_list_recache_status' ");
        $this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '0', `code` = 'module_lscache', `key` = 'module_lscache_product_list_recache_status', `value` = 'full'");
        
        $this->db->query("DELETE FROM " . DB_PREFIX . "setting WHERE store_id = '0' AND `code` = 'module_lscache' AND `key` = 'module_lscache_product_list_recache_total' ");
        $this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '0', `code` = 'module_lscache', `key` = 'module_lscache_product_list_recache_total', `value` = '" . $UrlsCount1 . "' ");

        return $UrlsCount1;

    }

 

[AndreyPopov]

third step
add function that prepare part of full Pruduct URLs list for crawling

    protected function BuildUrlsListForRecache($FirstItem,$LastItem {
     
        $UrlsList = array();
        for ($num_item = $FirstItem ; $num_item <= $LastItem ;  $num_item++ ) {
            $PathToRecache = (array)$this->db->query("SELECT `lscache_product_url` FROM `" . DB_PREFIX . "lscache_product_urls_list` WHERE url_list_id = '" . $num_item ."' " );
            $UrlsList[] = $this->url->link('product/product', $PathToRecache['row']['lscache_product_url'] );
        }
        return $UrlsList;
    }

 

[AndreyPopov]

fourth step
check if additional variables sets

        if ( !isset($this->lscache->setting['module_lscache_product_list_recache_status']) ) {
                        $this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '0', `code` = 'module_lscache', `key` = 'module_lscache_product_list_recache_status', `value` = 'empty' ");
                        $this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '0', `code` = 'module_lscache', `key` = 'module_lscache_product_list_recache_total', `value` = '0' ");
                        $this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '0', `code` = 'module_lscache', `key` = 'module_lscache_product_list_last_recached', `value` = '0' ");
        }

 

[AndreyPopov]

fifth step
build Product URLs List, prepare to recache

        if ( $this->lscache->setting['module_lscache_product_list_recache_status'] == 'empty') {
            $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "lscache_product_urls_list`");
            $this->db->query("DELETE FROM " . DB_PREFIX . "setting WHERE store_id = '0' AND `code` = 'module_lscache' AND `key` = 'module_lscache_product_list_recache_total' ");
            $this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '0', `code` = 'module_lscache', `key` = 'module_lscache_product_list_recache_total', `value` = '0' ");
            $this->db->query("DELETE FROM " . DB_PREFIX . "setting WHERE store_id = '0' AND `code` = 'module_lscache' AND `key` = 'module_lscache_product_list_last_recached' ");
            $this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '0', `code` = 'module_lscache', `key` = 'module_lscache_product_list_last_recached', `value` = '0' ");
            echo 'begin build product urls list for recache' . ($cli ? '' : '<br>') . PHP_EOL;
            $BuildListOfProductUrlsValue = $this->BuildListOfProductUrls($categoryPath);
            echo 'product urls list for recache built' . ($cli ? '' : '<br>') . PHP_EOL;
        }

 

[AndreyPopov]

final step
recache, remember last recached item, restart recache from last item

        $LastRecachedItem = $this->lscache->setting['module_lscache_product_list_last_recached'] + 1;
        $TotalUrlsInList = $this->lscache->setting['module_lscache_product_list_recache_total'];
        // crawl Urls from built Product List
        for ($i = $LastRecachedItem; $i <= $TotalUrlsInList; $i = $i+2042 ) {
            $LastRecacheItemNew = $i+2042;
            if ( $LastRecacheItemNew > $TotalUrlsInList ) $LastRecacheItemNew = $TotalUrlsInList;
            $UrlsCountCount = floor($i/2043)+1;
            echo 'recache '. $UrlsCountCount . ' part of product urls...' . ($cli ? '' : '<br>') . PHP_EOL;
            $UrlsTest = $this->BuildUrlsListForRecache($i,$LastRecacheItemNew);
            $this->crawlUrls($UrlsTest, $cli);
            $UrlsTest = array();
            $this->db->query("DELETE FROM " . DB_PREFIX . "setting WHERE store_id = '0' AND `code` = 'module_lscache' AND `key` = 'module_lscache_product_list_last_recached' ");
            $this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '0', `code` = 'module_lscache', `key` = 'module_lscache_product_list_last_recached', `value` = '" . $LastRecacheItemNew . "' ");
            echo 'recached product urls list from ' . $i . ' to ' . $LastRecacheItemNew . ' completed' . ($cli ? '' : '<br>') . PHP_EOL;
        }

 

[AndreyPopov]

may be solution not elegant, but it work!

with code above and ideas from here

PHP max_execution_time 3600s NOT MATTER!!!!

NOT problem with PHP memory exceed

full site recache for two UAs for 6500 products is 8 hours!!! (recache script started by crontab)

full site recache for me:
- recache all product pages
- recache all categories with pagination
- recache product catalog with pagination
- recache all manufacturers with pagination


P.S. some problems exists: for third and fourth steps script must be restarted each time.
setting value in database and reading "right now" not work. may be MySQL needed close connection?