[Typo3-dev] RealURL : high server load and optimization

[Ries van Twisk]

Hey List,

I added a few indexes:


I added these indexes to `tx_realurl_pathcache`
ALTER TABLE `tx_realurl_pathcache` ADD INDEX `pidlangrootexp` ( 
`page_id` , `language_id` , `rootpage_id` , `expire` ) ;
ALTER TABLE `tx_realurl_pathcache` ADD INDEX `hashroot` ( `hash` , 
`rootpage_id` ) ;

The index will be used for these query (taken from the source).
EXPLAIN SELECT pagepath FROM tx_realurl_pathcache WHERE page_id =86 AND 
language_id =0 AND rootpage_id AND expire =0
1 	SIMPLE 	tx_realurl_pathcache 	ref 	page_id,pidlangrootexp 
page_id 	4 	const 	1 	Using where


I noticed while doing that that the average retreival of cached pages 
actually goes up by about 10%-20%, this was done on my live server so 
they values are not acurate.

I did the same for the pages table:
ALTER TABLE `pages` ADD INDEX `piddeldok` ( `pid` , `deleted` , `doktype` )
ALTER TABLE `pages` ADD INDEX `uiddeldok` ( `uid` , `deleted` , `doktype` )
 
By doing this pages retreival time didn't went go down with about 10-20% 
aswell

Same for pages overlay:
ALTER TABLE `pages_language_overlay` ADD INDEX `ipid` ( `pid` )

I don't find anything here because I don't do languages :)

All in all my site IS faster now which is great...
The above let me think this:

I am currently writing a little BE module that hooks into one of the 
*SELECT* functions in class.t3lib_db.php
I am going to log all select to the DB and see  what  'where' clauses 
are used mostly and doign index optimalisations
based on these statistics...

any thought about that?


cheers,
Ries

>Dmitry Dulepov wrote:
>
>  
>
>>Hi!
>>
>><snip>
>>
>>    
>>
>>>y case it's really realUrl.
>>>   
>>>
>>>      
>>>
>>Try mmcache/eaccelerator and see if it helps :)
>>
>>Dmitry.
>> 
>>
>>    
>>
>eaccelerator helps a lot indeed, aswell as tuning mysql 
>especially turning on the query cache.
>
>But this is about realUrl and I want to help to see if these change 
>can help speeding up a site, any site..
>
>
>Ries
>_______________________________________________
>Typo3-dev mailing list
>Typo3-dev at lists.netfielders.de
>http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev
>  
>