Moodle

I do not think the upgrade is "severely defective"

• the differences in keys and indexes are not IMHO critical
• switched order of fields too - if I remember it correctly pg has much more incorrect orders

And yes, the performance is much worse in 1.8.x than in 1.6.x, 1.9.x should scale much better for large sites. In fact you must tune the server differently for 1.6/7/8/9 to get some better results.

Hi Heinz-Günter,

100% agree with Petr about order and indexes not being really critical (although they can have some impact, for sure). And that was the main cause to implement, under Admin->XMLDB Editor, one utility to look for all those missing indexes.

In fact, Moodle 1.9 also includes one additional check to look for all those INT <==> BIGINT differences so you can end with one DB 100% equivalent to one installed from scratch.

About the field order within tables it's not important at all, in fact only MySQL allows you to "place" new fields in order. It isn't significant at all.

Also, when reading the discussion above ( http://moodle.org/mod/forum/discuss.php?d=83216 ) I've seen that you comment about the workshop->phase field being missing in the upgrade. And that's really strange, mainly because that field exists since, at least, Moodle 1.4, so the upgrade from 1.6 to 1.8 has nothing to do with that field (I guess the problem was in some previous upgrade).

In fact, one of the new features arriving with Moodle 1.7 was the improved upgrade script, where all DBs share the same upgrade execution, allowing to develop once and execute everywhere. And it's really more consistent than previous individual scripts.

Finally, as reference, we performed all those comparisons and analyzed them carefully in http://tracker.moodle.org/browse/MDL-6855 .

Ah, I missed this question:

When upgrading... why do you upgraded to 1.8.2 ? IMO it's always recommendable to upgrade to the latest "" version in the series you want, in this case, 1.8.3 (right now) or 1.8.2+ (if 1.8.3 wasn't available the day you started the upgrade). But always the "+" release IMO.

Ciao

Dear Petr and Eloy,

We had to upgrade to 1.8.2, because we ideally only perform upgrades in the semester break. At that time, Moodle 1.8.2 was the most up-to-date version available. It is completely unrealistic to expect administrators to be constantly upgrading production systems. An upgrade to 1.9 is not an option for us at this stage, firstly for the reason stated above and secondly because 1.9 has not yet been cleared by moodle.org for productive use. We are not prepared to take the chance of installing a beta system and aggravating our 33 248 users even more.

And just to clear up the facts: I did not make any remark about missing workshops. That issue was raised by André Krüger.

This bug is not a performance issue! An Apple Quadcore Xserve with 8 GB of main memory, which was our emergency solution after the initial crash, was down in 2 min. We transferred our database to the university's central DB server, a Solaris cluster that serves over 80 databases, and were required to remove our Moodle database by the admin, because we crashed the server in 3 min! The problem was caused by Moodle spawning multiple processes and deadlocking the server.

Only after we resolved the issues mentioned in this bug report and in the forum postings were we able to resume normal operations.

This bug is not an issue that should be trivialised. With respect, I fail to see how incorrect indices and missing keys are not critical. Indices are directly related to query optimisation. When the indices are damaged or missing, it has a direct impact on how quickly queries can be processed. To date we have 33 248 users on our Moodle. If the query optimisation is dodgy, then believe me, indices are an issue.

And since one can only efficiently reference a dataset uniquely by means of keys (foreign keys would not be efficient), I fail to see how missing keys is not a critical issue. At the risk of repeating myself: our database wasn't slow, it was completely deadlocked, and could no longer process any queries, and this was directly related to poor quality control and dodgy upgrade scripts released by moodle.org. Obviously the analysis conducted for Moodle 1.7 missed some issues. As you can clearly see from the forum postings, other users have had similar issues and ours is not an isolated case.

We purely entered this bug as a courtesy to the Moodle community. We have already solved the problem to our satisfaction and if you are not interested in taking any steps, well, that's disappointing, but your decision to make. I can well understand if you are focussing your energies on the release of 1.9, because trying to fix 1.8 would appear to be a waste of time. It is unfortunate that moodle.org even released 1.8, because it was obviously far from being production ready.

Best regards,
Heinz Kuper

Hi Heinz,

first of all, and most important! Please, don't get worried about answer (neither its tone). I'm really grateful about your courtesy and interest sharing your experience and problems. Absolutely! Tons of thanks! That's the way!

So, please, consider all these as one open & ongoing discussion about the problem, always trying to find its causes and providing the best possible solution. At least that's my objective. Nothing else. Sincerely.

After that, some comments about your message:

• About the missing workshop->phase thing, sorry, I simply mixed your names in my mind. Apologizes for that.

• About the indexes and keys I agree that they are important, very important. I never have said that they aren't important (just pointed about the fact that the missing ones weren't really critical at all). And that's the cause for Moodle 1.8.1... 1.8.3+ evolution, where this has been addressed by:

1) Adding some new indexes as part of the upgraded (detected after 1.8.0 release).
2) Adding the "search for missing indexes" utility inside the XMLDB editor. It will detect automatically all those missing indexes, generating the needed SQL code to add them.

With both, all your indexes (and keys - they are "the same" in the Moodle world) should be up-to-date. That's all.

• About the target upgrade I didn't suggested you to upgrade to Moodle 1.9. Hehe, it's a Beta release (that promises to be far better than 1.8), but Beta for now. Neither said to update every month. Just was trying to suggest you to upgrade to the latest 1.8 "" version available (because it is stable and includes all the changes / improvements) performed, instead of upgrading to "static" 1.8.2, 1.8.3 (without the "") releases. Just a suggestion.

Anyway, it's great to know that you've found a solution to your problem. And thanks for sharing it once more, sincerely.

In my messages I just tried to expose / explain how Moodle has "fixed" some of those problems and trying to comment how the upgrade should be performed (using the latest "+" available, running the "missing indexes" utility...).

Finally, of course, I must express that some of your opinions, IMO, are a bit inexact. Affirmations like "unfortunate that moodle.org even released 1.8, because it was obviously far from being production ready" aren't exact at all.

I can agree about 1.8.0 not being perfect, but that's the idea of this forums / tracker and people sharing their experiences / problems to make Moodle to evolve in the right way.

I can grant you that there are a bunch of servers out there running Moodle 1.8, with some big numbers easily comparable to your installation that are working daily. And they don't crash the server each 2-3 mins, not at all.

But I won't say (nor think) that they aren't properly dimensioned for your usage needs nor anything similar. Not at all. Never! That isn't the spirit!

Instead I would prefer to know HOW they are crashing, with WHAT queries, trying to detect WHY that's happening, always trying to HELP, both EXPLAINING how it works and, if finally caused by Moodle, FIXING it.

And that's the spirit in all my comments in the tracker. Finally I want to add some 100% sincere smilies to this message to confirm you that I'm not using any worried tone, Heinz, absolutely! Just exposing you my point of view (both when we agree and when we don't, that's life).

(if someday I really become worried, I use to say it in a explicit way, but that isn't my current feeling at all ).

Cheers and ciao, Eloy

Discussions surrounding this incident seem to be a bit confusing - we are mixing several unrelated problems:

• powerful server does not solve the performance problems by itself - it MUST be also properly configured - PHP, Apache, MySQL and Moodle too (different moodle versions require different tweaks to get the best performance)
• once the server is overloaded moodle does appear to behave really strange and it may also result in database breakage because we are not using db transaction yet

"completely deadlocked" - in this case it may or may not be caused by index and keys, I have reviewed the diff again and no important key or index is missing - I think that this should not affect the performance much and it does not IMO affect the function of moodle

I understand it may be a problem to upgrade to each 1.8.x relase, but (un)fortunately we keep fixing bugs in stable branches, for example some of the performace related problems were fixed in 1.8.3+. The problem is that developers can not simulate the real workload and fix potential performance bottlenecks before large sites deploy the software on production sites. We worked hard to improve performance in 1.9dev

I agree that the releases during the last year were all a bit rushed, we all hope that 1.9.x will be as good as 1.6.x or even better.

Anyway thanks for using Moodle and helping to improve it, the related thread on moodle.org helped people to discuss the performance which is great, thanks

Thanks Eloy and Petr. Since it appears the indexes are upgraded correctly post 1.8.2 I'll mark this bug as resolved.