Thread: [SQLObject] select() causes SQLObject + webware application to die
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Scott R. <sc...@to...> - 2004-03-02 19:28:53
|
I've found a situation in which SQLobject makes webware fall over like a straw house... First, a very simple webware servlet: ### Start 1 ## from WebKit.Page import Page from SQLObject import * __connection__ = MySQLConnection(host="host", user="user", passwd="passwd", db="db") class Person(SQLObject): email = StringCol(length=40, default=None) firstname = StringCol(length=30, default=None) lastname = StringCol(length=30, default=None) class TestPage(Page): def writeContent(self): p = Person(1) self.writeln('''Welcome.''') ### End 1 ## I hammer and hammer and hammer, and it holds up fine - as hard as I can hit it, it keeps up with me. Now, a Second Servlet: (Only Changing the TestPage class - everything else is the same.) ### Start 2 ## class TestPage(Page): def writeContent(self): peeps = Person.select(Person.q.email=='xxx') self.writeln('''Welcome.''') ### End 2 ## Once again, this holds up wonderfully. It never fails, as far as I can tell. Rock solid. Now, for the bad one. One more TestPage servlet, this time with an accessor for peeps. ### Start 3 ## class TestPage(Page): def writeContent(self): peeps = Person.select(Person.q.email=='xxx') p = peeps[0] self.writeln('''Welcome''') ### End 3 ## This one functions fine, and seems to work fine until the hammering begins, in which case it is guaranteed to fall over within 3 seconds, repeatably, every time. If used at a reasonable pace, it still falls over, eventually. Similar tests have found select() to be the culprit - th emore you use it, the faster death occurs. The strangest issue? How it dies - I've modified Webkit to print it's exit code. Normally, it's 3, and that tells webkit's shell script to restart it. When it falls over here, it receives a 137 - which is a "kill -KILL" signal, if I'm correct... Can someone explain this, and hopefully show what I'm doing wrong? I'm actually supposed to be deploying a site reasonably soon, and can't in it's current state - two users kill it in just over a minute. I'll be in #webware on irc.freenode.net if anyone wants to compare notes. - Scott |
From: Scott R. <sc...@to...> - 2004-03-02 22:27:28
|
Replying to myself here... Replacing the MySQL connection with a postgres one doesn't fix anything - although it seems to last about twice as long, maybe 6 seconds under constant hammering. How would I even start debugging this? On Tue, 2004-03-02 at 14:18, Scott Russell wrote: > I've found a situation in which SQLobject makes webware fall over like a > straw house... > > First, a very simple webware servlet: > > ### Start 1 ## > from WebKit.Page import Page > from SQLObject import * > > __connection__ = MySQLConnection(host="host", > user="user", > passwd="passwd", > db="db") > > class Person(SQLObject): > email = StringCol(length=40, default=None) > firstname = StringCol(length=30, default=None) > lastname = StringCol(length=30, default=None) > > class TestPage(Page): > def writeContent(self): > p = Person(1) > self.writeln('''Welcome.''') > ### End 1 ## > > I hammer and hammer and hammer, and it holds up fine - as hard as I can > hit it, it keeps up with me. > > Now, a Second Servlet: (Only Changing the TestPage class - everything > else is the same.) > > ### Start 2 ## > class TestPage(Page): > def writeContent(self): > peeps = Person.select(Person.q.email=='xxx') > self.writeln('''Welcome.''') > ### End 2 ## > > Once again, this holds up wonderfully. It never fails, as far as I can > tell. Rock solid. > > Now, for the bad one. One more TestPage servlet, this time with an > accessor for peeps. > > ### Start 3 ## > class TestPage(Page): > def writeContent(self): > peeps = Person.select(Person.q.email=='xxx') > p = peeps[0] > self.writeln('''Welcome''') > ### End 3 ## > > This one functions fine, and seems to work fine until the hammering > begins, in which case it is guaranteed to fall over within 3 seconds, > repeatably, every time. If used at a reasonable pace, it still falls > over, eventually. Similar tests have found select() to be the culprit - > th emore you use it, the faster death occurs. The strangest issue? How > it dies - I've modified Webkit to print it's exit code. Normally, it's > 3, and that tells webkit's shell script to restart it. When it falls > over here, it receives a 137 - which is a "kill -KILL" signal, if I'm > correct... > > Can someone explain this, and hopefully show what I'm doing wrong? I'm > actually supposed to be deploying a site reasonably soon, and can't in > it's current state - two users kill it in just over a minute. I'll be > in #webware on irc.freenode.net if anyone wants to compare notes. > > - Scott > > > > ------------------------------------------------------- > SF.Net is sponsored by: Speed Start Your Linux Apps Now. > Build and deploy apps & Web services for Linux with > a free DVD software kit from IBM. Click Now! > http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Scott R. <sc...@to...> - 2004-03-02 22:52:18
|
At LoppEar's suggestion in #webware, I've made a crude threaded app to test with - it also dies, mysteriously printing "Killed" and dropping back to the command prompt. Any idea where this comes from? Can anyone else reproduce? ### Start Test 4 # def main(args): import thread for t in range(2): thread.start_new_thread(whackit, ()) while -1: pass def whackit(): t = 0 while t < 1000: t += 1 peeps = Person.select(Person.q.email=='xxx') try: user = peeps[0] except: user = None print t ### End Test 4 # |
From: David M. <da...@re...> - 2004-03-02 22:57:15
|
I'm not a webware user, so can only offer a general comment... Have you tried setting up this thrashing in a non-webware environment? For instance, you could cook up something quick with the python HTTPServer classes (don't forget to add the threading mixin). Or even, servers aside - cook up multi-threaded programs which repeatedly thrash. One scenario would be a prog which launches 32 threads, each of which sit in a loop and thrash. Another scenario would be a prog which spawns 32 processes, each of which do the thrash thing. Cheers David Scott Russell wrote: > Replying to myself here... Replacing the MySQL connection with a > postgres one doesn't fix anything - although it seems to last about > twice as long, maybe 6 seconds under constant hammering. > > How would I even start debugging this? > > On Tue, 2004-03-02 at 14:18, Scott Russell wrote: > >>I've found a situation in which SQLobject makes webware fall over like a >>straw house... >> >>First, a very simple webware servlet: >> >>### Start 1 ## >>from WebKit.Page import Page >>from SQLObject import * >> >>__connection__ = MySQLConnection(host="host", >> user="user", >> passwd="passwd", >> db="db") >> >>class Person(SQLObject): >> email = StringCol(length=40, default=None) >> firstname = StringCol(length=30, default=None) >> lastname = StringCol(length=30, default=None) >> >>class TestPage(Page): >> def writeContent(self): >> p = Person(1) >> self.writeln('''Welcome.''') >>### End 1 ## >> >>I hammer and hammer and hammer, and it holds up fine - as hard as I can >>hit it, it keeps up with me. >> >>Now, a Second Servlet: (Only Changing the TestPage class - everything >>else is the same.) >> >>### Start 2 ## >>class TestPage(Page): >> def writeContent(self): >> peeps = Person.select(Person.q.email=='xxx') >> self.writeln('''Welcome.''') >>### End 2 ## >> >>Once again, this holds up wonderfully. It never fails, as far as I can >>tell. Rock solid. >> >>Now, for the bad one. One more TestPage servlet, this time with an >>accessor for peeps. >> >>### Start 3 ## >>class TestPage(Page): >> def writeContent(self): >> peeps = Person.select(Person.q.email=='xxx') >> p = peeps[0] >> self.writeln('''Welcome''') >>### End 3 ## >> >>This one functions fine, and seems to work fine until the hammering >>begins, in which case it is guaranteed to fall over within 3 seconds, >>repeatably, every time. If used at a reasonable pace, it still falls >>over, eventually. Similar tests have found select() to be the culprit - >>th emore you use it, the faster death occurs. The strangest issue? How >>it dies - I've modified Webkit to print it's exit code. Normally, it's >>3, and that tells webkit's shell script to restart it. When it falls >>over here, it receives a 137 - which is a "kill -KILL" signal, if I'm >>correct... >> >>Can someone explain this, and hopefully show what I'm doing wrong? I'm >>actually supposed to be deploying a site reasonably soon, and can't in >>it's current state - two users kill it in just over a minute. I'll be >>in #webware on irc.freenode.net if anyone wants to compare notes. >> >> - Scott >> >> >> >>------------------------------------------------------- >>SF.Net is sponsored by: Speed Start Your Linux Apps Now. >>Build and deploy apps & Web services for Linux with >>a free DVD software kit from IBM. Click Now! >>http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click >>_______________________________________________ >>sqlobject-discuss mailing list >>sql...@li... >>https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > > > > > ------------------------------------------------------- > SF.Net is sponsored by: Speed Start Your Linux Apps Now. > Build and deploy apps & Web services for Linux with > a free DVD software kit from IBM. Click Now! > http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > -- leave this line intact so your email gets through my junk mail filter |
From: Scott R. <sc...@to...> - 2004-03-02 23:13:40
|
David and Ian: Yes, (as you'll see in my mail that arrived the same time yours did) it looks like a thread concurrency issue. If I turn on debugging, the threading test survives many additional threads, but will die eventually. (David, 32 kills it very handily.) Whether because of some serialization that gets forced during printing, because it slows the process down enough to avoid contention, or some third issue, I can't say. Here is a sample run, with 32 threads. Sometimes it lasts for 30-40 queries, sometimes (as seen here) only 3.... $ python Main/TestPage.py 1:Dummy-1/Select : SELECT person.id, person.email, person.firstname, person.lastname FROM person WHERE (person.email = 'xxx') LIMIT 1 1:Dummy-2/Select : SELECT person.id, person.email, person.firstname, person.lastname FROM person WHERE (person.email = 'xxx') LIMIT 1 1:Dummy-3/Select : SELECT person.id, person.email, person.firstname, person.lastname FROM person WHERE (person.email = 'xxx') LIMIT 1 Killed What next? |
From: Ian B. <ia...@co...> - 2004-03-02 23:32:25
|
Scott Russell wrote: > David and Ian: > > Yes, (as you'll see in my mail that arrived the same time yours did) it > looks like a thread concurrency issue. > > If I turn on debugging, the threading test survives many additional > threads, but will die eventually. (David, 32 kills it very handily.) > Whether because of some serialization that gets forced during printing, > because it slows the process down enough to avoid contention, or some > third issue, I can't say. > > Here is a sample run, with 32 threads. Sometimes it lasts for 30-40 > queries, sometimes (as seen here) only 3.... > > $ python Main/TestPage.py > 1:Dummy-1/Select : SELECT person.id, person.email, person.firstname, person.lastname FROM person WHERE (person.email = 'xxx') LIMIT 1 > 1:Dummy-2/Select : SELECT person.id, person.email, person.firstname, person.lastname FROM person WHERE (person.email = 'xxx') LIMIT 1 > 1:Dummy-3/Select : SELECT person.id, person.email, person.firstname, person.lastname FROM person WHERE (person.email = 'xxx') LIMIT 1 > Killed > > What next? Well, it's reusing the same connection each time. Which it probably shouldn't be -- it can reuse them sometimes, but you'd think it would need to do otherwise sometimes. Hmm... Bah, DBConnection.iterSelect is dumb. It shouldn't be written like it is. Right now it looks like: def iterSelect(self, select): return self._runWithConnection(self._iterSelect, select, self, False) Which means the connection is returned to the pool immediately when the generator is returned, then reused when it shouldn't be, and worse it can be returned to the pool another time if the select results are exhausted. It should look like: def iterSelect(self, select): conn = self.getConnection() return self._iterSelect(conn, select, self, False) This isn't quite right either, as the connection will only be returned if you exhaust the select. _iterSelect should be broken out into an object that returns the connection on __del__. Anyway, this iterSelect should resolve your concurrency problems for the moment (I haven't tested it, though; report back if it works) Ian |
From: Scott R. <sc...@to...> - 2004-03-02 23:55:26
|
Presto, seems to hold up just fine now. Performance appears improved, as well. Once again, for anyone searching the mailinglist, in DBConnection.py, def iterSelect(self, select): conn = self.getConnection() return self._iterSelect(conn, select, self, False) On Tue, 2004-03-02 at 18:17, Ian Bicking wrote: > Scott Russell wrote: > > David and Ian: > > > > Yes, (as you'll see in my mail that arrived the same time yours did) it > > looks like a thread concurrency issue. > > > > If I turn on debugging, the threading test survives many additional > > threads, but will die eventually. (David, 32 kills it very handily.) > > Whether because of some serialization that gets forced during printing, > > because it slows the process down enough to avoid contention, or some > > third issue, I can't say. > > > > Here is a sample run, with 32 threads. Sometimes it lasts for 30-40 > > queries, sometimes (as seen here) only 3.... > > > > $ python Main/TestPage.py > > 1:Dummy-1/Select : SELECT person.id, person.email, person.firstname, person.lastname FROM person WHERE (person.email = 'xxx') LIMIT 1 > > 1:Dummy-2/Select : SELECT person.id, person.email, person.firstname, person.lastname FROM person WHERE (person.email = 'xxx') LIMIT 1 > > 1:Dummy-3/Select : SELECT person.id, person.email, person.firstname, person.lastname FROM person WHERE (person.email = 'xxx') LIMIT 1 > > Killed > > > > What next? > > Well, it's reusing the same connection each time. Which it probably > shouldn't be -- it can reuse them sometimes, but you'd think it would > need to do otherwise sometimes. > > Hmm... > > Bah, DBConnection.iterSelect is dumb. It shouldn't be written like it > is. Right now it looks like: > > def iterSelect(self, select): > return self._runWithConnection(self._iterSelect, select, self, > False) > > Which means the connection is returned to the pool immediately when the > generator is returned, then reused when it shouldn't be, and worse it > can be returned to the pool another time if the select results are > exhausted. It should look like: > > def iterSelect(self, select): > conn = self.getConnection() > return self._iterSelect(conn, select, self, False) > > This isn't quite right either, as the connection will only be returned > if you exhaust the select. _iterSelect should be broken out into an > object that returns the connection on __del__. Anyway, this iterSelect > should resolve your concurrency problems for the moment (I haven't > tested it, though; report back if it works) > > Ian > > > ------------------------------------------------------- > SF.Net is sponsored by: Speed Start Your Linux Apps Now. > Build and deploy apps & Web services for Linux with > a free DVD software kit from IBM. Click Now! > http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Minh L. <mi...@to...> - 2004-03-04 03:02:39
|
How do I specify default value for a column? Following the example in the document, I have the following code but the generated SQL didn't have the default value see the debug output. Thanks ==== Code ===== class MyTable (SQLObject): name = StringCol(length=20, notNone=True, default='') age = IntCol(notNone=True, default=0) dummy = StringCol(length=20, notNone=True, default='DUMMY') === End code ==== === Debug output ===>> 1/Query : CREATE TABLE my_table ( id INT PRIMARY KEY AUTO_INCREMENT, age INT NOT NULL, name VARCHAR(20) NOT NULL, dummy VARCHAR(20) NOT NULL ) === End output ===== |
From: Ian B. <ia...@co...> - 2004-03-02 22:54:19
|
Scott Russell wrote: > ### Start 2 ## > class TestPage(Page): > def writeContent(self): > peeps = Person.select(Person.q.email=='xxx') > self.writeln('''Welcome.''') > ### End 2 ## Note that this doesn't do anything. When you call .select(), it only creates a query, it doesn't run it or send anything to the database. Only when you iterate or get an index will a query happen. > Once again, this holds up wonderfully. It never fails, as far as I can > tell. Rock solid. > > Now, for the bad one. One more TestPage servlet, this time with an > accessor for peeps. > > ### Start 3 ## > class TestPage(Page): > def writeContent(self): > peeps = Person.select(Person.q.email=='xxx') > p = peeps[0] > self.writeln('''Welcome''') > ### End 3 ## Now you've actually done something. Which means there's two concurrent threads hitting the same site. I've written badly unthreadsafe MySQL code before, and it gives me wonky results but not a crash like you describe. That's ugly. But I assume it's some sort of threadsafety issue. Have you tried turning debugging on in the connection (add debug=1 to your connection constructor)? Whatever comes before the fall? Each line starts with a connection ID, which should be useful. Adding the thread name to DBConnection.printDebug would also probably be useful, like: def printDebug(self, conn, s, name, type='query'): if type == 'query': sep = ': ' else: sep = '->' s = repr(s) n = self._connectionNumbers[id(conn)] spaces = ' '*(8-len(name)) threadName = threading.currentThread().getName() print ('%(n)2i:%(threadName)s/%(name)s%(spaces)s%(sep)s %(s)s' % locals()) Ian |