Since upgrading to Moodle 1.8.1 (from 1.5.2) the server is behaving very differently, I have attached some charts that illustrate just how differently!
1) The number of running MySQL threads has increased from 1-2 to averaging maybe 5 with some significant peaks.
2) The number of connected threads has not really changed
3) The CPU usage is constantly running at 99.9% almost all of it taken by MySQL.
4) The number of users accessing the system seems to have little bearing on the performance, i.e. it is constantly "running hot", even during the small hours when there are few users accessing.
I have statsitics enabled, and after one week they are still not up to date, I can see that these have been populating tables during the day time, although I have set this process to stop at 06:00.
We have rebooted the server, and restarted MySQL, however the system just re-consumes its resources.
I have increased the thread cache size to 40, and set the timeout on connections to 5 minutes, but this has not had any impact on performance. I have also meddled with the query cache, gain to no avail.
I am not using a PHP accelerator which I will look into, however, as the server has not changed in terms of OS / DB version I do not believe that will solve the problem
If anyone has any thoughts on this I would love to hear them!!
Just a stab in the dark, but as you say you've gone from 1.5 to 1.8 (presumably via 1.6/1.7 to do the Unicode conversion) I'm wondering if there's some problem with the format of your database which is causing MySQL to either have to do some major conversion every time it accesses any data or whether there's something MySQL now can't do which is causing it to chase its tail in an endless loop as it keeps trying.
Once you've restarted MySQL does it go into overdrive immediately or just the first time someone tries to access Moodle? If it happens once someone tries Moodle then it looks like a problem with your Moodle database, if it happens immediately then there's some sort of problem with MySQL itself.
I'd be inclined to use PHPMyAdmin or whatever to check and repair all your tables, make sure everything is properly converted to Unicode, and see if there are any other problems or warnings coming up. There might also be some logs you can access which might be full of error messages, but I haven't explored PHPMyAdmin to that extent yet.
I am as sure as I can be that the conversion worked correctly, we checked this during and post upgrade...
I think the problem is with MySQL itself... we restarted the server over the weekend and it seems better now, though this is not the first time we have re-started.... I will post back when we get to the bottom of it!!
we seem to have exactly the same issue as you and we too went from version 1.5.2 to version 1.8.1
I too am not using a php accelerator although I'm looking at it as it cannot go on like this.
I'm also thinking that perhaps I should change our php from version 5.2.0 to something later (I read that php 5.2.0 had problems in Linux, but I'm on Windows).
Did you get yours sorted? Was it the database. I've read suggestions about missing indices, but I don't currently know how to find out which ones are missing or how to find them for it!
Here's one more. From 1.64 -> 1.81. Since we had some issues with high CPU load on mysql.
Ive tried many differente my.cnf settings. Tuning mysql-server to suit more, but no help there.
MySQL High-Performance Tuner - Major Hayden <firstname.lastname@example.org>
Bug reports, feature requests, and downloads at mysqltuner.com
Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[**] Up for: 1d 3h 46m 57s (16M q [167.091 qps], 157K conn, TX: 97M, RX: 2G)
[**] Reads / Writes: 93% / 7%
[**] Total buffers per thread: 4.9M
[**] Total global buffers: 462.0M
[OK] Maximum possible memory usage: 954.6M (47% of installed RAM)
[OK] Slow queries: 0%
[OK] Highest usage of available connections: 20%
[OK] Key buffer size / total MyISAM indexes: 128.0M/1.3G
[OK] Key buffer hit rate: 100.0%
[OK] Query cache efficiency: 54.3%
[!!] Query cache prunes per day: 689252
[OK] Sorts requiring temporary tables: 0%
[!!] Joins performed without indexes: 6289
[!!] Temporary tables created on disk: 39%
[OK] Thread cache hit rate: 99%
[OK] Table cache hit rate: 62%
[OK] Open file limit used: 49%
[OK] Table locks acquired immediately: 99%
It seems the problem is in Query cache prunes per day: 689252. So lots of big queries are runned through and temp tables are created on disk, which cause the problem?
We have 2GB ram and dual processor server. Moodle and mysql are running at the same machine. No acceleration installed for apache. Moodle cache is not in use.
I have check slow queries, and I think there might still be some index issues in Moodle databases