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

[Martin Pala]

I have tested it ... it works, maybe you forgot to reload monit with the 
new configuration?


Testing output:

1.) i have set the connection limit to 5 in my.cnf using:

--8<--
max_connections = 5
--8<--


2.) i have started 6 connections as mysql admin to block the additional 
connections:

mysql> show variables like '%conn%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
| connect_timeout          | 5                 |
| init_connect             |                   |
| max_connect_errors       | 10                |
| max_connections          | 5                 |
| max_user_connections     | 0                 |
+--------------------------+-------------------+
7 rows in set (0.00 sec)

mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info 
        |
+----+------+-----------+------+---------+------+-------+-----------------------+
|  6 | root | localhost | NULL | Query   |    0 | NULL  | show full 
processlist |
|  7 | root | localhost | NULL | Sleep   |    8 |       | NULL 
        |
|  8 | root | localhost | NULL | Sleep   |  326 |       | NULL 
        |
|  9 | root | localhost | NULL | Sleep   |  323 |       | NULL 
        |
| 10 | root | localhost | NULL | Sleep   |  321 |       | NULL 
        |
| 11 | root | localhost | NULL | Sleep   |  318 |       | NULL 
        |
+----+------+-----------+------+---------+------+-------+-----------------------+
6 rows in set (0.00 sec)


unicorn:~/cvs/monit# mysql -u '' -h 127.0.0.1
ERROR 1040 (00000): Too many connections

unicorn:~/cvs/monit# mysql -u '' -s /var/run/mysqld/mysqld.sock
ERROR 1040 (00000): Too many connections




3.) monit is set to check both mysql tcp and unix sockets:

--8<--
check process mysql with pidfile /var/run/mysqld/mysqld.pid
   start program = "/etc/init.d/mysql start"
   stop program = "/etc/init.d/mysql stop"
   if failed port 3306 protocol mysql then alert
   if failed unixsocket /var/run/mysqld/mysqld.sock protocol mysql then 
alert
   group database
--8<--



4.) both sockets failed:


'mysql' zombie check passed [status_flag=0000]
'mysql' PID has not changed since last cycle
'mysql' PPID has not changed since last cycle
'mysql' cpu usage check passed [current cpu usage=0.0%]
'mysql' succeeded connecting to UNIX[/var/run/mysqld/mysqld.sock]
MYSQL: error sending login -- Broken pipe
'mysql' failed protocol test [MYSQL] at UNIX[/var/run/mysqld/mysqld.sock]
'mysql' succeeded connecting to INET[localhost:3306] via TCP
MYSQL: error receiving login response
'mysql' failed protocol test [MYSQL] at INET[localhost:3306] via TCP




5.) mysql connections dropped to free the connection pool, tested again, 
it is possible to connect as expected:


'mysql' zombie check passed [status_flag=0000]
'mysql' cpu usage check passed [current cpu usage=0.0%]
'mysql' succeeded connecting to UNIX[/var/run/mysqld/mysqld.sock]
'mysql' succeeded testing protocol [MYSQL] at 
UNIX[/var/run/mysqld/mysqld.sock]
'mysql' succeeded connecting to INET[localhost:3306] via TCP
'mysql' succeeded testing protocol [MYSQL] at INET[localhost:3306] via TCP



=> the 'protocol mysql' should detect the 'too many connections bug

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?

Thanks,
Martin


Allen Shaw wrote:
> Martin Pala wrote:
>
> > I think that monit mysql protocol test should detect this, for example:
>
> Hi Martin,
>
> I think the following configuration is what you mean, but it does not work:
> --8<--
> check process mysql with pidfile /var/lib/mysql/iifwpdata.com.pid
> start program = "/usr/bin/safe_mysqld --user=mysql"
> stop program = "/usr/bin/mysqladmin -pyogiism -u root shutdown"
> if failed unixsocket /var/lib/mysql/mysql.sock protocol mysql then exec 
> "/root/bin/force-mysql-restart.sh"
> --8<--
>
> I tested this by running a PHP script which maxes out the connections 
> and thus makes it impossible to connect to the mysql server (except on 
> the command line as mysql root).  Then run "monit" to awaken monit 
> daemon.  The monit log reports the following for that run of monit:
>
> [CST Dec 20 16:52:16] debug    : 'mysql' zombie check passed 
> [status_flag=0000]
> [CST Dec 20 16:52:16] debug    : 'mysql' PID has not changed since last 
> cycle
> [CST Dec 20 16:52:16] debug    : 'mysql' PPID has not changed since last 
> cycle
> [CST Dec 20 16:52:16] debug    : 'mysql' succeeded connecting to 
> UNIX[/var/lib/mysql/mysql.sock] via TCP
> [CST Dec 20 16:52:16] debug    : 'mysql' succeeded testing protocol 
> [MYSQL] at UNIX[/var/lib/mysql/mysql.sock] via TCP
>
> Looks like that syntax for some reason doesn't detect that there are no 
> available connections.  Am I using the wrong config?
>
> Thanks,
> Allen