Page MenuHomePhabricator

Use InnoDB engine for searchindex table in MySQL
Open, Needs TriagePublic

Description

Newer versions of Mysql (5.6+) support full-text indexes on innodb tables. We should move the searchindex table that is used for core search support to innodb because innodb sucks less and to be more consistent to user's wishes.

The largest issue here, is doing it in a way that makes tables.sql backaward compat with old mysql.

Event Timeline

Paladox created this task.Aug 4 2015, 1:47 PM
Paladox raised the priority of this task from to Needs Triage.
Paladox updated the task description. (Show Details)
Paladox added subscribers: Paladox, Krinkle, Legoktm and 3 others.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 4 2015, 1:47 PM

Please ALWAYS follow https://www.mediawiki.org/wiki/How_to_report_a_bug and describe which specific problem you would like to solve, plus what extension this refers to and why.

Hi this is not a bug this is a suggestion.

It does not matter how you call it as long as you do not explain why you make that suggestion and why that suggestion could be useful. Please read my previous comment again and follow it, in general.

Krinkle removed a subscriber: Krinkle.Sep 17 2015, 5:51 AM
Bawolff renamed this task from Support searchindex in innodb in mysql to Use innodb engine for searchindex table in mysql.Sep 17 2015, 1:36 PM
Bawolff updated the task description. (Show Details)
Bawolff edited projects, added MediaWiki-Search; removed MediaWiki-extensions-General.
Bawolff set Security to None.
Restricted Application added a project: Discovery. · View Herald TranscriptSep 17 2015, 1:36 PM

Please ALWAYS follow https://www.mediawiki.org/wiki/How_to_report_a_bug and describe which specific problem you would like to solve, plus what extension this refers to and why.

Paladox didn't exactly explain it the best, but... He's referring to mediawiki core support for search when there are no search extensions installed. Using InnoDB here may potentially result in better search results, may be less likely to have data corruption during server crashes, and may have better performance under contention (I have never tested any of these claims, nor know enough about it to say for certain if that's true. But it probably is). Additionally, we normally use innodb everywhere else (based on a config variable), so its nice to be consistent.

Restricted Application added a subscriber: StudiesWorld. · View Herald TranscriptDec 23 2015, 5:37 AM

Discovery is focussed on Wikimedia users, so this task is out of scope for us.

@Bawolff I'm talking about currently searchindex or the search table in MediaWiki only uses Utf-8 and doesn't support innodb. Where as if we add a check to check weather a user uses a compatible MySQL then we could add sql for using innodb and any type of prefix for example binary or Utf-8. This is for search.

Change 290061 had a related patch set uploaded (by Paladox):
InnoDB now supports fulltext which we need for searchindex so lets use it

https://gerrit.wikimedia.org/r/290061

Restricted Application added a project: Discovery-Search. · View Herald TranscriptMay 22 2016, 12:02 PM

With https://gerrit.wikimedia.org/r/#/c/290061/ instead of updating tables.sql to use innodb for searchindex I added the sql patches instead so based on the MySQL version you use it will detect and use the correct one.

Change 291346 had a related patch set uploaded (by Paladox):
InnoDB now supports fulltext which we need for searchindex so lets use it

https://gerrit.wikimedia.org/r/291346

Change 291346 abandoned by Paladox:
InnoDB now supports fulltext which we need for searchindex so lets use it

https://gerrit.wikimedia.org/r/291346

Change 290061 abandoned by Paladox:
InnoDB now supports fulltext which we need for searchindex so lets use it

https://gerrit.wikimedia.org/r/290061

Change 291594 had a related patch set uploaded (by Paladox):
InnoDB now supports fulltext which we need for searchindex so lets use it

https://gerrit.wikimedia.org/r/291594

G9981 added a subscriber: G9981.Jun 9 2017, 8:44 AM

I recently needed to deploy Wikimedia on MySQL instances (5.6+) having only InnoDB Engine (and no MyISAM). The mw-config naturally failed due to searchindex table creation that needs MyISAM, and was fixed with a quick patch. Just wanted to share it as it was made trying to be "backward compatible" (for mediawiki new installation with MySQL usecase):

Replace lines 1274 to 1291 in tables.sql with following (searchindex creation script, https://phabricator.wikimedia.org/source/mediawiki/browse/master/maintenance/tables.sql$1282 ):

--- The table is created with the db default engine / charset
--- For old MySQL version 5 (5.5 or older), the table creation is forced to MyISAM engine and utf8 charset.
SET @base = 'CREATE TABLE /*_*/searchindex ( si_page int unsigned NOT NULL ,  si_title varchar(255) NOT NULL default '''' , si_text mediumtext NOT NULL )';
SET @legacy_compat = ' ENGINE=MyISAM DEFAULT CHARSET=utf8;';
SET @script = IF( substring(@@GLOBAL.version,1,1) = 5 and substring(@@GLOBAL.version,3,1) < 6  , concat(@base,@legacy_compat), @base );
PREPARE statement FROM @script;
EXECUTE statement;
Magol added a subscriber: Magol.Sep 28 2017, 8:23 AM
mmodell closed this task as Resolved.Oct 8 2017, 10:39 PM
mmodell claimed this task.
mmodell added a subscriber: mmodell.

This is ancient history now.

Bawolff reopened this task as Open.Oct 11 2017, 8:02 PM

This is ancient history now.

Its not. Issue is still present in MediaWiki.

mmodell removed mmodell as the assignee of this task.Oct 11 2017, 8:03 PM
Henryfunk added a subscriber: Henryfunk.EditedJul 7 2018, 4:07 AM

I have changed the searchindex table on my MediaWiki-based site to InnoDB. I have not done any formal tests, but the problems I noticed with searches against the MyISAM-based table were solved. I have noted two things that may be useful for others to know if they make the same change: rebuildtextindex.php deletes two search indexes for the table and then tries to create them again in one go. With some versions of MySQL (certainly 5.7.22) this crashes the script, for only one InnoDB fulltext index can be made at a time. Another problem is that searches that include a hyphen followed by a space or a hyphen ending the search string cause a "Wikimedia\Rdbms\DBQueryError". (On MediaWiki 1.30).

Krinkle moved this task from Untriaged to Schema changes on the Wikimedia-Rdbms board.
ToBeFree added a subscriber: ToBeFree.

I'm moving a site to Percona and this keeps biting me. Thanks to @G9981, I'm going to test his suggested code and see if it works. In the meantime, I'm creating a hook for a custom extension to change the engine type.

I'm proposing changing this to innodb and bumping MediaWiki's minimum version supported of MySQL to 5.6.10 as a result.

Given that 5.6.10 has been available for use for 6 years and that 5.5 is not supported on Redhat systems (it was supported on SCL until Oct 2016) and that Debian only supports it on OldStable now and hasn't updated since April 2018, I think we're safe it telling users of MySQL 5.5 that newer versions of MediaWiki won't support them.

For those interested in query differences between MyISAM and InnoDB fulltext queries, see this post on Percona's site.

Change 519324 had a related patch set uploaded (by markahershberger; owner: markahershberger):
[mediawiki/core@master] Use InnoDB engine for the searchindex table in MySQL

https://gerrit.wikimedia.org/r/519324

Krinkle renamed this task from Use innodb engine for searchindex table in mysql to Use InnoDB engine for searchindex table in MySQL.Jul 18 2019, 10:28 PM
Krinkle edited projects, added MediaWiki-Installer; removed Wikimedia-Rdbms.

Change 519324 abandoned by markahershberger:
Use InnoDB engine for the searchindex table in MySQL

https://gerrit.wikimedia.org/r/519324