1

I have an Flask application that used SQLAlchemy with MySQL as the database. I have a function defined like so:

def count_items():
    total = 0
    items = db.session.query(models.Item).yield_per(100)
    for item in items:
        total += 1
    return total

It works as expected but the memory usage of uWSGI goes up by 100 MB every time I call it (the function is an app route so nothing else is being called). This can be repeated until the machine runs out of memory so I don't think it's just an issue of SQLAlchemy caching objects. Am I missing something obvious here for clearing the memory? If not, then are there any tips on how to debug it? I don't have the slightest idea where to start, especially with uWSGI in the mix.

Ivanna
  • 1,197
  • 1
  • 12
  • 22
  • `db.session.query(models.Item).count()` not working? – kylieCatt Jan 26 '16 at 22:50
  • That would work fine for counting but I really need to do more complicated processing in the loop. I stripped out as much of the logic as I could while still seeing the memory balloon in order to narrow down on what's causing the problem. – Ivanna Jan 26 '16 at 22:56
  • Sounds like you probably could be using [`offset()`](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.offset) – kylieCatt Jan 26 '16 at 23:00
  • I think that yield_per() accomplishes the same thing. The issue isn't memory usage during the loop, it's that the memory is never freed after the function returns. – Ivanna Jan 26 '16 at 23:14
  • Are you doing something else in your code that might cause the memory to not be returned? The code you have posted here doesn't seem like it would create that issue. – kylieCatt Jan 26 '16 at 23:30
  • The code is exactly what I'm running when I see the issue. It was originally more complicated but I simplified it to this and absolutely see the same behavior. – Ivanna Jan 26 '16 at 23:45
  • This question seems to have some helpful info: http://stackoverflow.com/questions/2145177/is-this-a-memory-leak-a-program-in-python-with-sqlalchemy-sqlite. Seems like you might want to try using `offset()` and incrementing the offset as you reach the end of each chunk. – kylieCatt Jan 26 '16 at 23:49
  • I've debug a memory leak for sqlalchemy here: https://bitbucket.org/zzzeek/sqlalchemy/issues/2427/memory-leak-075 any doubts response this comment – iuridiniz Jan 27 '16 at 04:27
  • @IanAuld: The same behavior is observed using limit() instead of yield_per(). This is true whether I progress through all of the data using offset() or not. – Ivanna Jan 27 '16 at 13:00

0 Answers0