Ideas….

I just migrated MDID from a Windows Server to a new Windows Server.  It wasn’t as easy to figure out as you might think.  The MDID wiki doesn’t have directions on how to do this, so here are my directions.

First I always recommend keeping up the old installation while you’re migrating and not taking it down for at least a week or two (perhaps even longer if you’re paranoid).  That way if you need to, you can always go back.

Here are the steps that I followed:

1. I installed a blank MDID2 to make sure that I had everything I needed on the server to make MDID2 work.  When installing MySQL I made sure that it was the same version as the old server to ensure that MySQL weirdness didn’t interfere with the installation.  For instructions on installing MDID2, I used the MDID wiki’s instructions.  I actually ran the system and had our Visual Resources Curator test things out to see if it worked like he expected a fresh MDID would work (because lets face it, I don’t know nearly as much about the system as he does).
2. Once our Visual Resources Curator gave me the thumbs up I mounted, as a network drive, the directory on the old server that had the MDID2 files and images.  I then copied ALL of the files over to the new directory on the new server.  I then went home for the night…because that’s going to take a while.
3. The next morning go into the config.xml file (found at the root level of mdid2) and change the information in the file so that it’s settings match your new servers configuration.  Most likely all you’ll need to do is change the location of the image files or the username and password of the database, but you should check over the rest of the file just to be safe.
4. Create a mysqldump of the old database using: mysqldump -u mdid2 -p -q mdid2 > mdid2export.sql I then moved the dump over to the new server.  Keep in mind that your MySQL command may be different if:
   • you don’t use passwords
   • have a different MySQL user set to run mdid2
   • your mdid2 user doesn’t have privileges to do this type of command.
5. Once I got the dump onto the new server I opened the dump file in notepad and did a find replace.  In my case on the old server mdid was on the d drive and on the new server it was going to be on the c drive.  So I had notepad go through the file and find all instances of d:\\mdid2\\ and replace it with c:\\mdid2\\.  Again this may be different for you, depending on how your installation is set up.  Once this started I went to go get myself some tea, because this was going to take a bit (although not as long as moving all those files over).
6. Now that the database file has been “fixed” you’re ready to import the database.
   1. Go into MySQL’s commandline and drop the existing mdid2 database: drop database mdid2;
   2. Recreate the mdid2 database: create database mdid2; The reason you just destroyed and recreated the database was because the database has structure and data attached to it based on the work you did in step 1, you want to overwrite that.
   3. import the mysql database by going to Window’s commandline and doing: mysql -u mdid2 -p mdid2 < mdid2export.sql
7. This step is key to avoiding any indexing weirdness you may see.  Trust me, I did these steps multiple times before finally figuring this out (thanks to the mdid listserv).  You’ll need to go in and delete all instances of the _ftindex directory in the image directory.  Go through each and every directory in the mdid2/image directory to get rid of this folder.  If you don’t you’ll find some weirdness in the indexing of data.  Don’t worry this directory will be rebuilt once you start up mdid2.
8. Start up IIS and wait a bit for the full text index to finish up.  If you want, you can log in to your newly migrated MDID2 and click background transactions to see if the indexing process is still running (it should be the second thing in the list).
9. Last but certainly not least, have your Visual Resources Curator check the system over and give their stamp of approval.  Without that, you might as well be dead in the water.

Like I stated above, this is the process that I followed.  Everyone’s systems are set up differently, but hopefully this can act as a guide of sorts to whatever poor schmuck needs to do something similar (ie me…so I don’t forget like I always seem to).

For those of you that aren’t Systems Administrators, here is the reason you don’t want to be.  At 12:30am when things go down, you have to wake up and figure out why.  And here I am writing a blog post about it.  Luckily its not 4am.  That was totally worse (I say this now…let’s see how long it takes me to figure this stuff out).

So I got my text message at 12:30am.  It confused me but then I woke up a bit.  It was a good thing I wrote the notes I did in my previous post, because trust me, I could have never come up with that stuff on my own right now.

First thing I did was run top.  CPU was at 100.1% (way to work hard server) for the mysqld process.  Second thing I did was check the slow query log.  There were actually a few slow queries, the first ones are still puzzling me.  Basically these were checking a table that had a whole lot of rows of data, the data didn’t need to be stored in those tables, i removed the data, and I stopped logging that data in the tables.  I probably won’t see those queries ever again. But this doesn’t really matter, those queries weren’t the ones hanging up the system, they were only take 11 t0 17 seconds to run.

I did see the last query that ran though, and this seems to be part of the problem, but not the query that was actually causing mysqld to hang. This query was:

INSERT INTO stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)

SELECT ‘enrolments’, timeend, courseid, roleid, COUNT(DISTINCT userid), 0
FROM (
SELECT 1251691200 AS timeend, pl.courseid, pl.roleid, pl.userid
FROM (
SELECT DISTINCT ra.roleid, ra.userid, c.id as courseid
FROM role_assignments ra JOIN context ctx
ON ctx.id = ra.contextid
CROSS JOIN course c
JOIN role_capabilities rc
ON rc.roleid = ra.roleid LEFT OUTER JOIN course_categories cc1
ON cc1.id = c.category
WHERE ((rc.capability = ‘moodle/course:view’ )
AND rc.permission = 1 AND rc.contextid = 1
AND (ctx.contextlevel = 10
OR (c.id = ctx.instanceid AND ctx.contextlevel = 50) OR (cc1.id = ctx.instanceid AND ctx.contextlevel = 40)))
) pl
) inline_view
GROUP BY timeend, courseid, roleid;

This query took a total of 41 seconds to execute.  I’m assuming that the next query after this was the one that brought the house down.  I saw the beginning of this when I went into mysql and did a show processlist:

+——-+————+———–+——–+———+——+————–+——————————————————————————————————+
| Id    | User       | Host      | db     | Command | Time | State        | Info                                                                                                 |
+——-+————+———–+——–+———+——+————–+——————————————————————————————————+
| 45654 | moodleuser | localhost | moodle | Query   | 1776 | Sending data | UPDATE stats_daily
SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
|

The full query finally showed up in my query log once I killed the process.  Here it is in case you’re curious:

090902  0:01:29      45654 Query       UPDATE stats_daily
SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
FROM role_assignments ra JOIN context ctx
ON ctx.id = ra.contextid
CROSS JOIN course c
JOIN role_capabilities rc
ON rc.roleid = ra.roleid LEFT OUTER JOIN course_categories cc1
ON cc1.id = c.category
WHERE ra.roleid = stats_daily.roleid AND
c.id = stats_daily.courseid AND
((rc.capability = ‘moodle/course:view’ )
AND rc.permission = 1 AND rc.contextid = 1
AND (ctx.contextlevel = 10
OR (c.id = ctx.instanceid AND ctx.contextlevel = 50) OR (cc1.id = ctx.instanceid AND ctx.contextlevel = 40))) AND
EXISTS (SELECT ‘x’
FROM log l
WHERE l.course = stats_daily.courseid AND
l.userid = ra.userid AND l.time >= 1251604800  AND l.time  < 1251691200))
WHERE stats_daily.stattype = ‘enrolments’ AND
stats_daily.timeend = 1251691200 AND
stats_daily.courseid IN
(SELECT DISTINCT l.course
FROM log l
WHERE l.time >= 1251604800  AND l.time  < 1251691200)

I also saw a whole bunch of other queries locked, waiting for this query to run.  I thought that perhaps switching the tables over to InnoDB rather than MyISAM might help alleviate the problem, but the truth is this appears to be a “the query is just ridiculous” problem rather than a “InnoDB is better at performance” problem.

So at anyrate I looked around and it seems this is a problem with the stats that Moodle keeps about itself.  Apparently in version 1.6 people had this problem a lot (because stats was new).  Basically I enabled stats because someone thought it was a good idea and I didn’t bother to consider how resource intensive this process could be (note to self: be a better sys admin and read documentation when someone thinks something might be useful).  I changed some settings in the stats configuration page and hope that this reduces the load and processing time of the stats query.  We’ll see if this fixes my problem.

Also, the amount of time I spent on this right now totally makes up for leaving work an hour and a half early to go meet my brother in law the electrician so he could replace outlets and light fixtures (my $12 sconces from lowes look quite lovely).

If you’re not interested in Moodle or troubleshooting MySQL problems, than you probably don’t want to read this post.  I however wanted to write some notes for myself and need it to be portable.  Since that’s technically the purpose of this blog, I figured I should put it here.

Recently Nagios started informing me that HTTPS was going down infrequently (but started to become more frequent).

***** Nagios  *****
Notification Type: PROBLEM
Service: HTTPS
Host: Moodle Server
Address: 123.456.78.912
State: CRITICAL
Date/Time: Fri Aug 28 01:06:50 EDT 2009
Additional Info:
CRITICAL – Socket timeout after 10 seconds

What was strange was that HTTP wasn’t going down.  I felt like Nagios was lying to me so I figured I would have to wake up late at night (early in the morning?) and catch it lying to me.  To do this I decided I’d try to figure out if my cellphone had an email to associate to text messaging.  Mine wasn’t working so I had to talk to Sprint about it.

In the mean time though, it was still going down.  Luckily last night at 4am I randomly woke up. I ran downstairs (imagine kid at Christmas…but like a reverse Christmas) and fired up the laptop.  Sure enough when I went to the Moodle homepage I saw this:

Error: Database connection failed.

It is possible that the database is overloaded or otherwise not running properly.

The site administrator should also check that the database details have been correctly specified in config.php

Now in hindsight, there were so many things I could have done at this point.  But it was 4am and my house was freezing so I made some mistakes.  The first thing I did was restart Apache.  It appeared to be running and the restart didn’t throw any errors, but it didn’t fix the problem either.  Next I “turned off SSL”.  I was able to get to the error page without the HTTPS so I determined at this point that Nagios clearly was lying to me, something else was wrong (p.s. i am thankful to Nagios for telling me that something was wrong).  So then I decided that maybe it was a database problem.  I went into MySQL and was able to access the Moodle database and do a “show tables;”.  So I thought at that point I should restart MySQL, in hindsight there are so many other things I should have done first, but I will document those below.  A restart of MySQL fixed the issue.

So what will I do next time (and yes…there will be a next time):

1) run top on the server: See if MySQL is hogging the CPU.  This could lead you to a number of options.

2) check the mysql-slow-queries.log: I set up the mysql option log-slow-queries.  this in conjunction with the long-query-time option will log any query that takes over a certain amount of time (default is 10s).

3) show mysql processlist: If you haven’t set up the log slow queries option this might also be helpful in determining what mysql is doing.  Here is a brief explaination of show processlist and its companion of kill <processid>.  I also found this handy script that you could set up as part of a cron to kill any runaway processes.

4) query is poorly written: I’ve found a number of reports that MySQL queries are poorly written in Moodle and that these can cause the CPU usage to top out at %100 which causes Moodle to be unusable.  Below are a few Moodle Tracker reports of this:

• http://tracker.moodle.org/browse/MDL-14911
• http://tracker.moodle.org/browse/MDL-19584
• http://tracker.moodle.org/browse/MDL-17051
• http://tracker.moodle.org/browse/MDL-10848

To see if this is the case with your Moodle use the slow query logging (or just the plain old query logging) with a backup of your database to see if you can replicate the problem somewhere else.  If you can the problem is probably the query and maybe you can figure out a way to rewrite the query (or get it rewritten) to see if that fixes the problem.

5) check your persistent db connection: Check to see if you have persistent database connections set up in the Moodle config.php file and in the php.ini file.  If you do, turn it off and see if this solves the problem.  it may be that your connections aren’t closing and they are killing your database regularly.

6) check your max connections: Are the maximum number of Apache connections greater than your maximum number of MySQL connections?  If so that might be a problem.  Basically users could get to the website but then when they go to request something in the database they can’t because the # of connections are taken up.  I have a feeling though that this may be a completely different error than what I was experiencing (people…including myself…really need to take screenshots or document errors better).

7) check your MySQL performance: Perhaps you haven’t configured Moodle right.  Take a look at these performance tuning tips to see if you can get it working better.

8) check your database tables: Stop mysqld and run a check on your Moodle tables.  See if some of the tables are corrupted, if they are fix them.

So if you’re wondering what I’m doing at 4am tomorrow, there’s the list.  Wish me luck.