Hello,
I was wondering which is the best method to change our existing Moodle MyISAM db engine to InnoDB would be?
Would you use the convert script built in Moodle :
http://mymoodle.domain.org/admin/innodb.php
or via MySQL manipulation :
- dump the table to a SQL file
- change table type from MyISAM to InnoDB in the dumped file
- SET AUTOCOMMIT to 0 (for speeding up the process)
- load the table
- SET AUTOCOMMIT to 1
- enable keys (the index is rebuilt at this point) needed?
Thanks
Use the built in convert script
Thank you. I have done just that and it will be interesting to see if the stats run faster or about the same speed.
Also, could anyone recommend urls or online docs on how to best do a backup of the innoDB and test a restore?
would it be the same way using mysqldump if it's MyISAM? I'm guessing yes. Any gotchas?
Also the best method of reindexing the innoDB tables?
What about the settings in my.cnf? Should I fine tune that also for a innoDB db engine?
thanks
chris
Also, could anyone recommend urls or online docs on how to best do a backup of the innoDB and test a restore?
would it be the same way using mysqldump if it's MyISAM? I'm guessing yes. Any gotchas?
Also the best method of reindexing the innoDB tables?
What about the settings in my.cnf? Should I fine tune that also for a innoDB db engine?
thanks
chris
mysqldump is fine for backing up innodb.
mysqlcheck is able to re-index your db/tables (optimize on innodb forces re-index)
my.cnf can be tweaked to your liking, see http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html
mysqlcheck is able to re-index your db/tables (optimize on innodb forces re-index)
my.cnf can be tweaked to your liking, see http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html
Thank you Jordan.
If case you or other Moodlers are interested, we've just updated our Moodle servers. Here are our specs:
Software:
· Moodle weekly build 1.9.5+
· SuSE Linux Enterprise Server 10 SP2 32-bit with latest patches
· Apache Apache/2.2.3
· PHP 5.1.2 +
· MySQL 5.0.26 Standard / innoDB database engine
Networking Security:
· Novell Access Manager 3
· Novell eDirectory / LDAP
Hardware:
· HP Blade (model BL460c G1) with local 72 GB HD
· Two (2) Quad-Core Intel® Xeon® processor X5450 (3.00GHz 1333 FSB, 120 W) (Part number 463056-B21)
· 16 GB FBD PC2-5300 2 x 8 GB Kit (Part number 413015-B21)
· HP Smart Array E200i Controller (447711-B21)
· HP 128MB Battery-Backed Read/Write cache Module
· Two (2) embedded NC373i Multifunction Gigabit ( two embedded NICs)
· QLogic QMH2462 4Gb FC HBA for HP c-Class BladeSystem (403619-B21)
· EVA 6100 disks 8 x 600GB 10K Fibre Channel (for SAN storage)
Logical Design:
------------------
· Separating the disk I/O from the application, DB, and data over SAN mount points should provide improved performance.
· Server will have the latest SuSE 10 SP2 patched version, Apache, PHP and MySQL, and Moodle 1.9.5 weekly build installed on local drive.
· There will be 2 LUNs setup connected to our SAN at our A location – one connected to MySQL, which requires 50 GB and another for MoodleData, which requires 500 GB to start.
· The innoDB database engine will be used instead of MyISAM to ensure ACID compliance for data integrity. Because Moodle has a lot of concurrent inserts/updates, and innoDB supports row-locking. MyISAM is unable to process high percentage of inserts/updates.
So far, the server is rockin'
If case you or other Moodlers are interested, we've just updated our Moodle servers. Here are our specs:
Software:
· Moodle weekly build 1.9.5+
· SuSE Linux Enterprise Server 10 SP2 32-bit with latest patches
· Apache Apache/2.2.3
· PHP 5.1.2 +
· MySQL 5.0.26 Standard / innoDB database engine
Networking Security:
· Novell Access Manager 3
· Novell eDirectory / LDAP
Hardware:
· HP Blade (model BL460c G1) with local 72 GB HD
· Two (2) Quad-Core Intel® Xeon® processor X5450 (3.00GHz 1333 FSB, 120 W) (Part number 463056-B21)
· 16 GB FBD PC2-5300 2 x 8 GB Kit (Part number 413015-B21)
· HP Smart Array E200i Controller (447711-B21)
· HP 128MB Battery-Backed Read/Write cache Module
· Two (2) embedded NC373i Multifunction Gigabit ( two embedded NICs)
· QLogic QMH2462 4Gb FC HBA for HP c-Class BladeSystem (403619-B21)
· EVA 6100 disks 8 x 600GB 10K Fibre Channel (for SAN storage)
Logical Design:
------------------
· Separating the disk I/O from the application, DB, and data over SAN mount points should provide improved performance.
· Server will have the latest SuSE 10 SP2 patched version, Apache, PHP and MySQL, and Moodle 1.9.5 weekly build installed on local drive.
· There will be 2 LUNs setup connected to our SAN at our A location – one connected to MySQL, which requires 50 GB and another for MoodleData, which requires 500 GB to start.
· The innoDB database engine will be used instead of MyISAM to ensure ACID compliance for data integrity. Because Moodle has a lot of concurrent inserts/updates, and innoDB supports row-locking. MyISAM is unable to process high percentage of inserts/updates.
So far, the server is rockin'
Hardware sounds nice, with 16GB im sure you plan on running the entire database in memory!
You should also consider using something like php eaccelerator to cache the php files.
I would highly recommmend ditching php 5.1 and mysql 5.0 in favor of php 5.2(.11) and mysql 5.1(.39)
There are SUSE mysql packages available from http://dev.mysql.com/downloads/mysql/5.1.html#downloads
I found a huge performance increase with using the latest mysql over the distro bundled versions.
You should also consider using something like php eaccelerator to cache the php files.
I would highly recommmend ditching php 5.1 and mysql 5.0 in favor of php 5.2(.11) and mysql 5.1(.39)
There are SUSE mysql packages available from http://dev.mysql.com/downloads/mysql/5.1.html#downloads
I found a huge performance increase with using the latest mysql over the distro bundled versions.
Jordan,
I was thinking of doing the same thing but my concerns a few weeks ago were:
1. php eaccelerator may cause problems and from what I read, it's not that "enterprise-level" yet. I could be wrong so please correct me.
2. I was also looking into not using the distro's php and mysql but thought that these packages may be optimized more for them.
3. The good thing is that my test server mirrors production so that means it's the same and I could the rebuild to see the difference. Nice when the company I work for believes in spending some $$$ for both platforms. =)
Do you have any good links to recompile using php 5.2 instead?
thanks
Chris
I was thinking of doing the same thing but my concerns a few weeks ago were:
1. php eaccelerator may cause problems and from what I read, it's not that "enterprise-level" yet. I could be wrong so please correct me.
2. I was also looking into not using the distro's php and mysql but thought that these packages may be optimized more for them.
3. The good thing is that my test server mirrors production so that means it's the same and I could the rebuild to see the difference. Nice when the company I work for believes in spending some $$$ for both platforms. =)
Do you have any good links to recompile using php 5.2 instead?
thanks
Chris
Awww heck, I took a chance and installed eAccelerator and yes it does look even faster than before ... let's see how it handles loads and concurrent users/connections.
I'll still try and go ahead with the PHP upgrade though it's tempting to leave it as is =)
I'll still try and go ahead with the PHP upgrade though it's tempting to leave it as is =)
1. we use eaccelerator on moodle.org without any problems, I couldn't imagine running without it!
2.MySQL.com packages will be much better than ones rolled by SUSE, ideally you can compile from source on the server you plan to run it on for even more hardware optimised code goodness.
As for PHP, we use Red Hat Enterprise and I am using RPMS rolled using the official Red Hat SRPM spec file with the latest php source tarball - this way RPM dependancies are still met and you dont end up in the dreaded rpmhell! You should be able to do this with SUSE packages as well.
2.MySQL.com packages will be much better than ones rolled by SUSE, ideally you can compile from source on the server you plan to run it on for even more hardware optimised code goodness.
As for PHP, we use Red Hat Enterprise and I am using RPMS rolled using the official Red Hat SRPM spec file with the latest php source tarball - this way RPM dependancies are still met and you dont end up in the dreaded rpmhell! You should be able to do this with SUSE packages as well.
Hey Jordan,
Our org has decided to use Red Hat instead of SLES instead.
Are you running 64-bit or 32-bit RHEL? I'm thinking that 32-bit is the way to go to prevent the 64-bit bugs I've read about. Is this your recommendation.
I'd be interested to know who has installed RHEL 64-BIT and if they have encountered any problems.
Thanks
Chris
Our org has decided to use Red Hat instead of SLES instead.
Are you running 64-bit or 32-bit RHEL? I'm thinking that 32-bit is the way to go to prevent the 64-bit bugs I've read about. Is this your recommendation.
I'd be interested to know who has installed RHEL 64-BIT and if they have encountered any problems.
Thanks
Chris
On a split-service server, I am running 32bit webservers and a 64bit DB server.
No problems with CentOS which is essentially the same as RHEL.
No problems with CentOS which is essentially the same as RHEL.
So your MySQL is 64 bit then, correct? and CentOS is 32...
My apologies for not being explicit.
The database server is running 64bit CentOS with Mysql 64bit as well.
Cheers,
-n
The database server is running 64bit CentOS with Mysql 64bit as well.
Cheers,
-n
Noveck,
How are you connecting your Moodle app to your db? via dedicated network? what is the load like and how long has that been up and running?
thanks
How are you connecting your Moodle app to your db? via dedicated network? what is the load like and how long has that been up and running?
thanks
2 32 bit CentOS webservers running UltraMonkey configured for High Availibility (not load balancing per se) with eaccelerator
1 64bit CentOS Database server (soon to install memcached)
Same subnet with gigabit netowrk cards.
The moodle code and config.php are identical on both webservers and the setup is session aware - thanks to the ultramonkey.
This has been running for over 3 years.
I have some load issues, purely because i have outgrown the hardware, and the db server only has 4GB RAM. The userbase has doubled each year since initial deployment and is now up to 22000 Users.
The webservers also have 'underspecced' memory, but this is going to be upgraded soon.
Typically normal RAM on the DB Server usage is 3.95 , average load is 2-4 and when we have quizzes or heavy assignment submissions, my 3 servers starts swapping to disk (eeek). I have seen the Webservers' load hit 30, with quizzes and hundreds of simultaneous logins.
I am currently working on a RAM upgrade, next year perhaps I am considering a system rebuild on new servers.
Considering this setup: http://www.howtoforge.com/high-availability-load-balancer-haproxy-heartbeat-fedora8
It goes without saying that I will need to test this proposed setup heavily before even suggesting migration.
Whew!
Cheers,
-n
1 64bit CentOS Database server (soon to install memcached)
Same subnet with gigabit netowrk cards.
The moodle code and config.php are identical on both webservers and the setup is session aware - thanks to the ultramonkey.
This has been running for over 3 years.
I have some load issues, purely because i have outgrown the hardware, and the db server only has 4GB RAM. The userbase has doubled each year since initial deployment and is now up to 22000 Users.
The webservers also have 'underspecced' memory, but this is going to be upgraded soon.
Typically normal RAM on the DB Server usage is 3.95 , average load is 2-4 and when we have quizzes or heavy assignment submissions, my 3 servers starts swapping to disk (eeek). I have seen the Webservers' load hit 30, with quizzes and hundreds of simultaneous logins.
I am currently working on a RAM upgrade, next year perhaps I am considering a system rebuild on new servers.
Considering this setup: http://www.howtoforge.com/high-availability-load-balancer-haproxy-heartbeat-fedora8
It goes without saying that I will need to test this proposed setup heavily before even suggesting migration.
Whew!
Cheers,
-n
We use 64bit RHEL for all our moodle.[org/com]servers (10 of them) with no issues relating to 64bit.
I think I mentioned in an earlier post, we use the latest MySQL stable release from mysql.com and I roll my own rpms for the latest PHP (5.2.11)
Otherwise, its pretty much stock standard RHEL 5.4 64bit across the board
I think I mentioned in an earlier post, we use the latest MySQL stable release from mysql.com and I roll my own rpms for the latest PHP (5.2.11)
Otherwise, its pretty much stock standard RHEL 5.4 64bit across the board
Yes. The only issue with 64 bit is that it requires nearly twice as much memory to execute the same code. However, it lets you add much, much more memory to your server, which more than compensates. I would have thought it is not a big deal these days, but I have not seen any straight performance comparisons.
What RHEL 64bit bugs?
Hi Jordan,
Its seems like converting them to InnoDB has caused stats error when the cron runs. Looks like a documented bug ... 0 days of statistics ... error in processing.
Any tips as to why this may be happening...
Could be the bug in /lib/statslib.php the way that innodb uses temp files that locks my CPU 106% for close to 9 hours where I had to restart the service.
don't know why ... frustrating and thinking of going back to MyISAM.
Its seems like converting them to InnoDB has caused stats error when the cron runs. Looks like a documented bug ... 0 days of statistics ... error in processing.
Any tips as to why this may be happening...
Could be the bug in /lib/statslib.php the way that innodb uses temp files that locks my CPU 106% for close to 9 hours where I had to restart the service.
don't know why ... frustrating and thinking of going back to MyISAM.
Christopher,
I cannot reproduce your issue with InnoDB and stats on 1.9.7 or 1.9.8 - its working fine for me here and on moodle.org
I suggest you file a bug report in the tracker
I cannot reproduce your issue with InnoDB and stats on 1.9.7 or 1.9.8 - its working fine for me here and on moodle.org
I suggest you file a bug report in the tracker
Hi Jordan,
I believe it's already there.. heard it's the way the query is written for innodb engine types.
http://tracker.moodle.org/browse/MDL-18484
Running daily statistics gathering, starting at ....:
......error occured, completed 0 days of statistics.
I have a test system that is exactly the same as production but it works on the test one only... someone else has experienced the same thing.
What are your innoDB settings? maybe i missed something... my.cnf, etc.
http://moodle.org/mod/forum/discuss.php?d=118272#p641711
http://moodle.org/mod/forum/discuss.php?d=103883
thanks
I believe it's already there.. heard it's the way the query is written for innodb engine types.
http://tracker.moodle.org/browse/MDL-18484
Running daily statistics gathering, starting at ....:
......error occured, completed 0 days of statistics.
I have a test system that is exactly the same as production but it works on the test one only... someone else has experienced the same thing.
What are your innoDB settings? maybe i missed something... my.cnf, etc.
http://moodle.org/mod/forum/discuss.php?d=118272#p641711
http://moodle.org/mod/forum/discuss.php?d=103883
thanks
Jordan,
I noticed that the script is "unsupported"
is this correct?
moodle, Our Courses -> Administration -> Unsupported: Convert to InnoDB
what does unsupported mean?
I noticed that the script is "unsupported"
is this correct?
moodle, Our Courses -> Administration -> Unsupported: Convert to InnoDB
what does unsupported mean?
It means that if you run it and your Moodle site turns into a hamster, or something else weird happens, you can't blame us; and more importantly we are not promising to help you sort out the mess.
On the other hand, the script has worked fine for other people in the past, and so is potentially useful, which is why we keep the script in the standard distribution. So, if you take a full backup first, and know how to restore it should the worst happen, then go ahead and try it.
On the other hand, the script has worked fine for other people in the past, and so is potentially useful, which is why we keep the script in the standard distribution. So, if you take a full backup first, and know how to restore it should the worst happen, then go ahead and try it.
Here is a good article to understand the differences between ISam and InnoDB.
http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB
John Wyatt
John,
I came across this article also and I was just wondering why the innoDB conversion script that come with Moodle is "unsupported".
For the record, I did convert to innoDB and will run some test in the next few weeks. Luckily we are still in end user testing until mid November.
I came across this article also and I was just wondering why the innoDB conversion script that come with Moodle is "unsupported".
For the record, I did convert to innoDB and will run some test in the next few weeks. Luckily we are still in end user testing until mid November.
I just did it on our Moodle 2.0 test site and it looks like it went without issue. I just used the MySQL dump command (the one I use for DB backups), opened up the file in notepad and did a find/replace, replacing MyISAM with InnoDB. Then used the restore command to put it back in the server. Took less than 3 minutes.
Does anyone know if this causes any long term issues?