Hi everyone,
My moodle system (1.6.2+) began slowing down earlier today, and even now (after a MySQL and an apache reboot) it's slowed to a crawl. Just editing something on the front page takes 3-4 minutes. At first, the issue was that MySQL was rejecting new connections. After bumping max connections from 250 to 500 (with set global max_connections=500), I started looking at the processlist (with show processlist) I see a lot of (more than 5) locked processes that look like this: "SELECT * FROM mdl_grade_items WHERE courseid = '584' AND itemtype = 'course' ORDER BY id" and also a bunch of processes that appear to be copying to a temp table using phrases like: "SELECT DISTINCT go.userid FROM mdl_grade_grades go JOIN mdl" or something.
Can anyone shed some light on this for me? I've got the mysql and the apache servers currently running on two separate boxes. Is this some kind of cache building that will clean itself up, or do I need to do something more drastic? Has anyone seen this behavior? It's been doing this for a few hours, and "top" reports that 200% (2 out of 8 CPUs) of the processing power is in use by MySQL. I've got 13 GB of RAM free, and I'm using MySQL 5.0.67. Thanks for any help!
- Kevin
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
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