Connection Loggers Leaking Memory

[Firbydude]

Issue Description

New connections each create a unique logger. These loggers end up in
logging.Logger.manager.loggerDict which is not cleaned up, causing memory
usage to increase with each new connection created. We have a number of long-
lived processes which create many connections and this issue is causing steady
growth in memory usage.

I do not believe this is the intended use of the python logging libraries. As I
understand it, the Connection class should always use the same Logger instance
and add/remove a new handler if instance specific behavior is desired.

Connection Logger Creation

logger_name = 'vertica_{0}_{1}'.format(id(self), str(uuid.uuid4())) # must be a unique value
self._logger = logging.getLogger(logger_name)

Uncollected Logger Instances

for logger_name in logging.Logger.manager.loggerDict.keys():
    print(logger_name)
    
vertica_4468787808_bba5c933-fb01-4722-af07-8cecafd64c73
vertica_4462597216_9c089f1f-4081-4b59-8f0b-5ff5ff4d6d34
vertica_4467788384_28058ab1-b95c-46d1-aab6-a48f098f16d7
vertica_4466267520_818d1ff7-2272-41d5-a781-d54936dd8b00
vertica_4462597264_4f7ec578-06a6-4c9d-b3b4-778bb113251e
vertica_4462597312_0954a3d1-5f3e-483c-986d-e9e8e61a64f2
vertica_4466267472_5df8e67d-d801-4c0c-afa8-f6cb9a22e75b
vertica_4462597216_a425b91d-ed0f-4182-81da-91c8340ff156
vertica_4462597264_10aabfd8-15a6-4b77-b4bc-a8bdd0818446

Uncollected Allocations After 50 Connections (tracemalloc)

0:   File "/Users/koswald/.virtualenvs/sp-dev3/lib/python3.9/site-packages/vertica_python/vertica/connection.py", line 0 count=89 size=23154
1:   File "/usr/local/Cellar/python@3.9/3.9.1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/logging/__init__.py", line 0 count=302 size=21129

[Benjamin-liv]

Hello,

Do you have any updates on this issue? Our team encountered the same problem, even when the logger was disabled.

If this issue persists, we may need to consider switching to the pyodbc library as an alternative solution.

[Firbydude]

@Benjamin-liv We mitigated the issue by wrapping connections in a context manager that performs necessary cleanup.

import vertica_python


@contextlib.contextmanager
def vertica_connect(**options):
    """Create a new Vertica Connection with the provided options.

    This is a context manager wrapping vertica_python.connect.  This should be
    used in all places where one would normally use the library function.

    This can be removed when https://github.com/vertica/vertica-python/issues/476
    is addressed.

    Args:
        **options: Options to pass to vertica_python.connect.

    Returns:
        vertica_python.vertica.connection.Connection: New connection instance.
    """
    cnn = vertica_python.connect(**options)
    logger_name = cnn._logger.name
    try:
        with cnn:
            yield cnn
    finally:
        # XXX vertica_python connections each create a unique logger that
        # is not cleaned up appropriately. Remove it here to prevent memory leaking.
        del logging.Logger.manager.loggerDict[logger_name]

[Benjamin-liv]

Thank you a lot @Firbydude, I will test that!