overloading mysql

overloading mysql

by Zahra E -
Number of replies: 8
hi,
I occasionally encounter with the "database connection failed" error. after searching around it I found this is because of the database overloading.
although in my php.in I have the string mysql.allow_persistant=Off and in the moodle config.php I have $CFG->dbpersist = false; this error still takes palace.
I want to know how can I determine the capacity of mysql to accept connections without causing this problem?

I have one server and data base , web server and moodledata all have placed on it.
the server hardware charactristics are:

CPU

2 * Dual-Core Intel Xeon 5140, 2.66 GHz, 1333 MHz FSB, 4MB Cache



RAM

4 GB(2*2 GB) PC2-5300 Fully Buffered DIMMs



Average of ratings: -
In reply to Zahra E

Re: overloading mysql

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
> I occasionally encounter with the "database connection failed" error.

Obviously your database server did not deliver a result within the expected time.

> I want to know how can I determine the capacity of mysql to accept connections without causing this problem?

What should be the unit of measurement of the result?

> the server hardware charactristics are:
[tons of crapy font definitions deleted]

Something has gone wrong in your posting.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: overloading mysql

by Zahra E -
hardware charactristics:
RAM: 4G
cpu: 2 * Dual-Core Intel Xeon 5140, 2.66 GHz, 1333 MHz FSB, 4MB Cache

H.D.D: 2*HP 72 GB Hot Plug 2.5" SAS 15K
2*HP 146 GB Hot Plug 2.5" SAS 10K

I think increasing the RAM can be helpful. isn't it?
Please guide me clearly!
Average of ratings: -
In reply to Zahra E

Re: overloading mysql

by kevin metcalf -
How many connections do you have when you get this error? You can find out by loggin into the mysql console and typing:
show status like "%onn%";
and looking under "max used connections."

To see what you can handle, log into the MySQL console and type this:
show variables like "max_connections";

How many are allowed? A machine running a normal moodle install with 4GB of RAM should be able to handle at least a few hundred. I sent mine to 250 using this command:
set global max_connections=250;

HIH!
Average of ratings: -
In reply to kevin metcalf

Re: overloading mysql

by Zahra E -
Thank you Kevin
I did what you said and this is the result of mentioned command:
--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| Aborted_connects 3129
| Connections 384379
| Max_used_connections 140
| Ssl_client_connects 0
| Ssl_connect_renegotiates 0
| Ssl_finished_connects 0
| Threads_connected 32
+--------------------------+--------+

and in my.cnf I set max_connections=350.
what is the problem in your opinion?
Average of ratings: -
In reply to Zahra E

Re: overloading mysql

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hello Zahra

But then you have raised this question once and got answers http://moodle.org/mod/forum/discuss.php?d=125212

Please do not double post!
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: overloading mysql

by Zahra E -
Hello Vistvanath
yes I have raised the question in that post but the problem was not solved!
Average of ratings: -
In reply to Zahra E

Re: overloading mysql

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
It's an irony that my messsage got posted in duplicate - they are in the same thread at least. ;(

Anyway, you've got sound advice in the other thread. You can't just throw them overboard and start from the beginning!
Average of ratings: -