Over 1000 database querys for one page load

[jungwirbtgut]

Hello Guys,

I got a really difficult problem with my shop and scaling system.

We have a shop with +1000 products and we do launches every month, that means we got around 500-800 customers in one our who checkout.

And we already had been on a 32core with 128gb ram and the server crashed in TOTAL. It was too much performance for the server and its not possible to checkout for any user.

Now we moved to a cloud system which we can scale, that means during a launch we scale the cloud system to 20-30 webnodes 4cores and 8gb ram and scale DB to 96 cores.

 

Than the server is not crashing.

BUT.. the server it TOTALLY slow. We now tried different cloud systems and hosting provider and all of them telling me that the problem is a prestashop problem itself.
 

A cloud system works with 3 server minimum, that mean webfiles, database and storage are not LOCAL on one server, they are seperated to scale them.

The main problem which occurs is, that prestashop is doing for a normal page view a database query from 1000, which is very very much, thats why the server or the whole page is very slowly.

 

When we got the shop on a local system, the page is fast, but when we do it on a cloud system, its sooo slow, because the database querys are too slow and too much which are loading while a page is load.

 

Do you have any idea? Any expert who can help me with that case?
I'm tried to solve that problem month right now and it drives me crazy.

Thank you!
Its this page

[joseantgv]

Start by enabling PROFILING (https://sumselkawumsel.de/prestashop-1-7-how-to-enable-the-profiling-information-to-check-load-times/) and check if you see anything wrong.

[musicmaster]

What puzzles me most is the path for the Jquery. This is an area that I don't understand very well. But I am wondering whether a direct link might work better.

[jungwirbtgut]

14 hours ago, joseantgv said:

Start by enabling PROFILING (https://sumselkawumsel.de/prestashop-1-7-how-to-enable-the-profiling-information-to-check-load-times/) and check if you see anything wrong.

Thank you!

 

Home page: 2296 ms - You'd better run your shop on a toaster / 591 queries
product page: 4406ms - 684 queries
cart: 3319ms - 667 queries

In Backoffice, I can see and it says when I open the "orders" tab and only show 50 orders: Load time: 2694 ms - You'd better run your shop on a toaster 🤧

Edited March 25, 2021 by jungwirbtgut (see edit history)

[jungwirbtgut]

39 minutes ago, musicmaster said:

What puzzles me most is the path for the Jquery. This is an area that I don't understand very well. But I am wondering whether a direct link might work better.

Could you may explain in more detail? I don't understand really.. How should a path normally look like?

[musicmaster]

10 hours ago, jungwirbtgut said:

Could you may explain in more detail? I don't understand really.. How should a path normally look like?

Without webpack. But I know too little about Webpack to give more details.

[joseantgv]

En 24/3/2021 a las 9:47 PM, jungwirbtgut dijo:

Thank you!

 

Home page: 2296 ms - You'd better run your shop on a toaster / 591 queries
product page: 4406ms - 684 queries
cart: 3319ms - 667 queries

In Backoffice, I can see and it says when I open the "orders" tab and only show 50 orders: Load time: 2694 ms - You'd better run your shop on a toaster 🤧

Check the queries detail. Maybe you have some slow query.

[nick_developer]

Hi have you solved the performance issue? 
I notice that on the homepage there are a lot of queries about 300 / 600 queries. (with no extra modules installed). 

Your hardware is quite huge, and your store has not so much products, but the customers are a high number. 
Have you tried to optimize the basic queries ? 

[jungwirbtgut]

On 4/3/2021 at 3:50 PM, nick_developer said:

Hi have you solved the performance issue? 
I notice that on the homepage there are a lot of queries about 300 / 600 queries. (with no extra modules installed). 

Your hardware is quite huge, and your store has not so much products, but the customers are a high number. 
Have you tried to optimize the basic queries ? 

I don't know how to optimize. I got an agency for that now. 
You also can optimize the queries?

[nick_developer]

On 4/6/2021 at 8:13 AM, jungwirbtgut said:

I don't know how to optimize. I got an agency for that now. 
You also can optimize the queries?

Hi, we could optimize the main query system but it's not such a good move because after that you can't update Prestashop without losing the optimization. You could make a an override but I don't know how it's good. I've done  search in the code but I haven't found anything causing the problem but a big number of queries. 
So I think that's the way of how it's built. 

I'm talking about stores with 100.000 or more products, with no combinations (all single products).
Most of the pages are really fast, the only two that TTFB time it's a bit high. It's weird because the server waits before TTFB, I'm wondering during this time it's working on the queries of the products in homepage.