Repeated queries and scalability

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 ??

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.

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

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 ??

Hi Marcus,

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

Greeting,
Alex

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

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

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

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.

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

That would be neat.

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.

I’d happily contribute a beer or two too ??

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.

So GM_Alex, any news about the idea you had?

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.

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.