[TYPO3-dev] indexed_search kills server

Ernesto Baschny [cron IT] ernst at cron-it.de
Wed May 21 12:05:09 CEST 2008


Steffen Kamper wrote: on 21.05.2008 11:26:

> on my server i encountered massive problems with indexed_search.
> 
> Have a look to query times:
> 
> # Time: 080521 10:18:31
> # User at Host: web2[web2] @ localhost []
> # Query_time: 555  Lock_time: 0  Rows_sent: 30  Rows_examined: 21263272
> SELECT IR.phash
>                         FROM index_words IW,
>                                                 index_rel IR,
>                                                 index_section ISEC
>                         WHERE
>                                 IW.baseword LIKE '%einrichtung%'
>                                                 AND IW.wid=IR.wid
>                                                 AND ISEC.phash = IR.phash
>                                                 AND ISEC.rl0 IN (1)
>                                                  AND is_stopword=0
>                         GROUP BY IR.phash;

Even for this amount of rows it seems mysql is taking a bit "too much" 
time. I guess that your server is not well tuned for the Apache + MySQL 
configuration.

In one particular installation I have about 6 million entries in the 
index_rel table (where you have 21 million) and the same query returns 
in about 0.21 secs, and this is being hosted on a virtual environment 
(xen based).

So I guess your 555 secs are caused by memory exhaustion, server having 
to swap and the whole I/O performance going down to the ground. Consider 
tuning the mysql + apache combination to not go much over the amount of 
available RAM (by limiting max_connections and MaxClients) and tweak 
MySQL to make better use of the RAM. Also make sure you have fast 
HDD-access, make use of NCQ (if possible), etc.

Cheers,
Ernesto




More information about the TYPO3-dev mailing list