[Typo3-dev] indexed_search - Performance Issue Questions

[Georg Kuehnberger]

Ahoi,

- We just recently ran into a performance issue with indexed_search where 
some searches take more than 60 seconds.
- Using log-slow-queries and mysqldumpslow we traced down the issue to the 
function execPHashListQuery.
- Please find below some details in regards to the performance-problem and 
the installation in question.
- I did'nt find any changes/updates to this function in the CVS version of 
indexed_search, thus a few questions:


Questions:
============
- indexed_search - Tuning:
	Does/Did anybody experience similar performance-issues with indexed_search 
in general; not within the indexing-process though the search and 
especially the above mentioned function & how did you solve them?

- mysql-tuning  - Tuning:
	Does/Did anybody experience similar performance-issues with mysql & 
indexed_search or mysql & TYPO3 and is willing to share his tuned 
mysql-configuration? ( btw, guess we should aggregate 
TYPO3-mysql-performance information even aside this recent issue )

- Indexed Search & alternatives:
	Does/Did anybody have a closer look in integrating alternatives to 
indexed_search into TYPO3 in the same way as indexed_search is?
	We looked closer into mnogosearch; [1] Andreas Doleschal already 
implemented it in a custom way for the green-party in Austria.
	Anybody interested in the above ?

Thanks for listening,
regards georg


[1] 
http://tuga.at/index.php?id=602&tx_maillisttofaq_pi1[sword]=mnogo&tx_maillisttofaq_pi1[mode]=1&tx_maillisttofaq_pi1[showUid]=6588#childUid6601


# box - os, apps
#################################
proc	Intel(R) Xeon(TM) CPU 3.06GHz
ram	2GB
os	debian 3.1 sarge
mysql	4.0.24
php	PHP Version 4.3.10-15
typo3	3.8


# mysql-stats
#################################
MySQL on localhost (4.0.24_Debian-10-log)               up 9+12:47:05 
[02:36:08]
Queries: 19.2M  qps:   24 Slow:   812.0         Se/In/Up/De(%):    86/07/00/04
Cache Hits: 10.8M Hits/s: 13.8 Hits now:   0.0  Ratio: 65.2% Ratio now:  0.0%
Key Efficiency: 99.2%  Bps in/out:  4.0k/ 4.6k


# ind.search stats
#################################
Records
index_phash:   		27426
index_words:   		118403
index_rel:   		3551393
index_grlist:   		25675
index_section:   		29681
index_fulltext:   		27420
index_phash
TYPES
TYPO3 page (0):   		26145/25596
(doc):   		26145/327
(html):   		26145/21
(jpeg):   		26145/26
(pdf):   		26145/1437
(pps):   		26145/3
(ppt):   		26145/2
(tif):   		26145/4
(xls):   		26145/9


# Table Records / Size
#################################
index_config	 0	MyISAM 	1.0 KB 	 -
index_debug 	0 	 MyISAM	 1.0 KB 	-
index_fulltext 	27,379 	 MyISAM	 41.0 MB 	 1,032 Bytes
index_grlist 	25,638 	 MyISAM	 1.5 MB 	 28 Bytes
index_phash 	27,384 	 MyISAM	 6.8 MB 	 264 Bytes
index_rel 	3,544,990 	 MyISAM	 130.1 MB 	 1,232 Bytes
index_section 	29,626 	 MyISAM	 3.1 MB 	 29 Bytes
index_stat_search 	11,613 	 MyISAM	 6.0 MB 	-
index_stat_word 	19,281 	 MyISAM	 1.0 MB 	-
index_words 	118,163 	 MyISAM	 7.7 MB 	-



# mysqldumpslow - analysis
#################################
Count: 584  Time=97.58s (56985s)  Lock=17.28s (10094s)  Rows=3218.6 
(1879643), awo_typo3[awo_typo3]@localhost
   SELECT IR.phash
   FROM index_words IW,
   index_rel IR,
   index_section ISEC
   WHERE
   IW.baseword LIKE 'S'
   AND IW.wid=IR.wid
   AND ISEC.phash = IR.phash
   AND ISEC.rl0 IN (N)
   AND is_stopword=N
   GROUP BY IR.phash

Count: 87  Time=55.48s (4827s)  Lock=3.40s (296s)  Rows=18.1 (1574), 
awo_typo3[awo_typo3]@localhost
   SELECT IR.phash
   FROM index_words IW,
   index_rel IR,
   index_section ISEC
   WHERE
   IW.baseword LIKE 'S'
   AND IW.wid=IR.wid
   AND ISEC.phash = IR.phash
   AND ISEC.rl0 IN (N)AND ISEC.rl1 IN (N)
   AND is_stopword=N
   GROUP BY IR.phash

Count: 40  Time=52.17s (2087s)  Lock=0.20s (8s)  Rows=43.8 (1752), 
awo_typo3[awo_typo3]@localhost
   SELECT IR.phash
   FROM index_words IW,
   index_rel IR,
   index_section ISEC
   WHERE
   IW.baseword LIKE 'S'
   AND IW.wid=IR.wid
   AND ISEC.phash = IR.phash
   AND ISEC.rl0 IN (N)AND ISEC.rl2 IN (N)
   AND is_stopword=N
   GROUP BY IR.phash



# log-slow-queries - a few examples
#################################

# Query_time: 76  Lock_time: 0  Rows_sent: 506  Rows_examined: 7081382
use awo_typo3;
SELECT IR.phash
                         FROM index_words IW,
                                                 index_rel IR,
                                                 index_section ISEC
                         WHERE
                                 IW.baseword LIKE '%full%'
                                                 AND IW.wid=IR.wid
                                                 AND ISEC.phash = IR.phash
                                                 AND ISEC.rl0 IN (294)
                                                  AND is_stopword=0
                         GROUP BY IR.phash;




# Query_time: 89  Lock_time: 0  Rows_sent: 0  Rows_examined: 7089432
SELECT IR.phash
			FROM index_words IW,
						index_rel IR,
						index_section ISEC
			WHERE
				IW.baseword LIKE '%usbekistan1%'
						AND IW.wid=IR.wid
						AND ISEC.phash = IR.phash
						AND ISEC.rl0 IN (294)
						 AND is_stopword=0
			GROUP BY IR.phash;



# Query_time: 92  Lock_time: 0  Rows_sent: 7  Rows_examined: 7089439
SELECT IR.phash
			FROM index_words IW,
						index_rel IR,
						index_section ISEC
			WHERE
				IW.baseword LIKE '%usbekistan%'
						AND IW.wid=IR.wid
						AND ISEC.phash = IR.phash
						AND ISEC.rl0 IN (294)
						 AND is_stopword=0
			GROUP BY IR.phash;