[1.5.0.5] Performance

Hi guys...

I'm having EXTREME performance Issues with 1.5 at the moment, I think it's largely due to having a many products within the store I'm setting up, but none of these issues were present in the old version that was being run.

Currently I have 17500 products, with 400 categories.


As it stands, I can't use SEO because of the time it's taking to load - on average it's taking about 10seconds to load each page. I managed to figure out it was because of the amount of categories that exist. If I were to disable all the categories the page load time would be minute. This is obviously something we must have enabled, but as it stand I can't...

My second problem is down to the search function - as soon as you search for something it's just bringing the whole site down to a halt for 10mins or so, for me and everyone else. Its just completely unuseable! Once again I've managed to find the cause of it to be the DB query I've ran this on my localhost to see the time it'd actually take... I started it about 15mins ago and I'm still waiting - and no it hasnt crashed I believe - its STILL running the query.


The two things alone are just so bad I'ts making this store unuseable... SEO is a must and a store without a seach facility is rather bad! I have just read a post by Daniel about 1.5 being better at handling large amounts of data but so far it seems 1000x worse.


Both these problems where not present in the old 1.9.4 version, it's actually running quite nicely even with the amount of products / categories... But we really would like 1.5 running.



Any suggestions?

Does your host keep MySQL slow query logs at all?

No Idea to be honest, but as I said I've ran this same query on local host running a copy of that DB - same issue

You can submit a support request to your hosting provider to check your slowlogs for you. You will also want to ask if you are hitting a preset memory limit if you are using a shared hosting plan.

try this db query and see if it's any different. It's the same result, but I've just moved one thing around which should allow the db to take better advantage of indexes.

I have the same problem of slow website after upgrading from 1.4.9.5 to 1.5.0.5, I have products below 1000 product, althought my website is slow.
I posted a thread here but no answer
http://forum.opencart.com/viewtopic.php ... 2&p=166212

Please help

Xsecrets wrote:try this db query and see if it's any different. It's the same result, but I've just moved one thing around which should allow the db to take better advantage of indexes.

Code: Select all

SELECT COUNT(*) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id AND pd.language_id = '1') LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND (LCASE(pd.name) LIKE '%cat%' OR p.product_id IN (SELECT pt.product_id FROM product_tag pt WHERE pt.language_id = '1' AND pt.tag LIKE '%cat%'))

No Joy as of yet - I've ran the query and so far after 5 minutes I'm still waiting.

Just thought I'd report back now the query has finished...

Ran on localhost, directly into sql using Heidi.

/* 0 rows affected, 1 rows found. Duration for 1 query: 801.876 sec. */

nearly 14 minutes!

Also, I set the slow query as suggested, result was:

# Query_time: 801.874000 Lock_time: 0.002000 Rows_sent: 1 Rows_examined: 250187750

You must have some problem with your database I simply should not take that long to get through less than 20,000 products. Do you have lots of tags on each product? I suppose if you have lots of tags that could increase the number of rows searched exponentially.

try it without the tags.

Cheers for the reply.

Yep, each product has 1x tag, so in total about 17500 tags..

I've left the tags in place in the DB, but removed the query for them just like you've suggested and it is working ok now - querytimes are now down to 0.20 secs... clearly a lot better than 801.87 secs!

Obviously I had to do the same for the actual query to retrieve the products aswell (not just total), bringing it to roughly 1/2sec- for the size of the store I cant really complain about this!


Something that i'd suggest removing from the 1.5, can't really claim it can handle larger stores better than 1.4.9 when this problem exists, is a shame though.



Any Idea's on the SEO problem?

ok try this query and see what happens. if that one is better, but still a little slow try adding an index on product_id in the product_tag table. and see what happens.

Think it's no joy on that one too on it's own, probably been about.... 5mins now? still running...


But!
I ran that query after adding the index on the product_id column - query time went down to 0.478 secs!

nice job figuring that out...



I that ran the original query, one that OC has as standard, and the query time for that was also great, pretty much exactly the same after running 10-15times.


Perhaps, that index should be put in place on the default install? What's the down side for doing this?



Thanks for that anyway

not really a downside. I just don't think anyone has had time to go over 1.5.x for performance yet. Lots of stuff is the same, but the product queries changed quite a bit.

Yup I had noticed, and I like the logic behind retreving the product details (1 query instead of loads).



Anyway, now onto the SEO....

jcsmithy wrote:Any Idea's on the SEO problem?

Check if query and keyword are indexed in the url_alias table.

Query speeds are absolutely fine, this I've already tested (>0.03), but from what I have worked out, something weird is going on...

Added Qphoria's DB log mod to see what was going on, and what I found that on each page load, the file is deleted and re-populated about 3-4times, up until the point the page finishes loading...

Now, I dont completely know the in's and out's of how OC works when it comes to the barebone of the system, but from what I gather this file should be created only ONCE per page load, so, would this mean everything is running several times?

Perhaps Q might mention about this? Hopefully....

jcsmithy wrote:Query speeds are absolutely fine, this I've already tested (>0.03), but from what I have worked out, something weird is going on...

Added Qphoria's DB log mod to see what was going on, and what I found that on each page load, the file is deleted and re-populated about 3-4times, up until the point the page finishes loading...

Now, I dont completely know the in's and out's of how OC works when it comes to the barebone of the system, but from what I gather this file should be created only ONCE per page load, so, would this mean everything is running several times?

Perhaps Q might mention about this? Hopefully....

my guess is that there have been ajax call added, and each time an ajax call happens it runs back through the system and starts the log over again.

I did think of this, but didnt look into it tha much, perhaps I'll have another look.

But, if this is the problem, I'm guessing it shouldn't reload litterally everything again (including re-generating seo links etc)


I'll have another look at the ajax.

jcsmithy wrote:I did think of this, but didnt look into it tha much, perhaps I'll have another look.

But, if this is the problem, I'm guessing it shouldn't reload litterally everything again (including re-generating seo links etc)


I'll have another look at the ajax.

it doesn't reload everything. It will only generate the html from the tpl being rendered, so no header and meta tag etc, but it does initialize the engine again which creates a new connection to the database which clears the db log since that's the way it's written.

But then, surely the last, surviving log would only contain the DB calls that were made for that AJAX call?

Thats not actually happening - it's returning everything, including category, product and SEO calls...



Something else I've just tried while figuring out what you said...

• Internet Explorer : creates DB log 7 times;
• Firefox : creates DB log 1 time (javascript enabled);
• Chrome: creates DB log 3 times

Just getting more and more confusing...