Description
Describe the bug
I have a long running process that periodically requests data from a postgresql database and converts to the results to a numpy array for analysis. I see the process leaking memory on each coercion of the results to a numpy array.
In my real process after a couple of days it can be using 9 GB of RAM and after a week it crashes the server. The previous version (python2.7) uses less than 200MB even after running for weeks but that version used psycopg2 directly. I could go back to doing that but I wanted to use sqlalchemy across the codebase by default and I'd also like to understand why this isn't working properly.
While creating the test case, I found that the same issue occurs with a sqlite in memory database. I also saw the same issue on my laptop and my server (both using sqlalchemy v.1.4.32 and v1.4.23)
To Reproduce
import sqlalchemy as sql
import numpy as np
import time
dburl = 'sqlite://'
engine = sql.create_engine(dburl, echo=False)
meta = sql.MetaData()
tbl = sql.Table('test_table',meta,
sql.Column('a',sql.Integer,primary_key=True),
sql.Column('b',sql.Integer),
)
meta.create_all(engine)
#fill table
with engine.connect() as conn:
conn.execute(tbl.insert(),
[{'b': i} for i in range(200)]
)
#test
while True:
with engine.connect() as conn:
query = sql.text("SELECT a, b FROM test_table ORDER BY a LIMIT 200")
results = conn.execute(query)
rows = results.fetchall()
data = np.array(rows)
#fixes that didn't work
del rows
results.close()
del data
time.sleep(0.1)Error
Resident memory usage is up to 358MB in just a few minutes and there's no sign of stabilisation.
after a couple of minutes
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1952762 aaa 20 0 762204 186264 16188 S 5.7 0.9 0:09.77 python3
a bit later
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1952762 aaa 20 0 934200 358092 16188 S 5.6 1.8 0:21.20 python3
Versions
- OS: Linux (CentOS)
- Python: 3.6.8
- SQLAlchemy: 1.4.32, 1.4.23
- Database: Postgresql (v12.9 64bit)
- DBAPI: psycopg2
Additional context
No response
Metadata
Metadata
Assignees
Labels
Type
Projects
Milestone
Relationships
Development
Participants
Activity
zzzeek commentedon Mar 30, 2022
hi there -
memory holds completely steady for me when i run the test case. additionally, if there is some leak it seems more likely this would be related to numpy. have you reported to them? also what happens in your local reproduction case if you replace "data = np.array(rows)" with "data = rows" ? is there any leak when you remove numpy from the script? that would also indicate numpy as the source of the leak.
zzzeek commentedon Mar 30, 2022
OK, the issue can be reproduced if I compile with SQLAlchemy's C extensions. Why numpy is having a problem is not clear. What is clear is that if I consume the results first into a list of dictionaries, there's no problem, and of course if I dont use numpy, there's no problem.
so this still seems to be some issue in numpy and should still be reported to them. As a workaround, convert to a list of dicts first:
The C extensions in SQLAlchemy are replaced with cython code in 2.0 and the approach is entirely different.
CaselIT commentedon Mar 30, 2022
Does it reproduce also on v2 with cython?
zzzeek commentedon Mar 30, 2022
nope, cython is of course fine.
I dont know how we would diagnose this because if you've ever looked at numpy's source you'd...well....you'll see. forget it
zzzeek commentedon Mar 30, 2022
it looks like numpy.array() is calling iter() and len() on each row. but if i make a program that calls these methods without using a numpy array, there's no leak.
zzzeek commentedon Mar 30, 2022
yeah im not finding any method that inherently leaks and this seems like something numpy is doing, it's extremely weird about datatypes. just convert to dict/ tuple first, i.e.
data = np.array([tuple(x) for x in rows])ordata = np.array([dict(x) for x in rows])zzzeek commentedon Mar 30, 2022
reproduces in SQLAlchemy 1.3 also. so this is old stuff
zzzeek commentedon Mar 30, 2022
here's a little bit on numpy: https://numpy.org/devdocs/reference/c-api/data_memory.html#configurable-memory-routines-in-numpy-nep-49 they have a very elaborate C / memory implementation that's all custom. it would appear to be incompatible with our C extensions.
32 remaining items