Post by thbor83 » Tue Aug 28, 2012 2:42 am

Hello everyone,

I have got a problem on of the VPS servers I run.
We have several opencart installations on that server but one of them are using 99% cpu when starting a search.

Have tried to optimize the mysql my.cnf file but this hasn't helped yet. The site has around 7000 product and 40-50 categories.

I the mysql-slow.log file I get this message:
SELECT COUNT(DISTINCT p.product_id) 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) LEFT JOIN product_tag pt ON (p.product_id = pt.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND ( LCASE(pd.name) LIKE '%product10%' OR LCASE(pt.tag) LIKE '%product10%' AND pt.language_id = '2');

Does anyone know what that can be the problem here?

Newbie

Posts

Joined
Fri Nov 04, 2011 4:00 pm

Post by Avvici » Tue Aug 28, 2012 3:14 am

My advice? Use APACHE LUCENE SEARCH. Most don't know about this but Justin from Host Jars made a version for Open Cart. It will enhance your search process and provide less strain. Why>? Because it creates an index outside in a folder.

You can read up on it here: http://lucene.apache.org/core/

And purchase the mod here: http://opencart.hostjars.com/search-improved

User avatar
Expert Member

Posts

Joined
Tue Apr 05, 2011 12:09 pm
Location - Asheville, NC

Post by thbor83 » Tue Aug 28, 2012 4:16 am

Thanks avvici, I will read up on apache lucene and see if that is something that will help me :)

Are there other things that can cause this? I have had many opencart installations on my server but never had this problem before. Of course I haven't had sites with 7000 products before either.

I have removed product count on both main category and sub category with this module: http://www.opencart.com/index.php?route ... h=Category speed up / Remove product count / Remove Category Count / Opencart speedup / Improve page load time

Newbie

Posts

Joined
Fri Nov 04, 2011 4:00 pm

Post by Avvici » Tue Aug 28, 2012 6:02 am

Open cart is not set up for advanced search, concerning thousands and thousands of products. Take my advice;)

User avatar
Expert Member

Posts

Joined
Tue Apr 05, 2011 12:09 pm
Location - Asheville, NC

Post by thbor83 » Tue Aug 28, 2012 2:01 pm

avvici wrote:Open cart is not set up for advanced search, concerning thousands and thousands of products. Take my advice;)
Oh I will take you advice without any doubt :)
But there has to be more to it since this also happens on the front page as well.

And I have turned off banners and don't have any products on the front page at the moment. Still it takes about 6-7 seconds to load the front page.

Link to the shop: http://tinyurl.com/9254glg

Newbie

Posts

Joined
Fri Nov 04, 2011 4:00 pm

Post by Avvici » Tue Aug 28, 2012 10:17 pm

You have barely any categories (which is what would be causing your home page to load a little slow) Who is your shared hosting because it is obviously shared hosting. The home page took me 5 seconds to load which is dreadfully slow for not categories.

User avatar
Expert Member

Posts

Joined
Tue Apr 05, 2011 12:09 pm
Location - Asheville, NC

Post by thbor83 » Tue Aug 28, 2012 11:07 pm

The site isn't a shared hosting, it's my private server. (At my own office) It's an vm in esxi 5.

On this server there is 3 opencart installations and 1 joomla installtion.
All of the other sites loads just fine but not this one. And it's the only one that uses alot of cpu.

The category amount for two of the sites are almost the same but not the product amount. The other site that has almost the same category amount loads in 1 second (Haven't optimized that.)

Newbie

Posts

Joined
Fri Nov 04, 2011 4:00 pm
Who is online

Users browsing this forum: Majestic-12 [Bot] and 65 guests