438696 - Search Index Insert is flooding the database

Status: RESOLVED FIXED

(support.mozilla.org :: Knowledge Base Software, task)

Description

[Jeremy Orem [:oremj]]

I am seeing the following query happen about 300 times/s in production which is flooding the database.

insert into `tiki_searchindex` (`location`,`page`,`searchword`,`count`,`last_update`) VALUES (?) 

We need to take it out before we can put the master/slave patch back in production.

Comment 1

[Nelson Ko [:nkoth]]

Attachment: do not index into tiki_searchindex table when MySQL fulltext indexes are being used

Comment 2

[Nelson Ko [:nkoth]]

The tiki_searchindex table contains indexes that won't be used when search is using MySQL fulltext index mode anyway.

Comment 3

[Nelson Ko [:nkoth]]

Now this makes me wonder:  did we not switch to InnoDB? Does InnoDB support MySQL fulltext searches?

Comment 4

[Nelson Ko [:nkoth]]

in r15328 (trunk), r15330 (1.1), and r15331 (production). 

Comment 5

[Laura Thomson :laura]

InnoDB doesn't support FTS, but I left all the tables that had full text indexes as MyISAM for that exact reason.

Comment 6

[Laura Thomson :laura]

Comment on attachment 324702 [details] [diff] [review]
do not index into tiki_searchindex table when MySQL fulltext indexes are being used

Let's stage this.  oremj?

Comment 7

[Nelson Ko [:nkoth]]

To test this: you can go to log in as an admin user, then goto tiki-admin.php?page=search

and then click on the "Refresh wiki search index now" and "Refresh tracker search index now" links, and see if those nasty queries happen.

Comment 8

[Jeremy Orem [:oremj]]

That test case on stage does not cause a huge amount of INSERTs or any INSERTs for that matter.

Comment 9

[Laura Thomson :laura]

Incorporated in https://svn.mozilla.org/projects/sumo/tags/1.0/20080612_r15378.

Comment 10

[Marc Laporte]

Perhaps InnoDB + Zend_Search_Lucene

Comment 11

[James Socol [:jsocol, :james]]

Per the discussion in bug 532156, we're keeping our own implementation of Sphinx-based search, which isn't compatible with general tiki needs.

However you choose to deal with search, it's worth making sure that the initial problem here is fixed.