Hi Kevin,

First, I wanted to thank you for your awesome plugin.  We've been using it for the last couple years and love it.  We couldn't run our site without Role Scoper because we need to assign different permissions to each user based on post categories.

At this point, however, our site is encountering some really bad performance issues, which I think are caused by Role Scoper.  About once every 8-12 hours, our site experience significant slowness.  And sometimes, the site acutally crashes.

When I inspected the issue, I realized that the issue is caused by a SQL query that is taking a very long time to execute.  This SQL query slows our DB down a lot.  And mutliple requests try to execute this same query simultaneously.  I think that's because WP caches the results of the query.  But because the query is taking a long time, multiple requests are trying to generate the same object simultaneously.

Below is output from the MySQL slow query log that shows the problematic query:

# User@Host: root[root] @ ip-10-3-3-29.ec2.internal [10.3.3.29]
# Query_time: 2.517209  Lock_time: 0.000145 Rows_sent: 9781  Rows_examined: 75439
SET timestamp=1313256461;

SELECT DISTINCT wp_1_posts.ID, tt.term_taxonomy_id, tt.term_id, tr.object_id FROM wp_1_posts INNER JOIN wp_1_term_relationships AS tr ON wp_1_posts.ID = tr.object_id  INNER JOIN wp_1_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE 1=1  AND (  ( wp_1_posts.post_status = 'publish' AND (  (  (  ( 1=1 )  )  )  ) ) ) AND tt.taxonomy = 'category' AND tt.term_id IN ('82','4281','4229','93','3','4216','4115','1','8','4196')  AND wp_1_posts.post_type IN ('post'); 

So I then turned on the SAVEQUERIES setting in WP and printed out all DB queries.  I was able to find the DB query that caused the problem.  Here is the output from the WP query log.

[0] => SELECT DISTINCT wp_1_posts.ID, tt.term_taxonomy_id, tt.term_id, tr.object_id FROM wp_1_posts INNER JOIN wp_1_term_relationships AS tr ON wp_1_posts.ID = tr.object_id  INNER JOI\

N wp_1_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE 1=1  AND (   ( wp_1_posts.post_status = 'publish' AND (  (  (  ( 1=1 )  )  )  ) ) ) AND tt.taxonomy = 'category' AND \

tt.term_id IN ('82','4281','4229','93','3','4216','4115','1','8','4196')  AND wp_1_posts.post_type IN ('post')

 [1] => 0.00262784957886

 [2] => require, require_once, include, get_sidebar, locate_template, load_template, require_once, do_shortcode, preg_replace_callback, do_shortcode_tag, call_user_func, shortcode_page_body, do_shortcode, preg_replace_callback, do_shortcode_tag, call_user_func, shortcode_subcategory_tree, get_categories, get_terms, apply_filters, call_user_func_array, ScoperHardwayTaxonomy->flt_get_terms, rs_tally_term_counts, scoper_get_results, scoper_db_method, call_user_func

As you can see, the problem is caused by a call to get_categories.  This call triggers some RoleScoper functions like ScoperHardwayTaxonomy->flt_get_terms, rs_tally_term_counts.

Can you please let me know why get_categories needs to trigger a query that examines 75K+ records and returns 9K+ records?  Is there some way to turn off this behavior via a configuration setting in RoleScoper?

Thanks for your help!

Hi Kevin,

Thanks for the quick reply!  I checked the table and it has the normal WP indexes and its status is OK.

I think that the problem is caused by mutliple requests trying to get this large set of records simultaneously due to a cache miss.  And then these requests are blocked by another query that locks the DB.

Is there any way to temporarily disable the RS calls in the code?  I believe that this problem only happens when a specific short code is executed on our site because this short code calls get_categories.  But all the categories the short code retrieves are always globally viewable.

Thanks again for the help! 

Thanks for the help Kevin!  Does this mean that we should modify the get_categories call?  If so, can you please share that?  

Also, when do you plan on releasing your next version?  And will this change be part of it?

Thanks again!