Possible Memory Leak on simple operations

[Luca Zulian]

I am currently playing around with SQLAlchemy a bit, and I found a strange behaviour about memory usage. I'm using sqlalchemy with version 1.1.10 running on python 2.7.13, macOS Sierra version 10.12.5, MySQL-python 1.2.5, mysql driver version Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using EditLine wrapper

from contextlib import contextmanager

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

e = create_engine('mysql://root:root@127.0.0.1:3306/MyDb?charset=utf8')


class Currency(Base):
    __tablename__ = "currency"
    id = Column(Integer, primary_key=True)

Base.metadata.create_all(e)
Session = sessionmaker(autoflush=True, bind=e)


@contextmanager
def session_scope():
    """Provide a transactional scope around a series of operations."""
    session = Session()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


def foo():
    with session_scope() as session2:
        result = session2.query(Currency).filter_by(id=1).first()
        print(result.id)


while True:
    foo()

It seems that the memory it's never freed, and that continuously increase. Also I've got the same problem only opening and closing the session.

from contextlib import contextmanager

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

e = create_engine('mysql://root:root@127.0.0.1:3306/MyDb?charset=utf8')


class Currency(Base):
    __tablename__ = "currency"
    id = Column(Integer, primary_key=True)

Base.metadata.create_all(e)
Session = sessionmaker(autoflush=True, bind=e)


@contextmanager
def session_scope():
    """Provide a transactional scope around a series of operations."""
    session = Session()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


def foo():
    with session_scope() as session2:
        pass


while True:
    foo()

[Mike Bayer]

First run of your second script (just the session), with mysqlclient 1.3.10 which note replaces the now-unmaintained Python-MySQL (e.g. you should switch to mysqlclient in any case), python 2.7.13 on Fedora.

First 5 seconds:

#!

 PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND  
19931 classic   20   0  238120  27104   9312 R  99.0  0.3   0:03.68 python 

then about a minute later:

#!

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
19931 classic   20   0  238120  27104   9312 R  99.3  0.3   1:28.24 python

memory does not change at all. Next ill try Python-MySQL 1.2.5

[Mike Bayer]

MySQL-Python 1.2.5 also didn't have a problem, it stayed here:

#!


 PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                         
20096 classic   20   0  238116  27120   9296 R  99.7  0.3   1:00.14 python  

MySQL-Python==1.2.3 definitely did have a memory leak issue specifically with unicode.

now will try the SELECT test

[Mike Bayer]

OK, no data provided, so "result.id" is NoneType error, let's fix that:

s = Session()
s.add(Currency(id=1))
s.commit()

now the script prints out a huge list of 1's, this is still mysql-python 1.2.5, memory starts like:

#!

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                         
20241 classic   20   0  238640  27932   9580 R  80.1  0.3   0:23.03 python      

around a minute later:

#!

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                         
20241 classic   20   0  238640  27932   9580 R  80.5  0.3   1:12.91 python     

so far, nothing reproduced here. On your end, what you need to do is try to distinguish between a memory leak at the "native" level, e.g. in the MySQL driver that's in this case written in C or even the Python interpreter (is this a custom Python build on OSX ? homebrew? system python?), vs. the Python level. As mentioned on SO, the pympler tool will show you exactly what's going on with the Python interpreter memory usage, e.g. the script itself creating objects and not releasing them. There's lots of other tools too and the most classic one is heapy, I find pympler to be a lot more modern but I've used heapy too.

Since the MySQL-Python driver has definitely been implicated in memory leaks in the past I would strongly recommend seeing if your leak reproduces when you use "mysql+pymysql://" for your URL.

[Luca Zulian]

It seems that using "mysql+pymysql://" in my your URL solves the problem. I would like to test in another environment as check what happens. I think we could close the issue for the moment. Thanks a lot!

[Mike Bayer]

two other things to try:

1. try mysqlclient - make sure you uninstall mysql-python first. then connect again with "mysql://" URL. It would be nice to confirm this leak is local to mysql-python

2. Take out the "utf-8" part of your URL. Also, try with "?use_unicode=1" as well as "?use_unicode=0" in the URL. The issues with MySQL-Python leaks had to do with use_unicode mode.

[Mike Bayer]

anyway, looks like no bug on our end thanks!