Hardware and performance

Hi Kevin,

Though I'm not aware of your specific problem form your description I'd recommend a couple avenues of exploration.

1) Look at the raw output of your slowquerry log. Find a version of the query that is going slow. Then run an explain on it:

mysql> explain select * from mdl_grade ....

This will tell you if there are any missing indexes. if it's using a temp table and lots of other important information that will help you diagnose the issue.
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

2) IF temp tables are your issue then look at a couple things. How big are your temp tables in my.cnf? If these are going to disk that eats a lot more resources than memory. You can also do a:

mysql> show global status;
Take a look at your temp tables and see if you are experiencing this:

"Created_tmp_tables
The number of in-memory temporary tables created automatically by the server while executing statements. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-base"
http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html

Another option to fix this beyond just increasing your temporary tables memory is to point the specific temp tables directory to a tmpfs partition. We've experimented with this and I recommend it... though test it before you implement.
http://openquery.com/blog/experiment-mysql-tmpdir-on-tmpfs


3) Finally the most drastic is to switch your engine type. You said there appears to be a locking problem. If you're on MYISAM still then this is a common issue espically for write heavy tables of which mdl_grade_items is one. INNODB supports row-level locking unlike MYISAM which only supports full table locks. There are a lot of articles on this both here on moodle.org and other locations. I strongly recommend making this switch though in order to do so you need to reallocate memory in your my.cnf to your INNODB_buffer.

Hope some of this helps.

Taylor