[Deploying Sakai] MySQL: MyISAM or InnoDB?

Tue Aug 9 08:32:50 PDT 2011

When we briefly had SAKAI_PRESENCE as a MEMORY table (some years ago),
it caused very bad contention because the engine appears to use a much
coarser locking strategy than for InnoDB tables. We switched it back to
InnoDB.

Regards
Stephen 

-- 
Stephen Marquard, Learning Technologies Co-ordinator
Centre for Educational Technology, University of Cape Town
http://www.cet.uct.ac.za
Email / IM (Jabber/XMPP): stephen.marquard at uct.ac.za
Phone: +27-21-650-5037 Cell: +27-83-500-5290 

>>> Wolfgang Rohregger <Wolfgang.Rohregger at mci.edu> 8/9/2011 3:01 PM
>>> 
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"
_______________________________________________
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"

###
UNIVERSITY OF CAPE TOWN 

This e-mail is subject to the UCT ICT policies and e-mail disclaimer
published on our website at
http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from
+27 21 650 9111. This e-mail is intended only for the person(s) to whom
it is addressed. If the e-mail has reached you in error, please notify
the author. If you are not the intended recipient of the e-mail you may
not use, disclose, copy, redirect or print the content. If this e-mail
is not related to the business of UCT it is sent by the sender in the
sender's individual capacity.

###