Slow site after user login

[PureWebDev]

We have had an issue recently where our site http://www.puremtb.com.au slows down once a user creates an account and logs in.

We had our mysql connection terminated by one host stating that mysql resource usage was excessively high.

We subsequently migrated to a new host and are experiencing similar issues.

It seems the issue is the same. The new host even put us on a new server that they will be putting into production the server had no users apart from us had 8Gb ram and a total of 16 processing cores and still we had the same issue.

The site runs well and the visitors can browse the catalog fine until they log in, once logged in the site slows to a crawl with some pages taking 30-60 seconds to load.

We thought it may have been the statistics modules and subsequently disabled those modules and trimmed the database of 500k rows of stats but the issue persisted.

The site was moved to the new host without issues, we then tried to move to another server they have and noticed the following error:

Incorrect key file for table '/tmp/#sql_fc9_0.MYI'; try to repair it

 SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`,
  i.`id_image`, il.`legend`, t.`rate`
 FROM `ps_product` p
 LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1)
 LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
 LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1)
 LEFT JOIN `ps_tax` t ON t.`id_tax` = p.`id_tax`
 LEFT JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product`)
 INNER JOIN `ps_category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
 INNER JOIN `ps_customer_group` cg ON (cg.`id_group` = ctg.`id_group`)
 WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
 AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= '2010-04-20' AND `reduction_to` >= '2010-04-20'))
 AND p.`active` = 1
 AND (cg.`id_customer` = 311 OR ctg.`id_group` = 1)
 ORDER BY RAND()

We thought that may be due to the import/account transfer process of the control panel so we subsequently backed the database up and restored it and the error is the same. The error wasn't there with the first host nor was it there with the second. It did appear when we moved from server to another with the second host and surely may have something to do with our problem.

The error stops the items in the right side bar and footer from loading and is only when a user is logged in.

I can browse the siteadmin fine and have great response times with the page loads.

We have had to disable the store as it is causing load issues on the server and we do not wish to be suspended again.

I would appreciate any assistance in this matter as we have now been through 2 hosts and a total of four servers and the issue is still there.

Since it is a live production site would it be advisable to try the new 1.3 version?

Thanks

Che
PureMTB

[tomerg3]

Try to disable the tag cloud, let me know if that helps.

As for the mysql error, try to check the tables (using phpmyadmin), if you see an error, run the repair command.

[PureWebDev]

I have disabled the tag cloud and I am still experiencing the problem. The site can be browsed fine until I try and log in.

Then whilst I wait for the logged in page the server load shoots up due to mysql.

Secondly I have checked each of the tables in phpmyadmin and they all seem ok.

What would /tmp/#sql_fc9_0.MYI be?

I am happy to demonstrate the issue if required, but at the moment need to keep the store closed as it is not functional.

Any assistance would be greatly appreciated.

[tomerg3]

Do you see anything in the mysql slow query log?
It can be found on most hosts at /tmp/mysql_slow_queries, if you have files there and can identify a really slow query, I may be able to help.

[PureWebDev]

I disabled the Specials module and all is ok now.

[Patric]

I disabled the Specials module and all is ok now.

Are you talking about the PrestaShop default Specials module or a third party module?

[PureWebDev]

Hi Patric,

This was the default PrestaShop Specials module.

As you will see when you view the site, we use a large number of the available PrestaShop resources. Traffic recently increased to above 250 uniques per day on a consistent basis (after 6 months trading) this is when to problem occurred.

We had to systematically turn off every module before we discovered the problem.

regards

Che

[PureWebDev]

I'm still noticing the following slow queries in mysql. Can anybody shed some light on these queries and why they would be taking so long to execute?

# Time: 100421 12:03:01
# User@Host: puremtb[puremtb] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 20  Rows_examined: 446007
use puremtb_shopdb;
SELECT t.name, COUNT(pt.id_tag) AS times
       FROM `ps_product_tag` pt
       LEFT JOIN `ps_tag` t ON t.id_tag = pt.id_tag
       LEFT JOIN `ps_product` p ON p.id_product = pt.id_product
       LEFT JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product`)
       INNER JOIN `ps_category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
       INNER JOIN `ps_customer_group` cg ON (cg.`id_group` = ctg.`id_group`)
       WHERE id_lang = 1
       AND p.active = 1
       AND (cg.`id_customer` = 313 OR ctg.`id_group` = 1)
       GROUP BY t.id_tag
       ORDER BY times DESC
       LIMIT 0, 20;
# Time: 100421 12:03:03
# User@Host: puremtb[puremtb] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 30  Rows_examined: 446017
SELECT t.name, COUNT(pt.id_tag) AS times
       FROM `ps_product_tag` pt
       LEFT JOIN `ps_tag` t ON t.id_tag = pt.id_tag
       LEFT JOIN `ps_product` p ON p.id_product = pt.id_product
       LEFT JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product`)
       INNER JOIN `ps_category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
       INNER JOIN `ps_customer_group` cg ON (cg.`id_group` = ctg.`id_group`)
       WHERE id_lang = 1
       AND p.active = 1
       AND (cg.`id_customer` = 313 OR ctg.`id_group` = 1)
       GROUP BY t.id_tag
       ORDER BY times DESC
       LIMIT 0, 30;

and this one...

# Time: 100421 13:03:19
# User@Host: puremtb[puremtb] @ localhost []
# Query_time: 6  Lock_time: 0  Rows_sent: 20  Rows_examined: 984378
use puremtb;
SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`, i.`id_image`, il.`legend`, t.`rate`, (p.`reduction_price` + (p.`reduction_percent` * p.`price`)) AS myprice, m.`name` AS manufacturer_name
       FROM `ps_product` p
       LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1)
       LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
       LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1)
       LEFT JOIN `ps_tax` t ON (t.`id_tax` = p.`id_tax`)
       LEFT JOIN `ps_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
       LEFT JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product`)
       INNER JOIN `ps_category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
       INNER JOIN `ps_customer_group` cg ON (cg.`id_group` = ctg.`id_group`)
       WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
       AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= '2010-04-21' AND `reduction_to` >= '2010-04-21'))
       AND p.`active` = 1
       AND (cg.`id_customer` = 313 OR ctg.`id_group` = 1)
       GROUP BY p.`id_product`
       ORDER BY p.`price` ASC
       LIMIT 0, 20;

Thanks

Che

[Patric]

Can you post this problem into the bug tracker ?
Thanks.

[tomerg3]

The first 2 queries are to do with tags, and calculating the most popular ones to display.

There really is no need to rerun those queries since that info changes very rarely.
I have create a little "cache" system for that for a client, so the long query would not need to run every time.
I have not had the time to publish it, but I will when I get a chance in this thread http://www.prestashop.com/forums/viewthread/45000/development/slow_database_queries_identifying_and_fixing_dot_

[xavriley]

For anyone else suffering similar problems, try the following. If you have slow query logging for mysql then identify the slow queries. If you put 'EXPLAIN' before the select eg

'EXPLAIN SELECT * FROM...'

then mysql will tell you how many rows its analyzing and what keys its using. One thing to watch out for is that you might have the right keys to make a fast query but the table is somehow missing them. Often if you run a repair on the offending tables that should reassert the keys and everything will work properly.

Hopefully that wasn't too off topic...