I did a sanity test of PDO 1.2.2 vs. MySQLdb because I
was running into a major memory leak when using pdo.
I'm using python 2.2.2:
Python 2.2.2 (#1, Apr 2 2003, 23:18:14)
[GCC 2.96 20000731 (Red Hat Linux 7.3 2.96-112)] on linux2
Here is the table:
CREATE test_tb (
row_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(10),
INDEX(name)
);
INSERT INTO test_tb (name) VALUES ('test1')
INSERT INTO test_tb (name) VALUES ('test2')
INSERT INTO test_tb (name) VALUES ('test3')
INSERT INTO test_tb (name) VALUES ('test4')
Here is the pdo testcode: (hopefully this will format
correctly)
import pdo
while (1):
con = pdo.connect("module=MySQLdb;host=sql;db=batchostat;user=bstat;passwd=cable")
rs = con.open("SELECT * FROM test_tb")
while rs.next():
a = rs.fields['row_id'].value
I ran this and opened up a 'top'. Immediately, the RAM
usage starts going up and never stops, despite the fact
that all these variables should go out of scope for
each iteration of the loop
Here is the MySQLdb code:
import MySQLdb
con =
MySQLdb.connect(host='sql',db='batchostat',user='bstat',passwd='cable')
cursor = con.cursor()
while (1):
num_rows = cursor.execute("SELECT * FROM test_tb")
rows = cursor.fetchall()
table = {}
for elem in cursor.description:
table[elem[0]] = []
for row in rows:
col_count = 0
while col_count < len(cursor.description): table[cursor.description[col_count][0]].append(row[col_count])
col_count += 1
Running this example, and switching to 'top' shows
memory usage hovering around 2.3 megs and not changing
much if at all
PDO must be creating some ciruclar references or
something and it's causing the garbage collection in
Python to fail to reclaim memory allocated by the module.
Any clues why this would be occuring? Does it have to
do with the resultsource referential generation of the
field objects?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Nate,
I think I see the problem. This is a kind-of memory leak, but not in the way you're expecting. The culprit is the connection you make every time through the loop in the pdo version. Let me explain:
Since pdo supports multiple DBAPI modules and is designed to support multiple modules loaded at once (a connection to a postgresql db and a mysqldb at the same time, for example), the underlying DBAPI module is loaded via import magic and assigned to an internal variable of the newly created connection object. Thus, in your example, the MySQLdb module is being imported every time through the loop!
One would hope that when con went out of scope that the python GC would unload the memory used by the imported-and-assigned module within con - but as we can see, it does not. Perhaps this is why import magic isn't done casually, or it may just be a bug in the GC.
Moving the connection to above the while (1): should fix the problem. If it does not, please let us know immediately!
~Jon Franz
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Jon,
Thanks for the prompt response. I changed the code a bit so that the connection is only created once. However, the problem still occurs. Here is the new code:
import pdo
def test():
global con
rs = con.open("SELECT * FROM show_tb")
while rs.next():
a = rs.fields['show_id'].value
con = pdo.connect("module=MySQLdb;host=sql;db=batchostat;user=bstat;passwd=cable")
while(1):
test()
The memory allocation still goes crazy even though the conneciton is created outside the while loop. I'm trying ot write a server-type application that needs high uptime using PDO. It worked fine while I was developing it, but now that it's stable, I'm seeing the memory problem, and I really don't want to have to reimplement pdo!
Thanks for the help.
-Nate
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This is a repost in case you're tracking your comments to the original thread:
I downloaded and installed the new source and made the appropriate change to the test-stub program by adding rs.close() after the while rs.next() loop ends. The problem still persists.
I made an interface-similar version of pdo that's as stripped down as possible and from what I can tell, the memory problem is stemming from how fields are created. It seems that by passing in a reference to the result_set:
line: 252
for i in self.__cursor.description:
\tself.fields[i[0]] = Field(i[0], i[1], i[2], i[3], i[4], i[5], i[6], position, self)
This creates a circular reference since the result_set has field objects which then have references to the parent result set. Also, when the cursor is passed around different objects, this might cause the problem as well.
P.S.
Off-Topic:
The HTML docs are missing the 'insertid' field for the SimpleExecute object.
-Nate
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hrm, I'll dig some more - we should be able to work around the circular reference issue fairly quickly. I think we never noticed this since all of our test scripts run, then exit, and are thus short lived. I guess we'll need to modify those as well to have one that runs for a long period of time and looks for bloat to avoid issues like this is the future.
I'll fix the problem and upload a b version here in a bit.
On the off-topic part:
insertid is broken with many DBAPI modules, so we didn't document it for now until we figure out a workaround - you'l notice that the 1.2.2a version you have actualy wraps the assignment in a 'try:' clause to work aorund this issue.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
give her a whirl. .close() explicitly kills the reference to the resultsource, and dels each Field object as it goes. .close() also explicitly sets the internal reference in the resultset to the list-of-tuples storing the data to None.
Again, let me know if this doesn't work out.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Jon,
I checked the forum last night at home but wasn't able to actually run the new code until I got into work this morning. Hallelujah! It works! The memory stays at about 2.5megs, much like the version that used MySQLdb alone.
The only issue is that you have to call rs.close() in order for the memory to be reclaimed. I wonder if there's a more elegant way because any time a user forgets to add that single close() call, memory will not be reclaimed. Perhaps there's a way of using the __del__ builtin to call close() but I fear that __del__ won't be called due to the reference counts not being fully decremented to zero.
For the meantime, I'll add rs.close() calls to all of my code. Thanks for getting this fixed and working so quickly to do so!
-Nate Reid
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I think you're right on the __del__ issue - which stinks, I'd rather not add a func to the api, especialy such a non-pythonesque way of clearing memory.
I just did this test, which so far goes to show that a __del__ doesn't get called on reasignment if the object and its children contain circular references:
>>> class foo(dict):
... def __del__(self):
... print "here"
...
>>> bar = foo()
>>> bar = foo()
here
>>> bar['sally'] = bar
>>> bar = foo()
>>> bar
{}
-----------------------------
I'll dig to see if theres some other workaround to ensure that __del__ gets called in this sort of situation.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I downloaded and installed the new source and made the appropriate change to the test-stub program by adding rs.close() after the while rs.next() loop ends. The problem still persists.
I made an interface-similar version of pdo that's as stripped down as possible and from what I can tell, the memory problem is stemming from how fields are created. It seems that by passing in a reference to the result_set:
line: 252
for i in self.__cursor.description:
\tself.fields[i[0]] = Field(i[0], i[1], i[2], i[3], i[4], i[5], i[6], position, self)
This creates a circular reference since the result_set has field objects which then have references to the parent result set. Also, when the cursor is passed around different objects, this might cause the problem as well.
P.S.
Off-Topic:
The HTML docs are missing the 'insertid' field for the SimpleExecute object.
-Nate
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I did a sanity test of PDO 1.2.2 vs. MySQLdb because I
was running into a major memory leak when using pdo.
I'm using python 2.2.2:
Python 2.2.2 (#1, Apr 2 2003, 23:18:14)
[GCC 2.96 20000731 (Red Hat Linux 7.3 2.96-112)] on linux2
Here is the table:
CREATE test_tb (
row_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(10),
INDEX(name)
);
INSERT INTO test_tb (name) VALUES ('test1')
INSERT INTO test_tb (name) VALUES ('test2')
INSERT INTO test_tb (name) VALUES ('test3')
INSERT INTO test_tb (name) VALUES ('test4')
Here is the pdo testcode: (hopefully this will format
correctly)
import pdo
while (1):
con = pdo.connect("module=MySQLdb;host=sql;db=batchostat;user=bstat;passwd=cable")
rs = con.open("SELECT * FROM test_tb")
while rs.next():
a = rs.fields['row_id'].value
I ran this and opened up a 'top'. Immediately, the RAM
usage starts going up and never stops, despite the fact
that all these variables should go out of scope for
each iteration of the loop
Here is the MySQLdb code:
import MySQLdb
con =
MySQLdb.connect(host='sql',db='batchostat',user='bstat',passwd='cable')
cursor = con.cursor()
while (1):
num_rows = cursor.execute("SELECT * FROM test_tb")
rows = cursor.fetchall()
table = {}
for elem in cursor.description:
table[elem[0]] = []
for row in rows:
col_count = 0
while col_count < len(cursor.description): table[cursor.description[col_count][0]].append(row[col_count])
col_count += 1
Running this example, and switching to 'top' shows
memory usage hovering around 2.3 megs and not changing
much if at all
PDO must be creating some ciruclar references or
something and it's causing the garbage collection in
Python to fail to reclaim memory allocated by the module.
Any clues why this would be occuring? Does it have to
do with the resultsource referential generation of the
field objects?
Nate,
I think I see the problem. This is a kind-of memory leak, but not in the way you're expecting. The culprit is the connection you make every time through the loop in the pdo version. Let me explain:
Since pdo supports multiple DBAPI modules and is designed to support multiple modules loaded at once (a connection to a postgresql db and a mysqldb at the same time, for example), the underlying DBAPI module is loaded via import magic and assigned to an internal variable of the newly created connection object. Thus, in your example, the MySQLdb module is being imported every time through the loop!
One would hope that when con went out of scope that the python GC would unload the memory used by the imported-and-assigned module within con - but as we can see, it does not. Perhaps this is why import magic isn't done casually, or it may just be a bug in the GC.
Moving the connection to above the while (1): should fix the problem. If it does not, please let us know immediately!
~Jon Franz
Jon,
Thanks for the prompt response. I changed the code a bit so that the connection is only created once. However, the problem still occurs. Here is the new code:
import pdo
def test():
global con
rs = con.open("SELECT * FROM show_tb")
while rs.next():
a = rs.fields['show_id'].value
con = pdo.connect("module=MySQLdb;host=sql;db=batchostat;user=bstat;passwd=cable")
while(1):
test()
The memory allocation still goes crazy even though the conneciton is created outside the while loop. I'm trying ot write a server-type application that needs high uptime using PDO. It worked fine while I was developing it, but now that it's stable, I'm seeing the memory problem, and I really don't want to have to reimplement pdo!
Thanks for the help.
-Nate
Hey Nate,
let me do some tests to see if I can place this problem - if its MySQLdb-pdo specific issue or pdo-wide. I'll get back to you asap.
Try this version of pdo:
http://www.neurokode.com/PDO-1.2.2a.zip
you'll need to add a line in the test function that does:
rs.close()
when you're done looping over the resultsource.
Let me know asap if this doesn't fix it.
This is a repost in case you're tracking your comments to the original thread:
I downloaded and installed the new source and made the appropriate change to the test-stub program by adding rs.close() after the while rs.next() loop ends. The problem still persists.
I made an interface-similar version of pdo that's as stripped down as possible and from what I can tell, the memory problem is stemming from how fields are created. It seems that by passing in a reference to the result_set:
line: 252
for i in self.__cursor.description:
\tself.fields[i[0]] = Field(i[0], i[1], i[2], i[3], i[4], i[5], i[6], position, self)
This creates a circular reference since the result_set has field objects which then have references to the parent result set. Also, when the cursor is passed around different objects, this might cause the problem as well.
P.S.
Off-Topic:
The HTML docs are missing the 'insertid' field for the SimpleExecute object.
-Nate
Hrm, I'll dig some more - we should be able to work around the circular reference issue fairly quickly. I think we never noticed this since all of our test scripts run, then exit, and are thus short lived. I guess we'll need to modify those as well to have one that runs for a long period of time and looks for bloat to avoid issues like this is the future.
I'll fix the problem and upload a b version here in a bit.
On the off-topic part:
insertid is broken with many DBAPI modules, so we didn't document it for now until we figure out a workaround - you'l notice that the 1.2.2a version you have actualy wraps the assignment in a 'try:' clause to work aorund this issue.
ok, 1.2.2b is up at:
http://www.neurokode.com/PDO-1.2.2b.zip
give her a whirl. .close() explicitly kills the reference to the resultsource, and dels each Field object as it goes. .close() also explicitly sets the internal reference in the resultset to the list-of-tuples storing the data to None.
Again, let me know if this doesn't work out.
Jon,
I checked the forum last night at home but wasn't able to actually run the new code until I got into work this morning. Hallelujah! It works! The memory stays at about 2.5megs, much like the version that used MySQLdb alone.
The only issue is that you have to call rs.close() in order for the memory to be reclaimed. I wonder if there's a more elegant way because any time a user forgets to add that single close() call, memory will not be reclaimed. Perhaps there's a way of using the __del__ builtin to call close() but I fear that __del__ won't be called due to the reference counts not being fully decremented to zero.
For the meantime, I'll add rs.close() calls to all of my code. Thanks for getting this fixed and working so quickly to do so!
-Nate Reid
I think you're right on the __del__ issue - which stinks, I'd rather not add a func to the api, especialy such a non-pythonesque way of clearing memory.
I just did this test, which so far goes to show that a __del__ doesn't get called on reasignment if the object and its children contain circular references:
>>> class foo(dict):
... def __del__(self):
... print "here"
...
>>> bar = foo()
>>> bar = foo()
here
>>> bar['sally'] = bar
>>> bar = foo()
>>> bar
{}
-----------------------------
I'll dig to see if theres some other workaround to ensure that __del__ gets called in this sort of situation.
I downloaded and installed the new source and made the appropriate change to the test-stub program by adding rs.close() after the while rs.next() loop ends. The problem still persists.
I made an interface-similar version of pdo that's as stripped down as possible and from what I can tell, the memory problem is stemming from how fields are created. It seems that by passing in a reference to the result_set:
line: 252
for i in self.__cursor.description:
\tself.fields[i[0]] = Field(i[0], i[1], i[2], i[3], i[4], i[5], i[6], position, self)
This creates a circular reference since the result_set has field objects which then have references to the parent result set. Also, when the cursor is passed around different objects, this might cause the problem as well.
P.S.
Off-Topic:
The HTML docs are missing the 'insertid' field for the SimpleExecute object.
-Nate