Non-persistent MySQL connections are not being closed in Cacti.

[elyograg]

Description

I have been investigating a problem with Cacti where it opens a new database connection on every 5 minute poll interval, and never closes them, eventually running the DB server out of connections, even with the max_connections set to 1000. It is a new install of Cacti, I am not upgrading.

Eventually I figured out that configuring Cacti to use persistent database connections solved the issue. So there is a workaround.

I am seeing the biggest problem with the long-running daemons, one for cacti itself called cactid, the other for a cacti plugin called flowview. The service for that one is called "flow-capture".

The cacti project is claiming that they have never had to worry about closing connections before and they think something probably changed in PDO.

This is the issue I opened on Cacti for the problem:

Cacti/cacti#5401

PHP Version

PHP 8.1.2-1ubuntu2.13

Operating System

Ubuntu 22.04

[Girgias]

That would require bisecting commits, as there have been several changes in PDO but none that I can see which would change that behaviour.

[MohamedAbuZamil]

The issue you encountered with Cacti and its long-running daemons, cactid and flow-capture, is likely related to how PHP handles database connections with the PDO (PHP Data Objects) extension.

In previous versions of PHP, closing database connections was not always critical because PHP automatically handled resource cleanup at the end of a script's execution. However, in PHP 8, the behavior has changed with the introduction of Zend Resource Manager, which affects how PHP manages and cleans up resources, including database connections.

The Zend Resource Manager in PHP 8 provides more robust resource handling and allows for better memory management, but it also requires developers to be more conscious of resource usage. With the new Zend Resource Manager, leaving database connections open indefinitely can lead to resource exhaustion over time, as you have observed with Cacti running out of connections.

To resolve this issue, you have two main options:

1. Use Persistent Connections: As you mentioned, configuring Cacti to use persistent database connections is a valid workaround. Persistent connections allow PHP to reuse existing connections instead of opening new ones for each request. This can help mitigate the problem of running out of connections since connections are reused rather than created and closed on each poll.

2. Manually Close Database Connections: The other approach is to modify the Cacti code to ensure that database connections are explicitly closed when they are no longer needed. This requires updating the code where the database connections are opened to include explicit code for closing them after their use.

Persistent connections are often the preferred solution in long-running applications because they minimize the overhead of connection establishment and teardown, but they may not be suitable for all scenarios.

It's worth noting that the behavior of the Zend Resource Manager is not unique to Cacti but affects any PHP application that handles resource management. Developers of other long-running PHP applications may also encounter similar resource exhaustion issues if they do not properly manage resources like database connections.

In conclusion, while PHP 8 introduced changes to resource management, it also provides more control over resource usage. Using persistent connections or explicitly closing connections after use should address the problem you encountered with Cacti and ensure that your long-running daemons can work efficiently without causing database connection issues.