INNODB instead of MYISAM

Larger instances of ELGG have substantial benefit of INNODB as database engine over MYISAM since it supports record locking, where MYISAM tables locks the whole table during a write action. When this occurs, other users have to wait until the lock is freed. Obviously, this hardly occurs with a record locking based system.

The biggest reason for not moving to INNODB was that it did not support Full Text search, that is used in ELGG since 1.8.9. The good news is that since Mysql 5.6.4 "Full Text Search" is supported for INNODB.

I am currently running Mysql 5.6.10 with all elgg tables in INNODB and Full text search works !! How to do that.

First upgrade your mysql and php, using yum and/or rpm.

After that migrate the MYISAM tables to INNODB tables: To do this, run these commands (linux only)

#mysql -e "SHOW TABLES IN your_database_name;" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql

#perl -p -i -e 's/(search_[a-z_]+ ENGINE=)INNODB/\1MYISAM/g' alter_table.sql
#mysql your_database_name < alter_table.sql

(your_database_name represents the actual name of your ELGG database)

Afterwards you need to enable FULL text search by either setting the param innodb_optimize_fulltext_only to ON in phpmyadmin and restart your database. or manually by using mysql command prompt and use OPTIMIZE TABLE table_name; 

Just to be sure do it both !

Remark: In one of my instances i actually had to use "OPTIMIZE TABLE elgg_annotations; " to enable full text search for tags.

Special thanks to Jeroen Dalsem on pointing me to the direction of INNODB !

  • The core developers would like it if someone could write a script quantifying the performance differences between innodb and myisam on various Elgg datasets. That would help us make an objective decision and give us some numbers to brag about when we make the release that includes this change.

  • I like MYISAM because it lock whole table and we will easily find which table is locked.

  • First suggestion is to use INNODB for the ELGG community, so everyone can benefit from it at first hand.

    I will try to provide some stats, but honestly this is hard since the only way is to have hundreds to thousands users on an MYISAM and also on INNODB at the same time. Personnaly I upgraded to INNODB to see the benefit and I am already notice it by having a more responsive system. My system currently has 11.000 users and was slowing down due to writes.

  • @LIANG why is that a benefit ? Knowing who locked the table is only beneficial if it is possible to lock the table. Not having any table locked removes the need to know who did it. It is like wanting to know who stole your bike, instead of not being able to steal the bike in the first place.

  • One addtional remark: You need to changes in the these files. INSERT DELAYED is not supported by INNODB, nor is it usefull.

    File: engine/lib/location.php
    $query = "INSERT INTO {$CONFIG->dbprefix}geocode_cache "

    File: engine/lib/system_log.php 

    $query = "INSERT into {$CONFIG->dbprefix}system_log

     

  • Well just to let you know guys, We have been moved to INNODB from since elgg 1.8.3 is here. And it is working perfectly. And We are not working on a large database where we have around 2 million entities.

    Well we are not using elgg's full text search We have used elasticsearch for our fulltext feature. but there is no doubt INNODB with fulltext search make sense for elgg to move to INNODB.

    I am in little hurry at this time. But will try to post my experience with this change with some Stats.

  • Seleral of my tables would not convert.  The message was 

    Example error message: 

    ALTER TABLE elggsites_entity ENGINE=INNODB;
    ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

     

  • Which version of Mysql do you have ? It has to be at least 5.6.4 to be able to support FULLTEXT search

  • Hi @Gerard, I´m planning to migrate to INNODB, are these posted instructions updated?, Must I have any other consideration?

    I´ve Mysql 5.6.21 and Elgg 1.8.20 so...

    Summary:

    1. 

    #mysql -e "SHOW TABLES IN your_database_name;" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql

    #perl -p -i -e 's/(search_[a-z_]+ ENGINE=)INNODB/\1MYISAM/g' alter_table.sql
    #mysql your_database_name < alter_table.sql

    2.

    innodb_optimize_fulltext_only to ON

    3.

    File: engine/lib/location.php
    $query = "INSERT INTO {$CONFIG->dbprefix}geocode_cache "

    File: engine/lib/system_log.php 

    $query = "INSERT into {$CONFIG->dbprefix}system_log

    4.

    Migrate to INNODB!

Performance and Scalability

Performance and Scalability

If you've got a need for speed, this group is for you.