[Typo3] optimising (optimizing) indexed search

[Darren Clark]

On Thu, Sep 22, 2005 at 11:57:03AM +0100, Dave Chewter wrote:
> Nate Russell wrote:
> > Dave Chewter wrote:
> > 
> >> Hi All
> >>
> >> We recently launched a large(ish) typo3 site and everything is goind 
> >> swimmingly, however as more and more pages are getting indexed (about 
> >> 10,000 live at the moment) the indexed search plugin has started to 
> >> take longer to process searches. Our currently stats from the backend 
> >> Indexing extension are:
> >>
> >> index_phash:     8336
> >> index_words:     53438
> >> index_rel:     786879
> >> index_grlist:     8336
> >> index_section:     8336
> >> index_fulltext:     8336
> >>
> >> We're going to probably double in size in the next 6 months - and a 
> >> lot of the pages are text rich, so my question is what are the best 
> >> ways of optimising the indexed search to generate results quicker. 
> >> Should I start adding indexed to tables in mysql, or are there any 
> >> advanced options i can turn off using typoscript to speed up 
> >> execution. The site I'm talking about is www.intersites.co.uk you can 
> >> check out the search there (on the front page and also category and 
> >> resource centre search boxes searching bits of the page tree).
> >>
> >> I imagine long term we're going to have to start looking for another 
> >> solution, but i'm really happy with indexed search and would like to 
> >> use it for as long as possible.
> >>
> >> Any pointers would be much appreciated.
> >>
> >> Kind Regards
> >> Dave
> > 
> > 
> > Hello Dave,
> > 
> > I have the same problem. Our database is getting huge and our server is 
> > taking a beating. When someone does a complicated search it often takes 
> > a few minutes to get a reault or it crashes the DB. We are also looking 
> > for another solution which, for starters, will include at least a faster 
> > server where we have root access. Our stats currently look like this:
> > 
> > index_phash:           10857
> > index_words:           112099
> > index_rel:           2198462
> > index_grlist:       10857
> > index_section:       10857
> > index_fulltext:       10857
> > 
> > What I don't understand is why our index_rel is so much bigger. Our 
> > MySQL database is around 450MB! I wonder if I has some something 
> > configured wrong that makes it so large or if there is a way to trim the 
> > fat and get rid of useless data in some of the tables.
> > 
> > I did install a PHP accelerator but it seemed to actually make the 
> > problem worse. Have you tried this?
> > 
> > I like your site by the way. Good design and TYPO3 implementation.
> > 
> > Nate
> > 
> > 
> Hi Nate
> 
> I'm glad to hear that it's not just me having these problems. I haven't 
> tried a php accelerator yet, will try that soon. We're on a dedicated 
> server that's reasonably high spec so I'm hoping that we don't have to 
> upgrade the hardware.
> 
> The slow down seems to be just due to the size of the tables generated 
> (as you mentioned). I don't think there's anything wrong with your 
> index_rel table, I think that because it's mapping the relationships 
> between all the words it indexes in index_words it will get really big.
> 
> I was hoping that maybe adding some indexes to the table in MySQL might 
> help but I'm no expert in MySQL so don't want to break anything 
> unintentionally. I'm still hoping someone with a bit more MySQL 
> experience might be able to add some pointers.
> 
> Other than that I'm sure the indexed search extension will be 
> continually tweaked as it's developed. Hopefully some of the work will 
> go on optimisation of it.
> 
> Thanks for your kind words about the site. I'm going to continue trying 
> to make some progress so will post back if I find anything helpful that 
> speeds it up.
>

If you haven't already it may be worth upgrading to Mysql 4 and using
the query cache.

By default the query cache in mysql 4 is disabled this will enable it
(in the main mysql config my.cnf, [mysqld] section):

query_cache_type = 1
query_cache_size = 50M

As root on mysql:

mysql> show variables LIKE 'query%';

Checks that it is enabled

mysql> SHOW STATUS LIKE '%qcache%';

Gives you the free memory and cached queries.

You'll need to do some testing though as if there are a huge amount of
unique queries this *could* be a problem.

As for PHP Accelerators I'd recomment TurckMMCache

Homepage:

http://turck-mmcache.sourceforge.net/index_old.html#download

Turck MMCache

Example settings I use are:

zend_extension="/usr/lib/php4/20020429/mmcache.so"
mmcache.shm_size="0"
mmcache.cache_dir="/var/cache/turck-mmcache"
mmcache.enable="1"
mmcache.optimizer="1"
mmcache.check_mtime="1"
mmcache.debug="0"
mmcache.filter=""
mmcache.shm_max="0"
mmcache.shm_ttl="0"
mmcache.shm_prune_period="0"
mmcache.shm_only="0"
mmcache.compress="1" 

--
Darren

> Kind Regards
> Dave
> _______________________________________________
> Typo3-english mailing list
> Typo3-english at lists.netfielders.de
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-english