Hi,
I'm running mysql 5 on a windows 2003 box with 2x dual core xeons and am finding that when I get it to do something intensive, I cannot access the db from another browser window at all, as if only one query can happen at once.
I have heard in other places that mysql does not use multi core cpus very well, is there some setting that will help with this?
Matt
What intenstive things are you running? Updates will lock an entire table if you're using MyISAM, and if that table is involved in your read query either as the subject of it or via a JOIN etc. then you're correct, you won't see any data back until the update has finished. This isn't a processor issue so much as a constraint on what can be read while you're writing. Can be rectified by moving to InnoDB tables.
If you're currently on InnoDB then I can only imagine that you have some hefty indexes in there which during the write are not available to optimise the other read queries.
Hi Ian,
I'm using MyISAM and I was thinking of the bulk messaging, which seems to freeze everything. I gather that because of Moodle's need to log everything, only one query can run at once with table level locking, so InnoDB is looking more and more attractive due to row level locking. Do you know if it would affect performance at all or if there are any other issues around using it?
Matt
I'm using MyISAM and I was thinking of the bulk messaging, which seems to freeze everything. I gather that because of Moodle's need to log everything, only one query can run at once with table level locking, so InnoDB is looking more and more attractive due to row level locking. Do you know if it would affect performance at all or if there are any other issues around using it?
Matt
Hi Matt
I guessing that the problems with MySQL using multi-core CPUs refer to the MySQL bug number 15815 (see http://bugs.mysql.com/bug.php?id=15815). This was related to using InnoDB and was fixed in MySQL release 5.0.37, which came out back in 27 February 2007. So, can I suggest you try:
Does this help?
Thanks
Ken
I guessing that the problems with MySQL using multi-core CPUs refer to the MySQL bug number 15815 (see http://bugs.mysql.com/bug.php?id=15815). This was related to using InnoDB and was fixed in MySQL release 5.0.37, which came out back in 27 February 2007. So, can I suggest you try:
- Migrating to the latest version of MySQL if you're running anything below 5.0.37. You can find out which version you're running by typing connecting to your server with "mysql -u root -p".
- If that's too much, try moving your tables to MyISAM if you're running InnoDB. There's a script somewhere on the forums for doing this. I know this is contrary to Ian's advice, but that 15815 bug *only* occurs with the combination of InnoDB and multi-core CPUs.
- I remember that there was a recommendation on the MySQL developer forums to turn off hyperthreading for versions < 5.0.37. So see if this is relevant.
- Open Task Manager and add I/O Read Bytes and I/O Write Bytes to your columns (View -> Select Columns) - then sort by Image Name and highlight the mysql process. During the intensive period look for CPU spikes check if mysql is over 50% CPU utilisation. Look also at the I/O columns. Are there a lot of writes to the disk relative to reads? If that is the case, you're looking at an ineffective cache.
- For more depth, use Performance Monitor and add the I/O and CPU counters as appropriate. This will give you a good detailed view over a period of time, rather than an instant view of Task Manager.
Does this help?
Thanks
Ken
Dear All,
i have the follwing error:
071203 16:30:00 [ERROR] MySql: Incorrect key file for table '.\moodle18\mdl_user.MYI'; try to repair it
071203 16:34:07 [ERROR] MySql: Table '.\moodle18\mdl_user' is marked as crashed and should be repaired
if face such a problem will you pleas tell me the source of the problem
i have the follwing error:
071203 16:30:00 [ERROR] MySql: Incorrect key file for table '.\moodle18\mdl_user.MYI'; try to repair it
071203 16:34:07 [ERROR] MySql: Table '.\moodle18\mdl_user' is marked as crashed and should be repaired
if face such a problem will you pleas tell me the source of the problem
Hi Homam
The source of the problem is that the key file (.MYI) has become corrupt. This could have occurred as a result of a disk crash or a reboot without properly shutting down mysql. Has anything like this happened recently?
To fix the problem, take a look at the REPAIR TABLE command in the Administration FAQ (this link). It might be a good idea to run a full mysqlcheck repair as shown there - it will make sure everything else is OK.
To setup automatic fixing of the Moodle database, use the advice in the Performance FAQ (this link). You can use the php script linked to in there to do this or create your own script to run the mysqlcheck command.
Hope this is helpful!
Ken
The source of the problem is that the key file (.MYI) has become corrupt. This could have occurred as a result of a disk crash or a reboot without properly shutting down mysql. Has anything like this happened recently?
To fix the problem, take a look at the REPAIR TABLE command in the Administration FAQ (this link). It might be a good idea to run a full mysqlcheck repair as shown there - it will make sure everything else is OK.
To setup automatic fixing of the Moodle database, use the advice in the Performance FAQ (this link). You can use the php script linked to in there to do this or create your own script to run the mysqlcheck command.
Hope this is helpful!
Ken
Hi Ken,
I do really appreciate your reply to my question. We faced some power problems and our servers shut down many times.
i tried to repair the table but the process failed. i just restored my backup and everything is running well.
Thank you Ken,
Homam
I do really appreciate your reply to my question. We faced some power problems and our servers shut down many times.
i tried to repair the table but the process failed. i just restored my backup and everything is running well.
Thank you Ken,
Homam
Hi Ken,
I'm using MyISAM so this bug is probably not the cause, but I am now thinking of migrating to InnoDB so will be careful to upgrade MySQL first. I can't do the monitoring you suggest as I have both Apache and MySQL running as servicves and they don't appear in task manager, however, I seem to have had some luck by following these instructions to alter the buffers, which were ridiculously small. I have less processor time in use now, but am not sure about the locking of the tables.
Matt
I'm using MyISAM so this bug is probably not the cause, but I am now thinking of migrating to InnoDB so will be careful to upgrade MySQL first. I can't do the monitoring you suggest as I have both Apache and MySQL running as servicves and they don't appear in task manager, however, I seem to have had some luck by following these instructions to alter the buffers, which were ridiculously small. I have less processor time in use now, but am not sure about the locking of the tables.
Matt
Hi Matt
It is possible to monitor services using Task Manager - I've included a screenshot to hopefully explain things better. It was taken just this morning in our busy time on the server - hope it helps!
Just an aside on Gary's instructions. They're great
- but take care as settings like these are machine-specific, so make sure you work through them by reading the mysql documentation first. Remember that some buffer settings are *per connection*, so as more students use the server the more RAM will get used and soon mysql will start swapping - which is something we all try to avoid. I think that one of the reasons why mysql is distributed with such low settings is that not everyone who uses it has a high spec server - there are some organisations who just cannot afford those types of machines and run are running Moodle very low spec PCs. With the current mysql settings, Moodle works for them and their students - so it's probably prudent to leave them as they are and let people change with the supplied my-small.ini to my-huge.ini.
One MySQL setting you may find also helps reduce CPU utilisation on Windows only is:
That should help in reducing mysql CPU spikes and locking. Notice in the screenshot that, although our server (Wilberforce College) was busy at the time I took the screenshot, it was only Apache that was busy - the mysql service was humming away below 10% utilisation.
What PHP cache are you using?
Thanks
Ken
It is possible to monitor services using Task Manager - I've included a screenshot to hopefully explain things better. It was taken just this morning in our busy time on the server - hope it helps!
Just an aside on Gary's instructions. They're great
- but take care as settings like these are machine-specific, so make sure you work through them by reading the mysql documentation first. Remember that some buffer settings are *per connection*, so as more students use the server the more RAM will get used and soon mysql will start swapping - which is something we all try to avoid. I think that one of the reasons why mysql is distributed with such low settings is that not everyone who uses it has a high spec server - there are some organisations who just cannot afford those types of machines and run are running Moodle very low spec PCs. With the current mysql settings, Moodle works for them and their students - so it's probably prudent to leave them as they are and let people change with the supplied my-small.ini to my-huge.ini.One MySQL setting you may find also helps reduce CPU utilisation on Windows only is:
skip-thread-priorityAdd it to the [mysqld] section of your my.ini and restart the mysql service.
That should help in reducing mysql CPU spikes and locking. Notice in the screenshot that, although our server (Wilberforce College) was busy at the time I took the screenshot, it was only Apache that was busy - the mysql service was humming away below 10% utilisation.
What PHP cache are you using?
Thanks
Ken
Good tips! Thanks Ken.
I think you're right about the mysql settings, so I will update moodle docs for the windows installer to reflect the need to change the settings for big installs.
I'm using eaccelerator with the config cache setting set to use it too. I would ideally like to get the whole DB into RAM and never use swap at all. Any idea how I can ensure that? I have 8GB RAM so limit there but I'm not sure which performance monitor measures to use.
Matt
I think you're right about the mysql settings, so I will update moodle docs for the windows installer to reflect the need to change the settings for big installs.
I'm using eaccelerator with the config cache setting set to use it too. I would ideally like to get the whole DB into RAM and never use swap at all. Any idea how I can ensure that? I have 8GB RAM so limit there but I'm not sure which performance monitor measures to use.
Matt