Hardware and performance

I recently inherited a moodle site which had just been upgraded from 1.6 to 1.8.

$version = 2007021505;
$release = '1.8 +';

mysql-5.0.41

users = 27855
courses = 4910

When the semester started the performance was terrible. I started learning and scouring
the forums. I found that my database schema was out of sync with the fresh 1.8 schema.
I fixed that this week and performance has improved but I still have two specific questions.

The large query in accesslib.php which determines a user's permissions always creates
tmp tables on disk. I have tweaked all of the mysql variables I have read about which
I think might relate to this and I cannot prevent these disk tables from being created.

The same query occasionally will hang completely. I have seen the query in the mysql
queue for more than 2 minutes on occasion. Some times killing the longest-running of these
queries will fix things. Other times only restarting mysql seems to help.

Any suggestions gladly accepted.

--
patrick