WordPress.org

Ready to get started?Download WordPress

Forums

User Access Manager
[resolved] Repeated queries and scalability (84 posts)

  1. Marcus Fridholm
    Member
    Posted 9 months ago #

    I chose this plugin for an intranet I'm working on. It seemed simple and clear, and in general to be doing what I needed.

    I added the groups I needed, just short of 40, then categories to match them, and parent pages for each. Roughly 40 categories and top-level pages plus another 10 or so subpages.

    Since the groups aren't hierarchical, a lot of pages and categories have more than group assigned to them, on average two.

    I now started adding posts to a couple of the categories, about 70 posts in all.

    While I was working the system got slower and slower, not so much in the admin area as in the front.

    So I installed debug objects to see wtf was going on. And noted that on the same page where I had 18 (eighteen) queries without UAM, i had 10302 (ten thousand threehundred and two queries I sh*t you not!) with the plugin active.

    Total query time: 1 392,1ms for 10302 queries (1,392127990722656s)
    Page generated in 4 000,0ms; (4,6721889972686767578125s); 65,20% PHP; 34,80% MySQL

    In reality that means going from a page served in less than half a second to about four seconds on localhost and eight seconds on my actual host. Activating W3 total cache, the time got down to seven seconds, so that is still faaaaar too slow.

    I sat down and started to analyze the reported queries, to try to get why the sh*t hit the fan that way, and it turns out it is probably an iteration/recursivity problem.

    That is, rather than doing a single query and using the result to filter what is accessible or not, it asks for each and every restrictable object. In some cases it makes the same query three or more times per object almost consecutively.

    Like this:

    Time: 0.2ms (0.00022101402282715s)
    Query: SELECT object_id as id
    FROM wp_uam_accessgroup_to_object
    WHERE group_id = 1
    AND object_type = 'category'
    Function: UamUserGroup->getObjectsFromType()

    Time: 0.2ms (0.00017690658569336s)
    Query: SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = 'category' AND t.term_id = 1 LIMIT 1
    Function: get_term()

    Time: 0.2ms (0.00018811225891113s)
    Query: SELECT object_id as id
    FROM wp_uam_accessgroup_to_object
    WHERE group_id = 1
    AND object_type = 'category'
    Function: UamUserGroup->_getAssignedObjects()

    Time: 0.1ms (0.00014400482177734s)
    Query: SELECT COUNT(*)
    FROM wp_term_relationships AS tr,
    wp_term_taxonomy AS tt
    WHERE tr.object_id = '247'
    AND tt.term_id = '1'
    AND tr.term_taxonomy_id = tt.term_taxonomy_id
    AND tt.taxonomy = 'category'
    Function: UamUserGroup->_isPostInCategory()

    Time: 0.2ms (0.00021100044250488s)
    Query: SELECT object_id as id
    FROM wp_uam_accessgroup_to_object
    WHERE group_id = 1
    AND object_type = 'post'
    Function: UamUserGroup->_getAssignedObjects()

    And so on... for ever and ever and ever.

    So my choices are to accept the performance hit, go through and debug the 4000 lines of code in the plugin, or start over with another plugin.

    Alternative one is probably a deal-breaker, since either the customer or the customers host will flog me if I let this go live in its current condition.

    Alternative two would be doable if I can be fairly sure of success. The cost would be time, which I can pay if it works and not if it doesn't.

    The third alternative means starting over, which is very unappealing but might be what it comes down to in the end.

    What I would like is for the plugin author to test and see if he can recreate the problem, and share what might be the offending method, if there are any workarounds and whether it is a simple fix or means a total rewrite.

    If it is something he can recreate and if it is doable to fix it within a reasonable time-frame, I am available to help.

    http://wordpress.org/extend/plugins/user-access-manager/

  2. Marcus Fridholm
    Member
    Posted 9 months ago #

    Sorry if the above seemed a bit unfriendly. I think part of the annoyance in the post above stemmed from the fact that I like the plugin, which makes it frustrating to have to consider NOT using it.

    If I had had the time to pick it apart and analyze it a bit more thoroughly, I would have suggested solutions rather than whining about it not working as supposed.

    I would like to know if it is only me, or if someone else can recreate the problem.

    Try applying it to a site with a more complex taxonomy and more than just a few groups, pages and posts, then see if it slows down to a crawl.

    I'm testing another plugin, that is blazing fast in the front-end. But it lacks the file protection option, and in the back-end, stuff is not hidden as supposed to. If I could combine the speed in the front-end of that plugin with the competence in the back-end of this, i would probably dance a jig on my table :D

    If I could get a handle on the performance issues, this plugin is still what I would prefer to use.

    I am going on a short vacation starting tomorrow, but I will check in here in a few days. If I can be of any help do tell me.

  3. GM_Alex
    Member
    Plugin Author

    Posted 9 months ago #

    Hey,

    I'm tried a lot of times to figure out the reason why some people have performance issue. I imported big databases and so on. Could you provide me a dump of the database with all the source files, that I can debug it and speed up the plugin?

    Greeting,
    Alex

  4. Marcus Fridholm
    Member
    Posted 8 months ago #

    Sorry but I can't do that, since the customer data is not my property and certainly not public.

    But I did a testcase where I made a clean installation.
    In this I created two groups: test1 and test2,
    two categories test1 and test2,
    one page locked to test1 and one post locked to test2.

    I created a log and wrote to it from two places:
    UamAccessHandler::getUserGroups where I logged $aUserGroupDb['ID']
    and UamUserGroup::_isObjectAssignedToGroup where I logged the array $this->_getAssignedObjects and the variables $iObjectId and $sObjectType

    With this log handy I visited the locked page.
    This is the log, the number after _construct is the ID of the call:

    page_id=2
    
    Callin UamUserGroup::__construct 1
    Callin UamUserGroup::__construct 2
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [2] => 2 )
    id: 0
    type: category
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [2] => 2 )
    id: 2
    type: category
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [2] => 2 )
    id: 2
    type: page
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [3] => 3 )
    id: 0
    type: category
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [3] => 3 )
    id: 3
    type: category
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( )
    id: 2
    type: page
    
    Callin UamUserGroup::__construct 1
    Callin UamUserGroup::__construct 2
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [2] => 2 )
    id: 0
    type: category
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [2] => 2 )
    id: 2
    type: category
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [2] => 2 )
    id: 2
    type: page
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [3] => 3 )
    id: 0
    type: category
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( [3] => 3 )
    id: 3
    type: category
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( )
    id: 2
    type: page
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( )
    id: 2
    type: post
    
    UamUserGroup::_isObjectAssignedToGroup: Array ( )
    id: 2
    type: post

    Now, correct me if I'm wrong, but there is something fishy going on here:

    1. In this case it asks for categories no less than eight times, even though it is a page and there is nowhere on the page where category information is used.
    2. It creates no less than four UserGroup-objects, two per group.
    3. It calls page id 2 four times
    4. I actually seems to loop through the whole shebang twice, before it ends with three calls where the arrays are emtpy. I suspect it is once for the menu and once for the content.

    I made this installation to rule out any interference from my theme or other plugins, no widgets, no menu, no customizations. The only additions being this function and the lines where i call it:

    function mylog($msg) {
        error_log($msg . "\n",3,"/Library/WebServer/Documents/uam/uamdebug.log");
    }

    With this setup, what I would expect is a single call to check what user groups I'm part of, and then one to check which menu-items should be visible and one to check wether I've got access to the page or not (if by any chance the second array couldn't be stored and reused). Two calls, maybe three, not 18.

    When the content is counted in hundreds of items, groups in tens, widgets show categories, menus, etcetera -- then these redundant queries becomes a real and exponential problem. They may only have an overhead of half to a couple of milliseconds, but when they get into the thousands that means a lot.

    That I specifically had problems with this overhead is because the DB-server in question is a separate machine with a separate domain - a common setup - which means the query is waiting for a network response rather than a DB-server on the same machine as the PHP. Moving the installation and DB to my local machine cuts the overhead in half or less. It is basically the same kind of difference front-enders find when they move from mini-graphics to graphic sprites. Even if the sprites are bigger, they still load faster.

    To make things more efficient, I think you could/should store arrays of groups, categories, and items rather than querying each separately. That way you'd cut the overhead at the cost of having a few more items in memory.

    While your coding is pretty neat, the relations between classes and functions are not entirely clear to me yet. I can make reasonable guesses and look for bugs, but I haven't really cracked your strategy in making the plugin. Otherwise I would just have made the changes I want, and this post would be suggestions to include my code :)

  5. GM_Alex
    Member
    Plugin Author

    Posted 8 months ago #

    Hi Marcus,

    I got the problem, I will see what I can do.

    Greeting,
    Alex

  6. Marcus Fridholm
    Member
    Posted 8 months ago #

    I'm sending you a mail, thereby giving you my address.
    If I can be of any help you can mail me.

  7. Marcus Fridholm
    Member
    Posted 8 months ago #

    Any luck finding a way of getting the queries under control?

  8. GM_Alex
    Member
    Plugin Author

    Posted 8 months ago #

    Hi,

    I have an idea to solve that, but I found no time for this until now. Maybe on the weekend. I will inform you if I have solved this.

    Greeting,
    Alex

  9. Marcus Fridholm
    Member
    Posted 8 months ago #

    As I said before, don't be shy to ask for help if you want it or need it.

    I'll be gone during the coming week, but then I'm back and rocking.

  10. GM_Alex
    Member
    Plugin Author

    Posted 8 months ago #

    I will definitely send you an test version of the plugin if I think I have solved the problem.

  11. Marcus Fridholm
    Member
    Posted 8 months ago #

    That would be neat.

  12. Jonas Lundman
    Member
    Posted 8 months ago #

    Im following this thread with interest, th I have exact the same speed problem and frustration as Marcus. We like this plugin interface, neat and understanable for users. And the abililty to hide restriced stuff is better than s2member plugin. (!)

    We also spend a lot of money to implement some changes to the presentations/ templates with this plugin. We would like to still use this if the project scales up.

    Our projects start slowing down with 4 groups 50% of pageload ... Removing some front end css and js by this plugin helped a little. but we understand that something is renundant with the queries. The sql part of my life is poor, and I cant help or contribute.

    Hope I can buy a weekend beer or two to the author, solving this.

  13. Marcus Fridholm
    Member
    Posted 8 months ago #

    I'd happily contribute a beer or two too :D

    The idea in OO is to reuse what you have, not throw stuff on the heap and redoing the same thing again and again.

    Fixing this will make this plugin stellar, not just good... A five star in my book.

  14. Marcus Fridholm
    Member
    Posted 8 months ago #

    So GM_Alex, any news about the idea you had?

  15. Marcus Fridholm
    Member
    Posted 7 months ago #

    Now this is a very ugly hack, and if you need the [L] text in the menus it's not there...
    However, it works and it reduces the queries in the plugin by a factor of ten or so.

    Use at own risk, and remember that it will be overwritten if GM_Alex updates.
    If so you have to either reapply the hack or deactivate my addition to functions.php.

    The hack consists of three parts: creating a function in your functions.php, setting a condition in user-access-manager.php and setting a condition and providing a native wp-alternative in UamUserGroup.class.php.

    The first two parts hang together and must be done simultaneously: that is about half of the reduction, the last part is the other half.

    First the function, with a lot of comments:

    function myShowCustomMenu($aItems) {
        global $wpdb;
        // Get user to filter menu content
        $current_user = wp_get_current_user();
        $user_id = $current_user->ID;
    
        // admin should always see everything
        $adminuser = current_user_can( 'manage_options' ) ? true: false;
    
        // Make ONE global call to get object_id object_type group_id
        $group_to_object = $wpdb->get_results(
            'SELECT * FROM ' . $wpdb->prefix . 'uam_accessgroup_to_object', ARRAY_A
        );
    
        // convert this db object to two arrays,
        // one for user_groups and one for content objects
        $user_groups = array();
        $obj_groups = array();
    
        foreach ($group_to_object as $obj) {
            if ( $obj['object_type'] == 'user' && $obj['object_id'] == $user_id ) {
                // this array is a list of groups the user have access to
                $user_groups[] = $obj['group_id'];
            } elseif ( $obj['object_type'] != 'user' && $obj['object_type'] != 'role' ) {
                // a list of group assignments for object $arr[group][object] = type
                $obj_groups[$obj['group_id']][$obj['object_id']] = $obj['object_type'];
            }
        }
    
        // Add arrays for locked and assigned categories and pages
        $locked_cats = array();
        $cats = array();
        $locked_pages = array();
        $pages = array();
    
        // Filter the loop to get all the locked cats and pages
        foreach ($obj_groups as $oKey => $oValue) {
            foreach ($oValue as $objKey => $type) {
                if( $type == 'category' && ! in_array($objKey, $locked_cats) ) {
                    $locked_cats[] = $objKey;
                }
                elseif( ( $type == 'page' || $type == 'post' ) && ! in_array($objKey, $locked_pages) ) {
                    $locked_pages[] = $objKey;
                }
            }
        }
    
        // Now filter the assigned object list,
        // so that the resulting array is as simple as possible
        foreach ($user_groups as $group) {
            foreach ($obj_groups[$group] as $oKey => $oValue) {
                if( $oValue == 'category' ) {
                    if( ! in_array($oKey, $cats) ) {
                        $cats[] = $oKey;
                    }
                } else {
                    if( ! in_array($oKey, $pages) ) {
                        $pages[] = $oKey;
                    }
                }
            }
        }
    
        $aShowItems = array();
    
        // At this point we have four arrays,
        // two with assigned objects and two with all the locked cats and pages
        // use them to filter out what items to show
        foreach ($aItems as $oItem) {
            if (($oItem->object == 'post' || $oItem->object == 'page') && (in_array($oItem->object_id, $pages) || ! in_array($oItem->object_id, $locked_pages) || $adminuser) ) {
                $aShowItems[] = $oItem;
            } elseif ($oItem->object == 'category' && (in_array($oItem->object_id, $cats) || ! in_array($oItem->object_id, $locked_cats) || $adminuser) ) {
                $aShowItems[] = $oItem;
            } elseif ( $oItem->object != 'post' && $oItem->object != 'page' && $oItem->object != 'category' ) {
                $aShowItems[] = $oItem;
            }
        }
        return $aShowItems;
    }
    
    // I use this for the front-end, keeping the
    // original stuff for the backend - where it doesn't annoy regular users
    if ( ! is_admin() ) {
        add_filter('wp_get_nav_menu_items', 'myShowCustomMenu');
    }

    Then the first condition in user-access-manager.php (root of plugin folder), at the very end among the filters :

    if( is_admin() ) {
        // The below line isn't changed in any way
        add_filter('wp_get_nav_menu_items', array($oUserAccessManager, 'showCustomMenu'));
    }

    So, now we're half-way, let's reduce the queries by as much again.
    This time in UamUserGroup.class.php, I suppose the addition of the wp method would work in the admin too, but I'm lazy and wouldn't check... :)
    Find and replace the function _isPostInCategory with the below:

    protected function _isPostInCategory($iPostId, $iCategoryId)
        {
            /**
             * @var wpdb $wpdb
             */
            global $wpdb;
    
            // Start hack
            if( is_admin() ) {
                $iCount = (int)$wpdb->get_var(
                    "SELECT COUNT(*)
                    FROM ".$wpdb->term_relationships." AS tr,
                        ".$wpdb->term_taxonomy." AS tt
                    WHERE tr.object_id = '".$iPostId."'
                        AND tt.term_id = '".$iCategoryId."'
                        AND tr.term_taxonomy_id = tt.term_taxonomy_id
                        AND tt.taxonomy = 'category'"
                );
    
            } else {
                $iCount = in_category( $iCategoryId, $iPostId );
            }
            // End hack
    
            return ($iCount > 0) ? true : false;
        }

    For me this hack reduced the number of queries from more than 10000 to less than 300, but mileage may vary...

    Please tell me if there is anything I missed in this.

  16. ShayHurley
    Member
    Posted 7 months ago #

    Just to add I have experienced the same performance issues with UAM. With the plugin enabled my frontpage (a standard blog page) was taking 8 seconds to load. With the plugin disabled the page was loading in just under 2 seconds.

    In the end I had to disable UAM permanently, which is a real shame as the interface/functionality was exactly what I needed - performance was just too big an issue to keep it enabled.

    I really hope the author can address the performance issues with this plugin.

    Shay

    PS I haven't tried Marcus' hack but will take a look over the weekend to see if it helps with one of my client sites.

  17. Handoko
    Member
    Posted 7 months ago #

    Any progress for improving the performance?

    I'm looking for a plugin for limiting read/write access of posts. I found 2 candidates: User Access Manager and WordPress Access Areas. Can anyone please test the WordPress Access Area plugin and tell me if it also have any performance issue?

  18. Jonas Lundman
    Member
    Posted 7 months ago #

    Author?

    Sorry for nagging, but we like your interface and wondering about comment for solving the speed issue/ hack from this topic?

  19. GM_Alex
    Member
    Plugin Author

    Posted 7 months ago #

    Hi,

    sorry about not giving feedback but at the moment I have a lot to do at work, if I have some free time I will try to fix it. Hopefully next week.

    Greetings,
    Alex

  20. GM_Alex
    Member
    Plugin Author

    Posted 7 months ago #

    Hi,

    today I found some time. Could all interested people please try the developer version which can be found here: User Access Manager dev version

    NOTE: This is non stable version, but you feedback will help me with this issue. Thanks!

    Greetings,
    Alex

  21. Jonas Lundman
    Member
    Posted 7 months ago #

    Hi, Great! Downloading and gonna setup a test dev install. Are there any notes about the changes?

    And BTW, We just finished (today) a sync fix between MailPress and UAM, and subscriptions to new post or categories that are restricted, will be filtered as well for accepted users only.

    So we are very exited if UAM speeding thing up.

    Thanks for your time o far...

  22. Marcus Fridholm
    Member
    Posted 7 months ago #

    I have a testing environment where I did my hacks, will try it there too see if the issues are solved.

    Getting back to you as soon as I have tried it.

  23. Marcus Fridholm
    Member
    Posted 7 months ago #

    I'll have to try with at pristine install, but so far the problems with this version prevents useful testing.

    What I did was installing the dev-version on my local mirror of the site that caused troubles. Before I did so I dumped the old folder, to make sure there were no conflicting code. I also deleted the hack-function I used before (see earlier post).

    In the DB, there already was 39 groups. And among the pages and categories all these groups were in use. The thing that happened immediately was that all my data about access restrictions in the uam_accessgroups_to_object table was eradicated.

    Unless the most lax group, the one that all registered members get automatically was selected, there was no restrictions whatsoever. That is, no groups but group 1 could be assigned, and if group 1 was assigned, then all groups got assigned to the page/post/category.

    Needless to say. Testing whether the speed issues remains when all your restrictions get squashed isn't exactly meaningful.

    As I said, I'll try with at pristine install to see if there are any conflicts causing this, but for now I can't give any meaningful feedback on the speed of your changes.

  24. GM_Alex
    Member
    Plugin Author

    Posted 7 months ago #

    @Marcus: So if I got you right it dropped the content of your uam_accessgroups_to_object table? Could you provide me a dump of your data (I know I already asked) if you mask that with a simple mysql query?

  25. Marcus Fridholm
    Member
    Posted 7 months ago #

    I could provide you with the UAM table data, that is not any big secret.

    If you want anything else, please be more specific. I suppose i could filter out all the actual content...

    But then you'd need the tables as they were before stuff got dumped, so that you can see the effect.

  26. GM_Alex
    Member
    Plugin Author

    Posted 7 months ago #

    @Marcus: I was talking again about the whole database but with masked data.

  27. Marcus Fridholm
    Member
    Posted 7 months ago #

    I see.

    You want it in your gmail?

  28. GM_Alex
    Member
    Plugin Author

    Posted 7 months ago #

    Yes please.

  29. Marcus Fridholm
    Member
    Posted 7 months ago #

    OK I sent it.

    I masked content and passwords.

  30. progressiveimpact
    Member
    Posted 7 months ago #

    This is timely... I've just implemented a site and incorporated UAM.
    Page rendering is now exceeding 30 secs per page and each is reporting 15,000 queries !!!!!!! It is also very slow within the admin. Caching is no answer for me as the site is too dynamic. I needed to put this out live so I'm very keen to get this fixed even if only with a temporary hack.
    Any help... testing or code debug I'm happy to assist with... otherwise I'll just watch with keen interest.

Reply »

You must log in to post.

About this Plugin

About this Topic