[Deploying Sakai] MySQL: MyISAM or InnoDB?

Wolfgang Rohregger Wolfgang.Rohregger at mci.edu
Tue Aug 9 06:01:45 PDT 2011


Hi,

just had to add to the conversion stuff below one step number 10 (or just change the ENGINE type of SAKAI_PRESENCE in the result.sql file):

(10) convert SAKAI_PRESENCE as detailed in https://confluence.sakaiproject.org/display/DOC/Sakai+Admin+Guide+-+Database+Configuration+and+Tuning

DROP TABLE SAKAI_PRESENCE;

CREATE TABLE `SAKAI_PRESENCE` (
`SESSION_ID` varchar(36) default NULL,
`LOCATION_ID` varchar(255) default NULL,
KEY `SAKAI_PRESENCE_SESSION_INDEX` (`SESSION_ID`),
KEY `SAKAI_PRESENCE_LOCATION_INDEX` (`LOCATION_ID`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

After the conversion I had performance issues with my course information system connection that updates the Sakai sites.

Cheers,

Wolfgang

________________________________________
Von: production-bounces at collab.sakaiproject.org [production-bounces at collab.sakaiproject.org] im Auftrag von Wolfgang Rohregger [Wolfgang.Rohregger at mci.edu]
Gesendet: Montag, 08. August 2011 14:04
An: Hedrick Charles; Steve Swinsburg
Cc: production at collab.sakaiproject.org
Betreff: Re: [Deploying Sakai] MySQL: MyISAM or InnoDB?

I guess we had luck. At our pilot instance the default storage engine setting of MySQL had been set to MyISAM when we started with Sakai pilot, back then with version 2.6. I am currently testing a conversion process for the database, that I will share here. So far there have been no problems with this process. Before migration to 2.8 I will compare the "converted" schema to a vanilla 2.8 db schema to check if anything is missing.

As there are no foreign keys on MyISAM, I hope that none that should be in there are missing. I will see this when comparing the db schemas anyway.


Converting the database to InnoDB, assuming that the OS is Linux:

(1) Create database dump of Sakai, e.g. mysqldump --user=sakai -p --create-options --default-character-set=utf8 --hex-blob --disable-keys --single-transaction --comments=1 --complete-insert sakai -r SAKAIDUMP_FILE.sql

(2) Prepare a command file "InnoDB_conversion.txt" for the stream editor sed, containing one line with: s/ENGINE=MyISAM/ENGINE=InnoDB/g

(3) run sed: sed -f InnoDB_conversion.txt SAKAIDUMP_FILE.sql > result.sql
The command file from (2) tells sed to replace every occurence of "ENGINE=MyISAM" with "ENGINE=InnoDB". sed works even with very big files, so it will work even on big database dumps. The resulting database dump will be inside result.sql

(4) Check with a diff that only DDL statements have been modified: diff SAKAIDUMP_FILE.sql result.sql | less

(5) Don't forget to set the default-storage-engine on all MySQL databases by adding this line to the server part of the MySQL server configuration file: default-storage-engine = InnoDB

(6) Test this by restarting the MySQL server and querying the server with a MySQL client using the command "SHOW VARIABLES;". "default storage engine" or "database engine" should be set to "InnoDB".

(7) Now copy the dump on to a test system with its own MySQL server (with correct settings) and import it:
drop database sakai;
create database sakai default character set utf8;
source result.sql;

(8) Maybe there have been some tools, that couldn't execute their index creation. auto.ddl=true in sakai.properties should resolve this for the first run with the converted database.

(9) Start up the test Sakai instance and check if everything is working as expected.




Cheers,

Wolfgang


________________________________________
Von: production-bounces at collab.sakaiproject.org [production-bounces at collab.sakaiproject.org] im Auftrag von Hedrick Charles [hedrick at rutgers.edu]
Gesendet: Sonntag, 07. August 2011 03:55
An: Steve Swinsburg
Cc: production at collab.sakaiproject.org; Wolfgang Rohregger
Betreff: Re: [Deploying Sakai] MySQL: MyISAM or InnoDB?

Even back in very early days, like 2.1, you really, really wanted to use InnoDB. We mistakenly started 2.0 with Myisam. It took us quite a while to clean up the damage.

On Aug 1, 2011, at 7:00:20 AM, Steve Swinsburg wrote:

I've had troubles running the Sakai 2.8.0 DB upgrade scripts for MySQL. After doing some research, there have been at least some indicators that parts, if not the whole, database enginge of MySQL might have been switched over to InnoDB.

Is InnoDB now used for all Sakai tables and should I migrate the old MyISAM tables to InnoDB?


_______________________________________________
production mailing list
production at collab.sakaiproject.org
http://collab.sakaiproject.org/mailman/listinfo/production

TO UNSUBSCRIBE: send email to production-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"


More information about the production mailing list