Page MenuHomePhabricator
Create Task
Maniphest T11922

Upgrading: Changes to Fulltext Indexing (InnoDB Fulltext)
Closed, ResolvedPublic
Actions

Assigned To
epriestley
Authored By
epriestley
Nov 25 2016, 11:31 PM
Tags
Referenced Files
F2589905: Unhandled_Exception___AphrontQueryException__.png
Feb 6 2017, 12:34 PM
Subscribers
20after4
carwyn
constantx
csilvers
cspeckmim
eadler
epriestley
View All 11 Subscribers
Tokens
"Mountain of Wealth" token, awarded by vinzent."Love" token, awarded by salvian."Love" token, awarded by cspeckmim."Party Time" token, awarded by chad.

Description

We're making changes to fulltext indexing which impact existing installs. Updating through these changes may take more time and attention than usual because search indexes need to be rebuilt. This task discusses these changes at a high level.

These changes are not included in stable for 2016 Week 48 (rPcfcc3b83). Upgrading past that (including to HEAD of master) will trigger the changes.

Summary for operations:

  • Phabricator now manages table engines, and bin/storage upgrade will ALTER TABLE and change engines if any table is using an engine which Phabricator does not expect. Generally, this means that any manual engine changes you have made will now be undone.
  • If InnoDB FULLTEXT indexes are available in MySQL (which is common), Phabricator will now change the fulltext tables to use InnoDB. See T11741 for broader discussion.
  • This may take a long time if your install has a lot of data.
  • After the engine change, search indexes also need to be rebuilt for stemming support (see T6740). Without this rebuild, search won't be able to find older results consistently.

If you operate a large install, you can minimize downtime associated with the migration like this:

  • Warn users that search will be degraded for some time after the upgrade.
  • TRUNCATE the phabricator_search.search_documentfield table to destroy all the data it contains. This will break search.
  • Perform the upgrade. The engine change should execute quickly because no data needs to be migrated.
  • Phabricator will now be usable again, except that global search won't return results.
  • Run bin/search index --all --background --force to rebuild the entire search index. Search will gradually return to service as these tasks complete.

Benefits for operations:

  • If InnoDB FULLTEXT is available, Phabricator now upgrades all tables to InnoDB.
  • We saw various reports of these MyISAM tables crashing and requiring repair. Since (if possible) we no longer use MyISAM, this should no longer occur.
  • ft_boolean_syntax no longer needs to be configured on any system. This is particularly relevant to RDS, where it could not be configured (see T10642).
  • ft_min_word_len no longer need to be configured (if InnoDB FULLTEXT is available).
  • ft_stopword_file no longer needs to be configured (if InnoDB FULLTEXT is available).

Benefits for users:

  • Searches which return very large result sets should now execute faster.
  • Results where the object title matches the query are now ranked higher.
  • Search now uses stemming, and a query like "delete" can now find documents with the words "deletion", "deleting", "deletes", etc.
  • Search should now find more relevant results when backed by Amazon RDS.

We'll continue tuning this, but future changes should be significantly less disruptive.

Revisions and Commits

rPHU libphutil
D17001rPHU5ac2ca121489 Don't let stemming reduce a word beneath 3 characters
rP Phabricator
D16970rPdc73785c4ffc Add a "--force" argument to "bin/config done"
D16965rP29a3cd5121d9 Add "Manual Activities", to tell administrators to rebuild the search index

Related Objects

Event Timeline

epriestley created this task.Nov 25 2016, 11:31 PM
Herald added a subscriber: eadler. · View Herald TranscriptNov 25 2016, 11:31 PM
cspeckmim added a subscriber: cspeckmim.Nov 26 2016, 5:34 AM
joshuaspence added a subscriber: joshuaspence.Nov 28 2016, 8:54 AM
eadler added projects: Restricted Project, FreeBSD.Nov 28 2016, 11:23 PM
epriestley updated the task description. (Show Details)Nov 28 2016, 11:25 PM
chad awarded a token.Nov 28 2016, 11:55 PM
epriestley added a revision: D16965: Add "Manual Activities", to tell administrators to rebuild the search index.Nov 30 2016, 6:47 PM
epriestley mentioned this in T11932: Manual Activity: Rebuild Search Index.Nov 30 2016, 7:13 PM
epriestley added a commit: rP29a3cd5121d9: Add "Manual Activities", to tell administrators to rebuild the search index.Nov 30 2016, 7:24 PM
epriestley added a subscriber: csilvers.Nov 30 2016, 7:24 PM

@csilvers, not sure if you got directed here from IRC. See also T11932. At HEAD, we'll now issue more explicit guidance about the required index rebuild.

Herald added a subscriber: sophiebits. · View Herald TranscriptNov 30 2016, 7:24 PM
epriestley added a revision: D16970: Add a "--force" argument to "bin/config done".Dec 1 2016, 7:32 PM
epriestley added a commit: rPdc73785c4ffc: Add a "--force" argument to "bin/config done".Dec 1 2016, 9:53 PM
epriestley mentioned this in 2016 Week 49 (Early December).Dec 3 2016, 10:53 AM
cspeckmim awarded a token.Dec 3 2016, 6:47 PM
salvian awarded a token.Dec 6 2016, 4:23 AM
carwyn added a subscriber: carwyn.EditedDec 6 2016, 2:25 PM

After this update I couldn't find three letter words any more. I needed to add this change to the MySQL config:

innodb_ft_min_token_size=3

.. as ft_min_word_len=3 only applies to MyISAM tables.

epriestley added a comment.Dec 6 2016, 2:28 PM

What is innodb_ft_min_token_size set to? The default is 3.

mysql> select @@innodb_ft_min_token_size;
carwyn added a comment.Dec 6 2016, 2:34 PM

Found it and edited my comment, it was indeed innodb_ft_min_token_size at fault.

carwyn added a comment.EditedDec 6 2016, 3:39 PM

Note that full text indexing for InnoDB and hence innodb_ft_min_token_size were only introduced in MariaDB 10.0.5. RHEL/CentOS 7 ships with MariaDB 5.5 :( Ref: https://mariadb.com/kb/en/mariadb/fulltext-index-overview/

I've still got ft_min_word_len=3 in my config but some three letter word searches (lots of IT acronyms) aren't working. Not all results are missing.

carwyn added a comment.Dec 6 2016, 4:00 PM

FYI this is where things are at after storage upgrade on Week 49 stable using MariaDB 5.5. Is this what you would expect?

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';
+---------------------------+--------+
| TABLE_NAME                | ENGINE |
+---------------------------+--------+
| columns_priv              | MyISAM |
| db                        | MyISAM |
| event                     | MyISAM |
| func                      | MyISAM |
| help_category             | MyISAM |
| help_keyword              | MyISAM |
| help_relation             | MyISAM |
| help_topic                | MyISAM |
| host                      | MyISAM |
| ndb_binlog_index          | MyISAM |
| plugin                    | MyISAM |
| proc                      | MyISAM |
| procs_priv                | MyISAM |
| proxies_priv              | MyISAM |
| servers                   | MyISAM |
| tables_priv               | MyISAM |
| time_zone                 | MyISAM |
| time_zone_leap_second     | MyISAM |
| time_zone_name            | MyISAM |
| time_zone_transition      | MyISAM |
| time_zone_transition_type | MyISAM |
| user                      | MyISAM |
| conpherence_index         | MyISAM |
| search_documentfield      | MyISAM |
+---------------------------+--------+
epriestley added a comment.Dec 6 2016, 4:11 PM

That's expected if innodb_ft_max_token_size is not defined; specifically if this returns an error:

mysql> select @@innodb_ft_max_token_size;

If your IT acronyms look like short English words, it's possible they're being stemmed under the token size limit, but I can't look into this without knowing which specific acronyms you're having difficulty with. The stemmer is not exposed in the UI, so there's no easy way for you to check.

carwyn added a comment.EditedDec 6 2016, 4:20 PM
> select @@innodb_ft_max_token_size;
ERROR 1193 (HY000): Unknown system variable 'innodb_ft_max_token_size'

What you describe sounds familiar. I can find NTP and EoL. I can find NIS* but not NIS, DNS* but not DNS.

epriestley added a revision: D17001: Don't let stemming reduce a word beneath 3 characters.Dec 6 2016, 4:31 PM
carwyn added a comment.Dec 6 2016, 5:06 PM

Applied D17001 and confirmed fix. Thank you.

epriestley added a commit: rPHU5ac2ca121489: Don't let stemming reduce a word beneath 3 characters.Dec 6 2016, 5:10 PM
vinzent awarded a token.Dec 15 2016, 3:10 PM
vinzent added a subscriber: vinzent.
epriestley mentioned this in T9979: Build support for ngram indexes for substring searches (e.g., file, paste, package, task titles).Jan 13 2017, 5:50 PM
ftdysa mentioned this in T12137: MySQL fulltext search returns no results in boolean mode when a query includes +"x", where "x" is a stopword or short token.Jan 26 2017, 4:50 PM
constantx added a subscriber: constantx.Feb 6 2017, 11:46 AM
This comment was removed by constantx.
epriestley closed this task as Resolved.Feb 18 2017, 2:31 AM

This has seemed stable for a while. We've fixed a few stemming issues and have T12137 and some other followups outstanding, but these generally are not issues that are fundamental to InnoDB Fulltext.

• pasik added a subscriber: • pasik.Dec 17 2017, 2:58 PM
Phabricator · Built by Phacility