438370 - Need to test new search query on test cluster

Status: RESOLVED FIXED

(mozilla.org Graveyard :: Server Operations, task)

Description

[Chris Pollett]

Howdy,

I have a proposed patch to Bug400986 and I would like to test how it performs compared to the current search query being used in amo production. There are a couple of quirks about the new search which are described in 400098#c12. In particular, the patch creates two new tables: text_search_view and
versions_summary_view. The code for these tables can be found in remora.sql.
The script that keeps these tables up-to-date can be found in /bin/update-search-views.php it should be set up as a cron job going say every twenty minutes.

I'd like to know how my search compares with the current production search on one, two, three word queries when the databases are under a typical amo load.
For example,
firefox
firefox thunderbird
firefox thunderbird seamonkey.

I would also like to know how long the update script takes when the database is under load.

Finally, I am interested in tweaking the my.conf parameter ft_min_word_len to short word lengths than 4 (say 2 or 3).

Thanks for your help,
Chris

Comment 1

[Jeremy Orem [:oremj]]

What's the process for testing the search performance?  Are we throwing a query at the database or hitting certain URLs?  Which URLs?

Mark, what is your opinion on changing ft_min_word_len?

Comment 2

[Chris Pollett]

hitting urls you could try:

/en-US/firefox/search?q=firefox&cat=all
/en-US/firefox/search?q=firefox+thunderbird&cat=all
/en-US/firefox/search?q=firefox+thunderbird+seamonkey&cat=all

Comment 3

[Mark Smith [:xb95]]

I'm not really against changing it, if it's needed.  Guess it depends on the size of the table we're talking about.  Thousands or millions of rows?  The former I don't care, the latter it could lead to a rather large increase in the size of the index.

Comment 4

[Wil Clouser [:clouserw]]

let's test when q is something common like 'e' and something unusual like 'ç' also.  They are both under 4 chars also - not sure how that will affect things.

Comment 5

[Wil Clouser [:clouserw]]

(In reply to comment #3)
> I'm not really against changing it, if it's needed.  Guess it depends on the
> size of the table we're talking about.  Thousands or millions of rows?  The
> former I don't care, the latter it could lead to a rather large increase in the
> size of the index.
> 

Both tables are just over 7000 rows after running the script this morning.  Chris - that seems small; is that correct?

Comment 6

[Chris Pollett]

that's correct. most add-ons don't seem to be translated to more than one locale.

Comment 7

[Michael Morgan [:morgamic]]

Hey, any progress on this?  Anything we can do to help test?

Comment 8

[Jeremy Orem [:oremj]]

I think Comment #2 is everything I needed.  I should be able to run the test tomorrow.  Did anyone do the simple perf test of looking at all the queries a search page load generates on each revision then comparing?

Comment 9

[Chris Pollett]

I did this when on a single machine running mysql 5 (new stuff faster). Need to know for mysql 4.

Comment 10

[Wil Clouser [:clouserw]]

This is blocking 400986 which we want to commit today.  If we can help let us know.

Comment 11

[Jeremy Orem [:oremj]]

I just realized that we may not have a MySQL 4 test server anymore.  Mark?

Comment 12

[Dave Miller [:justdave]]

Looks like oremj is already working on this, so assigning it to him so it'll stop paging me. :)

I think that's correct though, all the staging/testing mysql servers got upgraded to mysql5.

Comment 13

[Mark Smith [:xb95]]

Correct, the only MySQL 4 machines are production.  Why do you need one of those old and decrepit things anyway?

Comment 14

[Jeremy Orem [:oremj]]

They need to test the query performance on MySQL 4 to see how it will hold up in production.

Comment 15

[Mark Smith [:xb95]]

I don't see the point when AMO is going to be on MySQL 5 in a week or so anyway.  This is a lot of work for, IMO, no real gain.

Especially given the timeframe of this request, I can't just make a machine appear.  We have no spare machines with which to build out a new MySQL 4 cluster.

Don't think it's going to happen, not without a week or two to get a new machine or blade or shuffle something around.  (I don't think putting it on a VM is a good idea, since you can't effectively load test on a VM.  You can test in development just as well as you can test on a VM.)

Comment 16

[Chris Pollett]

I am pretty sure I tuned my stuff for mysql 4 okay. Load tests on mysql 5 would still be useful before I put this one trunk and then production.

Comment 17

[Wil Clouser [:clouserw]]

I would be comfortable with testing on MySQL 5 and comparing it to current traffic.  Let's do that.

Comment 18

[Michael Morgan [:morgamic]]

Let's get it up on MySQL 5 and then push it to preview to do final testing when that's completed.  Preview should allow us to test on 4.1, correct?

Comment 19

[Jeremy Orem [:oremj]]

So are we basically testing:

SELECT DISTINCT a.id, a.name, a.summary, a.description,  MATCH(a.name, a.summary, a.description) AGAINST ('test') AS text_score,  v.created AS created  FROM text_search_summary AS a  INNER JOIN `versions_summary` AS v ON (v.addon_id = a.id)  WHERE (a.locale = 'en-US' OR a.locale = 'en-US' ) AND  MATCH(a.name, a.summary, a.description) AGAINST ('test') AND a.addontype IN (1,2,4,3,5,6) AND a.status IN(1,2,3,4) AND a.inactive = 0 AND ((a.addontype = 4 OR  v.application_id = 1 )) ORDER BY (a.status=4) DESC, text_score DESC;

vs

SELECT DISTINCT a.id, (IFNULL(`tr_name`.localized_string, `fb_name`.localized_string) LIKE '%test%') AS name_match, (IFNULL(`tr_summary`.localized_string, `fb_summary`.localized_string) LIKE '%test%') AS summary_match, (IFNULL(`tr_description`.localized_string, `fb_description`.localized_string) LIKE '%test%') AS description_match FROM addons AS a LEFT JOIN translations AS `tr_name` ON (`tr_name`.id = a.`name` AND `tr_name`.locale = 'en-US') LEFT JOIN translations AS `fb_name` ON (`fb_name`.id = a.`name` AND `fb_name`.locale = a.defaultlocale) LEFT JOIN translations AS `tr_summary` ON (`tr_summary`.id = a.`summary` AND `tr_summary`.locale = 'en-US') LEFT JOIN translations AS `fb_summary` ON (`fb_summary`.id = a.`summary` AND `fb_summary`.locale = a.defaultlocale) LEFT JOIN translations AS `tr_description` ON (`tr_description`.id = a.`description` AND `tr_description`.locale = 'en-US') LEFT JOIN translations AS `fb_description` ON (`fb_description`.id = a.`description` AND `fb_description`.locale = a.defaultlocale) WHERE (IFNULL(`tr_name`.localized_string, `fb_name`.localized_string) LIKE '%test%' OR IFNULL(`tr_summary`.localized_string, `fb_summary`.localized_string) LIKE '%test%' OR IFNULL(`tr_description`.localized_string, `fb_description`.localized_string) LIKE '%test%') AND a.addontype_id IN (1,2,4,3,5,6) AND a.status IN(1,2,3,4) AND a.inactive = 0 AND ((a.addontype_id = 4 OR ((SELECT v.created FROM versions AS v LEFT JOIN applications_versions AS av ON (av.version_id = v.id AND av.application_id = 1) WHERE v.addon_id = a.id ORDER BY v.created DESC LIMIT 1 ) IS NOT NULL))) ORDER BY (a.status=4) DESC, name_match DESC, summary_match DESC, description_match DESC;

?

Comment 20

[Michael Morgan [:morgamic]]

Affirmative.

Comment 21

[Jeremy Orem [:oremj]]

New search performance looks on par with old search performance.  The new query performs much better than the old.  In my tests the run time for the script under load was 3 to 5 seconds.