Slow website, mysql queries taking too long

[dave_d]

Hello Everyone,

 

We've been using prestashop on a small scale for quite awhile already and we've found it very useful for sites with less than 1000 products.

 

However recently we undertook a migration of a existing ecommerce site to prestashop.

 

We did all the proper tasks to properly migrate the products from the old site to the new development prestashop website. All the products totaled around 13,000 with around 9,000 combinations.

 

It's when we started to do tests that to our surprise we discovered that we were experiencing very slow performance. Just loading the first page took 8-15 seconds and every click in any category would just be the same slow performance.

 

The back office is fine though, it's snappy. It's just the website that's taking too long to display.

 

We investigated further and we found that the sql queries done by prestashop was the cause of the slowdown.

 

We already did most of the recommendations found on various sources on the internet including mysql innodb and memcached and although it did improve things, it's still not acceptable for each page to take 8-15 seconds to display.

Edited November 10, 2012 by dave_d (see edit history)

[dave_d]

Just to add;

 

I'm using a dedicated i7 server running Linux/Cpanel, 8GB RAM. The weird thing is after all the optimization efforts, the old ecommerce site we're trying to migrate out of is now blazing fast even with hundreds of concurrent users while our prestashop install located in the same machine can't even spit out webpages with just us developers testing it.

[ZenGraph]

We investigated further and we found that the sql queries done by prestashop was the cause of the slowdown.

 

 

Hello,

 

that is obvious, but.. what kind of hosting are you using for you website..is it a VPS or a dedicated server ?? if not.. don't even dream about using prestashop correctly on shared hosting plan.... that is not a good point it will cause too many slow down problems and you'll get so many nodes (if using layered navigation) that most of shared hoster will kick you out of their buisness.

 

Best Regards,

Zen

 

 

ok.. i juste read you post about dedicated server... so.. there might be a real problem on prestashop website.. what version of prestashop are u using.. and did you install external modules ?

 

is you compare osc with presta for speed.. there is no point to do that.. OSC is as fast as a ferrarri even without cache system..lol

Edited November 10, 2012 by ZenGraph (see edit history)

[dave_d]

Thank you for the prompt reply...

 

We're using Prestashop version 1.5.1 with just the built-in theme (we only modified the colors). I've already disabled all the modules as a test and I still get the long mysql queries.

 

Here's the query that's really slow;

 

 

SELECT product_shop.id_product, product_attribute_shop.id_product_attribute FROM `ai_ps_product

Edited November 10, 2012 by dave_d (see edit history)

[dave_d]

FYI.

 

We were able to solve it by modifying the default prestashop code in regards to how it queries.

 

So basically, this software will still need some work so it will scale up properly for large stores.

[Dh42]

When you say 9000 combos, do you mean for one product that it has 9k combos?

[bellini13]

it is generally considered good practice to provide the solution details on how you resolved the problem. As you came to the forums seeking help for your issue, there are likely others that will benefit from your feedback

[dave_d]

In a nutshell, our in-house database specialist made mods to the stock code the does the SELECT queries.

 

There are some nested queries in the stock code that work fine but slowly get slower and slower the larger the number of products & combos you put in your store. So after reading all the help here to speed up the server and unable to fix it, it had to be the stock code that needed some tweaking.

 

1. Isolate the slow queries using the mysql show processes.

2. Parse the code to find the culprit

3. Re-write them to avoid inefficient nesting yet return the same result required to complete the query

 

So now every page only takes 2-3 seconds to display no matter how much products or combinations we put in. There are other code we think can benefit from further optimization (like the add to cart process) so we will make more changes as required.

 

Again I reiterate from the 1st post we've been using prestashop for awhile now and most installs go thru without issues but this issue only became apparent due to the large amount of products this client had. The back office never experienced any speed issues, it's only the front end site that needed modification to allow it to remain fast vis-a-vis a smaller prestashop install.

[Dh42]

I was just asking about the combo's. If you are running products with a lot of combo's on one product you can use attribute wizard pro and it will speed things up a lot by changing how the combinations work.

[oshy]

dave_d can you give us more details about what you've done to fix the codes .

 

thanks