[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: how to monitor "too many mysql connections"?

From: Martin Pala
Subject: Re: how to monitor "too many mysql connections"?
Date: Sat, 23 Dec 2006 15:08:47 +0100
User-agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv: Gecko/20061105 Iceape/1.0.6 (Debian-1.0.6-1)

... there may be one reason why monit reports that your mysql is OK - as i mentioned, monit connects as anonymous user with empty password.

The protocol test however solves the case, where the anonymous user has no permission to connect to mysql - in such case the access is refused immediately by mysql ('access denied'), which is interpretted by monit as correct mysql response (i.e. athentication process failed as expected). In this case monit won't be able to detect the 'too many connections', since the refusal reason ('access denied') is here regardless the connection limit.

In the case that the anonymous account exists and is allowed to connect, monit can detect the 'too many connections' state, since this is different error in later phase then the above described case.

My mysql setting for anonymous user:

mysql> show grants for ''@'localhost';
| Grants for @localhost                |
| GRANT USAGE ON *.* TO ''@'localhost' |
1 row in set (0.00 sec)

This allows the second version of mysql test - monit authenticates as anonymous user but receives the 'too many connections' which is reported as error.


Allen Shaw wrote:
Martin Pala wrote:

I have tested it ... it works, maybe you forgot to reload monit with the new configuration?
Can you check the monit configuration for 'protocol mysql', reload monit and send the log output from monit verbose mode (-v option)? What monit version are you using?

Hi Martin,

The information you requested is below. But I think I may have found a clue in the difference between your testing regimen and mine.

1. max_connections in mysql is the default, 100

2. use a php ("a.php") script connecting as a normal user to max out those connections

for ($i=0; $i< 102; $i++) {
   $db[$i] = mysql_connect('localhost','user','pass',true);
echo "Done.\n";

# php a.php

Warning: mysql_connect(): Too many connections in /home/ashaw/a.php on line 5

Warning: mysql_connect(): Too many connections in /home/ashaw/a.php on line 5

Warning: mysql_connect(): Too many connections in /home/ashaw/a.php on line 5

3. Leave that process running, confirm that mysql connections are all taken up:
# mysql -u user -ppass
ERROR 1040: Too many connections

4. Sign in to mysql as admin
#mysql -u root -ppass
Welcome to mysql monitor (etc.)
mysql> quit

5. run monit
# monit -v

6. This is the log output:

[CST Dec 22 10:02:06] debug : 'mysql' zombie check passed [status_flag=0000] [CST Dec 22 10:02:06] debug : 'mysql' PID has not changed since last cycle [CST Dec 22 10:02:06] debug : 'mysql' PPID has not changed since last cycle [CST Dec 22 10:02:06] debug : 'mysql' succeeded connecting to UNIX[/var/lib/mysql/mysql.sock] via TCP [CST Dec 22 10:02:06] debug : 'mysql' succeeded testing protocol [MYSQL] at UNIX[/var/lib/mysql/mysql.sock] via TCP

Monit does not find any problems with mysql.

I think the difference may be that mysql always keeps one connection open for root. If you're maxing out connections as root, then there are actually Zero connections left. But if you max out connections as a regular user, there's still one connection reserved for root. Is monit using that connection to test the protocol? If so, it won't find any problem with the number of available connections.

Maybe there's a way to configure the protocol test to login as a particular user?

This is monit version 4.8.2

- Allen

reply via email to

[Prev in Thread] Current Thread [Next in Thread]