My moodle site always show this error when many users access same time.
MySQL is overloaded....
How do I tweak my MySQL for this error not to appear.
Hi Genner,
I have the same problem too. I don't know whether MySQL is easier to be overloaded than MS SQL Server and Oracle. Can any experts answer this question? And what about PostgreSQL?
MySQL is generally assessed as giving better request performance per CPU cycle/performance than rival products so I doubt that switching to an alternative database would help. However it is limited in some of its features (not a problem when it comes to Moodle as Moodle works within those features. A more productive path is probably to look at your hardware/software/operating system setup and MySQL tuning issues. Why not post more information about your setup here.
Marcus
Hello, we have the same "problem" in our server and I have modified the following things:
1) The error is not an "error" from MySQL but is a configuration of PHP. By Default PHP is using the persistent DB connexions more information in french
here or in english 
there
If you want to "correct" this, you must simply modify the php.ini has following:
Locate the php.ini on your server, if you are using the "entropy.ch" package it is locate here: /usr/local/php/lib/php-ini
Found the following line and write so
mysql.allow_persistant=Off
It's done an never more overloaded ...
Have a nice day
1) The error is not an "error" from MySQL but is a configuration of PHP. By Default PHP is using the persistent DB connexions more information in french
here or in english thereIf you want to "correct" this, you must simply modify the php.ini has following:
Locate the php.ini on your server, if you are using the "entropy.ch" package it is locate here: /usr/local/php/lib/php-ini
Found the following line and write so
mysql.allow_persistant=Off
It's done an never more overloaded ...
Have a nice day
Hello Philippe
Do you know if it is the same using Postgres, because I have the same problem.
Graciela.
Do you know if it is the same using Postgres, because I have the same problem.
Graciela.
Hi Graciela,
Because the db persitant connexion is handled from application to -> PHP to -> the Database and if Postgres take the "DB persitant connexion" parameter from the PHP I thing it's the same thing ... You must configure the php.ini or like Martin Dougiamas say
In your moodle/config.php file, you can also just set:
$CFG->dbpersist = false;
But for us because we are using other products that Moodle on our server it was better to modify the php.ini file ...
Because the db persitant connexion is handled from application to -> PHP to -> the Database and if Postgres take the "DB persitant connexion" parameter from the PHP I thing it's the same thing ... You must configure the php.ini or like Martin Dougiamas say
In your moodle/config.php file, you can also just set:
$CFG->dbpersist = false;
But for us because we are using other products that Moodle on our server it was better to modify the php.ini file ...
Hi Philippe:
In my config.php I have the $CFG->dbpersist = false; but in the php.ini I don´t have any parameter only the next lines
; php.ini for PEAR tests
include_path=..
Do you think that maybe it is the problem.
Thank you
Graciela.
In my config.php I have the $CFG->dbpersist = false; but in the php.ini I don´t have any parameter only the next lines
; php.ini for PEAR tests
include_path=..
Do you think that maybe it is the problem.
Thank you
Graciela.
Graciela -- I think you were using Postgres. Make sure you get today's 1.4.4+ because we fixed a performance issue with Postgres. Should give you far better response.
Postgres has really slow connection setup times, so you want to search this forum for a mini-guide on how to set maxclients, and when you have that tuned, set dbpersist to true (remember to monitor swap, if the machine touches swap, trim your maxclients).
Make good use of sysstat/sar and top to find your bottlenecks
There are really good Postgres tuning guides if you Google for them. In particular, you want to make good use of shared memory buffers with Postgres, and split the load across several spindles (move the data dir and transaction logs to a different drive). Get SCSI drives if possible or really-really-really fast SATA drives.
Postgres has really slow connection setup times, so you want to search this forum for a mini-guide on how to set maxclients, and when you have that tuned, set dbpersist to true (remember to monitor swap, if the machine touches swap, trim your maxclients).
Make good use of sysstat/sar and top to find your bottlenecks
There are really good Postgres tuning guides if you Google for them. In particular, you want to make good use of shared memory buffers with Postgres, and split the load across several spindles (move the data dir and transaction logs to a different drive). Get SCSI drives if possible or really-really-really fast SATA drives.
Martin after reading your post. Tried running tail -f /var/log/mysql.log
This is the error:
050406 9:04:27 [ERROR] /usr/sbin/mysqld-max: Can't open file: 'mdl_cache_text.MYI' (errno: 145)
050406 9:04:27 [ERROR] /usr/sbin/mysqld-max: Can't open file: 'mdl_cache_text.MYI' (errno: 145)
050406 9:04:27 [ERROR] /usr/sbin/mysqld-max: Can't open file: 'mdl_cache_text.MYI' (errno: 145)
Anyone, knows how to solve this problem been searching the web can't find answer.
This is the error:
050406 9:04:27 [ERROR] /usr/sbin/mysqld-max: Can't open file: 'mdl_cache_text.MYI' (errno: 145)
050406 9:04:27 [ERROR] /usr/sbin/mysqld-max: Can't open file: 'mdl_cache_text.MYI' (errno: 145)
050406 9:04:27 [ERROR] /usr/sbin/mysqld-max: Can't open file: 'mdl_cache_text.MYI' (errno: 145)
Anyone, knows how to solve this problem been searching the web can't find answer.
Try repairing your mdl_cache_text table using phpMyAdmin.
We are not using phpMyAdmin.
Just found out that the said table is corupted. I just delete the said table.
But, how to create it again don't know the fields. Is it ok if I only create the mdl_cache_text again.
Just found out that the said table is corupted. I just delete the said table.
But, how to create it again don't know the fields. Is it ok if I only create the mdl_cache_text again.
Don't delete your tables! Now to recreate the table you'll have to create them manually from the definition in the mysql.sql files (search for them in the Moodle directory). It may be easier to restore from backup or to drop the database (you'll lose all your data!) and recreate it with Moodle.
What you should do when you encounter this problem is to repair your tables. Use Google: "MySQL Repair Tables" and you'll find quite a few ways to do it.
http://www.google.com/search?q=mysql+repair+tables+&sourceid=mozilla-search&start=0&start=0&ie=utf-8&oe=utf-8
What you should do when you encounter this problem is to repair your tables. Use Google: "MySQL Repair Tables" and you'll find quite a few ways to do it.
http://www.google.com/search?q=mysql+repair+tables+&sourceid=mozilla-search&start=0&start=0&ie=utf-8&oe=utf-8
Fix it, thanks!
But it doen't solve the problem of mysql overloading.
But it doen't solve the problem of mysql overloading.
What about this?
http://moodle.org/mod/forum/discuss.php?d=21279#101082
http://moodle.org/mod/forum/discuss.php?d=21279#101082
Hi Martin:
I look for the commands sysstat and top/sar/vmstat but I couldn´t find them. The only one was top and the process look well in the % for CPU and MEM. I haven´t found the mini guide that you mention. I commented the logs in the postgresql.cong until I have a new drive and it is running faster, I have changed the max-connections to 128 and the tcpip_socket = true. I will look for Postgres tuning guide for more information. Could you help me with the commands and the mini guide.
Thank you very much for your help.
Graciela.
I look for the commands sysstat and top/sar/vmstat but I couldn´t find them. The only one was top and the process look well in the % for CPU and MEM. I haven´t found the mini guide that you mention. I commented the logs in the postgresql.cong until I have a new drive and it is running faster, I have changed the max-connections to 128 and the tcpip_socket = true. I will look for Postgres tuning guide for more information. Could you help me with the commands and the mini guide.
Thank you very much for your help.
Graciela.
Graciela,
you will have to install sysstat, which includes a utility called sar. Read the man page for those to understand how to use them. vmstat should be on your machine already, otherwise install the relevant package. All of this is pretty standard on Red Hat so you have RPMs for it somewhere.
You should _not_ disable the transaction log -- you'll end up losing your database completely if anything goes wrong.
With regards to maxclients, use top to figure out how much memory each Apache process consumes, how much is shared and then follow Stas Bekman's excellent guide: http://perl.apache.org/docs/1.0/guide/performance.html#Choosing_MaxClients
Then you run a few tests with many clients, and see how your memory usage is going. If you have free memory, raise maxclients. If you are using swap, lower maxclients.
you will have to install sysstat, which includes a utility called sar. Read the man page for those to understand how to use them. vmstat should be on your machine already, otherwise install the relevant package. All of this is pretty standard on Red Hat so you have RPMs for it somewhere.
You should _not_ disable the transaction log -- you'll end up losing your database completely if anything goes wrong.
With regards to maxclients, use top to figure out how much memory each Apache process consumes, how much is shared and then follow Stas Bekman's excellent guide: http://perl.apache.org/docs/1.0/guide/performance.html#Choosing_MaxClients
Then you run a few tests with many clients, and see how your memory usage is going. If you have free memory, raise maxclients. If you are using swap, lower maxclients.
Hi Martin:
I have now another drive, but I don´t know how to move the data dir and transaction logs to a different drive. Could you help me.
Thank you.
Graciela.
I have now another drive, but I don´t know how to move the data dir and transaction logs to a different drive. Could you help me.
Thank you.
Graciela.
by the way i did change my php.ini
mysql.allow_persistant=Off
the a error message appear, saying wierd error occured... after loging in.
mysql.allow_persistant=Off
the a error message appear, saying wierd error occured... after loging in.
Martin Langhoff
Thanks for the tip about MYSQL tuning guides I googled for the phrase that you suggested in another thread. This tuning guide looks good:
http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
I need one in Japanese though!
Tim
Thanks for the tip about MYSQL tuning guides I googled for the phrase that you suggested in another thread. This tuning guide looks good:
http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
I need one in Japanese though!
Tim
In your moodle/config.php file, you can also just set:
$CFG->dbpersist = false;
$CFG->dbpersist = false;
I have had this problem recently too - MySQL overloaded with about 40 users.
If the string in php.ini is still mysql.allow_persistant=Off but in the moodle config.php I have $CFG->dbpersist = false; should that be OK or not? (I had this configuration when the problem occured last week)
TIA for any advice
If the string in php.ini is still mysql.allow_persistant=Off but in the moodle config.php I have $CFG->dbpersist = false; should that be OK or not? (I had this configuration when the problem occured last week)
TIA for any advice
Hi Tim, folks would really need your hardware specs to offer advice.
Sorry, if I´m renting space in a server, how I can to change these settings?...
Here are some common PHP legends:
echo is faster than print
Echo is supposed to be faster because it doesn't return a value while print does. From my benchmarks with PHP 4.3, the difference is neglible. And under some situations, print is faster than echo (when ob_start is enabled).
You're more likely to notice a difference if you change the way you represent literals.
- Double-quotes ("something") cause PHP to seek through a literal look for escape sequences and variables which can waste a lot of processing time.
- Single-quotes ('something') won't parse variables, which means PHP doesn't have to look for them. Single-quoted strings are much faster to parse.
where in moodle should one change double quotes to single quotes ?
The only one you're able to do there is to change your Moodle configuration.
Open '
Open '
config.php' in your Moodle root directory and change:$CFG->dbpersist = true;to
$CFG->dbpersist = false;If it looks like that already and you're still having problems then it could be something else.
Should the "true" or the "false" be quoted like in 'true' or 'false' ?
What server hardware are you running, how much RAM does it have, what settings do you have in your my.conf file, and how many users are on the system when you get this error?
Hi Michael,
Do we have particular statistic (hardware, database, operating system, web server) to show load ability of Moodle?
In my config.php $CFG->dbpersist = false;
I have 1G of ram running fedora 3, below is my.cnf configuration, user 50
[mysqld]
skip-locking
skip-name-resolve
max_connections = 500
table_cache = 256
thread_cache = 40
key_buffer_size = 16M
sort_buffer = 4M
read_rnd_buffer_size = 128KB
tmp_table_size = 32MB
read_buffer_size = 2M
query_cache_type= 1
query_cache_size = 30M
wait_timeout = 15
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 16M
sort_buffer = 4M
read_buffer_size = 2M
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
----------------------------------------
With this configuration i got the same MySQL overload when logging simutaneously in moodle. hope some fine a better way to tweak MySQL
I have 1G of ram running fedora 3, below is my.cnf configuration, user 50
[mysqld]
skip-locking
skip-name-resolve
max_connections = 500
table_cache = 256
thread_cache = 40
key_buffer_size = 16M
sort_buffer = 4M
read_rnd_buffer_size = 128KB
tmp_table_size = 32MB
read_buffer_size = 2M
query_cache_type= 1
query_cache_size = 30M
wait_timeout = 15
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 16M
sort_buffer = 4M
read_buffer_size = 2M
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
----------------------------------------
With this configuration i got the same MySQL overload when logging simutaneously in moodle. hope some fine a better way to tweak MySQL
It would be hard to do accurately. You can show it for a base system, but once you add filtering, chat, autolinking, simultaneous access to the same resource, etc. then the numbers would be very inaccurate.
IME, the numbers for commercial systems are also pretty inaccurate, this is probably why, benchmarks are made in a best case scenario without using resource intensive features.
You also have to specific about what the users are doing, eg. 50 people logging in to the same quiz at the same time uses alot more resources than 50 people logged into the system doing various different things at various different times (the 1.5 quiz when questions per page is limited should dramatically improve this, but again, this introduces another variable to your minimum system specs).
IMO, the best way to get these numbers is to post your expected numbers of students and what you expect them to be doing (ie logged in to the same activity all at the same time, etc.), what filters you intend to use, if they are going to be all logging in at the same time, etc. here and ask for advice for your specific case b/c the minimum system for case 1 is going to be inadequate for case 2 and overkill for case 3.
If you plan to support thousands of users, then plan to spend some part of your revenue on a good sysadmin or support from a Moodle partner. You'll still need a good sysadmin and good hardware to run a commercial system, so you'll always save using Moodle.
IME, the numbers for commercial systems are also pretty inaccurate, this is probably why, benchmarks are made in a best case scenario without using resource intensive features.
You also have to specific about what the users are doing, eg. 50 people logging in to the same quiz at the same time uses alot more resources than 50 people logged into the system doing various different things at various different times (the 1.5 quiz when questions per page is limited should dramatically improve this, but again, this introduces another variable to your minimum system specs).
IMO, the best way to get these numbers is to post your expected numbers of students and what you expect them to be doing (ie logged in to the same activity all at the same time, etc.), what filters you intend to use, if they are going to be all logging in at the same time, etc. here and ask for advice for your specific case b/c the minimum system for case 1 is going to be inadequate for case 2 and overkill for case 3.
If you plan to support thousands of users, then plan to spend some part of your revenue on a good sysadmin or support from a Moodle partner. You'll still need a good sysadmin and good hardware to run a commercial system, so you'll always save using Moodle
Whenever you see the 'database overloaded' screen, the database itself is documenting the exact reason why it is refusing the connection in the logs. This applies to Postgres and MySQL.
If you are using Linux/Unix, you will usually find it in /var/log/mysql/ (or /var/log/postgres/), look for files with an ".err" extension or a ".log" extension. A good practice is to use the "tail" utility to track the file as the problem is occurring.
Under Windows, take a loot at the event log -- and perhaps at MySQL's data directory.
When you get the error msg, use google to find the database docs, and it'll probably tell you exactly what's happening.
Other things to do as well:
- run top and sysstat while the server is experiencing load
- do top/sar/vmstat say it's IO-bound (for instance, what is the percentage of iowait and system time reported)? Perhaps you need fast SCSI drives or to split the work over more spindles (drives).
- do top/sar/vmstat say you are using swap (are pageins/pageouts increasing during your peak-traffic) ? add memory -- as much as you can fit.
- Google for "mysql performance tuning" and follow the guides.
- Enable mysql's slow query log and monitor slow queries to help us improve the DB schema.
I've posted in this forum a mini-guide (based on mod_perl guide's performance tuning section -- go to the source for more detail). It explains how to tune MaxClients and connection_timeout in Apache+MySQL according to your expected load and available memory (remember to retune if you add memory!). Once it's well tuned, you can enable persistent connections for better performance. Seek and ye shall find...
Keep in mind, there's only so much a single machine can do
If you are using Linux/Unix, you will usually find it in /var/log/mysql/ (or /var/log/postgres/), look for files with an ".err" extension or a ".log" extension. A good practice is to use the "tail" utility to track the file as the problem is occurring.
Under Windows, take a loot at the event log -- and perhaps at MySQL's data directory.
When you get the error msg, use google to find the database docs, and it'll probably tell you exactly what's happening.
Other things to do as well:
- run top and sysstat while the server is experiencing load
- do top/sar/vmstat say it's IO-bound (for instance, what is the percentage of iowait and system time reported)? Perhaps you need fast SCSI drives or to split the work over more spindles (drives).
- do top/sar/vmstat say you are using swap (are pageins/pageouts increasing during your peak-traffic) ? add memory -- as much as you can fit.
- Google for "mysql performance tuning" and follow the guides.
- Enable mysql's slow query log and monitor slow queries to help us improve the DB schema.
I've posted in this forum a mini-guide (based on mod_perl guide's performance tuning section -- go to the source for more detail). It explains how to tune MaxClients and connection_timeout in Apache+MySQL according to your expected load and available memory (remember to retune if you add memory!). Once it's well tuned, you can enable persistent connections for better performance. Seek and ye shall find...
Keep in mind, there's only so much a single machine can do
A powerful thing to do is to disable stuff you don't use
- the 'languages' menu in the top-right is very resource-intensive
- disable filters
- disable modules
- disable blocks
The resource-heavy pages in Moodle (homepage, coursepage) load every enabled module and block, plus their strings files, and ask the DB whether there's anything for that block/module to show.
During a quick test, paring down my dev Moodle to just the stuff I'm using took it from 7 pages per second to 50, just serving the homepage which is really heavy.
- the 'languages' menu in the top-right is very resource-intensive
- disable filters
- disable modules
- disable blocks
The resource-heavy pages in Moodle (homepage, coursepage) load every enabled module and block, plus their strings files, and ask the DB whether there's anything for that block/module to show.
During a quick test, paring down my dev Moodle to just the stuff I'm using took it from 7 pages per second to 50, just serving the homepage which is really heavy.
the problem is the max number of connections that mysql accept. (default is 100)
you can try to insert this lines in my.cnf (/etc/my.cnf on linux)
[mysqld]
set-variable=max_connections=500
then restar mysql service.
I'm a bit sceptical about this - 100 connections is generally loads. I've worked on some very hefty mysql installations and we never went past about 25 connections. If you run out of connections with a max of 100 you are generally going into meltdown anyway. Without wanting to open old wounds I have stated in the past that I am deeply sceptical about the performance of adodb at high loads. As has been recommended I would *always* turn off persistent connections. The next thing to do is to check (and post here) the processlist in mysql when you start to see this message. At the mysql client command line it's
SHOW FULL PROCESSLIST;
phpmyadmin will do it to. What you might find is lots of locked tabled - mutual deadlock, and it would be interesting to see what the processes are doing at this time! If this is the case, you can set as many connections as you like as it will just run straight up to the max whatever it is!
Also bear in mind that each connection takes a chunk of memory - I forget the exact formula but to run 500 simultaneous connections will need *lots* of available RAM.
Another thing - is your database only used for Moodle. It isn't too hard to write 'leaky' scripts in PHP that leave connections hanging open all over the place (not using close functions). It might not be Moodle at all that's dragging it down. If that is the case you might want to consider setting max_user_connections to some non-zero value to restrict the number of connections any single user can make so they don't bring the whole server down.
SHOW FULL PROCESSLIST;
phpmyadmin will do it to. What you might find is lots of locked tabled - mutual deadlock, and it would be interesting to see what the processes are doing at this time! If this is the case, you can set as many connections as you like as it will just run straight up to the max whatever it is!
Also bear in mind that each connection takes a chunk of memory - I forget the exact formula but to run 500 simultaneous connections will need *lots* of available RAM.
Another thing - is your database only used for Moodle. It isn't too hard to write 'leaky' scripts in PHP that leave connections hanging open all over the place (not using close functions). It might not be Moodle at all that's dragging it down. If that is the case you might want to consider setting max_user_connections to some non-zero value to restrict the number of connections any single user can make so they don't bring the whole server down.
100 connections is plenty unless you are using persistent connections. If you are planning on a high-traffic setup, you'll need too read up on how persistent connections work towards your mysql/postgres maxconnections, and learn to tune that. The easiest trick is to limit things with maxclients in Apache.
PHP also has a handy 'max_persistentconections' (or something like it) which you can use to limit the pconnects a bit. It all boils down to how much memory you have.
I've posted a few miniguides on how to trim maxclients/maxconnections to your memory.
PHP also has a handy 'max_persistentconections' (or something like it) which you can use to limit the pconnects a bit. It all boils down to how much memory you have.
I've posted a few miniguides on how to trim maxclients/maxconnections to your memory.
It's a php.ini setting:
Have a look on the PHP.net website.
Have a look on the PHP.net website.
Just a note that for the inside story, the O'Reilly book High Performance MySql is a good (possibly vital) read if you are running a big installation.
Like I said turning it off a message is appearing on moodle (weird error...)
hello all,
I may be slow to this topic, but can someone please tell me where the php.ini file is located?
Thanks in advance
P
I may be slow to this topic, but can someone please tell me where the php.ini file is located?
Thanks in advance
P
for linux:
/etc/php.ini
/etc/php.ini
...maybe! Depends on how PHP was built. If it's not there, then at the command line, try
php -i | grep ini