On Friday 08 February 2013 12:20 AM, Thomas Sibley wrote:
On 02/07/2013 03:13 AM, Subin wrote:
.... the second query isn't run when the first returns 0.
This hint helped narrow down things. I figured out the pattern/situation
under which search failure occurs, which is:
"If I create/update a ticket and immediately perform fulltext: search
of the same content before reindexing is run(on delta), it will fail to
show up on fulltext searches even after the content is later
reindexed. In this situation if I run a 'search' from CLI(on delta index
specifically) it returns successful hits. Running SELECT COUNT by hand
also returns >0 integer at this point. If I do not search for newly created
ticket-content before its reindexed, this won't occur. Things are normal
in that case."
Thanks for your detailed investigation and description (which I've
trimmed from below). It sounds very much like you're hitting a cache on
the SELECT COUNT query that isn't expired until you create a new ticket
(or presumably after some longer amount of time). RT doesn't cache
database queries this way, so I suspect it's MySQL's standard query
cache or, perhaps more likely, some cache in the Sphinx engine for MySQL
(not the sphinxd server itself). Modifying one of the tables involved
in the query, as you do by creating a new ticket or replying to an
existing ticket, then triggers the cache expiry.
If you're interested in further tracking this down, I'd investigate
caching at the MySQL and Sphinx level. However, it may be moot in a
production environment where the caching is beneficial and there's
enough database activity to keep it from being noticeably stale.
Spot on! It was the mysql query cache that was responsible.
I disabled it and search just works fine without need to update
tables. All along I thought RT did some caching in this situation.
Well I guess this query caching can be left ON anyway since on
a busy production environment the likeliness of the cache to remain
state is very less.
Thank you for your effort and continued support.
--
Help improve RT by taking our user survey:
https://www.surveymonkey.com/s/N23JW9T
