[sqlalchemy] Understanding memory usage under SQLA

[Vlad K.]

Hello. I have a problem when processing relatively large number of rows. For example, when selecting 5000 main rows, each having a number of many-to-one relationships, memory usage shown by top skyrockets into 200+ MB range (RES), while heapy shows cca 20MB of Python heap. PostgreSQL backend via psycopg2. I've made a minimum example case based on the problem I'm noticing in my Pyramid app, so the session.commit() at line 130 is there to simulate commit done by Transaction used in Pyramid at the end of each request. If I'm understanding things correctly, committing would expire all objects involved in the session, and I even tried manual session.expunge(row), but there is no difference in memory usage. The following is source of an example case. Requires SQLAlchemy (tested with 0.7.5 and 0.7.7), guppy, psycopg2 (tested with 2.4.2 and 2.4.4). Happens both on Fedora 15 64-bit and CentOS 6.2 32-bit, though of course the 32-bit shows some 30% lower RES in top. http://pastebin.com/UFgduWVw Usage: setup a test database, update line 25 config. Prepopulate database with -p flag, then run again without any flags. I don't see where and how would any objects remain in memory, and heapy showing much lower memory use suggests something is retained in the involved C extensions?  I also tried with pympler, diff before and after selecting rows, shows nothing near reported by top. I guess there is no "leak" in traditional sense of the word because repeating the task does not yield growing memory consumption. It stabilizes at certain value and stays there. Heapy before selecting rows: Partition of a set of 102014 objects. Total size = 13160672 bytes.  Index  Count   %     Size   % Cumulative  % Kind (class / dict of class)      0  45901  45  4395296  33   4395296  33 str      1  26041  26  2186184  17   6581480  50 tuple      2   7039   7   900992   7   7482472  57 types.CodeType      3   6836   7   820320   6   8302792  63 function      4    235   0   761608   6   9064400  69 dict of module      5    608   1   689792   5   9754192  74 dict (no owner)      6    676   1   648544   5  10402736  79 dict of type      7    676   1   608344   5  11011080  84 type      8    199   0   206248   2  11217328  85 dict of class      9    185   0   167320   1  11384648  87 sqlalchemy.sql.visitors.VisitableType <334 more rows. Type e.g. '_.more' to view.> Heapy after 5000 rows have been selected: Partition of a set of 102587 objects. Total size = 16455168 bytes.  Index  Count   %     Size   % Cumulative  % Kind (class / dict of class)      0  45923  45  4397632  27   4397632  27 str      1      1   0  3146024  19   7543656  46 sqlalchemy.orm.identity.WeakInstanceDict      2  26090  25  2189480  13   9733136  59 tuple      3   7039   7   900992   5  10634128  65 types.CodeType      4   6859   7   823080   5  11457208  70 function      5    235   0   761608   5  12218816  74 dict of module      6    657   1   705048   4  12923864  79 dict (no owner)      7    676   1   650464   4  13574328  82 dict of type      8    676   1   608344   4  14182672  86 type      9    199   0   206248   1  14388920  87 dict of class <372 more rows. Type e.g. '_.more' to view.> What am I doing wrong? I'm hoping something trivial and blatantly obvious that I'm oblivious to. :) Thanks. -- .oO V Oo. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.