[TYPO3-dev] indexed_search kills server
Steffen Kamper
steffen at sk-typo3.de
Wed May 21 12:20:08 CEST 2008
"Ernesto Baschny [cron IT]" <ernst at cron-it.de> schrieb im Newsbeitrag
news:mailman.1.1211364311.13646.typo3-dev at lists.netfielders.de...
> 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
Thanks for your suggestions. I forwarded this to my sysadmin and he will
check your proposals.
vg Steffen
More information about the TYPO3-dev
mailing list