Post by jcsmithy » Mon Jun 20, 2011 2:38 am

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

Code: Select all

SELECT COUNT(*) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '1' AND 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%'))
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?
Last edited by jcsmithy on Mon Jun 20, 2011 4:07 pm, edited 1 time in total.

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm

Post by rph » Mon Jun 20, 2011 3:19 am

Does your host keep MySQL slow query logs at all?

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by jcsmithy » Mon Jun 20, 2011 3:37 am

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

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm

Post by teamsleep » Mon Jun 20, 2011 9:24 am

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.

Newbie

Posts

Joined
Fri Jun 17, 2011 8:14 am

Post by Xsecrets » Mon Jun 20, 2011 9:35 am

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%'))

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by ikhlief » Mon Jun 20, 2011 12:49 pm

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

Active Member

Posts

Joined
Wed Nov 24, 2010 9:15 pm

Post by jcsmithy » Mon Jun 20, 2011 3:47 pm

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.

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm

Post by jcsmithy » Mon Jun 20, 2011 4:38 pm

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

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm

Post by Xsecrets » Mon Jun 20, 2011 9:19 pm

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.

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%'

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by jcsmithy » Mon Jun 20, 2011 9:39 pm

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?

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm

Post by Xsecrets » Mon Jun 20, 2011 10:23 pm

ok try this query and see what happens.

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.product_id = p.product_id AND pt.tag LIKE '%cat%'))
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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by jcsmithy » Mon Jun 20, 2011 11:02 pm

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

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm

Post by Xsecrets » Mon Jun 20, 2011 11:22 pm

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by jcsmithy » Mon Jun 20, 2011 11:39 pm

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



Anyway, now onto the SEO....

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm

Post by rph » Tue Jun 21, 2011 1:53 am

jcsmithy wrote:Any Idea's on the SEO problem?
Check if query and keyword are indexed in the url_alias table.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by jcsmithy » Tue Jun 21, 2011 5:40 am

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....

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm

Post by Xsecrets » Tue Jun 21, 2011 6:03 am

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by jcsmithy » Tue Jun 21, 2011 6:14 am

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.

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm

Post by Xsecrets » Tue Jun 21, 2011 6:16 am

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by jcsmithy » Tue Jun 21, 2011 6:24 am

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...

Active Member

Posts

Joined
Fri Oct 01, 2010 9:54 pm
Who is online

Users browsing this forum: No registered users and 47 guests