MySQL Leak

[nservices]

Hi,
I have some problem with MySQL on DA Server
it's look like the MySQL case High Load to the server, And in about 24 hours the MySQL stop to work and give "Too many connections" message for all websites.

1. How can I know witch user case the problem?
2. how can I know where is the problem (connections not closed, really too many connections - Unlikely given the graph of the MRTG, something else?)
3. as temporary solution, how can I build script that check the Server Load and reset the MySQL service over some value

BTW, I change the value of mysql.allow_persistent to Off, let's see if it help.

Best Regards,
Star Network.

 

[zEitEr]

Hello,

1. A lot of services and software is at your service:

mytop
mysqladmin
MySQL Profilers, e.g. Jet Profiler for MySQL (can be used for free, there is a free edition)

Personally, I preferr mysqladmin

watch "mysqladmin --defaults-extra-file=/usr/local/directadmin/conf/my.cnf processlist"

Of course /usr/local/directadmin/conf/my.cnf should exist and contain a valid pair of login and password for MySQL server.


2. You might need limit concurrent connections for users from PHP scripts, something about 4-100 (you should make up your mind how much you want to allow)

Then limit max connections in /etc/my.cnf, I would set it bigger than the limit in php.ini.

3. Count threads with mysqladmin, or collect other available information and do a restart or kill thread with mysqladmin again.

You might need to read docs about mysqladmin, that is a really mighty tool.



And of course I can't not to mention munin (munin && munin-node), which can be used on a hosting server alongside with directadmin. With the help of this great tool you can draw graphs with number of MySQL connections, concurrent threads, queries and other things. It also can send you warnings.

 

[nservices]

I Will try, Thanks!

 

[zEitEr]

And this might be usefull read, you might want to learn how to set limits on connections to MySQL server and limit queries per hour on per user bases.