You'll find the docs for this in $ORACLE_HOME/rdbms/admin/spdoc.txt

Post the init.ora and the machine's parameters.

My economics of the situation: Getting a linux box running AOLserver should be around 1000 EUR plus 5 hours of work for setup and give you immediate gratification. Trying to tune OpenACS is most likely more costly *and* time consuming. The additional costs for system administration should be minimal but shouldn't be neglected, I agree.

We have made good experiences with the following setup:

• One server running a reverse proxy (we are using pound)
• One server with an aolserver instance (no openacs) serving "static images" (served from the file system not the content rep)
• One server with the openacs installation
• One db server (pg)
• The proxy uses the url to divide the requests between the image server and the openacs server

All servers are running linux rh8. We are currently using only one openacs server but of course that could be several machines as well. The proxy could do the load balancing. Unfortunately the more servers you have the more work you will have with handling failover problems :)

As far as openacs itself is concerned i think we (Vienna Univ of Business Admin) will have the same performance problems in a few weeks. I think a good point to start enhancing dotlrn performance is the portal system. For all community-portals (dotlrn_class, dotlrn_class_instance, dotlrn_club, dotlrn_department) but the user portal i have ripped out the real portal-system and created a "static" version where all portlet are called directly with given parameters (=unchangeable portal layout). Next important thing will be to enhance the real portal system and to cache portlet content.

At the moment it does not work with .LRN.  Also its caching is probably less effective than new-portal at the moment since I didn't address this problem while reorganizing and rewriting big chunks of it (mostly because, if folks approve my TIP, I'd prefer to use the caching db_* API rather than util_memoize to do the caching).

Mostly I find OF's use of ns_sets for passing stuff around annoying but beyond that haven't looked into what it would take to make specific portlets cache their content.  As I said above they really need to coordinate with caching versions of the underlying application if we're going to provide the user consistent views of application content.  That's not a short-term fix, obviously, and short-term we may need to kludge things optionally ...

Of course one can ease the pain by minimizing the number of portlets per portal page.  Ideal would be one, then you'd have the equivalent of application pages rather than portalled pages! :)  OK, I'm being silly, but perhaps this helps make clear that when it comes to content it is really the application's responsibility if we're to present consistent views of content?  Portal pages are bad performance-wise because rendering one's the equivalent of rendering index pages for several non-caching applications all at once.

Because the box has only 2 GB of RAM, and .LRN isn't the only thing running on it, there is over 1 GB of swap in use.  It appears that Oracle's SGA resides at least partly in swap (looking at iostat to see lots of swap activity while queries are run in sqlplus).  This, of course, just kills performance.

To make matters worse, most everything is installed out on the disk array, so all of the log files, both nsd and Oracle, are being written to over a single SCSI channel.

My recommendation is to first get some more RAM, at *least* bring it up to 4 GB, and then if possible split things up across multiple disks, either by moving the log files to an internal disk or attaching a second disk array.

I'm pretty sure things will be fine after that, but if not, we'll continue looking into it at that point.  I've tried doing a little query tuning but it's a lost cause right now;  nothing I do makes any difference.

Does the installation not scale adequately or not perform adequately?

And how many people are accessing .LRN during regular times and during peak times?

How big is the Oracle SGA?

What exactly doesn't scale or perform? The whole system, some pages?

What is eating up all the RAM?

Also shut down webct for a short test to see if makes a significant difference.

Can you also post your config.tcl without any sensitive data?

Also, can you post your authentication, kernel and main site parameter settings under /acs-admin/

How many authorities exists for your installation? Does it make a difference if you deactivate your URZ Heidelberg or Extern authority?

Have you ever tried postgresql instead. My installation with 22.000 users is quicker.

In particular we do not currently have PermissionCacheP turned on, but in that thread you reported having some trouble with it.  Since this is a production system I don't want to turn it on if users might encounter errors.  Is it is working cleanly now?

Request Completion Time: 
2004-04-08 17:59:41

Request Duration: 
2215 ms

IP: 
18.170.5.196

Method: 
GET

URL: 
/dotlrn/index

Query: 
(empty)


Request Processor

+49.4 ms: Applied transformation from /web/product/www / dotlrn/index -> ? - 7.5 ms
+63.3 ms: Served file /web/product/packages/dotlrn/www/index.adp with adp_parse_ad_conn_file - 2146.0 ms
+2211.2 ms: Applied GET filter: (for /dotlrn/index ds_trace_filter) - 10.2 ms
returned filter_ok


show RP debugging information


Comments

rp_handler: trying rp_serve_abstract_file /web/product/www / dotlrn/index
rp_handler: not found
rp_handler: trying rp_serve_abstract_file /web/product/packages/dotlrn/www / index


Headers


Host: 
athena2.uni-heidelberg.de

Accept: 
*/*

Accept-Language: 
en

Pragma: 
no-cache

Connection: 
Keep-Alive

Referer: 
http://athena2.uni-heidelberg.de/register/?blocale=en%5fUS&return%5furl=%2fdotlrn%2findex

User-Agent: 
Mozilla/4.0 (compatible; MSIE 5.23; Mac_PowerPC)

UA-OS: 
MacOS

UA-CPU: 
PPC

Cookie: 
ad_session_id=45199%2c110325%2c1%20%7b31%2010812456245179%20E459FB42B0DD8685A1F56F45645651CB19A532BA6BBDC8%7d; ad_user_login=110325%2c145636226%2cC4D30F674%20%7b531%201081468779%2071A84256603EB939973A0131D86AFB873DBA547E82B%7d

Extension: 
Security/Remote-Passphrase


Output Headers


Expires: 
Thu, 08 Apr 2004 15:59:41 GMT

Pragma: 
no-cache

Cache-Control: 
no-cache

Content-Type: 
text/html; charset=utf-8

MIME-Version: 
1.0

Date: 
Thu, 08 Apr 2004 15:59:41 GMT

Server: 
AOLserver/3.3.1+ad13

Content-Length: 
18207

Connection: 
close


Database Requests


  Duration  
  Pool  
Command

  1 ms  
  pool2  
gethandle (returned nsdb0)

  4 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  3 ms  
  pool2  
dbqd.dotlrn.tcl.dotlrn-security-procs.dotlrn::user_p.select_count: 0or1row nsdb0

            select count(*)
            from dual
            where exists (select 1
                          from dotlrn_users
                          where user_id = :user_id)



  4 ms  
  pool2  
dbqd.dotlrn.tcl.community-procs.dotlrn_community::get_all_communities_by_user.select_communities_by_user: select nsdb0

            select dotlrn_communities_full.*
            from dotlrn_communities_full,
                dotlrn_member_rels_approved
            where dotlrn_communities_full.community_id = dotlrn_member_rels_approved.community_id
            and dotlrn_member_rels_approved.user_id = :user_id



  1 ms  
  pool2  
getrow nsdb0


  6 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  3 ms  
  pool2  
dbqd.dotlrn.tcl.dotlrn-procs.dotlrn::get_portal_id_not_cached.select_user_portal_id: 0or1row nsdb0

            select portal_id
            from dotlrn_users
            where user_id = :user_id



  4 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  4 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  4 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::render.portal_select: 0or1row nsdb0

            select portals.name,
                  portals.portal_id,
                  portals.theme_id,
                  portal_layouts.layout_id,
                  portal_layouts.filename as layout_filename,
                  portal_pages.page_id
            from portals,
                portal_pages,
                portal_layouts
            where portal_pages.sort_key = :sort_key
            and portal_pages.portal_id = :portal_id
            and portal_pages.portal_id = portals.portal_id
            and portal_pages.layout_id = portal_layouts.layout_id



  3 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::render.element_select: select nsdb0

            select portal_element_map.element_id,
                  portal_element_map.region,
                  portal_element_map.sort_key
            from portal_element_map,
                portal_pages
            where portal_pages.portal_id = :portal_id
            and portal_element_map.page_id = :page_id
            and portal_element_map.page_id = portal_pages.page_id
            and portal_element_map.state != 'hidden'
            order by portal_element_map.region,
                    portal_element_map.sort_key



  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  9 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::evaluate_element.element_select: 0or1row nsdb0

            select pem.element_id,
                  pem.datasource_id,
                  pem.state,
                  pet.filename as filename,
                  pet.resource_dir as resource_dir,
                  pem.pretty_name as pretty_name,
                  pd.name as ds_name
            from portal_element_map pem,
                portal_element_themes pet,
                portal_datasources pd
            where pet.theme_id = :theme_id
            and pem.element_id = :element_id
            and pem.datasource_id = pd.datasource_id



  3 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::element_params_not_cached.params_select: select nsdb0

            select key,
                  value
            from portal_element_parameters
            where element_id = :element_id



  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  6 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  5 ms  
  pool2  
dbqd.dotlrn.www.dotlrn-main-portlet.select_communities: select nsdb0

            select dotlrn_communities_all.*,
                  dotlrn_community.url(dotlrn_communities_all.community_id) as url,
                  decode(dotlrn_communities_all.community_type, 'dotlrn_community', 'dotlrn_community',
                                                            'dotlrn_club', 'dotlrn_club',
                                                            'dotlrn_class_instance') as simple_community_type,
                  decode(dotlrn_community_admin_p(dotlrn_communities_all.community_id, dotlrn_member_rels_approved.user_id),'f',0,1) as admin_p,
                  tree.tree_level(dotlrn_communities_all.tree_sortkey) as tree_level,
                  nvl((select tree.tree_level(dotlrn_community_types.tree_sortkey)
                        from dotlrn_community_types
                        where dotlrn_community_types.community_type = dotlrn_communities_all.community_type), 0) as community_type_level
            from dotlrn_communities_all,
                dotlrn_member_rels_approved
            where dotlrn_communities_all.community_id = dotlrn_member_rels_approved.community_id
            and dotlrn_member_rels_approved.user_id = :user_id
            order by dotlrn_communities_all.tree_sortkey



  1 ms  
  pool2  
getrow nsdb0


  4 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::evaluate_element.element_select: 0or1row nsdb0

            select pem.element_id,
                  pem.datasource_id,
                  pem.state,
                  pet.filename as filename,
                  pet.resource_dir as resource_dir,
                  pem.pretty_name as pretty_name,
                  pd.name as ds_name
            from portal_element_map pem,
                portal_element_themes pet,
                portal_datasources pd
            where pet.theme_id = :theme_id
            and pem.element_id = :element_id
            and pem.datasource_id = pd.datasource_id



  3 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::element_params_not_cached.params_select: select nsdb0

            select key,
                  value
            from portal_element_parameters
            where element_id = :element_id



  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  6 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  4 ms  
  pool2  
dbqd.forums-portlet.www.forums-portlet.select_forums: select nsdb0

            select forums_forums.package_id,
            acs_object.name(apm_package.parent_id(forums_forums.package_id)) as parent_name,
            (select site_node.url(site_nodes.node_id)
            from site_nodes
            where site_nodes.object_id = forums_forums.package_id) as url,
            forums_forums.forum_id,
            forums_forums.name,
            case when last_modified > (sysdate - 1) then 't' else 'f' end as new_p
            from forums_forums_enabled forums_forums,
            acs_objects
            where acs_objects.object_id = forums_forums.forum_id and
            forums_forums.package_id in (0)
            order by parent_name,
            forums_forums.name



  1 ms  
  pool2  
getrow nsdb0


  4 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::evaluate_element.element_select: 0or1row nsdb0

            select pem.element_id,
                  pem.datasource_id,
                  pem.state,
                  pet.filename as filename,
                  pet.resource_dir as resource_dir,
                  pem.pretty_name as pretty_name,
                  pd.name as ds_name
            from portal_element_map pem,
                portal_element_themes pet,
                portal_datasources pd
            where pet.theme_id = :theme_id
            and pem.element_id = :element_id
            and pem.datasource_id = pd.datasource_id



  3 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::element_params_not_cached.params_select: select nsdb0

            select key,
                  value
            from portal_element_parameters
            where element_id = :element_id



  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  4 ms  
  pool2  
dbqd.faq-portlet.www.faq-portlet.select_faqs: select nsdb0

            select acs_objects.context_id as package_id,
                  acs_object.name(apm_package.parent_id(acs_objects.context_id)) as parent_name,
                  (select site_node.url(site_nodes.node_id)
                    from site_nodes
                    where site_nodes.object_id = acs_objects.context_id) as url,
                  faqs.faq_id,
                  faqs.faq_name
            from faqs,
                acs_objects
            where faqs.faq_id = acs_objects.object_id
            and faqs.disabled_p <> 't'
            and acs_objects.context_id in (0)
            order by lower(faq_name)



  1 ms  
  pool2  
getrow nsdb0


  4 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::evaluate_element.element_select: 0or1row nsdb0

            select pem.element_id,
                  pem.datasource_id,
                  pem.state,
                  pet.filename as filename,
                  pet.resource_dir as resource_dir,
                  pem.pretty_name as pretty_name,
                  pd.name as ds_name
            from portal_element_map pem,
                portal_element_themes pet,
                portal_datasources pd
            where pet.theme_id = :theme_id
            and pem.element_id = :element_id
            and pem.datasource_id = pd.datasource_id



  3 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::element_params_not_cached.params_select: select nsdb0

            select key,
                  value
            from portal_element_parameters
            where element_id = :element_id



  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  10 ms  
  pool2  
dbqd.news-portlet.www.news-portlet.select_news_items: select nsdb0

            select news_items_approved.package_id,
                  acs_object.name(apm_package.parent_id(news_items_approved.package_id)) as parent_name,
                  (select site_node.url(site_nodes.node_id)
                    from site_nodes
                    where site_nodes.object_id = news_items_approved.package_id) as url,
                  news_items_approved.item_id,
                  news_items_approved.publish_title,
                  to_char(news_items_approved.publish_date, 'YYYY-MM-DD HH24:MI:SS') as publish_date_ansi
            from news_items_approved
            where news_items_approved.publish_date < sysdate
            and (news_items_approved.archive_date >= sysdate or news_items_approved.archive_date is null)
            and news_items_approved.package_id in (0)
            order by parent_name,
                    news_items_approved.publish_date desc,
                    news_items_approved.publish_title




  2 ms  
  pool2  
getrow nsdb0


  5 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::evaluate_element.element_select: 0or1row nsdb0

            select pem.element_id,
                  pem.datasource_id,
                  pem.state,
                  pet.filename as filename,
                  pet.resource_dir as resource_dir,
                  pem.pretty_name as pretty_name,
                  pd.name as ds_name
            from portal_element_map pem,
                portal_element_themes pet,
                portal_datasources pd
            where pet.theme_id = :theme_id
            and pem.element_id = :element_id
            and pem.datasource_id = pd.datasource_id



  3 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::element_params_not_cached.params_select: select nsdb0

            select key,
                  value
            from portal_element_parameters
            where element_id = :element_id



  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  7 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  3 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  6 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  73 ms  
  pool2  
dbqd.calendar.www.view-one-day-display.select_day_items: select nsdb0

        select nvl(e.name, a.name) as name,
        nvl(e.status_summary, a.status_summary) as status_summary,
        e.event_id as item_id,
        (select type from cal_item_types where item_type_id= ci.item_type_id) as item_type,
        cals.calendar_id,
        cals.calendar_name
from    acs_activities a,
        acs_events e,
        timespans s,
        time_intervals t,
        cal_items ci,
        calendars cals
where    e.timespan_id = s.timespan_id
and      s.interval_id = t.interval_id
and      e.activity_id = a.activity_id
and      start_date between
        to_date(:current_date_system,:ansi_date_format) and
        (to_date(:current_date_system,:ansi_date_format) + (24 - 1/3600)/24)
and      ci.cal_item_id = e.event_id
and      to_char(start_date, 'HH24:MI') = '00:00'
and      to_char(end_date, 'HH24:MI') = '00:00'
and      cals.calendar_id = ci.on_which_calendar
and      e.event_id = ci.cal_item_id
and on_which_calendar in (110394) and (cals.private_p='f' or (cals.private_p='t' and cals.owner_id= :user_id))



  1 ms  
  pool2  
getrow nsdb0


  205 ms  
  pool2  
dbqd.calendar.www.view-one-day-display.select_day_items_with_time: select nsdb0

        select to_char(start_date, :ansi_date_format) as ansi_start_date,
        to_char(end_date, :ansi_date_format) as ansi_end_date,
        nvl(e.name, a.name) as name,
        nvl(e.status_summary, a.status_summary) as status_summary,
        e.event_id as item_id,
        (select type from cal_item_types where item_type_id= ci.item_type_id) as item_type,
        cals.calendar_id,
        cals.calendar_name
from    acs_activities a,
        acs_events e,
        timespans s,
        time_intervals t,
        cal_items ci,
        calendars cals
where    e.timespan_id = s.timespan_id
and      s.interval_id = t.interval_id
and      e.activity_id = a.activity_id
and      start_date between
        to_date(:current_date_system,:ansi_date_format) and
        (to_date(:current_date_system,:ansi_date_format) + (:end_display_hour - 1/3600)/:end_display_hour)
and      ci.cal_item_id = e.event_id
and      (to_char(start_date, 'HH24:MI') <> '00:00' or
          to_char(end_date, 'HH24:MI') <> '00:00')
and      cals.calendar_id = ci.on_which_calendar
and      e.event_id = ci.cal_item_id
and on_which_calendar in (110394) and (cals.private_p='f' or (cals.private_p='t' and cals.owner_id= :user_id))
order by to_char(start_date,'HH24')



  1 ms  
  pool2  
getrow nsdb0


  3 ms  
  pool2  
dbqd.acs-lang.tcl.locale-procs.lang::user::timezone_no_cache.select_user_timezone: 0or1row nsdb0

        select timezone
        from  user_preferences
        where  user_id = :user_id



  4 ms  
  pool2  
dbqd.calendar.www.view-one-day-display.select_day_info: 0or1row nsdb0

select  to_char(to_date(:current_date, 'yyyy-mm-dd'), 'Day, DD Month YYYY')
as day_of_the_week,
to_char((to_date(:current_date, 'yyyy-mm-dd') - 1), 'yyyy-mm-dd')
as yesterday,
to_char((to_date(:current_date, 'yyyy-mm-dd') + 1), 'yyyy-mm-dd')
as tomorrow
from    dual



  4 ms  
  pool2  
dbqd.dotlrn.tcl.dotlrn-security-procs.dotlrn::user_p.select_count: 0or1row nsdb0

            select count(*)
            from dual
            where exists (select 1
                          from dotlrn_users
                          where user_id = :user_id)



  3 ms  
  pool2  
dbqd.dotlrn.tcl.dotlrn-security-procs.dotlrn::user_p.select_count: 0or1row nsdb0

            select count(*)
            from dual
            where exists (select 1
                          from dotlrn_users
                          where user_id = :user_id)



  3 ms  
  pool2  
dbqd.new-portal.tcl.portal-procs.portal::navbar.list_page_nums_select: select nsdb0

            select pretty_name,
                  sort_key as page_num
            from portal_pages
            where portal_id = :portal_id
            order by sort_key



  1 ms  
  pool2  
getrow nsdb0


  130 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
getrow nsdb0


  53 ms  
  pool2  
dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

            select 1
            from dual
            where 't' = acs_permission.permission_p(:object_id, :party_id, :privilege)



  5 ms  
  pool2  
dbqd.curriculum.tcl.misc-procs.curriculum::enabled_elements.element_ns_set_list: select nsdb0


            select  cee.element_id,
                    cc.curriculum_id,
                    cc.name as curriculum_name,
                    cee.url,
                    cee.external_p,
                    cee.name
            from    (select  curriculum_id
                    from    cu_curriculums
                    where    package_id = :package_id
                    MINUS
                    select  curriculum_id
                    from    cu_user_curriculum_map
                    where    user_id = :user_id
                    and      package_id = :package_id) desired,
                    workflow_cases cas,
                    workflow_case_fsm cfsm,
                    cu_curriculums cc,
                    cu_elements_enabled cee
            where    cc.package_id = :package_id
            and      desired.curriculum_id = cc.curriculum_id
            and      cc.curriculum_id = cee.curriculum_id
            and      cas.object_id = cc.curriculum_id
            and      cfsm.case_id = cas.case_id
            and      cfsm.current_state = :state_id
            order by cc.sort_key,
                    cee.sort_key




  1 ms  
  pool2  
getrow nsdb0


  1 ms  
  pool2  
releasehandle nsdb0

  704 ms  
(total)



Developer Information

3 database commands totalling 19 ms

page served in  203 ms
dotlrn@uni-hd.de


-- Does the above make a significant difference if webct is shut down?

No, Carl tried this a few days ago and saw no difference at all.


-- How are the config.tcl settings?


ns_log notice "nsd.tcl: starting to read config file..."

######################################################################
#
# Instance-specific settings
# These default settings will only work in limited circumstances
# Two servers with default settings cannot run on the same host
#
######################################################################

#---------------------------------------------------------------------
# change to 80 and 443 for production use
set httpport                  80
set httpsport                443

# The hostname and address should be set to actual values.
#set hostname                  [ns_info hostname]
set hostname                  athena2.uni-heidelberg.de

#set address                  [ns_info address]
set address                  129.206.100.143

set server                    "product"
set servername                "Athena - dotLRN UNI HD"

set serverroot                "/web/${server}"

#---------------------------------------------------------------------
# which database do you want? postgres or oracle
set database              oracle

set db_name              $server

if { $database == "oracle" } {
    set db_password          "itsgonenow"
} else {
    set db_host              localhost
    set db_port              ""
    set db_user              $server
}

#---------------------------------------------------------------------
# if debug is false, all debugging will be turned off
set debug false

set homedir                  /usr/local/aolserver
set bindir                    [file dirname [ns_info nsd]]

#---------------------------------------------------------------------
# which modules should be loaded?  Missing modules break the server, so
# don't uncomment modules unless they have been installed.

ns_section ns/server/${server}/modules
ns_param  nssock            ${bindir}/nssock.so
ns_param  nslog              ${bindir}/nslog.so
ns_param  nssha1            ${bindir}/nssha1.so
ns_param  nscache            ${bindir}/nscache.so
ns_param  nsrewrite          ${bindir}/nsrewrite.so

#---------------------------------------------------------------------
# nsopenssl will fail unless the cert files are present as specified
# later in this file, so it's disabled by default
ns_param  nsopenssl          ${bindir}/nsopenssl.so

# Full Text Search
#ns_param  nsfts              ${bindir}/nsfts.so

# PAM authentication
ns_param  nspam              ${bindir}/nspam.so

# LDAP authentication
#ns_param  nsldap            ${bindir}/nsldap.so

# These modules aren't used in standard OpenACS installs
#ns_param  nsperm            ${bindir}/nsperm.so
#ns_param  nscgi              ${bindir}/nscgi.so
#ns_param  nsjava            ${bindir}/libnsjava.so

if { [ns_info version] >= 4 } {
    # Required for AOLserver 4.x
    ns_param  nsdb              ${bindir}/nsdb.so
} else {
    # Required for AOLserver 3.x
    ns_param  libtdom            ${bindir}/libtdom.so
}

#---------------------------------------------------------------------
#
# Rollout email support
#
# These procs help manage differing email behavior on
# dev/staging/production.
#
#---------------------------------------------------------------------

ns_section ns/server/${server}/acs/acs-rollout-support

# EmailDeliveryMode can be:
#  default:  Email messages are sent in the usual manner.
#  log:      Email messages are written to the server's error log.
#  redirect: Email messages are redirected to the addresses specified
#            by the EmailRedirectTo parameter.  If this list is absent
#            or empty, email messages are written to the server's error log.
#  filter:  Email messages are sent to in the usual manner if the
#            recipient appears in the EmailAllow parameter, otherwise they
#            are logged.

#ns_param  EmailDeliveryMode redirect
#ns_param  EmailRedirectTo    somenerd@yourdomain.test, othernerd@yourdomain.tes
t
#ns_param  EmailAllow        somenerd@yourdomain.test,othernerd@yourdomain.test

######################################################################
#
# End of instance-specific settings
#
# Nothing below this point need be changed in a default install.
#
######################################################################


#---------------------------------------------------------------------
#
# AOLserver's directories. Autoconfigurable.
#
#---------------------------------------------------------------------

#---------------------------------------------------------------------
# Where are your pages going to live ?
#
set pageroot                  ${serverroot}/www
set directoryfile            index.tcl,index.adp,index.html,index.htm


#---------------------------------------------------------------------
# Global server parameters
#---------------------------------------------------------------------

ns_section ns/parameters
ns_param  serverlog          ${serverroot}/log/error.log
ns_param  home              $homedir
ns_param  maxkeepalive      0
ns_param  logroll            on
ns_param  maxbackup          5
ns_param  debug              $debug

ns_param  HackContentType    1
ns_param  URLCharset        utf-8
ns_param  OutputCharset      utf-8
ns_param  HttpOpenCharset    utf-8
ns_param  DefaultCharset    utf-8

#---------------------------------------------------------------------
# Thread library (nsthread) parameters
#---------------------------------------------------------------------

ns_section ns/threads
ns_param  mutexmeter        true      ;# measure lock contention
# The per-thread stack size must be a multiple of 8k for AOLServer to run under M
acOS X
ns_param  stacksize          [expr 128 * 8192]

#
# MIME types.
#
#  Note: AOLserver already has an exhaustive list of MIME types, but in
#  case something is missing you can add it here.
#

ns_section ns/mimetypes
ns_param  Default            text/plain
ns_param  NoExtension        text/plain
ns_param  .pcd              image/x-photo-cd
ns_param  .prc              application/x-pilot
ns_param  .xls              application/vnd.ms-excel
ns_param  .doc              application/vnd.ms-word

#
# Tcl Configuration
#
ns_section ns/server/${server}/tcl
ns_param  library            ${serverroot}/tcl
ns_param  autoclose          on
ns_param  debug              $debug


#---------------------------------------------------------------------
#
# Server-level configuration
#
#  There is only one server in AOLserver, but this is helpful when multiple
#  servers share the same configuration file.  This file assumes that only
#  one server is in use so it is set at the top in the "server" Tcl variable
#  Other host-specific values are set up above as Tcl variables, too.
#
#---------------------------------------------------------------------

ns_section ns/servers
ns_param  $server            $servername

#
# Server parameters
#
ns_section ns/server/${server}
ns_param  directoryfile      $directoryfile
ns_param  pageroot          $pageroot
ns_param  maxconnections    5
ns_param  maxdropped        0
ns_param  maxthreads        5
ns_param  minthreads        5
ns_param  threadtimeout      120
ns_param  globalstats        false    ;# Enable built-in statistics
ns_param  urlstats          false    ;# Enable URL statistics
ns_param  maxurlstats        1000    ;# Max number of URL's to do stats on
#ns_param  directoryadp      $pageroot/dirlist.adp ;# Choose one or the other
#ns_param  directoryproc      _ns_dirlist          ;#  ...but not both!
#ns_param  directorylisting  fancy              ;# Can be simple or fancy

#
# Special HTTP pages
#

ns_param  NotFoundResponse  "/global/file-not-found.html"
ns_param  ServerBusyResponse "/global/busy.html"
ns_param  ServerInternalErrorResponse "/global/error.html"

#---------------------------------------------------------------------
#
# ADP (AOLserver Dynamic Page) configuration
#
#---------------------------------------------------------------------

ns_section ns/server/${server}/adp
ns_param  map                /*.adp    ;# Extensions to parse as ADP's
#ns_param  map                "/*.html" ;# Any extension can be mapped
ns_param  enableexpire      false    ;# Set "Expires: now" on all ADP's
ns_param  enabledebug        $debug    ;# Allow Tclpro debugging with "?debug"
ns_param  defaultparser      fancy

ns_section ns/server/${server}/adp/parsers
ns_param  fancy    ".adp"

#---------------------------------------------------------------------
#
# Socket driver module (HTTP)  -- nssock
#
#---------------------------------------------------------------------

ns_section ns/server/${server}/module/nssock
ns_param  timeout            120
ns_param  address            $address
ns_param  hostname          $hostname
ns_param  port              $httpport

#---------------------------------------------------------------------
#
# OpenSSL
#
#---------------------------------------------------------------------

ns_section "ns/server/${server}/module/nsopenssl"

ns_param ModuleDir            ${serverroot}/etc/certs

# NSD-driven connections:
ns_param ServerPort                $httpsport
ns_param ServerHostname            $hostname
ns_param ServerAddress            $address
ns_param ServerCertFile            certfile.pem
#ns_param ServerCertFile            athena2.pem
ns_param ServerKeyFile            keyfile.pem
ns_param ServerProtocols          "SSLv2, SSLv3, TLSv1"
ns_param ServerCipherSuite        "ALL:!ADH:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+E
XP"
ns_param ServerSessionCache        false
ns_param ServerSessionCacheID      1
ns_param ServerSessionCacheSize    512
ns_param ServerSessionCacheTimeout 300
#ns_param ServerPeerVerify          true
ns_param ServerPeerVerify          false
ns_param ServerPeerVerifyDepth    3
ns_param ServerCADir              ca
ns_param ServerCAFile              ca.pem
ns_param ServerTrace              false

# For listening and accepting SSL connections via Tcl/C API:
ns_param SockServerCertFile              certfile.pem
#ns_param SockServerCertFile              athena2.pem
ns_param SockServerKeyFile              keyfile.pem
ns_param SockServerProtocols            "SSLv2, SSLv3, TLSv1"
ns_param SockServerCipherSuite          "ALL:!ADH:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SS
Lv2:+EXP"
ns_param SockServerSessionCache          false
ns_param SockServerSessionCacheID        2
ns_param SockServerSessionCacheSize      512
ns_param SockServerSessionCacheTimeout  300
#ns_param SockServerPeerVerify            true
ns_param SockServerPeerVerify            false
ns_param SockServerPeerVerifyDepth      3
ns_param SockServerCADir                internal_ca
ns_param SockServerCAFile                internal_ca.pem
ns_param SockServerTrace                false

# Outgoing SSL connections
ns_param SockClientCertFile              certfile.pem
#ns_param SockClientCertFile              athena2.pem
ns_param SockClientKeyFile              keyfile.pem
ns_param SockClientProtocols            "SSLv2, SSLv3, TLSv1"
ns_param SockClientCipherSuite          "ALL:!ADH:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SS
Lv2:+EXP"
ns_param SockClientSessionCache          false
ns_param SockClientSessionCacheID        3
ns_param SockClientSessionCacheSize      512
ns_param SockClientSessionCacheTimeout  300
ns_param SockClientPeerVerify            true
ns_param SockClientPeerVerify            false
ns_param SockServerPeerVerifyDepth      3
ns_param SockClientCADir                ca
ns_param SockClientCAFile                ca.pem
ns_param SockClientTrace                false

# OpenSSL library support:
#ns_param RandomFile          /some/file
ns_param SeedBytes            1024


#---------------------------------------------------------------------
#
# Database drivers
# The database driver is specified here.
# Make sure you have the driver compiled and put it in {aolserverdir}/bin
#
#---------------------------------------------------------------------

ns_section "ns/db/drivers"
if { $database == "oracle" } {
    ns_param  ora8          ${bindir}/ora8.so
} else {
    ns_param  postgres      ${bindir}/nspostgres.so  ;# Load PostgreSQL driver
}

#
# Database Pools: This is how AOLserver  ``talks'' to the RDBMS. You need
# three for OpenACS: main, log, subquery. Make sure to replace ``yourdb''
# and ``yourpassword'' with the actual values for your db name and the
# password for it, if needed.

# AOLserver can have different pools connecting to different databases
# and even different different database servers.
#
ns_section ns/db/pools
ns_param  pool1              "Pool 1"
ns_param  pool2              "Pool 2"
ns_param  pool3              "Pool 3"

ns_section ns/db/pool/pool1
ns_param  maxidle            1000000000
ns_param  maxopen            1000000000
ns_param  connections        5
ns_param  verbose            $debug
ns_param  extendedtableinfo  true
ns_param  logsqlerrors      $debug
if { $database == "oracle" } {
    ns_param  driver            ora8
    ns_param  datasource        {}
    ns_param  user              $db_name
    ns_param  password          $db_password
} else {
    ns_param  driver            postgres
    ns_param  datasource        ${db_host}:${db_port}:${db_name}
    ns_param  user              $db_user
    ns_param  password          ""
}

ns_section ns/db/pool/pool2
ns_param  maxidle            1000000000
ns_param  maxopen            1000000000
ns_param  connections        5
ns_param  verbose            $debug
ns_param  extendedtableinfo  true
ns_param  logsqlerrors      $debug
if { $database == "oracle" } {
    ns_param  driver            ora8
    ns_param  datasource        {}
    ns_param  user              $db_name
    ns_param  password          $db_password
} else {
    ns_param  driver            postgres
    ns_param  datasource        ${db_host}:${db_port}:${db_name}
    ns_param  user              $db_user
    ns_param  password          ""
}

ns_section ns/db/pool/pool3
ns_param  maxidle            1000000000
ns_param  maxopen            1000000000
ns_param  connections        5
ns_param  verbose            $debug
ns_param  extendedtableinfo  true
ns_param  logsqlerrors      $debug
if { $database == "oracle" } {
    ns_param  driver            ora8
    ns_param  datasource        {}
    ns_param  user              $db_name
    ns_param  password          $db_password
} else {
    ns_param  driver            postgres
    ns_param  datasource        ${db_host}:${db_port}:${db_name}
    ns_param  user              $db_user
    ns_param  password          ""
}

ns_section ns/server/${server}/db
ns_param  pools              "*"
ns_param  defaultpool        pool1

ns_section ns/server/${server}/redirects
ns_param  404                "global/file-not-found.html"
ns_param  403                "global/forbidden.html"


#---------------------------------------------------------------------
#
# Access log -- nslog
#
#---------------------------------------------------------------------

ns_section ns/server/${server}/module/nslog
ns_param  debug              false
ns_param  dev                false
ns_param  enablehostnamelookup false
ns_param  file              ${serverroot}/log/${server}.log
ns_param  logcombined        true
ns_param  extendedheaders    COOKIE
#ns_param  logrefer          false
#ns_param  loguseragent      false
ns_param  maxbackup          1000
ns_param  rollday            *
ns_param  rollfmt            %Y-%m-%d-%H:%M
ns_param  rollhour          0
ns_param  rollonsignal      true
ns_param  rolllog            true

#---------------------------------------------------------------------
#
# nsjava - aolserver module that embeds a java virtual machine.  Needed to
#          support webmail.  See http://nsjava.sourceforge.net for further
#          details. This may need to be updated for OpenACS4 webmail
#
#---------------------------------------------------------------------

ns_section ns/server/${server}/module/nsjava
ns_param  enablejava        off  ;# Set to on to enable nsjava.
ns_param  verbosejvm        off  ;# Same as command line -debug.
ns_param  loglevel          Notice
ns_param  destroyjvm        off  ;# Destroy jvm on shutdown.
ns_param  disablejitcompiler off
ns_param  classpath          /usr/local/jdk/jdk118_v1/lib/classes.zip:${bindir}/
nsjava.jar:${pageroot}/webmail/java/activation.jar:${pageroot}/webmail/java/mail.
jar:${pageroot}/webmail/java

#---------------------------------------------------------------------
#
# CGI interface -- nscgi, if you have legacy stuff. Tcl or ADP files inside
# AOLserver are vastly superior to CGIs. I haven't tested these params but they
# should be right.
#
#---------------------------------------------------------------------

#ns_section "ns/server/${server}/module/nscgi"
#      ns_param  map "GET  /cgi-bin/ /web/$server/cgi-bin"
#      ns_param  map "POST /cgi-bin/ /web/$server/cgi-bin"
#      ns_param  Interps CGIinterps

#ns_section "ns/interps/CGIinterps"
#      ns_param .pl "/usr/bin/perl"


#---------------------------------------------------------------------
#
# PAM authentication
#
#---------------------------------------------------------------------

ns_section ns/server/${server}/module/nspam
ns_param  PamDomain          "aolserver"

ns_log notice "nsd.tcl: finished reading config file."


-- What are the authentication, kernel and main site parameter settings under /acs-admin/?

Are there particular values you're interested in?  I tried to copy/paste the pages but the values in the edit boxes don't copy.  I won't type them all up unless you really want to see them all...

How many authorities exists for your installation? Does it make a difference if you deactivate your URZ Heidelberg or Extern authority?

I haven't tried this;  I don't know much about external authentication and it sounds like something I probably can't do during the day.  But since virtually every page in the site is slow, this is an unlikely culprit, isn't it?

1. sample request info:
It says 2215 ms for request duration, 2146.0 ms for the /dotlrn/www/index.adp page with a total of 704 ms for database stuff.

Mannheim:
49 database commands totalling 677 ms
page served in 1091 ms

Question: The database stuff seems equivalent. So where is the 1sec loss in Heidelberg?

2. config.tcl

Should be the problem since as far as I know at present you don't have many active connections anyway:

Heidelberg:
ns_param  maxconnections    5
ns_param  maxdropped        0
ns_param  maxthreads        5
ns_param  minthreads        5

Mannheim:
ns_param  maxconnections    100
ns_param  maxdropped        0
ns_param  maxthreads        50
ns_param  minthreads        50
ns_param  threadtimeout      3600

Database settings:

Heidelberg (3 x)
ns_param  connections        5

Mannheim (3 x)
ns_param  connections        10

3. authentication, kernel and main site parameter settings
I don't know yet. Just wanted to compare all of them. Can you save the html pages and send them to me? I will convert them and post it for you (leaving away the heidelberg info stuff) - if you like.

4. authorities
I had performance changes with multiple authorities enabled. Just give it a try.

It contains not quite four hours of data so it's not exactly a definitive report, but it's a start.

I only looked it over briefly and didn't make it all the way through (I have a meeting to go to) but the only thing that jumped out at me is that the memory usage in the shared pool is very low. It was 39% when I checked it last week so I cut down the shared pool almost in half, and it has only made it up to 40%. Obviously some more trimming could be done here (80% utilization is ideal) but we're only talking about roughtly 130 MB at this point so it's not enough to make a huge difference.

I'm not paying a huge amount of attention to the lists of slow SQL because the application is very nearly uniformly slow. We know that performance of .LRN sites is not always this bad, so I think we should be looking for more global problems and not getting bogged down in tuning individual queries (yet).

If anyone spots anything I missed or has a different interpretation, please let me know!

I cut down the size of the Oracle SGA radically, from roughly 930 MB to about 46 MB. I did this with no regard at all for formulas; I just grabbed the numbers off of one of my Linux boxes, which runs a fairly busy Oracle site.

This is not an entirly fair test, because Solaris systems don't fully recover from having gone into swap without a reboot. But I did improve the memory situation; after nsd has been running a while things look like this:

Memory: 2048M real, 1041M free, 730M swap in use, 4855M swap free

There's still too much swap in use for my taste, but as I said, that's not going away without rebooting the system.

The good news, which is also the bad news, is that this did not change the site performance one iota. It's no worse than it was, but it's no faster either. So we just reclaimed a bunch of space (though perhaps a bit too drastically) but it didn't help either. Keeping in mind that a reboot still might halp us out, it looked like time to move on to other ideas.

I still think that this is a system or database problem, not a site problem, simply because the performance is so uniformly bad. So instead of profilng the application, I took a known slow query (from /dotlrn/admin/users) and ran it in sqlplus, while running a variation on iostat at the same time. I got these results (edited to remove data we don't care about):

athena:/> iostat -xMne 1 60
                            extended device statistics       ---- errors --- 
    r/s    w/s   Mr/s   Mw/s wait actv wsvc_t asvc_t  %w  %b s/w h/w trn tot device
    1.4    2.0    0.1    0.0  0.2  0.1   56.5   19.4   0   2  40   0   0  40 c3t0d0
    0.0    3.0    0.0    0.0  0.0  0.0    0.0    7.8   0   1  40   0   0  40 c3t0d0
    0.0   26.0    0.0    0.2  0.0  0.3    0.0   10.2   0  26  40   0   0  40 c3t0d0
    0.0  107.0    0.0    0.8  0.0  1.0    0.0    9.2   0  98  40   0   0  40 c3t0d0
    0.0  125.0    0.0    1.0  0.0  1.0    0.0    7.7   0  97  40   0   0  40 c3t0d0
    0.0  144.0    0.0    1.1  0.0  1.0    0.0    6.9   0  95  40   0   0  40 c3t0d0
    0.0  139.0    0.0    1.1  0.0  0.9    0.0    6.4   0  90  40   0   0  40 c3t0d0
    0.0  141.0    0.0    1.1  0.0  1.0    0.0    6.7   0  95  40   0   0  40 c3t0d0
    0.0  134.0    0.0    1.0  0.0  1.0    0.0    7.2   0  92  40   0   0  40 c3t0d0
    0.0  149.0    0.0    1.2  0.0  1.0    0.0    6.5   0  97  40   0   0  40 c3t0d0
    0.0  144.0    0.0    1.1  0.0  1.0    0.0    6.8   0  97  40   0   0  40 c3t0d0
    0.0  140.0    0.0    1.1  0.0  1.0    0.0    7.4   0  96  40   0   0  40 c3t0d0
    0.0  147.0    0.0    1.1  0.0  1.0    0.0    6.6   0  97  40   0   0  40 c3t0d0
    0.0  156.0    0.0    1.2  0.0  1.0    0.0    6.2   0  97  40   0   0  40 c3t0d0
    0.0  136.0    0.0    1.1  0.0  1.0    0.0    7.3   0  96  40   0   0  40 c3t0d0
    0.0  108.0    0.0    0.8  0.0  1.0    0.0    9.1   0  98  40   0   0  40 c3t0d0
    0.0   92.0    0.0    0.7  0.0  0.9    0.0    9.4   0  87  40   0   0  40 c3t0d0
    0.0   45.0    0.0    0.4  0.0  0.6    0.0   14.4   0  37  40   0   0  40 c3t0d0
    0.0  108.0    0.0    0.8  0.0  1.0    0.0    9.1   0  98  40   0   0  40 c3t0d0
    0.0  112.0    0.0    0.9  0.0  1.0    0.0    8.7   0  98  40   0   0  40 c3t0d0
    0.0  106.0    0.0    0.8  0.0  1.0    0.0    9.7   0  98  40   0   0  40 c3t0d0
    0.0  108.0    0.0    0.8  0.0  1.0    0.0    8.9   0  97  40   0   0  40 c3t0d0
    0.0  109.0    0.0    0.9  0.0  1.0    0.0    9.0   0  98  40   0   0  40 c3t0d0
    0.0  111.0    0.0    0.9  0.0  1.0    0.0    9.3   0  98  40   0   0  40 c3t0d0
    0.0   44.0    0.0    0.3  0.0  0.4    0.0    8.8   0  39  40   0   0  40 c3t0d0
    0.0    0.0    0.0    0.0  0.0  0.0    0.0    0.0   0   0  40   0   0  40 c3t0d0
    0.0    3.0    0.0    0.0  0.0  0.0    0.0   12.3   0   2  40   0   0  40 c3t0d0
    0.0    0.0    0.0    0.0  0.0  0.0    0.0    0.0   0   0  40   0   0  40 c3t0d0

There are a few iinteresting things to note here.

One is that there is basically no data being read from the array (r/s). This is good, becuase it means that all the data used for this query came from memory (we hope it didn't come from swap :).

Another is that a fair number of disk writes are happening (w/s). This is because there are a lot of log files being written to - redo, rollback, archive, trace files, web server logs... and they are all on this one RAID array.

The last interesting thing to note is that there are 40 software errors (s/w) being reported by the disk array. That's 40 total, probably since the last reboot, which is not a whole lot but it's 40 more than there should be. This is probably not important, but it might hint at a problem with one of the disks in the array.

The next thing to try here would be to start moving log and data files that are written to frequently to the internal disk, except it doesn't have a whole lot of room and I'm not sure I want to start doing that to a production system if I don't have to.... I'm going to see if we can get this recreated on another system, without the disk array, and see what happens.

I'm also going to give Oracle some of it's SGA back; a very short statspack snapshot shows we're now using 95% of the shared pool, which is now too high.

The saga continues....

Initially -- not knowing how the array is configured or it's state -- I thought that slow disk writes and contention might be contributing to the problem. RAID 5 arrays are slow on writes and degraded arrays (those with  failed or missing drives) are too, but neither look to be a factor here.

It's a perplexing problem -- we're running several busy Oracle sites, one with a database in excess of 10GB and over 15 million hits per month on less hardware with much better performance.

It does indeed look like this has to do this particular setup and has nothing to do with .LRN/OpenACS. We just did a comparison on a MUCH SMALLER Sun box (with cob webs and all) and .LRN was faster than what we are experiencing now.

We are moving forward and will report when we find out the exact problem for posterity.

Carl

P.S. Mike wrote, "we're running several busy Oracle sites, one with a database in excess of 10GB and over 15 million hits per month on less hardware" and I am sure we can do the same thing with a .LRN site, it is just a question of some of the .LRN users cooperating on making it happen with gradual improvements over time.

my feeling - after installing OpenACS and dotLRN more than a dozen time is that the problem is from OpenACS itself.

So one suggestion! Just to make sure that I am wrong! Please install on the same box a clean instance of OpenACS on another port using the same files on the machine but a new database service1 without any users batch synch'ed.

If I am right this instance will run very fast as lightning. And if it does there was a misconfiguration with the OpenACS params. Soplease post your kernel, main site and authorities params for a quick check (maybe you can simply make screenshots to save you time).

Greetings,
Nima

As Al mentions, Janine's wearing her MIT, not Furfly, hat on this one and presumably Mike took a look and chimed in as a personal favor.

This is very curious.  If it takes more than two weeks to solve a bunch of us will probably spend our nights and mornings huddled around the box trying to figure out what's going on (since we'll be in Heidelberg).

Janine, please turn on timed_statistics (need to bounce the instance, about 1% overhead) and don't worry about frequent snapshots. Each snapshots takes about a blocks to be written - this just pales in light of the expected load. If you put perfstat into its own tablespace you could measure it and you would NEVER worry about this again.

With timed_statistics=on all the empty columns will be filled with values and we will see on which wait event Oracle is losing time.

If you think it is the RAID or the Oracle instance which are slacking do this: create a a few files (large, mid-sized, small ones) and copy them around from a script. Measure the performance. Do the same with a few tables and a few access paths (table scan, index access, small commits) and measure the performance. What is the result?

A question to the Heidelberg people: is this the same machine that serves the WebCT production system? Were/Are you happy with its performance?

Don't ignore the SQL results!

Look how much acs-service-contracts queries are there. It almost looks like a denial-of-service attack: one query is executed 84.000 in about 4 hours. Another one just looks whether there is one service contract - probably just to be able to gracefully tell the user "service-contract foo$$%$%"bar doesn't exist.

What about aggressive caching for service contracts (and replacing it for the next release - I don't like the package anyway because I think it is a complexifying replacement for tcl namespaces that is ALSO expensive)

What is this query about: select dotlrn_communities_all.*,
dotlrn_community.url(... ? It is *extremely* expensive.

The next query in the ordered by gets is also extremely expensive: what does it do?

Number  is probably using cc_users.

Can you at least try to cache the service-contracts and then take snapshots during the day when there is at least some activity on the system?

Yes, to be honest, with "exponential" I went really over the top.
This kind of behavior was observed, when we ran AOLServer3.3 on Sun Solaris. There we often had, regardless of special pages/nodes, statistics like less than 800ms for db queries, but more than 10000 ms total time for request processor.
In avoidance of any severity expression :), I noticed following performace facts:
- Requesting pages not belonging to or at least not portal-rendered by dotLRN are running faster, i.e. db query time and request processor delivery time are getting very close to each other.
This make sense, because rendering portals require "some" extra steps to be done.
- Especially the memberportlets of dotLRN-(sub-)groups have some weird statistic values (ok, about 200 members in this example):
  25 database commands totalling 607 ms
  page served in 12360 ms

Although statistics for subcomm's member administration page, which additionally contains super comm's users not yet included in subcommunity, shows statistics like:
  20 database commands totalling 5333 ms
  page served in 9805 ms

I wonder, if this behavior may be caused by nested loops, so it would be no problem of templating system itself (Maybe, should do a diff to dotLRN 2.1-queries... upgrading to 2.1 soon :).


Regarding templating system, I made a simple performance comparison performance by just displaying some information for a set of dotLRN users. For the first check I used ns_write output and for the second one templating system with multirow. Results (manually measured by clock):
- 2500 Users:
a) ns_write: 7 seconds total (including db query)
b) template: 10 seconds total
- 5000 Users:
a) ns_write: 20 seconds
b) template: 30 seconds
- 7500 Users:
a) ns_write: 42 seconds
b) template: 62 seconds

Maybe, I have to consider that some extra seconds are caused, because templating system first completely builds html result before sending it back to the browser, so ns_write has a little head start.

Don, you mentioned space set up for ns_cache. Do you mean Kernelparameter Memoize-MaxSize? It was 200000 and I set it to 300000 not knowing if this is an reasonable value.
ns_cache stats says:
  Cache Max Current Entries Flushes Hits Misses Hit Rate
  util_memoize 300000 299932 2229 5685 2222425 77774 96%
  secret_tokens 32768 4080 102 0 2326 102 95%
  nsfp:product 5120000 1364032 61 0 10550 61 99%
  ns:dnshost 100 0 0 0 0 0 0%
  ns:dnsaddr 100 1 1 0 7 1 87%

Is this MaxSize-Parameter limited by ns_configured StackSize (right now 512 Kb) or is this parameter independent?

What about nsv_buckets? Are those performance relevant?
nsv:7:product 17 3046879 44932 1.47468934605
nsv:6:product 18 1743471 221 0.0126758632636
nsv:5:product 19 33300250 120067 0.360558854663
nsv:4:product 20 455283 15 0.00329465409427
nsv:3:product 21 5668477 29275 0.516452655625
nsv:2:product 22 1415574 1310 0.0925419653088
nsv:1:product 23 763659 63 0.00824975545368
nsv:0:product 24 306966 6 0.00195461386603
ns:cache:util_memoize 81 2337795 4334 0.185388368099

Don't know, if mutex locks still use them...

Thanks for your answers & help and sorry for this exaggerated, not true performance severity statemant (dreaming for O(log(n)) ).

Martin


P.S.: Just one O(n^2) left: Our logger installation:
Only 184 entries, but about 100 seconds to display index page... but that's really a problem of logger itself.