InnoDB / MyISAM

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

InnoDB / MyISAM

Wikipedia Developers mailing list
Hello,

I've recently noticed that in a wiki farm I look after, about 20% of the
tables are MyISAM, but it doesn't seem very consistent - i.e. some
tables that are MyISAM in some wikis are InnoDB in others. searchindex
and math look to be all MyISAM.

From what I've read about them it seems that InnoDB is the best option,
especially for recent versions. Would it be a good idea to change them
all to InnoDB?

Thanks,
Aran


_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: InnoDB / MyISAM

Bartosz Dziewoński
The 'searchindex' table uses MyISAM because until recently, InnoDB did
not support fulltext indexes, which MediaWiki uses for the search. All
other tables should use InnoDB.

According to https://stackoverflow.com/a/9397060 fulltext indexes are
available on InnoDB since MySQL 5.6.4. If you're running that version or
newer, it is possible you could use InnoDB for that table, but as far as
I know no one has tried it before.

According to https://www.mediawiki.org/wiki/Compatibility#Database 
MediaWiki only requires MySQL 5.5.8, so we can't change that in our
table definitions (yet).

No idea about the 'math' table.


--
Bartosz Dziewoński

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: InnoDB / MyISAM

Manuel Arostegui
On Sat, Jul 27, 2019 at 4:03 PM Bartosz Dziewoński <[hidden email]>
wrote:

> The 'searchindex' table uses MyISAM because until recently, InnoDB did
> not support fulltext indexes, which MediaWiki uses for the search. All
> other tables should use InnoDB.
>
> According to https://stackoverflow.com/a/9397060 fulltext indexes are
> available on InnoDB since MySQL 5.6.4. If you're running that version or
> newer, it is possible you could use InnoDB for that table, but as far as
> I know no one has tried it before.
>
> According to https://www.mediawiki.org/wiki/Compatibility#Database
> MediaWiki only requires MySQL 5.5.8, so we can't change that in our
> table definitions (yet).
>
> No idea about the 'math' table.


The math table isn't used, and it is being dropped in production:
https://phabricator.wikimedia.org/T196055

Regarding MyISAM vs InnoDB: Always use InnoDB unless you have a very good
reason to use MyISAM (like the one mentioned about full-text indexes).

Cheers
Manuel.
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: InnoDB / MyISAM

Wikipedia Developers mailing list
We're using InnoDB > 5.7 so full text search is definitely supported -
but also we use Elastic search anyway, does this mean we don't even need
the searchindex table?


On 28/07/19 1:07 PM, Manuel Arostegui wrote:

> On Sat, Jul 27, 2019 at 4:03 PM Bartosz Dziewoński <[hidden email]>
> wrote:
>
>> The 'searchindex' table uses MyISAM because until recently, InnoDB did
>> not support fulltext indexes, which MediaWiki uses for the search. All
>> other tables should use InnoDB.
>>
>> According to https://stackoverflow.com/a/9397060 fulltext indexes are
>> available on InnoDB since MySQL 5.6.4. If you're running that version or
>> newer, it is possible you could use InnoDB for that table, but as far as
>> I know no one has tried it before.
>>
>> According to https://www.mediawiki.org/wiki/Compatibility#Database
>> MediaWiki only requires MySQL 5.5.8, so we can't change that in our
>> table definitions (yet).
>>
>> No idea about the 'math' table.
>
> The math table isn't used, and it is being dropped in production:
> https://phabricator.wikimedia.org/T196055
>
> Regarding MyISAM vs InnoDB: Always use InnoDB unless you have a very good
> reason to use MyISAM (like the one mentioned about full-text indexes).
>
> Cheers
> Manuel.
> _______________________________________________
> Wikitech-l mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l


_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: InnoDB / MyISAM

Wikipedia Developers mailing list
Yep, I just had a look and all the searchindex tables are empty and the
tables doesn't seem to be accessed at all when using $wgSearchType =
'DcsCirrusSearch', so there's definitely no need for any MyISAM anywhere :-)

On 28/07/19 2:33 PM, Aran via Wikitech-l wrote:

> We're using InnoDB > 5.7 so full text search is definitely supported -
> but also we use Elastic search anyway, does this mean we don't even need
> the searchindex table?
>
>
> On 28/07/19 1:07 PM, Manuel Arostegui wrote:
>> On Sat, Jul 27, 2019 at 4:03 PM Bartosz Dziewoński <[hidden email]>
>> wrote:
>>
>>> The 'searchindex' table uses MyISAM because until recently, InnoDB did
>>> not support fulltext indexes, which MediaWiki uses for the search. All
>>> other tables should use InnoDB.
>>>
>>> According to https://stackoverflow.com/a/9397060 fulltext indexes are
>>> available on InnoDB since MySQL 5.6.4. If you're running that version or
>>> newer, it is possible you could use InnoDB for that table, but as far as
>>> I know no one has tried it before.
>>>
>>> According to https://www.mediawiki.org/wiki/Compatibility#Database
>>> MediaWiki only requires MySQL 5.5.8, so we can't change that in our
>>> table definitions (yet).
>>>
>>> No idea about the 'math' table.
>> The math table isn't used, and it is being dropped in production:
>> https://phabricator.wikimedia.org/T196055
>>
>> Regarding MyISAM vs InnoDB: Always use InnoDB unless you have a very good
>> reason to use MyISAM (like the one mentioned about full-text indexes).
>>
>> Cheers
>> Manuel.
>> _______________________________________________
>> Wikitech-l mailing list
>> [hidden email]
>> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>
> _______________________________________________
> Wikitech-l mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l


_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l