Continuous dequeue causes a slow memory leak

[iagube]

Version Information

• platform.platform: Windows-10-10.0.17763-SP0
• sys.maxsize > 2**32: True
• platform.python_version: 3.11.8
• Oracle Database Version: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.19.0.0.0

Problem

• Continuous dequeue causes a slow memory leak.
• Yes the application uses thick mode.
• This is happening on Windows Server 2019, Windows Server 2022, and Windows 11 Desktop. The only difference being that on the desktop, memory usage periodically falls back down (we suspect by garbage collection). On the servers, it continues to grow.

import time
import oracledb

class QueueProcessor:
    def __init__(self, dsn, user, password, queue_name, udt_name):
        self.connection = oracledb.connect(
            dsn=dsn,
            user=user,
            password=password,
        )
        udt_Request = self.connection.gettype(udt_name)
        self.queue = self.connection.queue(queue_name, udt_Request)
        self.queue.deqOptions.visibility = oracledb.DEQ_IMMEDIATE
        self.queue.deqOptions.wait = oracledb.DEQ_NO_WAIT

    def process_messages(self):
        try:
            while True:
                request = self.queue.deqOne()
                if request:
                    if request.payload.SHUTDOWNREQUEST == "Y":
                        break
                else:
                    time.sleep(0.05)
        except oracledb.DatabaseError as e:
            print(f"Database error occurred: {e}")
        except Exception as e:
            print(f"Unexpected error occurred: {e}")

    def close_connection(self):
        try:
            self.connection.close()
        except oracledb.DatabaseError as e:
            print(f"Error closing connection: {e}")

if __name__ == "__main__":
    oracledb.init_oracle_client()

    queue_processor = QueueProcessor(
        "xxxxx",
        "xxxxx",
        "xxxxx",
        "xxxx.MY_QUEUE",
        "UDT_MYUDT",
    )
    try:
        queue_processor.process_messages()
    finally:
        queue_processor.close_connection()

[cjbj]

Thanks for the report.

[iagube]

Hello @cjbj, please any updates on this? Do you know if a fix is coming for this soon?

[anthony-tuininga]

Can you supply the create statements for UDT_MYUDT and MY_QUEUE? It would also seem that the queue is full of a lot of messages? Can you supply a script that populates them or modify your Python code to work with only one message that is repeatedly dequeued? Can you also specify what version of the Oracle Client libraries you are using? And what version of python-oracledb you are using?

[anthony-tuininga]

What is your definition of slow leak as well? Approximately how many bytes/iteration? Or does it jump by a certain amount every few iterations? You can use psutil to gather the RSS in use after each iteration to gather that information if you don't have it already.

[iagube]

Thanks @anthony-tuininga. Is this better? This should print the amount of memory used by the process every 5s. You'll need a sys password to create the tablespace and user that will run the test.

import oracledb
import os
import psutil
import threading
import time

################################################################################
# Setup parameters. PLEASE SUPPLY THESE.
################################################################################
TNS = "*******"
SYS_PASSWORD = "*******"

oracledb.init_oracle_client()

################################################################################
# Setup the user.
################################################################################
sys_connection = oracledb.connect(
    f"sys/{SYS_PASSWORD}@{TNS}",
    mode=oracledb.AUTH_MODE_SYSDBA,
)
cursor = sys_connection.cursor()

# RESET DATABASE
# cursor.execute("drop tablespace IA_TS including contents and datafiles")
# cursor.execute("drop user IAUSER cascade")

cursor.execute("create tablespace IA_TS")
cursor.execute(
    """
    create user IAUSER identified by oracle
    default tablespace IA_TS
    temporary tablespace temp
    quota unlimited on IA_TS
    """
)
cursor.execute("grant create session to IAUSER")
cursor.execute("grant create type to IAUSER")
cursor.execute("grant execute on dbms_aqadm to IAUSER")
cursor.execute("grant execute on dbms_aq to IAUSER")

sys_connection.close()

################################################################################
# Create the queue.
################################################################################
create_queue_connection = oracledb.connect(f"IAUSER/oracle@{TNS}")
cursor = create_queue_connection.cursor()
cursor.execute(
    """
    create or replace type UDT_IA_TYPE as object(
        id number,
        data varchar2(4000)
    )
    """
)
cursor.callproc(
    "dbms_aqadm.create_queue_table",
    (
        "IA_QUEUE_TABLE",
        "UDT_IA_TYPE",
    ),
)
cursor.callproc(
    "dbms_aqadm.create_queue",
    (
        "IA_QUEUE",
        "IA_QUEUE_TABLE",
    ),
)
cursor.callproc(
    "dbms_aqadm.start_queue",
    ("IA_QUEUE",),
)
create_queue_connection.close()

################################################################################
# Run the test.
################################################################################
def MemoryUsage():
    process = psutil.Process(os.getpid())
    while True:
        mem_info = process.memory_info()
        now = time.strftime("%Y-%m-%d %H:%M:%S")
        print(f"{now} {mem_info.rss / 1024 / 1024}MB")

        # Log every 5 seconds.
        time.sleep(5)

t = threading.Thread(target=MemoryUsage, daemon=True)
t.start()

test_connection = oracledb.connect(f"IAUSER/oracle@{TNS}")
udt_Request = test_connection.gettype("UDT_IA_TYPE")
queue = test_connection.queue("IA_QUEUE", udt_Request)
queue.deqOptions.visibility = oracledb.DEQ_IMMEDIATE
queue.deqOptions.wait = oracledb.DEQ_NO_WAIT

try:
    while True:
        _ = queue.deqOne()

except oracledb.DatabaseError as e:
    print(f"Database error occurred: {e}")
except Exception as e:
    print(f"Unexpected error occurred: {e}")
except KeyboardInterrupt:
    print("Keyboard interrupt.")
finally:
    print("Closing connection.")
    test_connection.close()

• The queue is empty.
• Oracle Client Library version: 19.8.0.0.0
• oracledb version: 2.1.0
• I'm seeing about 1MB every 20s

[anthony-tuininga]

Thanks for the test case. Since the queue is empty, this looks very similar to #340 with the only difference being the use of deqmany() vs deqone(). That said, I tried your test case and discovered that with 23.4 client there is no memory leak. If I use the 19.15 client there is a memory leak. So the solution for you is to upgrade to the 23.4 client. You can also request Oracle Support to backport the bug fix to the 19c client. I'll ask internally if this has been done already and if so, which RU it has been included in.

[anthony-tuininga]

I have additionally found that the leak is not present with the 21.6 client.

[iagube]

Thank you @anthony-tuininga for letting me know of the 21.6 client. I checked the 21.14 client and its fixed there too.

We were really hoping to avoid 21c because it's an innovation release and we prefer to put our clients on LTSs.

I submitted an Oracle Support issue and I would really appreciate the backporting of the bug fix to the 19c client. If you find out that it this been done and there is an RU for it, that would be the most desirable outcome.

Thanks plenty!

[anthony-tuininga]

If you want to avoid the 21c innovation release you could also consider 23.4 which is also an LTS release -- although it has just been released, it does have this bug fix in it as well. I'm still trying to see when the patch was introduced so that an evaluation can be done on whether it can be easily backported to 19c or not. If I find out anything more, I'll let you know!

[iagube]

23.4 seems to be only available on Linux. Unfortunately, we're using Windows.

I'll just wait for a backport of the fix or the (coming soon?) release of 23ai client on Windows.

[cjbj]

@iagube the Windows Instant Client 23.4 release just landed: https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

[iagube]

Thank you @anthony-tuininga! and Thank you @cjbj! This is good news. I have tested against the 23.4 client and it does not have the bug. You can close the issue.

[cjbj]

@iagube no problems. In case it is of interest, Oracle Database 23ai Free for Windows is also available: https://download.oracle.com/otn-pub/otn_software/db-express/WINDOWS.X64_234000_free.zip