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

[Martin Pala]

... 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:

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

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

Martin


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
>
> a.php:
> --8<--
> for ($i=0; $i< 102; $i++) {
>    $db[$i] = mysql_connect('localhost','user','pass',true);
> }
> sleep(1000);
> echo "Done.\n";
> --8<--
>
> # 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