PyGres DB Connection in Zope drops when many Users are added (UserDB) (UserDB)
Hello everyone, This is a help message to every mailing list, I think this issue could be related to. We are in production and 5000 flyers (45000 follow) were sent out and the site keeps giving problems. I use: Zope 2.1.4, PyGres 2.x, Apache, and PostGres 7.0RC1 The issue: It seems like that whenever "many" (10, I know that is not much) people sign up at the same time on our Web Site (http://www.plandepot.com), we loose the DB connection. The connection is simple restored, when disconnecting and the reconnecting the database. Unfortunately I was unable to reproduce the error outside of business hours, so I had never the chance to inspect the issue in detail. I personally do not think it is PostGreSQL or Zope, since I would have read more about it. I have to mention, that PyGres is not thread safe and by default Zope uses more than one thread if required. So could that be the issue? How do I setup Zope in a way, that it uses only one thread? Also, I do not use any of the caching features in Zope SQL Methods either. Any ideas are appriciated. THANK YOU ALL IN ADVANCE!!! Regards, Stephan -- Stephan Richter - (901) 573-3308 - srichter@cbu.edu CBU - Physics & Chemistry; Framework Web - Web Design & Development PGP Key: 735E C61E 5C64 F430 4F9C 798E DCA2 07E3 E42B 5391
On Tue, Apr 25, 2000 at 01:19:25AM -0500, Stephan Richter wrote:
I use: Zope 2.1.4, PyGres 2.x, Apache, and PostGres 7.0RC1
The issue: It seems like that whenever "many" (10, I know that is not much) people sign up at the same time on our Web Site (http://www.plandepot.com), we loose the DB connection. The connection is simple restored, when disconnecting and the reconnecting the database. Unfortunately I was unable to reproduce the error outside of business hours, so I had never the chance to inspect the issue in detail. I personally do not think it is PostGreSQL or Zope, since I would have read more about it. I have to mention, that PyGres is not thread safe and by default Zope uses more than one thread if required. So could that be the issue? How do I setup Zope in a way, that it uses only one thread? Also, I do not use any of the caching features in Zope SQL Methods either.
Hi Stephan, I've been running a very similar setup for seven months now under a lot heavier traffic, and have never experienced db connection drops (although I've heard about people with similar problems). Maybe this is an operating system or compilation issue. I'm using Linux 2.2.9 and have compiled everything on the same development environment, especially the ZPyGres part matches by running version of pgsql. This is running pgsql 6.5.3, I do have 7beta5 on my development machine but didn't make any stress tests on it. On the other hand, Postgres itself can kill a connection depending on how severe an error it encounters. Broken indexes tend to do that. Try to run a "vacuum analyze verbose" daily, it will check for index consistency and improve your overall db performance. Another hint would be logging your standard_error_message to a file, it usually contains verbose data from the pgsql driver when there is an error. Cheers, jdrowell -- John Douglas Rowell Home of jdfetch 0.4.0, jdresolve 0.5.2, me@jdrowell.com jdrinfo 0.2, jdtracker 0.11 and http://www.jdrowell.com jdwhatsnew 0.21 Member of the AppWatch staff - http://appwatch.com - staff@appwatch.com
Have you tried running AB against some of the pages and watching the connections happen? Also, try playing around with the cache. I found that I could change the load times and db performance by playing with Zope db caching. I would also hate to mention it but 7 is still beta. Can you try it on 6.5.3 and see if it still happens? J At 1:19 AM -0500 4/25/2000, Stephan Richter wrote:
Hello everyone,
This is a help message to every mailing list, I think this issue could be related to. We are in production and 5000 flyers (45000 follow) were sent out and the site keeps giving problems.
I use: Zope 2.1.4, PyGres 2.x, Apache, and PostGres 7.0RC1
The issue: It seems like that whenever "many" (10, I know that is not much) people sign up at the same time on our Web Site (http://www.plandepot.com), we loose the DB connection. The connection is simple restored, when disconnecting and the reconnecting the database. Unfortunately I was unable to reproduce the error outside of business hours, so I had never the chance to inspect the issue in detail. I personally do not think it is PostGreSQL or Zope, since I would have read more about it. I have to mention, that PyGres is not thread safe and by default Zope uses more than one thread if required. So could that be the issue? How do I setup Zope in a way, that it uses only one thread? Also, I do not use any of the caching features in Zope SQL Methods either.
At 06:21 AM 4/25/00 -0400, J. Atwood wrote:
Have you tried running AB against some of the pages and watching the connections happen? Also, try playing around with the cache. I found that I could change the load times and db performance by playing with Zope db caching. I would also hate to mention it but 7 is still beta. Can you try it on 6.5.3 and see if it still happens?
I am sorry, I should have mentioned: It originally occurred with PostGres 6.5.3. What is AB? If everything fails, I will try the caching options. I just do not want to introduce new variables to the game. Regards, Stephan -- Stephan Richter - (901) 573-3308 - srichter@cbu.edu CBU - Physics & Chemistry; Framework Web - Web Design & Development PGP Key: 735E C61E 5C64 F430 4F9C 798E DCA2 07E3 E42B 5391
AB is apachebench which is a benchmarking program. You can basically pound on your installation to see where the weaknesses (slowness) is. For you, this could mean just a few Methods that have the SQL in them or the SQL Methods themselves. Watch them in top (in Linux) and see what happens. Caching is part of the PyGreSQL method (the advanced tab). Sorry about the 6.5.3, I thought I read 7. J At 8:06 AM -0500 4/25/2000, Stephan Richter wrote:
At 06:21 AM 4/25/00 -0400, J. Atwood wrote:
Have you tried running AB against some of the pages and watching the connections happen? Also, try playing around with the cache. I found that I could change the load times and db performance by playing with Zope db caching. I would also hate to mention it but 7 is still beta. Can you try it on 6.5.3 and see if it still happens?
I am sorry, I should have mentioned: It originally occurred with PostGres 6.5.3. What is AB? If everything fails, I will try the caching options. I just do not want to introduce new variables to the game.
Regards, Stephan -- Stephan Richter - (901) 573-3308 - srichter@cbu.edu CBU - Physics & Chemistry; Framework Web - Web Design & Development PGP Key: 735E C61E 5C64 F430 4F9C 798E DCA2 07E3 E42B 5391
At 09:07 AM 4/25/00 -0400, J. Atwood wrote:
AB is apachebench which is a benchmarking program. You can basically pound on your installation to see where the weaknesses (slowness) is. For you, this could mean just a few Methods that have the SQL in them or the SQL Methods themselves. Watch them in top (in Linux) and see what happens.
Cool. I will do that.
Caching is part of the PyGreSQL method (the advanced tab).
You mean SQL Method (I have seen caching options there before) or PyGreSQL DB Connection?
Sorry about the 6.5.3, I thought I read 7.
I switched to 7.0 last week. But it occurred for both versions. Regards, Stephan -- Stephan Richter - (901) 573-3308 - srichter@cbu.edu CBU - Physics & Chemistry; Framework Web - Web Design & Development PGP Key: 735E C61E 5C64 F430 4F9C 798E DCA2 07E3 E42B 5391
Stephan Richter wrote:
Hello everyone,
This is a help message to every mailing list, I think this issue could be related to. We are in production and 5000 flyers (45000 follow) were sent out and the site keeps giving problems.
I use: Zope 2.1.4, PyGres 2.x, Apache, and PostGres 7.0RC1
The issue: It seems like that whenever "many" (10, I know that is not much) people sign up at the same time on our Web Site (http://www.plandepot.com), we loose the DB connection. The connection is simple restored, when disconnecting and the reconnecting the database.
And that's exactly how we quick-fixed it :) def getUser(self, name): """Return the named user object or None if no such user exists""" try: # koos elluäratamisega try: ob=self.sqlUserQuery(username=name) except: conn = getattr(self,self.conn_id) conn.connect(conn.connection_string) ob=self.sqlUserQuery(username=name) except: return None if not ob: return None ob=ob[0] return User(sqlattr(ob, 'username'), sqlattr(ob, 'password'), sqlattr(ob, 'roles'), sqlattr(ob, 'domains')) It does not find or solve the original problem, but at least it revives it automatically -------------- Hannu
<snip original message, suggested code>
It does not find or solve the original problem, but at least it revives it automatically
What does your code do? Will it automatically "reset" the connection and work again? Regards, Stephan -- Stephan Richter - (901) 573-3308 - srichter@cbu.edu CBU - Physics & Chemistry; Framework Web - Web Design & Development PGP Key: 735E C61E 5C64 F430 4F9C 798E DCA2 07E3 E42B 5391
Stephan Richter wrote:
<snip original message, suggested code>
It does not find or solve the original problem, but at least it revives it automatically
What does your code do? Will it automatically "reset" the connection and work again?
Yes. try: try: ob=self.sqlUserQuery(username=name) except: # first get the PyGreSQL connection object conn = getattr(self,self.conn_id) # then "reset" it conn.connect(conn.connection_string) # and retry the query ob=self.sqlUserQuery(username=name) except: # failing after reset means a more serius problem (like bad query) return None ----------- Hannu
participants (4)
-
Hannu Krosing -
J. Atwood -
John D. Rowell -
Stephan Richter