Excessive MySQL Connections

[santosltd]

Hi Team,

We have offloaded our DB to a separate server. We are seeing a rather high amount of SQL Connections as detailed below;

mysql> show processlist;
+-----+-------------+-----------------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+-----------------------------+----------+---------+------+-------+------------------+
| 2 | nagios_user | adeuxmon01:37653 | nagiosxi | Sleep | 1 | | NULL |
| 5 | nagios_user | adeuxmon01:37671 | nagiosxi | Sleep | 19 | | NULL |
| 6 | nagios_user | adeuxmon01:37672 | nagiosql | Sleep | 19 | | NULL |
| 7 | nagios_user | adeuxmon01:37674 | nagiosxi | Sleep | 5 | | NULL |
| 8 | nagios_user | adeuxmon01:37675 | nagiosql | Sleep | 5 | | NULL |
| 9 | nagios_user | adeuxmon01:37676 | nagiosxi | Sleep | 4 | | NULL |
| 10 | nagios_user | adeuxmon01:37677 | nagiosql | Sleep | 4 | | NULL |
| 11 | nagios_user | adeuxmon01:37679 | nagiosxi | Sleep | 20 | | NULL |
| 12 | nagios_user | adeuxmon01:37680 | nagiosql | Sleep | 20 | | NULL |
| 13 | nagios_user | adeuxmon01:37681 | nagiosxi | Sleep | 12 | | NULL |
| 14 | nagios_user | adeuxmon01:37682 | nagiosql | Sleep | 12 | | NULL |
| 15 | nagios_user | adeuxmon01:37683 | nagiosxi | Sleep | 4 | | NULL |
| 16 | nagios_user | adeuxmon01:37684 | nagiosql | Sleep | 4 | | NULL |
| 17 | nagios_user | adeuxmon01:37693 | nagiosxi | Sleep | 20 | | NULL |
| 18 | nagios_user | adeuxmon01:37694 | nagiosql | Sleep | 20 | | NULL |
| 19 | nagios_user | adeuxmon01:37696 | nagiosxi | Sleep | 5 | | NULL |
| 20 | nagios_user | adeuxmon01:37697 | nagiosql | Sleep | 5 | | NULL |
| 21 | nagios_user | adeuxmon01:37698 | nagiosxi | Sleep | 4 | | NULL |
| 22 | nagios_user | adeuxmon01:37699 | nagiosql | Sleep | 4 | | NULL |
| 23 | nagios_user | adeuxmon01:37701 | nagiosxi | Sleep | 20 | | NULL |
| 24 | nagios_user | adeuxmon01:37702 | nagiosql | Sleep | 20 | | NULL |
| 25 | nagios_user | adeuxmon01:37717 | nagiosxi | Sleep | 14 | | NULL |
| 26 | nagios_user | adeuxmon01:37718 | nagiosql | Sleep | 14 | | NULL |
| 27 | nagios_user | adeuxmon01:37719 | nagiosxi | Sleep | 12 | | NULL |
| 28 | nagios_user | adeuxmon01:37720 | nagiosql | Sleep | 12 | | NULL |
| 29 | nagios_user | adeuxmon01:37721 | nagiosxi | Sleep | 19 | | NULL |
| 30 | nagios_user | adeuxmon01:37722 | nagiosql | Sleep | 19 | | NULL |
| 31 | nagios_user | adeuxmon01:37723 | nagiosxi | Sleep | 14 | | NULL |
| 32 | nagios_user | adeuxmon01:37724 | nagiosql | Sleep | 14 | | NULL |
| 33 | nagios_user | adeuxmon01:37725 | nagiosxi | Sleep | 4 | | NULL |
| 34 | nagios_user | adeuxmon01:37726 | nagiosql | Sleep | 4 | | NULL |
| 35 | nagios_user | adeuxmon01:37727 | nagiosxi | Sleep | 20 | | NULL |
| 36 | nagios_user | adeuxmon01:37728 | nagiosql | Sleep | 20 | | NULL |
| 37 | nagios_user | adeuxmon01:37732 | nagiosxi | Sleep | 14 | | NULL |
| 38 | nagios_user | adeuxmon01:37733 | nagiosql | Sleep | 14 | | NULL |
| 39 | nagios_user | adeuxmon01:37734 | nagiosxi | Sleep | 14 | | NULL |
| 40 | nagios_user | adeuxmon01:37735 | nagiosql | Sleep | 14 | | NULL |
| 55 | nagios_user | adeuxmon01:37760 | nagiosxi | Sleep | 19 | | NULL |
| 56 | nagios_user | adeuxmon01:37761 | nagiosql | Sleep | 19 | | NULL |
| 57 | nagios_user | adeuxmon01:37762 | nagiosxi | Sleep | 5 | | NULL |
| 58 | nagios_user | adeuxmon01:37763 | nagiosql | Sleep | 5 | | NULL |
| 495 | nagios_user | adeuxmon01:41895 | nagiosxi | Sleep | 55 | | NULL |
| 496 | nagios_user | adeuxmon01:41896 | nagiosql | Sleep | 55 | | NULL |
| 506 | nagios_user | adeuxmon01:42854 | nagiosxi | Sleep | 5 | | NULL |
| 507 | nagios_user | adeuxmon01:42855 | nagiosql | Sleep | 5 | | NULL |
| 510 | nagios_user | adeuxmon01:42860 | nagiosxi | Sleep | 5 | | NULL |
| 511 | nagios_user | adeuxmon01:42862 | nagiosql | Sleep | 5 | | NULL |
| 512 | nagios_user | adeuxmon01:42863 | nagiosxi | Sleep | 4 | | NULL |
| 513 | nagios_user | adeuxmon01:42864 | nagiosql | Sleep | 5 | | NULL |
| 516 | nagios_user | adeuxmon01:42870 | nagiosxi | Sleep | 5 | | NULL |
| 517 | nagios_user | adeuxmon01:42871 | nagiosql | Sleep | 5 | | NULL |
| 518 | nagios_user | adeuxmon01:42874 | NULL | Query | 0 | NULL | show processlist |
+-----+-------------+-----------------------------+----------+---------+------+-------+------------------+
52 rows in set (0.00 sec)

Is this normal behavior for NagiosXI to maintain so many connections or do you have any recommendations on resolving it otherwise?

Note we have put this onto an NDBCluster for resiliency purposes.

Kind Regards,
Hani

[mguthrie]

I know that Nagios XI uses persistent connections for the UI, so the connections will remain open and sleeping until it just times out. That could be the reason. If you have checks running against that DB server that could account for quite a few connections as well.

How many users do you have using the XI interface?

[santosltd]

How many users do you have using the XI interface?

We have 3-4 users using it at present.

Any checks against the DB are under a separate username and not accounted for in that output.

Thanks
Hani

[mguthrie]

I just ran a test to check for comparable results, and I had similar results for the processlist. Most of the connections are sleeping, but even without the interface open I had 30 sleeping processes. With the persistent connections set, any connection, including our subsystem cron jobs will persist until the connection simply times out.

Unless you're getting into the realm of 50+ simultaneous XI users, I haven't seen the max connections being hit on an XI box. The only place I know of that we had to turn off persistent connections and increase the max connection limit was on our public XI demo box.

[santosltd]

Thanks for the response.

The example I have shown is pretty normal, but at one point we hit 151 connections which ended up being the connection limit on the server at the time.

I will keep an eye on the connection pool and see how it progresses and raise the issue further should we see it happen again.

Thanks

[mguthrie]

Hmm, that does seem a little bit high. Can you access the Admin page and make sure all of the subsystem processes are completing ok? (Particularly the database maintenance job). It didn't occur to me until now, but if you had corruption in one of your tables you could get processes stalling out with an open connection. Just to be safe, here's the DB repair procedure.
http://assets.nagios.com/downloads/nagi ... tabase.pdf