monit-general
[Top][All Lists]
Advanced
[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 13:22:51 +0100
User-agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.8) Gecko/20061105 Iceape/1.0.6 (Debian-1.0.6-1)
Monit is using the anonymous bind (user="" and password="") - mysql should apply the user limit on this type of connect. 

I have retested with your setup and reached the same result.
I have used mysql 5.0.30 ... maybe there is bug in your mysql version or some setting of mysql which applies the max_connections per user?
Can you try the anonymous connect as soon as you reach the connection limit as 'user'? 


Second test results:

1.) max_connections commented out and mysql restarted:

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          | 100               |
| max_user_connections     | 0                 |
+--------------------------+-------------------+
7 rows in set (0.00 sec)


2.) script created:

--8<--
unicorn:~/cvs# cat mysql_test_maxconnections.php
#!/usr/bin/php
<?php

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

?>
--8<--


and started:

--8<--
unicorn:~/cvs# php -f ./mysql_test_maxconnections.php
Warning: mysql_connect(): Too many connections in /root/cvs/mysql_test_maxconnections.php on line 5
Warning: mysql_connect(): Too many connections in /root/cvs/mysql_test_maxconnections.php on line 5
Warning: mysql_connect(): Too many connections in /root/cvs/mysql_test_maxconnections.php on line 5 

--8<--

mysql> show full processlist;
+-----+------+-----------+-------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info | 
+-----+------+-----------+-------+---------+------+-------+-----------------------+
| 8 | root | localhost | mysql | Query | 0 | NULL | show full processlist | | 426 | user | localhost | NULL | Sleep | 15 | | NULL | | 427 | user | localhost | NULL | Sleep | 15 | | NULL | | 428 | user | localhost | NULL | Sleep | 15 | | NULL | | 429 | user | localhost | NULL | Sleep | 15 | | NULL | | 430 | user | localhost | NULL | Sleep | 15 | | NULL | | 431 | user | localhost | NULL | Sleep | 15 | | NULL | | 432 | user | localhost | NULL | Sleep | 15 | | NULL | | 433 | user | localhost | NULL | Sleep | 15 | | NULL | | 434 | user | localhost | NULL | Sleep | 15 | | NULL | | 435 | user | localhost | NULL | Sleep | 15 | | NULL | | 436 | user | localhost | NULL | Sleep | 15 | | NULL | | 437 | user | localhost | NULL | Sleep | 15 | | NULL | | 438 | user | localhost | NULL | Sleep | 15 | | NULL | | 439 | user | localhost | NULL | Sleep | 15 | | NULL | | 440 | user | localhost | NULL | Sleep | 15 | | NULL | | 441 | user | localhost | NULL | Sleep | 15 | | NULL | | 442 | user | localhost | NULL | Sleep | 15 | | NULL | | 443 | user | localhost | NULL | Sleep | 15 | | NULL | | 444 | user | localhost | NULL | Sleep | 15 | | NULL | | 445 | user | localhost | NULL | Sleep | 15 | | NULL | | 446 | user | localhost | NULL | Sleep | 15 | | NULL | | 447 | user | localhost | NULL | Sleep | 15 | | NULL | | 448 | user | localhost | NULL | Sleep | 15 | | NULL | | 449 | user | localhost | NULL | Sleep | 15 | | NULL | | 450 | user | localhost | NULL | Sleep | 15 | | NULL | | 451 | user | localhost | NULL | Sleep | 15 | | NULL | | 452 | user | localhost | NULL | Sleep | 15 | | NULL | | 453 | user | localhost | NULL | Sleep | 15 | | NULL | | 454 | user | localhost | NULL | Sleep | 15 | | NULL | | 455 | user | localhost | NULL | Sleep | 15 | | NULL | | 456 | user | localhost | NULL | Sleep | 15 | | NULL | | 457 | user | localhost | NULL | Sleep | 15 | | NULL | | 458 | user | localhost | NULL | Sleep | 15 | | NULL | | 459 | user | localhost | NULL | Sleep | 15 | | NULL | | 460 | user | localhost | NULL | Sleep | 15 | | NULL | | 461 | user | localhost | NULL | Sleep | 15 | | NULL | | 462 | user | localhost | NULL | Sleep | 15 | | NULL | | 463 | user | localhost | NULL | Sleep | 15 | | NULL | | 464 | user | localhost | NULL | Sleep | 15 | | NULL | | 465 | user | localhost | NULL | Sleep | 15 | | NULL | | 466 | user | localhost | NULL | Sleep | 15 | | NULL | | 467 | user | localhost | NULL | Sleep | 15 | | NULL | | 468 | user | localhost | NULL | Sleep | 15 | | NULL | | 469 | user | localhost | NULL | Sleep | 15 | | NULL | | 470 | user | localhost | NULL | Sleep | 15 | | NULL | | 471 | user | localhost | NULL | Sleep | 15 | | NULL | | 472 | user | localhost | NULL | Sleep | 15 | | NULL | | 473 | user | localhost | NULL | Sleep | 15 | | NULL | | 474 | user | localhost | NULL | Sleep | 15 | | NULL | | 475 | user | localhost | NULL | Sleep | 15 | | NULL | | 476 | user | localhost | NULL | Sleep | 15 | | NULL | | 477 | user | localhost | NULL | Sleep | 15 | | NULL | | 478 | user | localhost | NULL | Sleep | 15 | | NULL | | 479 | user | localhost | NULL | Sleep | 15 | | NULL | | 480 | user | localhost | NULL | Sleep | 15 | | NULL | | 481 | user | localhost | NULL | Sleep | 15 | | NULL | | 482 | user | localhost | NULL | Sleep | 15 | | NULL | | 483 | user | localhost | NULL | Sleep | 15 | | NULL | | 484 | user | localhost | NULL | Sleep | 15 | | NULL | | 485 | user | localhost | NULL | Sleep | 15 | | NULL | | 486 | user | localhost | NULL | Sleep | 15 | | NULL | | 487 | user | localhost | NULL | Sleep | 15 | | NULL | | 488 | user | localhost | NULL | Sleep | 15 | | NULL | | 489 | user | localhost | NULL | Sleep | 15 | | NULL | | 490 | user | localhost | NULL | Sleep | 15 | | NULL | | 491 | user | localhost | NULL | Sleep | 15 | | NULL | | 492 | user | localhost | NULL | Sleep | 15 | | NULL | | 493 | user | localhost | NULL | Sleep | 15 | | NULL | | 494 | user | localhost | NULL | Sleep | 15 | | NULL | | 495 | user | localhost | NULL | Sleep | 15 | | NULL | | 496 | user | localhost | NULL | Sleep | 15 | | NULL | | 497 | user | localhost | NULL | Sleep | 15 | | NULL | | 498 | user | localhost | NULL | Sleep | 15 | | NULL | | 499 | user | localhost | NULL | Sleep | 15 | | NULL | | 500 | user | localhost | NULL | Sleep | 15 | | NULL | | 501 | user | localhost | NULL | Sleep | 15 | | NULL | | 502 | user | localhost | NULL | Sleep | 15 | | NULL | | 503 | user | localhost | NULL | Sleep | 15 | | NULL | | 504 | user | localhost | NULL | Sleep | 15 | | NULL | | 505 | user | localhost | NULL | Sleep | 15 | | NULL | | 506 | user | localhost | NULL | Sleep | 15 | | NULL | | 507 | user | localhost | NULL | Sleep | 15 | | NULL | | 508 | user | localhost | NULL | Sleep | 15 | | NULL | | 509 | user | localhost | NULL | Sleep | 15 | | NULL | | 510 | user | localhost | NULL | Sleep | 15 | | NULL | | 511 | user | localhost | NULL | Sleep | 15 | | NULL | | 512 | user | localhost | NULL | Sleep | 15 | | NULL | | 513 | user | localhost | NULL | Sleep | 15 | | NULL | | 514 | user | localhost | NULL | Sleep | 15 | | NULL | | 515 | user | localhost | NULL | Sleep | 15 | | NULL | | 516 | user | localhost | NULL | Sleep | 15 | | NULL | | 517 | user | localhost | NULL | Sleep | 15 | | NULL | | 518 | user | localhost | NULL | Sleep | 15 | | NULL | | 519 | user | localhost | NULL | Sleep | 15 | | NULL | | 520 | user | localhost | NULL | Sleep | 15 | | NULL | | 521 | user | localhost | NULL | Sleep | 15 | | NULL | | 522 | user | localhost | NULL | Sleep | 15 | | NULL | | 523 | user | localhost | NULL | Sleep | 15 | | NULL | | 524 | user | localhost | NULL | Sleep | 15 | | NULL | 
+-----+------+-----------+-------+---------+------+-------+-----------------------+
100 rows in set (0.00 sec)



3.) monit reports error:

'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: login failed
'mysql' failed protocol test [MYSQL] at UNIX[/var/run/mysqld/mysqld.sock]
'mysql' succeeded connecting to INET[localhost:3306] via TCP
MYSQL: login failed
'mysql' failed protocol test [MYSQL] at INET[localhost:3306] via TCP


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
reply via email to
[Prev in Thread] Current Thread [Next in Thread]