HostGator shared server and Prestashop 1.5.4.1 - Whos side is the issue?

[Dolke]

Hi to all,

 

You have already noticed that I have issues with my Prestashop website which is hosted on the HostGator shared server.

 

After deeply examination and several hours talk over the phone this is what I get from their server admin on the issue of slow frontend search and 500 internal server errors that are occurring if 2+ visitors are searching the website at the same time.

 

 

Hello there! After analyzing your site, I have determined that the cause of the trouble is actually the caching system, cacheFs. The more I use the site, the more files that appear in the `~public_html/cache/cachefs` folder. I just brought it up to over 40,000 files.

 

It looks like PrestaShop is attempting to read a whole lot from these thousands of cache files, and it's having the opposite effect of what caching is supposed to do. Your site slows down because of the enormous number of read operations required.

 

When I cleared out the cache, there was still another problem. For each typing keystroke you make in the search bar, an AJAX request is sent to search for something from the slow MySQL database. It's good that MySQL is able to return results relatively quickly... but not quick enough. I type at over 100 words per minute, and if I typed a sentence into the search bar, queues would pile up, searching for whatever I had typed in at the time and completing the search despite my searching for a different string now. By a stroke of luck, I was able to capture one of these queries in action:

 

---------- TECHNICAL DETAILS ---------- SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, DATEDIFF( product_shop.`date_add`, DATE_SUB( NOW(), INTERVAL 7 DAY ) ) > 0 AS new, MAX(product_attribute_shop.id_product_attribute) id_product_attribute FROM `ps_product` p INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_lang` `pl` ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = 7 AND pl.id_shop = 1 LEFT JOIN `ps_image` `i` ON i.`id_product` = p.`id_product` LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `ps_image_lang` `il` ON i.`id_image` = il.`id_image` AND il.`id_lang` = 7 LEFT JOIN `ps_manufacturer` `m` ON m.`id_manufacturer` = p.`id_manufacturer` LEFT OUTER JOIN `ps_product_attribute` pa ON p.`id_product` = pa.`id_product` LEFT JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.default_on = 1) LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 ) WHERE (product_shop.`active` = 1) AND (product_shop.`visibility` IN ("both", "catalog")) AND ( DATEDIFF( product_shop.`date_add`, DATE_SUB( NOW(), INTERVAL 7 DAY ) ) > 0) AND (p.`id_product` IN ( SELECT cp.`id_product` FROM `ps_category_group` cg LEFT JOIN `ps_category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE cg.`id_group` = 1)) GROUP BY product_shop.id_product ORDER BY p.`date_add` DESC LIMIT 5 ----------

 

The large number of JOINs and calculations (such as DATEDIFF and DATE_SUB) are very resource intensive and take up some 0.01 seconds of time. But I type fast, and there are other people on the reseller server.

 

Combined with the caching problems I addressed earlier and the overhead required in the Apache web server and the PHP processes that are spawned for each AJAX request, the search for one letter takes way too long.

 

My recommendation would be to disable the AJAX search feature, as it is simply not efficient enough. Then, you should reconsider the disk caching algorithms to use fewer files. In short: reduce database usage, reduce file operations.

 

If the search feature was developed by PrestaShop, then you may wish to consider informing them of this serious design flaw. MySQL is doing a great job, Apache is moving smoothly, and the server is comfortable.

 

This is not a server problem. I hope this information has been helpful in pointing you to the right direction.

 

Please don't hesitate to follow up if you have any more questions or concerns. Sincerely, Nick L. Linux Systems Administrator HostGator.com, LLC http://support.hostgator.com

 

So I would like to make this final stand here.

Is this server or Prestashop related issue? Both sides have their own story...