Server overloaded.

[mixup]

Hello,

I have got this problem.
I use the latest opcart instalation, have the problems at to separate hosting providers.

When i import all my products and categories (11.000 products and 100 categories)
the server gets overloaded and my hosting provider shut my site down.

Is there anything i can change in the config that the site wont crashes the server?

mabey something with the cache or something??

regards Michiel

[Daniel]

split putting the products in to around 500 at a time.

its nothing to do with opencart if you importing 11,000 products.


11,000 proudcts will run fine on opencart.

[mixup]

hello daniel,

tnx for your reply. but the import goes well, but after i imported the products the pages load very slow (10 - 20 seconds to load)

And than it overload the server.

[Xsecrets]

hello daniel,

tnx for your reply. but the import goes well, but after i imported the products the pages load very slow (10 - 20 seconds to load)

And than it overload the server.

See this thread. I have it linked to my second vqmod post. Try that and it should help. http://forum.opencart.com/viewtopic.php?f=20&t=34851&start=20#p171445

[mixup]

oke im gonna try it, tnx


This is what the hosting company sended to me:

This query seems to be the problem:

SELECT COUNT( DISTINCT p.product_id ) AS total
FROM oc_product p
LEFT JOIN oc_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN oc_product_to_store p2s ON ( p.product_id = p2s.product_id )
WHERE pd.language_id = '2'
AND p.status = '1'
AND p.date_available <= NOW( )
AND p2s.store_id = '0'
AND p.product_id
IN (

SELECT p2c.product_id
FROM oc_product_to_category p2c
WHERE p2c.category_id = '485'
)

[Xsecrets]

oke im gonna try it, tnx


This is what the hosting company sended to me:

This query seems to be the problem:

SELECT COUNT( DISTINCT p.product_id ) AS total
FROM oc_product p
LEFT JOIN oc_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN oc_product_to_store p2s ON ( p.product_id = p2s.product_id )
WHERE pd.language_id = '2'
AND p.status = '1'
AND p.date_available <= NOW( )
AND p2s.store_id = '0'
AND p.product_id
IN (

SELECT p2c.product_id
FROM oc_product_to_category p2c
WHERE p2c.category_id = '485'
)

yes that is the exact query that the vqmod addresses. It doesn't eliminate the count query, but it rolls it in to the model and caches the results.

[Daniel]

working on a solution now.

[Daniel]

the solution is here:

http://code.google.com/p/opencart/source/detail?r=520

replace these files fromt he ones in the svn.

[Xsecrets]

the solution is here:

http://code.google.com/p/opencart/source/detail?r=520

replace these files fromt he ones in the svn.

I wouldn't jump on that just yet. You simply cannot do it with group by. I tried it every which way to do that and it's simply not possible. Daniel you need to take a close look at the data returned to make sure it's correct, because you cannot get a recursive lookup of all products in a category and it's subcategories using group by.

I have not looked over the whole solution yet, but the fact that it's using group by makes me very suspicious.

[Daniel]

its not using group its using COUNT(DISTINCT p.product_id)

[Daniel]

Code: Select all

   public function getProducts($data = array()) {
      if ($this->customer->isLogged()) {
         $customer_group_id = $this->customer->getCustomerGroupId();
      } else {
         $customer_group_id = $this->config->get('config_customer_group_id');
      }   
      
   //   $cache = md5(http_build_query($data));
      
      //$product_data = $this->cache->get('product.' . $cache . '.' . $customer_group_id);
      
   //   if (!$product_data) {
         $sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";
         
         if (isset($data['filter_category_id']) && $data['filter_category_id']) {
            $sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";         
         }
         
         if (isset($data['filter_tag']) && $data['filter_tag']) {
            $sql .= " LEFT JOIN " . DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id)";         
         }
         
         $sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
                     
         if (isset($data['filter_name']) && $data['filter_name']) {
            if (isset($data['filter_description']) && $data['filter_description']) {
               $sql .= " AND (LCASE(pd.name) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR p.product_id IN (SELECT pt.product_id FROM " . DB_PREFIX . "product_tag pt WHERE pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR LCASE(pd.description) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%')))";
            } else {
               $sql .= " AND (LCASE(pd.name) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR p.product_id IN (SELECT pt.product_id FROM " . DB_PREFIX . "product_tag pt WHERE pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%')))";
            }
         }
         
         if (isset($data['filter_category_id']) && $data['filter_category_id']) {
            if (isset($data['filter_sub_category']) && $data['filter_sub_category']) {
               $implode_data = array();
               
               $implode_data[] = $data['filter_category_id'];
               
               $this->load->model('catalog/category');
               
               $categories = $this->model_catalog_category->getCategoriesByParentId($data['filter_category_id']);
                              
               foreach ($categories as $category_id) {
                  $implode_data[] = "p2c.category_id = '" . (int)$category_id . "'";
               }
                        
               $sql .= " AND (" . implode(' OR ', $implode_data) . ")";         
            } else {
               $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
            }
         }         
                  
         if (isset($data['filter_tag']) && $data['filter_tag']) {
            $sql .= " AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_tag']) . "%')";
         }
         
         if (isset($data['filter_manufacturer_id'])) {
            $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
         }
         
         $sql .= " GROUP BY p.product_id";
         
         $sort_data = array(
            'pd.name',
            'p.model',
            'p.quantity',
            'p.price',
            'rating',
            'p.sort_order',
            'p.date_added'
         );   
         
         if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
               $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } else {
               $sql .= " ORDER BY " . $data['sort'];
            }
         } else {
            $sql .= " ORDER BY p.sort_order";   
         }
         
         if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC";
         } else {
            $sql .= " ASC";
         }
      
         if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
               $data['start'] = 0;
            }            
   
            if ($data['limit'] < 1) {
               $data['limit'] = 20;
            }   
         
            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
         }
         
         $product_data = array();
               
         $query = $this->db->query($sql);
      
         foreach ($query->rows as $result) {
            $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
         }
         
      //   $this->cache->set('product.' . $cache . '.' . $customer_group_id, $product_data);
      //}
      
      return $product_data;
   }




and

Code: Select all


   public function getTotalProducts($data = array()) {
      $sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";

      if (isset($data['filter_category_id']) && $data['filter_category_id']) {
         $sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";         
      }
      
      if (isset($data['filter_tag']) && $data['filter_tag']) {
         $sql .= " LEFT JOIN " . DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id)";         
      }
               
      $sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
      
      if (isset($data['filter_name'])) {
         if (isset($data['filter_description']) && $data['filter_description']) {
            $sql .= " AND (LCASE(pd.name) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR p.product_id IN (SELECT pt.product_id FROM " . DB_PREFIX . "product_tag pt WHERE pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%')) OR LCASE(pd.description) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%'))";
         } else {
            $sql .= " AND (LCASE(pd.name) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR p.product_id IN (SELECT pt.product_id FROM " . DB_PREFIX . "product_tag pt WHERE pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%')))";
         }
      }
                           
      if (isset($data['filter_category_id']) && $data['filter_category_id']) {
         if (isset($data['filter_sub_category']) && $data['filter_sub_category']) {
            $implode_data = array();
            
            $implode_data[] = "p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
            
            $this->load->model('catalog/category');
            
            $categories = $this->model_catalog_category->getCategoriesByParentId($data['filter_category_id']);
               
            foreach ($categories as $category_id) {
               $implode_data[] = "p2c.category_id = '" . (int)$category_id . "'";
            }
                     
            $sql .= " AND (" . implode(' OR ', $implode_data) . ")";         
         } else {
            $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
         }
      }         
               
      if (isset($data['filter_tag']) && $data['filter_tag']) {
         $sql .= " AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_tag']) . "%')";
      }
      
      if (isset($data['filter_manufacturer_id'])) {
         $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
      }
      
      $query = $this->db->query($sql);
      
      return $query->row['total'];
   }


[mixup]

oke im gonna try it, tnx


This is what the hosting company sended to me:

This query seems to be the problem:

SELECT COUNT( DISTINCT p.product_id ) AS total
FROM oc_product p
LEFT JOIN oc_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN oc_product_to_store p2s ON ( p.product_id = p2s.product_id )
WHERE pd.language_id = '2'
AND p.status = '1'
AND p.date_available <= NOW( )
AND p2s.store_id = '0'
AND p.product_id
IN (

SELECT p2c.product_id
FROM oc_product_to_category p2c
WHERE p2c.category_id = '485'
)

yes that is the exact query that the vqmod addresses. It doesn't eliminate the count query, but it rolls it in to the model and caches the results.

made the changes and the site works great!!!
tnx!

[jfn99]

I got a godaddy VPS with over 3GB of ram, and have 2 opencart stores v.1.5.1.3 and v.1.5.2.1 runing.
the server has been very slow lately. the sever load is getting to 17.5... after contacting the hosting they said the MySql server is causing issue related to the 2 sites...

http://safirabeauty.com/ and
http://glamoroustore.com/

does anybody have any idea what could be causing this?
thanks

[jfn99]

I guess this is the SQL query causing the load

SELECT COUNT( DISTINCT p.product_id ) AS total
FROM product p
LEFT JOIN product_description pd ON ( p.p

[Solan]

the solution is here:

http://code.google.com/p/opencart/source/detail?r=520

replace these files fromt he ones in the svn.

The page is no longer working and i would like to have the solution to since i have the same problem...

[Solan]

No answer since October..

Still have problem solwing this issue when the link is not working

[Daniel]

upgrade to the latest. it handles categories a lot better.