Slow Performance - 800,000 items

[tampasounds]

Hello all, Running VM 2.0.18
We have 800,000 items (no images) in our database and i know it may sound silly but search times have gotten really bad.

It wasnt always that slow though. Somewhere between 2.0.6 and 2.0.10 something slowed the searches to a crawl.
Average search result time now takes about 30 seconds. Before less than 4 seconds.

Today I will attempt to index the table but am still left wondering what else we can do to get a little more
performance.

Any advice greatly needed and appreciated.

Thanks in advance.

[jjk]

I would try how much of a difference it makes without the slideshow or less images in your slideshow (I think your slideshow loads a lot of files) and also avoid Google webfonts and Google jquery. And maybe you are using cufon with externally hosted (slow) fonts. Perhaps you gain 10 seconds without all this stuff ...

[tampasounds]

Hi thanks for the quick response.
Our search results page does not use the movie as on the homepage.
We also do NOT use any product images...
The test part i am search for also returns only 1 result (not like there are pages and pages)

The homepage (with movie) with no search result loads in 2-4 seconds. (This tells me its not a server issue or Joomla issue)
Today searches are taking between 30-60 seconds.
I suppose thats better than a 500 error but still is very bad for business.

I saw another guy claiming 2.5 second response time with 450k items and 150+ categories..

Still seeking answers , Thanks in advance

UPDATE: I have turned off all other scripts to try and save time but seems like no difference.
Even after getting search result when clicking details page still take 20+ seconds just to get into part deatails page.

Is 800k simply too much for VM2 to handle ? I could have sworn with same amount it was running fine before
the holidays.

[PRO]

no its not,

1st is to have a fast template

all plugins that can be disabled should be

have you posted a url?

[tampasounds]

Hi Pro

I sent you a PM with the url since the site is live and we dont want google to pick up this thread when people search our name

Thanks again

[PRO]

1st

Use JCH optimize to gzip your css files
http://extensions.joomla.org/extensions/core-enhancements/performance/site-performance/12088

^^ CSS files ONLY

That will speed it up.


THEN.
you are loading 2 different Jquery files

You only need 1

THEN

On product page you are loading the "modal"

Vmart has switched to fancybox.
So, you might need to edit your template override to NOT call the modal code.

Just those changes will speed up your site a good bit

[tampasounds]

Yes we already use JCH Optimize and love it. I had it turned off to try diagnose the issue.
I was just recommending it to another guy in forum not even 5 mins ago.

With JCH on, the home page pulls very fast but not the search results unfortunately.

I will look into the modals as recommended. I think i may even be using prettybox in some areas.

[PRO]

what happens when you use the Joomla search module && enable PRODUCTS only for search??


There is a plugin where you can enable products, categories etc.

[tampasounds]

Managed to knock 7-12 secs right off the top today tweaking the php.ini

If others are following this keep in mind this tweak was Netslol specific

Before memory_limit = 32M                After memory_limit = 64M
Before output_buffering = 1024          After output_buffering = 2048

Note
If you do your own custom php.ini (on Netsol) remember to drop it into your cgi bin and
delete the phi-coelesed in teh php dat folder

By default they only give you 32M and 1024 which will cause http response delays
We were getting http response times of 8.5-13.5 seconds to deliver the first byte. (ie: waiting on response from website message in IE staus bar)

Now http response is under 1 second almost everytime.
It doesnt solve my search problem but every little bit helps.

Im checking the some of the things you mentioned as well Pro and will post results as i find more gains

[PRO]

tampasounds

test this out.

what this will do (I think) is limit the # of results. (so it will maybe return results after it finds 20)

mod_virtuemart_search/tmpl/default.php

change
      <input type="hidden" name="limitstart" value="0" />


to       <input type="hidden" name="limitstart" value="20" />

I used the search, and searched for something. and it had pages and pages.

[tampasounds]

Hi Everyone , Sorry i was pulled away on a few other large projects but I am back to revisit this issue.

So here's what ive done and what i've found.
First..Netsol is a very poor choice for Jooma sites of any kind. I definitely can see and feel the diference when we moved the sites to Rochen.
Second...We still found performance issues, even while on Rochen. Although not as severe but 8-16 search times are still enough to get me back on this.

What Ive found...
After dumping the data , and rebuilding this databases over and over (about 5-6 times) now, i have found an unmistakeable performance leak.
I am 100 percent positive that the cause of this (which can very easily be fixed) is pointing directly at the virtuemart_products_manufacturers table

I'd like to talk about this a little bit from a dba standpoint.
This is just a linking table. (long story short, in a nutshell) It links virtuemart productid to manufacturer id

What it also does its create and additional "pull" and place to look when searching (for a product list / detail)

First vm finds the part in the products table. Then it has to look for the mfr id in the product_manufacturers table.
When your dealing with 800k rows .... it theoretically would have to search the first 800k then 800k more plus....custom fields etc....

I tested this. What i have found is (if you dump the products_manufacturer table completely) virtuemart search times are literally cut in HALF

For Max, Eugen and the other developers
I propose dropping this table (products_manufacturers) and simply add the manufacturerId field to the products table ?
You will have to adjust a couple of queries but searching 1 table is faster than searching 2 , especially at this capacity.

You will still get the one to one relationship that allows you to still "link" the manufacturer to the product.
It will also save the overhead of having to search through the entire second table to match a manufacturer_id with a product_id.
It will also save substantial amount of space for people hosting on shared servers.  (we have over half gb of data for only 863k rows...pictures NOT included)

Let me know if you need to test this and ill shoot you some current login infos.
Also (Max) I am still willing to take you up on last offer when we get this resolved once and for all

[Milbo]

uhmm difficult. When the feature for multiple manufacturers was added, the performance was my argument against it. But people insisted.

The best would be two different database layouts, one for multiple manufacturers, the other not. In your case with 800k products, you should hack the core. I usually never say this. To add an option is maybe not the simpelst method.