Options for handling concurrency?
Hi all, One of my Zope sites is used by classes of students - up to 60 or so at a time. When they use the site the response time is really slow - they could be waiting minutes between clicks. The site uses ZSQL methods *extensively* and external methods. I realise that part of this problem is because of our Sun iron (2400 pystones - my *iMac* does 4400, and a PIII 500MHz does 5500), but as I can't get this site onto another box in the near future, I'm after other ideas. Can anyone give me some pointers (even just rule of thumb) to improve the response time? I was going to set the threads to be something like 32, but I've read other posts suggesting that wouldn't help too much. Is there someway I can profile what's going on for example? Any thought gratefully received. tone
From: "tony" <tony.mcdonald@ncl.ac.uk>
Hi all, One of my Zope sites is used by classes of students - up to 60 or so at a time. When they use the site the response time is really slow - they could be waiting minutes between clicks. The site uses ZSQL methods *extensively* and external methods. I realise that part of this problem is because of our Sun iron (2400 pystones - my *iMac* does 4400, and a PIII 500MHz does 5500), but as I can't get this site onto another box in the near future, I'm after other ideas.
Can anyone give me some pointers (even just rule of thumb) to improve the response time? I was going to set the threads to be something like 32, but I've read other posts suggesting that wouldn't help too much. Is there someway I can profile what's going on for example?
Any thought gratefully received.
- Have a look at /Control_Panel/manage_debug. It'll show you reference counts and currenlty open connections, which can be very helpful in targeting hotspots. - Also, what DBMS are you using? If you are using a level 1 or 2 DA (see http://www.zope.org/Members/petrilli/DARoadmap) then you can do only one DB query at a time. It might be time to choose a different DB, or if you are using the old MySQL DA, to upgrade. - If you are using ZSQL a lot, do you use caching? Even a cache expiry of 1 second would help a heavily hit site tremendously, noone will notice the data returned being at most 1 second out of date.But all hits with equal parameters on the same thread in Zope within that second need only one DB query. Martijn Pieters | Software Engineer mailto:mj@digicool.com | Digital Creations http://www.digicool.com/ | Creators of Zope http://www.zope.org/ | The Open Source Web Application Server ---------------------------------------------
At 9:43 am +0100 27/2/00, Martijn Pieters wrote:
- Have a look at /Control_Panel/manage_debug. It'll show you reference counts and currenlty open connections, which can be very helpful in targeting hotspots.
Aye carumba! - what a lot of information. I'm going to start delving around there and see what happens. Is there any additional documentation available on what the info. means? - I've looked in the ApplicationManager.py code is a wee bit over my head. As an aside (to the list), looking around the ApplicationManager.py reveals something called manage_profile. It looks like the main Zope functions are being profiled here (ie not at the object level). It's interesting, but it does seem to be a resource hog...
- Also, what DBMS are you using? If you are using a level 1 or 2 DA (see http://www.zope.org/Members/petrilli/DARoadmap) then you can do only one DB query at a time. It might be time to choose a different DB, or if you are using the old MySQL DA, to upgrade.
It's MySQL, but using the latest DA - I'm pretty sure I'm doing more than one query at a time as I set off a ZSQL query that takes 9 seconds from the MySQL command line, and then started to use aspects of the site that I *know* use MySQL lookups. BTW, the 9 second SQL query isn't used in the site! :)
- If you are using ZSQL a lot, do you use caching? Even a cache expiry of 1 second would help a heavily hit site tremendously, noone will notice the data returned being at most 1 second out of date.But all hits with equal parameters on the same thread in Zope within that second need only one DB query.
I do use caching Martijn, but I must admit that it's been a hit and miss affair. I'm trying to do as many of my SQL queries in external methods (ironically for the performance), so I may well be losing a lot of the caching performance breakthrough there .... hmmm. I wonder if I could do something like... Thanks a lot for the pointers, I'll report back to the list if I come across anything really useful! Tone ------ Dr Tony McDonald, FMCC, Networked Learning Environments Project http://nle.ncl.ac.uk/ The Medical School, Newcastle University Tel: +44 191 222 5888 Fingerprint: 3450 876D FA41 B926 D3DD F8C3 F2D0 C3B9 8B38 18A2
Tony McDonald wrote:
At 9:43 am +0100 27/2/00, Martijn Pieters wrote:
- Have a look at /Control_Panel/manage_debug. It'll show you reference counts and currenlty open connections, which can be very helpful in targeting hotspots.
Aye carumba! - what a lot of information. I'm going to start delving around there and see what happens. Is there any additional documentation available on what the info. means?
Nope.
- I've looked in the ApplicationManager.py code is a wee bit over my head.
As an aside (to the list), looking around the ApplicationManager.py reveals something called manage_profile. It looks like the main Zope functions are being profiled here (ie not at the object level). It's interesting, but it does seem to be a resource hog...
Profiling is turned off by default. You're right though, when it's turned on it does slow down Zope considerably. But it shouldn't matter because that isn't documented either.
- Also, what DBMS are you using? If you are using a level 1 or 2 DA (see http://www.zope.org/Members/petrilli/DARoadmap) then you can do only one DB query at a time. It might be time to choose a different DB, or if you are using the old MySQL DA, to upgrade.
It's MySQL, but using the latest DA - I'm pretty sure I'm doing more than one query at a time as I set off a ZSQL query that takes 9 seconds from the MySQL command line, and then started to use aspects of the site that I *know* use MySQL lookups. BTW, the 9 second SQL query isn't used in the site! :)
- If you are using ZSQL a lot, do you use caching? Even a cache expiry of 1 second would help a heavily hit site tremendously, noone will notice the data returned being at most 1 second out of date.But all hits with equal parameters on the same thread in Zope within that second need only one DB query.
I do use caching Martijn, but I must admit that it's been a hit and miss affair.
har har...
I'm trying to do as many of my SQL queries in external methods (ironically for the performance), so I may well be losing a lot of the caching performance breakthrough there .... hmmm. I wonder if I could do something like...
There really should be very, very little performance difference between calling ZSQL methods from DTML and from an external method. Have you notice an significant speedup do to this? If so, we should look at that... -Michel
At 3:54 pm -0800 28/2/00, Michel Pelletier wrote:
Aye carumba! - what a lot of information. I'm going to start delving around there and see what happens. Is there any additional documentation available on what the info. means?
Nope.
As an aside (to the list), looking around the ApplicationManager.py reveals something called manage_profile. It looks like the main Zope functions are being profiled here (ie not at the object level). It's interesting, but it does seem to be a resource hog...
Profiling is turned off by default. You're right though, when it's turned on it does slow down Zope considerably. But it shouldn't matter because that isn't documented either.
Thanks for the reply, I get it. "Here be Dragons", fair enough.
I do use caching Martijn, but I must admit that it's been a hit and miss affair.
har har...
I didn't realise what I was typing was a pun... I meant that I have caches set at 1000 rows, 1000 results and 600 seconds on the main ZSQL queries, but I don't have a way of testing to see if it's having any effect (see below),
I'm trying to do as many of my SQL queries in external methods (ironically for the performance), so I may well be losing a lot of the caching performance breakthrough there .... hmmm. I wonder if I could do something like...
There really should be very, very little performance difference between calling ZSQL methods from DTML and from an external method. Have you notice an significant speedup do to this? If so, we should look at that...
-Michel
My methods are more complex than simple ZSQL lookups. I need to do a tree traversal through a database to get the relevant items to display. So I need to do nested SQL queries.The first Zope attempt was ; <dtml-var standard_html_header> <dtml-if child><dtml-var show_html> <dtml-in expr="Actors.SQL.get_children(parent=child)"><dtml-var show_html> <dtml-in expr="Actors.SQL.get_children(parent=child)"><dtml-var show_html> <dtml-in expr="Actors.SQL.get_children(parent=child)"><dtml-var show_html> <dtml-in expr="Actors.SQL.get_children(parent=child)"><dtml-var show_html></dtml-in get_children L4></dtml-in get_children L3></dtml-in get_children L2></dtml-in get_children L1> <dtml-else> <form action='cascade2' method=post> child <input type=text name=child size=10> </form> </dtml-if> <dtml-var standard_html_footer> That is, it's a nested loop of ZSQL methods and show_html is an external method that displays HTML dependant on the contents of a DTML document in the ZODB (this is *so* cool - I can change the look and feel of 'elements' with triviality...) It took about 27 seconds to render a (120k) document. Using a full External Method solution, with calls to DBH.query (and a one-time lookup for all the DTML 'templates' in the ZODB) it took about 2 seconds (it's not completely debugged though! :). So, I'm *not* calling ZSQL methods from an External method, I'm using direct connections to the database using Python libraries. Obviously, I lose a lot of Zope goodies here, but I really need that speedup... Thanks for the thoughts, Tone ------ Dr Tony McDonald, FMCC, Networked Learning Environments Project http://nle.ncl.ac.uk/ The Medical School, Newcastle University Tel: +44 191 222 5888 Fingerprint: 3450 876D FA41 B926 D3DD F8C3 F2D0 C3B9 8B38 18A2
Tony McDonald wrote:
It's MySQL, but using the latest DA - I'm pretty sure I'm doing more than one query at a time as I set off a ZSQL query that takes 9 seconds from the MySQL command line, and then started to use aspects of the site that I *know* use MySQL lookups. BTW, the 9 second SQL query isn't used in the site! :)
Oh one more thing, I'm pretty certain that the MySQL DA serializes ALL requests because the MySQL library for python is not thread-safe. -Michel
tony wrote:
Hi all, One of my Zope sites is used by classes of students - up to 60 or so at a time. When they use the site the response time is really slow - they could be waiting minutes between clicks. The site uses ZSQL methods *extensively* and external methods. I realise that part of this problem is because of our Sun iron (2400 pystones - my *iMac* does 4400, and a PIII 500MHz does 5500), but as I can't get this site onto another box in the near future, I'm after other ideas.
Can anyone give me some pointers (even just rule of thumb) to improve the response time? I was going to set the threads to be something like 32, but I've read other posts suggesting that wouldn't help too much. Is there someway I can profile what's going on for example?
Have you times your SQL queries independent of Zope? If your queries take minutes then, of course, Zope will also take that much time. It is easy to write SQL that causes your databases to take a loooooong time to finish. 60 concurrent students is just not enough load in my opinion to bog Zope at all. -Michel
At 2:33 am -0800 27/2/00, Michel Pelletier wrote:
Have you times your SQL queries independent of Zope? If your queries take minutes then, of course, Zope will also take that much time. It is easy to write SQL that causes your databases to take a loooooong time to finish.
Absolutely aware of that Michel (through bitter experience). I'm going over some of the SQL queries now to try and get a handle on things, but I'm pretty sure they're ok.
60 concurrent students is just not enough load in my opinion to bog Zope at all.
Glad to hear that it's a figure that seems ok. I'm going to move more of the static material (images etc) to the Apache server that's acting as the backend to the Zope system. That is, along with the measures that Martijn and Brian mentioned too...
-Michel
Many thanks for the insights. tone. ------ Dr Tony McDonald, FMCC, Networked Learning Environments Project http://nle.ncl.ac.uk/ The Medical School, Newcastle University Tel: +44 191 222 5888 Fingerprint: 3450 876D FA41 B926 D3DD F8C3 F2D0 C3B9 8B38 18A2
Hi Tone, I just recently solved a performance problem that was related to ZODB lock contention due to a idiosyncrasy with an external method that was causing writes to ZODB on every page view, for my top page... it couldn't be anything like this, could it? One easy way to check is to look at the undo list, if there are lots of transactions by the Anonymous user on pages that don't look like they should be getting changed, that's a good heads up. The DB is another possible bottleneck - I have been using Sybase, which with the level 3 DA gives very good performance, I can have 20 or more simultaneous, persistent connections open to the DB... tuning the SQL, as Martijn mentioned, is also important - I profiled ZSQL methods once and for the stuff I tested, selects from the cache were over 100 times faster than direct access to the database. Hope you can solve your problem! --Brian On Sun, 27 Feb 2000 07:17:06 +0000 tony <tony.mcdonald@ncl.ac.uk> wrote:
Hi all, One of my Zope sites is used by classes of students - up to 60 or so at a time. When they use the site the response time is really slow - they could be waiting minutes between clicks. The site uses ZSQL methods *extensively* and external methods. I realise that part of this problem is because of our Sun iron (2400 pystones - my *iMac* does 4400, and a PIII 500MHz does 5500), but as I can't get this site onto another box in the near future, I'm after other ideas.
Can anyone give me some pointers (even just rule of thumb) to improve the response time? I was going to set the threads to be something like 32, but I've read other posts suggesting that wouldn't help too much. Is there someway I can profile what's going on for example?
Any thought gratefully received. tone
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
At 10:37 am +0900 28/2/00, Brian Takashi Hooper wrote:
Hi Tone,
I just recently solved a performance problem that was related to ZODB lock contention due to a idiosyncrasy with an external method that was causing writes to ZODB on every page view, for my top page... it couldn't be anything like this, could it? One easy way to check is to look at the undo list, if there are lots of transactions by the Anonymous user on pages that don't look like they should be getting changed, that's a good heads up.
That's a pretty neat thought - I checked my undo list however and there's no transactions from Anonymous there :(
The DB is another possible bottleneck - I have been using Sybase, which with the level 3 DA gives very good performance, I can have 20 or more simultaneous, persistent connections open to the DB... tuning the SQL, as Martijn mentioned, is also important - I profiled ZSQL methods once and for the stuff I tested, selects from the cache were over 100 times faster than direct access to the database.
For the time being we're using MySQL, as we're a bit strapped for cash. I'm mightily impressed by the cache performance over direct access to the database. However, a lot of my database calls are from external methods (as I figured they'd be faster), so I guess I'd lose a lot of that speedup. We do have a cache for the MySQL database, which (I've always thought) would help out. Is there a documented way of profiling ZSQL method perhaps?
Hope you can solve your problem!
I certainly hope so - I guess I'm a little disappointed in the performance from our 'Sun Iron' - particularly when a PIII 500MHz gave twice as many raw 'pystones' as our vastly more expensive Solaris box. :( thanks for the advice, tone, ------ Dr Tony McDonald, FMCC, Networked Learning Environments Project http://nle.ncl.ac.uk/ The Medical School, Newcastle University Tel: +44 191 222 5888 Fingerprint: 3450 876D FA41 B926 D3DD F8C3 F2D0 C3B9 8B38 18A2
participants (5)
-
Brian Takashi Hooper -
Martijn Pieters -
Michel Pelletier -
tony -
Tony McDonald