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

[Allen Shaw]

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

--
===========================================================
Allen Shaw                        
UPF Data Services