Over the past couple of weeks we have been testing the new moodle.org servers under a variety of different settings and scenarios (Jordan's been doing most of the actual work).
The general approach was to tweak and tune three separate database configs (MySQL with MyISAM, MySQL InnoDB, and Postgres) as best as we could by running Jmeter , and then run a variety of workloads on all three to compare them.
Some notes are here:
http://docs.moodle.org/en/Performance_testing_moodle.org
The biggest workload simulated 300 people logging on at the same time, navigating to a forum discussion and making a post.
In summary:
MyISAM: can be a little flaky under very heavy loads that involve writing, and we certainly know all about table corruption from the old server.
Innodb: scaled well, had no errors and runs quite fast. It's still MySQL, so we get to use our existing knowledge, plus migration was easy (use the admin/innodb.php script in Moodle!).
Postgres: scaled well, had no errors with fast reads but slow writes. We had a very weird problem where the database would rapidly get slower and slower (vacuums did not help, only a re-index would solve it). I know this is not normal, it must be something to do with our environment, but it's currently still a frustrating mystery). In case you want to try it, Tim and Jordan have documented how to use Moodle to convert MySQL to Postgres
I was hoping we could use Postgres because it's what the more hardcore db nerds seem to all prefer, but from our testing InnoDB seems to do an equally good job running Moodle, and doesn't seem to have any downsides, so I'm naming it a winner for this particular situation.
Actually, the Postgres problem was even weirder than that. A re-index (even dropping the index and re-creating) did not change things. The only thing that good good perfomance back was dropping the rows that the JMeter user had added. The JMeter test was adding data to three tables, log, forum_post and forum_disucssion. The problem was not caused by log, but the other two.
It made no sense at all.
It made no sense at all.
tip o' the hat to you and especially to Jordan
Thanks for posting the information. It's extremely useful. 
Is is perhaps fair to assume that innodb would be the best bet for running higher loads of users? I've seen a few insert errors during high usage, but am using myisam. Also, you say it was an "easy" migration to innodb using the moodle script. Are their detailed instructions that can assist me if I decide to make the move?
Is is perhaps fair to assume that innodb would be the best bet for running higher loads of users? I've seen a few insert errors during high usage, but am using myisam. Also, you say it was an "easy" migration to innodb using the moodle script. Are their detailed instructions that can assist me if I decide to make the move?
Yes, I do feel tuned InnoDB on *good hardware* would generally be better for a heavily-loaded Moodle sites than MyISAM. It just seems stronger. There are a lot of variables though.
The conversion script is trivial and is at:
http://yoursite/admin/innodb.php
However, a 'How to convert MySQL to use InnoDB" would be a good page in Moodle Docs if it covered all the variables and issues in detail to help people decide what to do.
The conversion script is trivial and is at:
http://yoursite/admin/innodb.php
However, a 'How to convert MySQL to use InnoDB" would be a good page in Moodle Docs if it covered all the variables and issues in detail to help people decide what to do.
Those are very interesting results! And kind of unexpected...
Thanks.
mike
Thanks.
mike
On the Postgres slowdown over time issue - this is usually caused by tables not being vacuumed often enough (needing to vacuum is a bit of a PITA).
What version of Postgres did you use? I'd recommend 8.3 as it is (usually) much more forgiving about not vacuuming often enough, and the automatic vacuum works properly (which removes the need to manage vacuums). Also it is typically noticeably faster than earlier versions.
(addition):
Looking at the comments about dropping/deleting the Jmeter rows fixing the problem... suggests that Postgres was choosing a poor execution plan for queries involving those rows, probably due to insufficient vacuuming or analyzing (both of which should be fixable by the automatic vacuum in 8.3, altho you might need to make it a little more agressive).
What version of Postgres did you use? I'd recommend 8.3 as it is (usually) much more forgiving about not vacuuming often enough, and the automatic vacuum works properly (which removes the need to manage vacuums). Also it is typically noticeably faster than earlier versions.
(addition):
Looking at the comments about dropping/deleting the Jmeter rows fixing the problem... suggests that Postgres was choosing a poor execution plan for queries involving those rows, probably due to insufficient vacuuming or analyzing (both of which should be fixable by the automatic vacuum in 8.3, altho you might need to make it a little more agressive).
Unfortunately this issue had nothing to do with vacuuming as auto vacuum was turned on. I also ran manual full vacuums and analyze' as well as full re-indexing through out the test. At one point i had left the postgresql idle over a weekend and upon returning monday the slowness was still present when performing JMeter tests.
Tests were done with Postgres 8.3.5
Tests were done with Postgres 8.3.5
Ah right, I was thinking that autovacuum was not aggressive enough to stop table bloat, but vacuum full would have fixed that...
Hmm, so it looks like Postgres is choosing poor plans for queries on forum_post and forum_discussion - for some unknown reason... we (meaning me or some folks at Catalyst) possibly need to run your test script and try to replicate the problem (and identify the problematic queries) - is it complex to setup and run? (Moodle newbie here...)
Hmm, so it looks like Postgres is choosing poor plans for queries on forum_post and forum_discussion - for some unknown reason... we (meaning me or some folks at Catalyst) possibly need to run your test script and try to replicate the problem (and identify the problematic queries) - is it complex to setup and run? (Moodle newbie here...)
The test script is just a JMeter template and very easy to run on any moodle instance. The thing is the issue might be related to the moodle.org database (its not a standard moodle install and has been built up over time) rather than moodle itself and might not be replicatable without our database.
Right - could well be only reproducible with your data. Might be worth setting log_min_duration_statement and trying to catch the statement(s) that are slowing down over time.
I'm wondering if I might be able to get my hands on the JMeter script template you're referring to? I've been looking for something to use against our install, as we recently switched over to InnoDB (based on this thread).
If there's any way I could get the script(s), that would be fantastic.
Let me know thanks,
Mike.
If there's any way I could get the script(s), that would be fantastic.
Let me know thanks,
Mike.
The Jmeter script used to benchmark moodle.org is now in CVS and can be found at http://cvs.moodle.org/contrib/tools/jmeter/LoginForumView.jmx?view=co
you will need to modify some settings, specifically username/password and a few ID numbers relating to courses/users
you will need to modify some settings, specifically username/password and a few ID numbers relating to courses/users
This is interesting, the run down you gave seems to point towards Innodb as being the database engine to use.
Would you recommend making a move from MyISAM? We have not experienced any "problems" but I'm wondering if I could squeeze a little bit more performance out of our Moodle server.
Typically we have about 5-20 concurrent users, with a possibility of rising to something in the region of 100 at peak times.
We run both Apache and MySQL on the same server with a Ubuntu Linux OS. The server spec is 1x Quad Core Intel Xeon E5310 (1.6ghz), 4Gb RAM.
Of course there are a lot of variables to go by, but I was just wondering if you'd be inclined to make the move or stay put.
Would you recommend making a move from MyISAM? We have not experienced any "problems" but I'm wondering if I could squeeze a little bit more performance out of our Moodle server.
Typically we have about 5-20 concurrent users, with a possibility of rising to something in the region of 100 at peak times.
We run both Apache and MySQL on the same server with a Ubuntu Linux OS. The server spec is 1x Quad Core Intel Xeon E5310 (1.6ghz), 4Gb RAM.
Of course there are a lot of variables to go by, but I was just wondering if you'd be inclined to make the move or stay put.
I too am in the same situation. See my earlier post a couple of days ago ..
http://moodle.org/mod/forum/discuss.php?d=128062
I think we will use innoDB instead this time and before we go live with the new machine, I'll do some load testing
http://moodle.org/mod/forum/discuss.php?d=128062
I think we will use innoDB instead this time and before we go live with the new machine, I'll do some load testing