We upgraded from Moodle 1.6.4 to Moodle 1.8.2 about a month ago and have been having major performance issues since then. Our semester started last week and, with the influx of new students, the load on our Moodle increased exponentially. We had noticed that the database queries seemed somewhat inefficient and eventually the load reached a point where our database became completely deadlocked. Users kept getting database connection errors and were unable to use the system in any way.

In our attempts to solve the problem we swapped to a new server, installed the latest versions of MySQL 5 and PHP 5 and tested everything running Apache 1.3 and Apache 2.2 without any improvement. We also updated to Moodle 1.8.3+, but did not notice any performance enhancements.

The major issues, as far as we can tell, are:
1. The upgrade scripts are severely defective: fields were not correctly initialised, keys were not created. We established this by comparing the database table structure of our Moodle with a fresh, 'vanilla' install of Moodle. The create table statements of the fresh install were markedly different to those used by the upgrade scripts. For example, the order of some table columns in a fresh Moodle install are different to those generated in an existing database by the upgrade scripts.
2. The entire database became deadlocked due to damaged or missing indices (another side effect of the defective upgrade scripts): Moodle queries were locking tables in the database faster than the MySQL daemon could free them. This also caused data corruption in the database.

In the attachments (same content, one in Open Office format and one in MS Excel) you will find a comparison between existing tables after an upgrade and the tables after a clean install of Moodle. Items in red are missing, items in green are swapped.

More information can be found in the moodle.org General Problems Forum:
http://moodle.org/mod/forum/discuss.php?d=83216